mariadb 如何找到最长的并发数序列

mm9b1k5b 于 2022-11-29 发布在其他

关注(0)|答案(1)|浏览(104)

我有一个表格，描述了成员及其参加跑步的时间，例如：

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找出每个runner的最长的连续序列，以及最新和最长的序列是什么，假设有许多类似的序列，并假设runno是按日期顺序排列的。
例如，1017在一行中最多运行4次，1079最多运行12次。
应该有办法解决这个问题，但我一直没能找到解决办法。
我在Windows 10上使用的是MariaDB v10.4.22。

mariadb

来源：https://stackoverflow.com/questions/74577649/mariadb-how-to-find-longest-sequence-of-concurrent-numbers

1条答案

按热度按时间

qyyhg6bp1#

这个问题可以通过recursive CTEs解决，如下所示：

WITH RECURSIVE runlength AS
  (SELECT memberId AS id,
          RunNo + 1 AS next,
          1 AS length
   FROM members
   UNION ALL SELECT members.memberId,
                    RunNo+1,
                    length+1
   FROM members
   JOIN runlength ON members.memberId = runlength.id
   AND members.runNo = next)
SELECT id,
       max(length)
FROM runlength
GROUP BY id;

锚将初始runlength内容设置为初始值，每个值的长度为1，并且下一个值是它想要增加序列的值。

SELECT memberId AS id,
          RunNo + 1 AS next,
          1 AS length
   FROM members

递归部分查找序列的下一部分并增加长度：

SELECT members.memberId,
                    RunNo+1,
                    length+1
   FROM members
   JOIN runlength ON members.memberId = runlength.id
   AND members.runNo = next

最后，由于这将有太多的短序列，我们只需要每个用户的最大值：

SELECT id,
       max(length)
FROM runlength
GROUP BY id;

注意：由于生成了大量行，因此这绝不是一个有效的查询，应仅用于小型数据集。
参考：fiddle

赞(0）回复(0）举报 2022-11-29

我来回答

mariadb 如何找到最长的并发数序列

1条答案

相关问题

热门标签

最新问答