MariaDB查询各会员最长连续RunNo序列及最新最长序列方案

现有一张记录会员跑步信息的表，结构及示例数据如下：

memberid(varchar), RunNo(integer)
"1017",1868
"1017",1875
"1017",1877
"1017",1878
"1017",1879
"1017",1880
"1017",1882
"1017",1884
"1017",1885
"1017",1886
"1017",1887
"1017",1889
"1017",1894
"1017",1895
"1017",1896
"1017",1897
"1017",1902
"1017",1903
"1017",1904
"1017",1906
"1017",1907
"1017",1909
"1017",1910
"1017",1911
"1017",1929
"1017",1930
"1017",1931
"1017",1934
"1017",1935
"1079",1840
"1079",1844
"1079",1846
"1079",1847
"1079",1850
"1079",1854
"1079",1857
"1079",1859
"1079",1861
"1079",1863
"1079",1865
"1079",1866
"1079",1869
"1079",1870
"1079",1871
"1079",1872
"1079",1873
"1079",1874
"1079",1875
"1079",1876
"1079",1877
"1079",1878
"1079",1879
"1079",1880
"1079",1882
"1079",1884
"1079",1885
"1079",1886
"1079",1889
"1079",1890
"1079",1891
"1079",1893
"1079",1895
"1079",1897
"1079",1902
"1079",1903
"1079",1904
"1079",1905
"1079",1907
"1079",1908
"1079",1910
"1079",1911
"1079",1923

需针对每个memberid，查询其最长连续RunNo序列的长度；若存在多个长度相同的最长序列，需获取其中最新的序列（RunNo按日期顺序排列）。例如会员1017的最长连续序列长度为4，会员1079为12。当前使用环境为Windows 10系统下的MariaDB v10.4.22。

利用MariaDB 10.4支持的窗口函数，通过以下步骤实现需求：

思路说明 #

连续的RunNo满足“后一个值=前一个值+1”的特征，我们可以通过RunNo减去按会员分组排序后的行号，得到一个固定的分组标识——连续序列内的所有记录该值相同，不同序列则不同。之后聚合计算每个序列的长度，再筛选出每个会员最长且最新的序列。

完整SQL代码 #

WITH run_groups AS (
    SELECT 
        memberid,
        RunNo,
        -- 生成连续序列的分组标识：连续RunNo对应相同的group_id
        RunNo - ROW_NUMBER() OVER (PARTITION BY memberid ORDER BY RunNo) AS group_id
    FROM your_table_name -- 替换为实际表名
),
group_stats AS (
    SELECT 
        memberid,
        group_id,
        COUNT(*) AS sequence_length,
        MAX(RunNo) AS max_run_no -- 记录序列的最大RunNo，用于判断序列新旧
    FROM run_groups
    GROUP BY memberid, group_id
),
ranked_groups AS (
    SELECT 
        memberid,
        sequence_length,
        max_run_no,
        -- 按会员分组排序：先按序列长度降序，长度相同则按最大RunNo降序
        RANK() OVER (PARTITION BY memberid ORDER BY sequence_length DESC, max_run_no DESC) AS rnk
    FROM group_stats
)
SELECT 
    memberid,
    sequence_length AS longest_continuous_sequence_length
FROM ranked_groups
WHERE rnk = 1;

代码分步解释 #

1. run_groups CTE：为每条记录生成分组标识group_id，连续的RunNo会得到相同的group_id，以此区分不同的连续序列。
2. group_stats CTE：按会员和分组标识聚合，计算每个连续序列的长度，同时记录每组的最大RunNo（RunNo按日期顺序排列，最大RunNo越大，序列越新）。
3. ranked_groups CTE：使用RANK()窗口函数对每个会员的序列排序，优先按长度从长到短，长度相同时按序列的最大RunNo从大到小，排名第一的就是目标序列。
4. 最后筛选出排名为1的记录，得到每个会员的最长连续序列长度。

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