Início / dba / Perguntas / 129358
Accepted
obautista
Asked: 2016-02-16 17:20:17 +0800 CST

Como usar CTE para comparar valores

  • 772

Preciso retornar um 0 ou 1 dependente dos registros anteriores. Tabela de exemplo:

DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);
INSERT @x VALUES
(101, 0, '20151201', 1),    
(101, 1, '20151205', 2),    
(101, 0, '20151206', 3),
(101, 1, '20151208', 2), 
(102, 1, '20151202', 1),
(102, 0, '20151204', 2),    
(102, 0, '20151205', 3),
(103, 0, '20160101', 1),
(103, 1, '20160102', 2),
(103, 0, '20160103', 2),
(104, 0, '20160101', 1),
(104, 0, '20160102', 2),
(104, 0, '20160103', 3);

A única coisa que importa no último registro é o LevelCode (ou seja, o último registro de cada ProductID). Não importa se o último registro foi aprovado/reprovado. Em seguida, examinamos todos os outros registros para esse ProductID (portanto, todos os registros antes do último registro) e, se houver uma falha com o mesmo LevelCode do último registro, definimos IsLastRunSameLevelAsPreviousRun como 1 senão 0:

 ProductID     IsLastRunSameLevelAsPreviousRun
 101           1
 102           0
 103           1
 104           0

Se não houver falhas para a , ProductIDo IsLastRunSameLevelAsPreviousRundeve retornar 0.

Qualquer ajuda ou dicas são muito apreciadas.

sql-server sql-server-2014

3 respostas

  1. Best Answer

    Usando o LAG , você pode atingir seu objetivo sem junções:

    WITH cte AS
  (
    SELECT
      ProductID,
      SampleDate,
      MaxSampleDate = MAX(SampleDate) OVER (PARTITION BY ProductID),
      PrevFailed    = LAG(Failed, 1, 0) OVER (PARTITION BY ProductID, LevelCode
                                              ORDER BY SampleDate)
    FROM
      @x
  )
SELECT
  ProductID,
  IsLastRunSameLevelAsPreviousRun = PrevFailed
FROM
  cte
WHERE
  SampleDate = MaxSampleDate
;

    O cteobtém o Failedestado anterior para o mesmo LevelCodepara cada linha dentro de cada ProductIDgrupo, retornando 0 quando não há nenhuma linha correspondente. Ele também calcula a última data em cada grupo para usá-la posteriormente para determinar a última linha do grupo.

    Isto é o que ele retorna:

    ProductID  SampleDate  MaxSampleDate  PrevFailed
---------  ----------  -------------  ----------
101        2015-12-01  2015-12-08     0
101        2015-12-05  2015-12-08     0
101        2015-12-08  2015-12-08     1
101        2015-12-06  2015-12-08     0
102        2015-12-02  2015-12-05     0
102        2015-12-04  2015-12-05     0
102        2015-12-05  2015-12-05     0
103        2016-01-01  2016-01-03     0
103        2016-01-02  2016-01-03     0
103        2016-01-03  2016-01-03     1
104        2016-01-01  2016-01-03     0
104        2016-01-02  2016-01-03     0
104        2016-01-03  2016-01-03     0

    O SELECT principal basicamente pega a última linha de cada grupo usando o SampleDate = MaxSampleDatefiltro, puxando apenas ProductIDe PrevFailedtambém renomeando o último para IsLastRunSameLevelAsPreviousRun, para que a saída final se torne o que você deseja:

    ProductID  IsLastRunSameLevelAsPreviousRun
---------  -------------------------------
101        1
102        0
103        1
104        0
    • 6

  2. Uma maneira de fazer isso é usar ROW_NUMBERduas vezes.

    Primeiro, numere todas as linhas da tabela particionadas pelo ProductID( CTE_RowNumbersAll) e obtenha apenas a última linha para cada ProductID( CTE_LastAll).

    Em seguida, numere todas as linhas com falha ( CTE_RowNumbersFailed) e obtenha a última linha com falha para cada ProductID( CTE_LastFailed).

    Finalmente LEFT JOINresultados intermediários juntos e usados CASE​​para comparar os LevelCodevalores.

    Atualizar

    Com base no seu último comentário, alterei a consulta. CTE_RowNumbersFailedagora é baseado CTE_RowNumbersAlle há um filtro extra rnAll > 1para remover a última linha de consideração.

    WITH
CTE_RowNumbersAll
AS
(
    SELECT
        ProductID
        ,Failed
        ,SampleDate
        ,LevelCode
        ,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC) AS rnAll
    FROM @x
)
,CTE_LastAll
AS
(
    SELECT
        ProductID
        ,LevelCode
    FROM CTE_RowNumbersAll
    WHERE rnAll = 1
)
,CTE_RowNumbersFailed
AS
(
    SELECT
        ProductID
        ,Failed
        ,SampleDate
        ,LevelCode
        ,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC) AS rnFailed
    FROM CTE_RowNumbersAll
    WHERE
        Failed = 1
        AND rnAll > 1
)
,CTE_LastFailed
AS
(
    SELECT
        ProductID
        ,LevelCode
    FROM CTE_RowNumbersFailed
    WHERE rnFailed = 1
)
SELECT
    CTE_LastAll.ProductID
    ,CASE WHEN CTE_LastAll.LevelCode = CTE_LastFailed.LevelCode 
        THEN 1 ELSE 0 END AS IsLastRunSameLevelAsPreviousRun
FROM
    CTE_LastAll
    LEFT JOIN CTE_LastFailed ON CTE_LastFailed.ProductID = CTE_LastAll.ProductID
ORDER BY CTE_LastAll.ProductID;

    Resultado

    +-----------+---------------------------------+
| ProductID | IsLastRunSameLevelAsPreviousRun |
+-----------+---------------------------------+
|       101 |                               1 |
|       102 |                               0 |
|       103 |                               1 |
|       104 |                               0 |
+-----------+---------------------------------+
    • 5

  3. Espero que isto ajude

    DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);

INSERT @x 
select * 
from
(VALUES (101, 0, '20151201', 1),
(101, 1, '20151205', 2),
(101, 0, '20151206', 3), (101, 1, '20151208', 2), (102, 1, '20151202', 1), (102, 0, '20151204', 2),
(102, 0, '20151205', 3), (103, 0, '20160101', 1), (103, 1, '20160102', 2), (103, 0, '20160103', 2), (104, 0, '20160101', 1), (104, 0, '20160102', 2), (104, 0, '20160103', 3)
) V(ProductID, Failed, SampleDate, LevelCode);


with T as (

select ProductID, 
RANK() OVER (PARTITION BY ProductID ORDER BY SampleDate desc) AS [Rank],
levelcode
from @x
where Failed = 1
)

select  FT.ProductID, FT.LevelCode,
    case
        when FT.LevelCode = ST.LevelCode then 1
        else 0
    END as IsLastRunSameLevelAsPreviousRun
FROM T AS FT
inner join T as ST on FT.ProductID = ST.ProductID and FT.[Rank]+1 = ST.[Rank]
WHERE FT.[Rank] = 1
    • 1

relate perguntas