使用 SQL 查找连续日期组中的最长递增序列-解网

问：

使用 MARIA-DB 10.6

我有一个包含列和每行的表。日期将始终是唯一的（任何日期只有 1 行，如果存在）。随着日期的滚动，已解决的计数可以相同或更高。userdatessolved

下面是一个示例表：user

日期	解决
2010-11-26	2
2010-11-27	4
2010-11-28	6
2010-11-29	10
2010-12-05	11
2010-12-06	11
2010-12-07	11
2010-12-08	12
2010-12-09	12
2010-12-10	12

我需要从中找到的是：最长的连续日期序列，其中已解决的计数正在增加。

现在我处理它的方式是，这有两个部分。第一部分是找到连续天数的组。在这篇文章之后，我通过这个查询实现了这一点：

WITH consecutiveDateGroups AS (
  SELECT
   ROW_NUMBER() OVER (ORDER BY date) AS rowNum,
   DATE_ADD(date, INTERVAL (-ROW_NUMBER() OVER (ORDER BY date)) DAY) AS consecutiveDateGroup,
   date,
   solved
  FROM user
)

SELECT *
FROM consecutiveDateGroups
ORDER BY rowNum

就我而言，我们有 3 组连续日期：

[第 1 组] -> 2010-11-26 至 2010-11-29 （4 天）

[第 2 组] -> 2010-12-05 至 2010-12-10 （6 天）

我可以使用此查询按每个组中的连续天数进行分组：

WITH 
            
consecutiveDateGroups AS (
  SELECT
   ROW_NUMBER() OVER (ORDER BY date) AS rowNum,
   DATE_ADD(date, INTERVAL (-ROW_NUMBER() OVER (ORDER BY date)) DAY) AS consecutiveDateGroup,
   date
  FROM user
)
            
SELECT
  COUNT(*) AS consecutiveDates,
  MIN(date) AS minDate,
  MAX(date) AS maxDate
FROM consecutiveDateGroups
GROUP BY consecutiveDateGroup

到目前为止，我得到的结果是：

consecutiveDates	最小日期	最大日期
4	2010-11-26	2010-11-29
6	2010-12-05	2010-12-10

到目前为止，我一切都很好。但在此之后，事情对我来说变得泥泞，我被困住了。如何返回每个连续日期组的已解决列数？

例如，在本例中：

[第 1 组] 的序列递增为 4 [2,4,6,10]

[第2组]的序列递增为2个[11,12]

所以我想要的结果是：

consecutiveDates	最小日期	最大日期	增加已解决序列
4	2010-11-26	2010-11-29	4
6	2010-12-05	2010-12-10	2

任何帮助将不胜感激！

小提琴：https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=c80ffb091bb272b0c3b2ae8abbbbbaf1

MySQL SQL 日期 MariaDB 序列

set @cn = 1;
WITH consecutiveDateGroups AS 
(
  SELECT
   ROW_NUMBER() OVER (ORDER BY date) AS rowNum,
   DATE_ADD(date, INTERVAL (-ROW_NUMBER() OVER (ORDER BY date)) DAY) AS consecutiveDateGroup,
   date,
   solved
  FROM user
)
, 
cte AS
(
 SELECT *,
 COALESCE(LEAD(solved) OVER (PARTITION BY consecutiveDateGroup ORDER BY date), solved) AS LD
 FROM consecutiveDateGroups
 ORDER BY rowNum
),
cte2 as 
(
 SELECT *,
 CASE WHEN solved <> ld THEN @cn:=@cn+1 ELSE @cn:=1 END AS ch
 FROM cte
)
SELECT
  COUNT(*) AS consecutiveDates,
  MIN(date) AS minDate,
  MAX(date) AS maxDate,
  MAX(ch) AS increasingSolvedSequence  
FROM cte2
GROUP BY consecutiveDateGroup

观看 db<>fiddle 的演示。

感谢您抽出宝贵时间回答。遗憾的是，DISTINCT（solved）不会涵盖日期组具有许多不同已解决计数的情况，但它们不一定在增加。就像在这种情况下：[2,4,6,10] ->最长的 inc. seq 在这里是 4 而 [11,11,12,12,13,13,14,14,15,15,16,16] 最长的 inc. seq 在这里是 2。小提琴来证明这一点：dbfiddle.uk/......

1赞 ahmed 8/9/2022

你能解释一下什么是最长的递增序列吗，我以为是已解的不同值。

0赞 Squidonis 8/9/2022

递增序列是一系列数字，其中每个数字都高于前一个数字。像 [1,2,3,4] 是一系列递增的数字，因为 2>1、3>2、4>3。然而，[1,2,2,3] 不是一个真正的递增序列，因为中间有 2,2。2 不大于 2，因此中间重复 2 会破坏序列。希望这是有道理的。

1赞 ahmed 8/9/2022

答案已更新，请检查一下，让我知道是否可以。

1赞 Squidonis 8/10/2022

非常感谢艾哈迈德！这非常有效。经过多天的挣扎，你帮助我摆脱了困境。说真的，谢谢!!!!!!!!

上一个：如何为组中的每一行分配顺序值

下一个：我正在创建序列，但出现错误如何在没有现有表的情况下创建序列

使用 SQL 查找连续日期组中的最长递增序列

Find longest increasing sequence in groups of consecutive dates using SQL

评论

评论