主页 / dba / 问题 / 334012
Accepted
seevans38
Asked: 2023-12-13 16:26:47 +0800 CST

返回特定列中值的最长连续序列

  • 772

我正在使用 PostgreSQL 12.0 并尝试获取特定列和值的最长连续行序列。

该表称为 team2，包含团队的结果，如下所示：

match_id (pk), team_name (varchar), opposition (varchar), match_result (varchar)

我的查询试图找到 match_result 中最长的“Win”序列。总共有 23 场胜利，用肉眼观察，最长的序列应该返回 5。然而，返回的是 23。如何更改查询以仅选择最长的序列？

WITH ConsecutiveSequences AS (
  SELECT
    match_result,
    ROW_NUMBER() OVER () - ROW_NUMBER() OVER (ORDER BY match_id) AS grp
  FROM team2
  WHERE match_result = 'Win'
),
GroupedSequences AS (
  SELECT
    match_result,
    COUNT(*) AS consecutive_count
  FROM ConsecutiveSequences
  GROUP BY match_result, grp
)
SELECT
  COALESCE(MAX(consecutive_count), 0) AS longest_consecutive_sequence
FROM GroupedSequences;
cte

1 个回答

  1. Best Answer

    “连续行的最长序列”意味着一个顺序，在本例中为 match_id。

    让我们举一个通用的例子：

    CREATE UNLOGGED TABLE matches (
    match_id INTEGER PRIMARY KEY,
    crit     INTEGER NOT NULL,
    result   INTEGER NOT NULL
);
INSERT INTO matches SELECT n,random()*100,random()*0.8 FROM generate_series(1,1000) n;
    • “结果”可能是 0 或 1，表示胜利或失败。
    • “暴击”是一个通用标准，可以是正在考虑的团队（或其他任何标准，例如）。

    因此，让我们按 match_id 顺序获取最长的连续行序列的长度，并具有相同的结果和 crit。检测相同值的固定序列意味着将每一行与前一行进行比较，这意味着需要窗口函数 LAG()。

    SELECT  crit, match_id, result, 
        lag(crit,1)   OVER (PARTITION BY crit ORDER BY match_id) as last_result,
        row_number() OVER (PARTITION BY crit ORDER BY match_id) as rownum
FROM matches;
 crit | match_id | result | last_result | rownum
------+----------+--------+-------------+--------
    0 |       43 |      0 |        Null |      1
    0 |       71 |      0 |           0 |      2
    0 |      393 |      0 |           0 |      3
    0 |      410 |      0 |           0 |      4
    0 |      597 |      0 |           0 |      5
    0 |      696 |      1 |           0 |      6
    0 |      728 |      0 |           0 |      7
    0 |      783 |      0 |           0 |      8
    0 |      824 |      1 |           0 |      9
    1 |      512 |      1 |        Null |      1
    1 |      552 |      1 |           1 |      2
    1 |      610 |      1 |           1 |      3
    1 |      633 |      0 |           1 |      4
    1 |      823 |      1 |           1 |      5
    1 |      892 |      0 |           1 |      6
    1 |      980 |      1 |           1 |      7
    2 |        3 |      1 |        Null |      1
 ...

    我们不能像您在问题中那样在 WHERE 中使用 match_result，因为这会消除具有错误值的行...并且我们需要保留这些行，因为它们充当相同结果值运行之间的分隔符。在您的查询中，您只保留胜利，因此没有失败的比赛来设置胜利序列的界限。只剩下一个获胜序列。因此，正如您所注意到的，您会得到表中获胜的计数，这不是您想要的。

    因此，我们需要通过比较当前行和前一行来定位具有相同 Crit 和结果的行序列的开头和结尾。要记住的一件重要事情是 LAG() 对于分区中的第一行返回 NULL，因此第一行需要有一个特殊情况。

    SELECT  crit, match_id, result, 
        (   (row_number() OVER (PARTITION BY crit ORDER BY match_id)) = 1
         OR result != lag(result,1) OVER (PARTITION BY crit ORDER BY match_id)
        ) AS start_run
FROM matches;
 crit | match_id | result | start_run
------+----------+--------+---------
    0 |       43 |      0 | t
    0 |       71 |      0 | f
    0 |      393 |      0 | f
    0 |      410 |      0 | f
    0 |      597 |      0 | f
    0 |      696 |      1 | t
    0 |      728 |      0 | t
    0 |      783 |      0 | f
    0 |      824 |      1 | t
    1 |      512 |      1 | t
    1 |      552 |      1 | f
    1 |      610 |      1 | f
    1 |      633 |      0 | t
...

    所以现在我们在新序列的开头有 new_run=true 。为了计算序列中的行数，我们要么需要给每个序列一个 id 并使用 count() 和 group by，要么获取序列的第一行和最后一行的 row_number() 并减去它们。

    要检测序列的最后一行，我们需要使用 Lead() 而不是 lag() 将当前行与下一行进行比较。然而，如果这是分区中的最后一行，lead() 将为 NULL。在前一种情况下，我们检测到分区中 row_number()=1 的第一行，但对于最后一行来说这是不可能的，因为它的 row_number() 未知。因此，我们将在不可为空的列（例如 match_id）上使用 Lead()。如果“结果”不可为空，也可以使用它。

    SELECT  crit, match_id, result, 
    CASE WHEN 
        (   (lag(match_id,1) OVER (PARTITION BY crit ORDER BY match_id)) IS NULL
         OR result != lag(result,1) OVER (PARTITION BY crit ORDER BY match_id))
        THEN row_number() OVER (PARTITION BY crit ORDER BY match_id) ELSE NULL END
        AS start_run,
    CASE WHEN (   (lead(match_id,1) OVER (PARTITION BY crit ORDER BY match_id)) IS NULL
         OR result != lead(result,1) OVER (PARTITION BY crit ORDER BY match_id))
        THEN row_number() OVER (PARTITION BY crit ORDER BY match_id) ELSE NULL END
        AS end_run
FROM matches;
 crit | match_id | result | start_run | end_run
------+----------+--------+-----------+---------
    0 |       43 |      0 |         1 |    Null
    0 |       71 |      0 |      Null |    Null
    0 |      393 |      0 |      Null |    Null
    0 |      410 |      0 |      Null |    Null
    0 |      597 |      0 |      Null |       5
    0 |      696 |      1 |         6 |       6
    0 |      728 |      0 |         7 |    Null
    0 |      783 |      0 |      Null |       8
    0 |      824 |      1 |         9 |       9
    1 |      512 |      1 |         1 |    Null
    1 |      552 |      1 |      Null |    Null
    1 |      610 |      1 |      Null |       3
    1 |      633 |      0 |         4 |       4
    1 |      823 |      1 |         5 |       5
    1 |      892 |      0 |         6 |       6
    1 |      980 |      1 |         7 |       7
    2 |        3 |      1 |         1 |    Null
    2 |      127 |      1 |      Null |    Null
    2 |      231 |      1 |      Null |       3

    检查结果正确后（尤其是在边界上），我们可以删除 start_run 和 end_run 设置为 NULL 的行，并仅保留指示运行开始和结束的行，然后获取其长度。具有 COALESCE 的位是由于如果长度为 1 或更大，则在同一行或上一行中具有 tart_run 的运行。

    SELECT * FROM (
    SELECT crit, result,
        CASE 
            WHEN end_run IS NOT NULL 
            THEN 1+end_run-COALESCE( start_run, LAG(start_run,1) OVER (PARTITION BY crit ORDER BY match_id))
            ELSE NULL END
            AS run_length
        FROM (
        SELECT  crit, match_id, result, 
            CASE WHEN 
                (   (lag(match_id,1) OVER (PARTITION BY crit ORDER BY match_id)) IS NULL
                 OR result != lag(result,1) OVER (PARTITION BY crit ORDER BY match_id))
                THEN row_number() OVER (PARTITION BY crit ORDER BY match_id) ELSE NULL END
                AS start_run,
            CASE WHEN (   (lead(match_id,1) OVER (PARTITION BY crit ORDER BY match_id)) IS NULL
                 OR result != lead(result,1) OVER (PARTITION BY crit ORDER BY match_id))
                THEN row_number() OVER (PARTITION BY crit ORDER BY match_id) ELSE NULL END
                AS end_run
        FROM matches) a
        WHERE start_run IS NOT NULL or end_run IS NOT NULL
    ) b WHERE run_length IS NOT NULL;

 crit | result | run_length
------+--------+------------
    0 |      0 |          5
    0 |      1 |          1
    0 |      0 |          2
    0 |      1 |          1
    1 |      1 |          3
    1 |      0 |          1
    1 |      1 |          1
    1 |      0 |          1

    要获得最长的运行时间，您可以将上一个查询的第一行和最后一行替换为

    SELECT crit, result, max(run_length) FROM (
...rest of the query...
) b WHERE run_length IS NOT NULL GROUP BY crit, result;
    • 0

相关问题