在MariaDB 10.6中用SQL查询连续日期组内最长递增solved序列

假设user表结构如下：

CREATE TABLE user (
  date DATE PRIMARY KEY,
  solved INT
);

要实现需求，我们可以通过嵌套窗口函数，先完成连续日期分组，再在每个分组内识别solved的递增序列，最后统计最长序列长度。完整SQL如下：

WITH date_groups AS (
  -- 划分连续日期组：连续日期的group_id值一致
  SELECT
    date,
    solved,
    DATE_SUB(date, INTERVAL ROW_NUMBER() OVER (ORDER BY date) DAY) AS group_id
  FROM user
),
solved_sequences AS (
  -- 在每个日期组内标记递增序列：solved不大于前一行时开启新序列
  SELECT
    group_id,
    date,
    solved,
    SUM(CASE WHEN solved > LAG(solved, 1, -1) OVER (PARTITION BY group_id ORDER BY date) THEN 0 ELSE 1 END) OVER (PARTITION BY group_id ORDER BY date) AS seq_id
  FROM date_groups
),
sequence_lengths AS (
  -- 统计每个日期组内各递增序列的长度
  SELECT
    group_id,
    seq_id,
    COUNT(*) AS seq_len
  FROM solved_sequences
  GROUP BY group_id, seq_id
),
max_seq_per_group AS (
  -- 获取每个日期组的最长递增序列长度
  SELECT
    group_id,
    MAX(seq_len) AS longest_increasing_seq
  FROM sequence_lengths
  GROUP BY group_id
)
-- 整合最终结果：连续天数、起止日期、最长递增序列长度
SELECT
  COUNT(*) AS consecutive_days,
  MIN(d.date) AS start_date,
  MAX(d.date) AS end_date,
  m.longest_increasing_seq
FROM date_groups d
JOIN max_seq_per_group m ON d.group_id = m.group_id
GROUP BY d.group_id, m.longest_increasing_seq
ORDER BY start_date;

关键步骤说明 #

• date_groups：利用日期与行号的差值生成连续日期分组标识，确保连续日期归为同一组。
• solved_sequences：通过LAG()函数对比当前行与前一行的solved值，用累加标记区分不同的递增序列。
• sequence_lengths：统计每个递增序列的包含的行数（即长度）。
• max_seq_per_group：提取每个日期组内最长的递增序列长度。
• 最终查询：聚合每个日期组的基础信息，关联最长序列长度后输出结果。

内容的提问来源于stack exchange，提问作者Squidonis