Início / dba / Perguntas / 334012
Accepted
seevans38
Asked: 2023-12-13 16:26:47 +0800 CST

Retornar a sequência consecutiva mais longa de um valor em uma coluna específica

  • 772

Estou usando o PostgreSQL 12.0 e tentando obter a sequência mais longa de linhas contínuas para uma coluna e valor específicos.

A tabela é chamada team2 e contém os resultados de uma equipe que se parece com:

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

Minha consulta está tentando encontrar a sequência mais longa de 'Win' em match_result. Há 23 vitórias no total e usando o olho a sequência mais longa deve retornar 5. No entanto, 23 são retornados. Como posso alterar minha consulta para selecionar apenas a sequência mais longa?

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 respostas

  1. Best Answer

    "Sequência mais longa de linhas contínuas" implica uma ordem, que neste caso é match_id.

    Vamos fazer um exemplo genérico:

    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;
    • "resultado" pode ser 0 ou 1 para vitória ou derrota.
    • "crit" é um critério genérico que pode ser a equipe que está sendo considerada (ou qualquer outra coisa, por exemplo).

    Então, vamos obter o comprimento da sequência mais longa de linhas contíguas, na ordem match_id, tendo o mesmo resultado e crítico. Detectar uma sequência sólida de valores idênticos significa comparar cada linha com a anterior, o que significa que a função de janela LAG() é necessária.

    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
 ...

    Não podemos usar match_result em WHERE como você fez na sua pergunta, porque isso eliminaria as linhas com valores errados... e precisamos manter essas linhas, pois elas atuam como um delimitador entre execuções de valores de resultados idênticos. Na sua consulta, você mantém apenas as vitórias, portanto não há partidas perdidas para definir os limites em torno de uma sequência de vitórias. Resta apenas uma única sequência de vitórias. Assim você obtém a contagem de vitórias na tabela, como percebeu, o que não é o que você deseja.

    Portanto, precisamos localizar o início e o fim de uma sequência de linhas com o mesmo crítico e resultado, comparando as linhas atuais e anteriores. Uma coisa importante a lembrar é que LAG() retorna NULL para a primeira linha da partição, portanto é necessário que haja um caso especial para a primeira linha.

    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
...

    Então agora temos new_run=true no início de uma nova sequência. Para contar as linhas em uma sequência, precisamos dar a cada sequência um id e usar count() e agrupar por, ou obter row_number() para a primeira e última linha da sequência e subtraí-las.

    Para detectar a última linha de uma sequência, precisamos comparar a linha atual com a próxima, usando lead() em vez de lag(). No entanto, lead() será NULL se esta for a última linha da partição. No caso anterior detectamos a primeira linha na partição com row_number()=1 mas isso não é possível para a última linha porque seu row_number() não é conhecido. Portanto, usaremos lead() em uma coluna não anulável como match_id. "resultado" também pode não ser usado se não for anulável.

    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

    Depois de verificar se os resultados estão corretos (especialmente nos limites), podemos remover as linhas com start_run e end_run definidos como NULL e manter apenas as linhas que indicam o início e o fim de uma execução, e então obter seu comprimento. O bit com COALESCE é devido a execuções com thatt_run na mesma linha ou na linha anterior se o comprimento for 1 ou maior.

    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

    Para obter a execução mais longa, você pode substituir a primeira e a última linhas da consulta anterior por

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

relate perguntas