MariaDB 如何找到最长的并发数序列-解网

问：

我有一个描述成员以及他们何时运行的表格，例如：

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 找到每个运行器的最长连续运行编号序列，以及假设有许多相似的序列并假设运行编号按日期顺序排列的最新和最长序列是什么。

例如，1017 最多连续运行 4 次，而 1079 最多连续运行 12 次。

应该有办法解决这个问题，但我一直无法找到解决方案。

我在 Windows 10.4.22 上使用 MariaDB v10。

并发 MariaDB 数字序列

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;

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

  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;

注意：由于生成的行数较多，这绝不是一个有效的查询，只能用于小型数据集。

编号：小提琴

上一个：序列项目以及如何打印未从 Tableau Modele 中选择的内容

下一个：字符串中有多少个数字是连续的

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

mariadb how to find longest sequence of concurrent numbers

评论