Início / coding / Perguntas / 79561633
Accepted
kagmole
Asked: 2025-04-08 17:16:39 +0800 CST

Detecte a 1ª linha de uma cadeia ordinal usando CTE recursivo ou alternativo

  • 772

Tenho os seguintes dados:

ID do usuário SubID EduId SemOrd
1 701 500 10
1 702 255 11
1 703 500 12
1 704 500 14
1 705 500 15
1 706 500 16
1 707 500 17
2 ... ... ..

Dado um UserIde SubId, quero encontrar o menor SemOrdna mesma EduIdcadeia.
Por exemplo: dado UserId = 1e SubId = 706, o resultado deve ser SemOrd = 12.
Como isso implica uma cadeia, optei pela solução CTE recursiva.

;WITH Base AS (
    SELECT
        UserId,
        SubId,
        EduId,
        SemOrd,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
        ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted
    FROM /* ... */
), Chain AS (
    SELECT
        SemOrd AS SemOrd_First,
        UserId,
        SubId,
        EduId,
        SemOrd,
        Ordinal,
        OrdinalParted
    FROM Base
    UNION ALL
    SELECT
        Chain.SemOrd_First,
        Base.UserId,
        Base.SubId,
        Base.EduId,
        Base.SemOrd,
        Base.Ordinal,
        Base.OrdinalParted
    FROM
        Base
        INNER JOIN Chain
            ON Base.UserId = Chain.UserId
            AND Base.EduId = Chain.EduId
            AND Base.Ordinal = Chain.Ordinal + 1
            AND Base.OrdinalParted = Chain.OrdinalParted + 1
), Minimal AS (
    SELECT
        UserId,
        SubId,
        MIN(SemOrd_First) AS SemOrd_First
    FROM Chain
    GROUP BY
        UserId,
        SubId
)
SELECT SemOrd_First FROM Minimal WHERE UserId = 1 AND SubId = 706

No papel e em tabelas pequenas, funciona.
Mas nas tabelas de produção, é basicamente inutilizável, pois precisa calcular primeiro todas as entradas CTE antes de fazer o WHEREfiltro. A consulta é muito lenta.

Claro que uma solução seria mover a WHEREcláusula para dentro do 1º CTE:

;WITH Base AS (
    SELECT
        UserId,
        SubId,
        EduId,
        SemOrd,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
        ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted
    FROM /* ... */
    WHERE UserId = 1 AND SubId = 706
), Chain AS (
/* ... */

E então é rápido. Mas eu gostaria de criar uma visualização a partir desta consulta.
Por isso, a WHEREcláusula não pode estar dentro do 1º CTE, caso contrário, não podemos escolher o UserIdand SubIdao chamar a visualização.

O problema é que o SQL Server não consegue incluir a WHEREcláusula dentro do 1º CTE e reduzir a quantidade de trabalho no plano de execução.

Então as perguntas são:

  • Existe uma maneira de fazer o SQL Server exibir a WHEREcláusula?
  • Existe uma alternativa para a solução recursiva CTE?

Se nada disso funcionar, acho que minha melhor aposta seria um procedimento armazenado.

sql

2 respostas

  1. Best Answer

    Você está pensando demais nisso. Este é um tipo de problema de lacunas e ilhas, e existem muitas soluções mais simples.

    Você pode fazer isso em uma única passagem pela tabela base, usando LAGpara obter o valor anterior (decrescente), e uma condicional em janela COUNTcomparando cada linha com a anterior (para verificar se é uma cadeia) nos dará um ID exclusivo para a cadeia.

    Então, basta agrupá-los e pegar o mínimo SemOrd, usando apenas a cadeia que contém o valor que queremos.

    WITH NextValues AS (
    SELECT t.*,
      LAG(t.EduId) OVER (PARTITION BY t.UserId ORDER BY t.SemOrd DESC) AS PrevEduId
    FROM YourTable t
    WHERE t.UserId = 1
),
Counted AS (
    SELECT *,
      COUNT(CASE WHEN t.EduId <> t.PrevEduId OR t.PrevEduId IS NULL THEN 1 END)
          OVER (PARTITION BY t.UserId ORDER BY t.SemOrd DESC ROWS UNBOUNDED PRECEDING) AS ChainId
    FROM NextValues t
)
SELECT
  t.UserId,
  MIN(t.SemOrd) AS SemOrd_First
FROM Counted t
GROUP BY
  t.UserId,
  t.ChainId
HAVING COUNT(CASE WHEN t.SubId = 706 THEN 1 END) > 0;

    db<>violino

    • 1

  2. Seu resultado deve ser SemOrd=10 para UserId = 1 e SubId = 706?

    Mas você pode tentar isto:

    SELECT UserId,EduId,min(SemOrd)
FROM yourTable
where EduId=(SELECT EduId FROM yourTable WHERE UserId=1 AND SubId=701)
group by UserId,EduId
    • 0

relate perguntas