Início / dba / Perguntas / 307440
Accepted
Healyhatman
Asked: 2022-02-14 16:26:25 +0800 CST

Obter linhas agrupadas por uma chave estrangeira com contagem de valores consecutivos

  • 772

Eu tenho um banco de dados SQL Server com uma transactionstabela com client_id, datee is_cancelled.

Estou tentando obter os client_ids que têm 3 ou mais transações em uma linha marcadas como is_cancelled, junto com a contagem in_a_row. Cheguei ao seguinte, o que me dá um 1 para is_same quando os suportes is_cancelled são consecutivos e um total de transações canceladas (que não é bem o que eu preciso)

SELECT 
client_id,
date,
is_same,
SUM(is_same) OVER (PARTITION BY client_id ORDER BY date) AS sum_same,
transCancelled
FROM
(
    SELECT
    client_id,
    LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
    CASE 
        WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
        THEN 1
        ELSE 0
    END as is_same,
    date,
    is_cancelled
    FROM transactions
    WHERE deleted_at IS NULL -- Ignore soft-deleted rows
) AS t_01
WHERE previous_cancelled = 1
ORDER BY date

Mexa com dados de amostra: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a0c9b12203ab2d0c83f73604ccc9d0a0

Dados esperados (client_id, contagem) 1, 3 3, 6

sql-server window-functions

1 respostas

  1. Best Answer

    Este é um tipo de problema de lacunas e ilhas .

    A chave para a maioria das soluções desse tipo é contar as alterações , então você quer uma is_differentcoluna, não is_same. Em seguida, você conta condicionalmente essa coluna (mais fácil se você usar NULLem vez de 0) para criar um ID para cada grupo de linhas.

    Não está claro exatamente quais resultados finais você deseja, mas agrupando esse resultado, você pode obter o número máximo e mínimo de linhas consecutivas, bem como a contagem de grupos de linhas reais, por client_id:

    WITH PrevValues AS (
    SELECT
      client_id,
      LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
      CASE WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
        THEN NULL ELSE 1 END
        as is_different,
      date,
      is_cancelled
    FROM transactions
),
Grouped AS (
    SELECT 
      client_id,
      date,
      is_different,
      COUNT(is_different) OVER (PARTITION BY client_id ORDER BY date ROWS UNBOUNDED PRECEDING) AS group_id
    FROM PrevValues
),
ByGroups AS (
    SELECT
      client_id,
      COUNT(*) as in_a_row
    FROM Grouped
    GROUP BY
      client_id,
      group_id
    HAVING COUNT(*) >= 3
)
SELECT
  client_id,
  MAX(in_a_row) as max_in_a_row,
  MIN(in_a_row) as min_in_a_row,
  COUNT(*) as num_groups
FROM ByGroups
GROUP BY
  client_id;

    db<>violino

    Observe que seus dados de amostra têm linhas com datas idênticas e é um motivo pelo qual você deve sempre usar ROWS UNBOUNDED PRECEDING(o padrão para funções de janela ordenadas é RANGE UNBOUNDED PRECEDINGque é sutilmente diferente). De qualquer forma, você deve sempre tentar ter uma ordenação determinística.

    • 4

relate perguntas