Início / dba / Perguntas / 130120
Accepted
obautista
Asked: 2016-02-23 15:49:40 +0800 CST

Como comparar o registro atual com os registros anteriores

  • 772

Existe uma maneira de agrupar e comparar o registro atual com os registros anteriores ao ler de uma tabela. Minha tabela é algo como abaixo.

ID    PASSED    LEVEL    CREATEDATE
100   1         2        2016-01-10 07:35:02.123
100   1         2        2016-01-10 07:05:33.135
100   0         2        2016-01-09 08:35:29.123
100   1         2        2016-01-08 09:35:30.123
100   1         1        2016-01-07 11:35:31.123
101   1         1        2016-01-10 08:45:12.123
102   1         1        2016-01-10 09:45:22.222
102   1         1        2016-01-09 08:15:29.123
103   0         1        2016-01-10 07:35:02.123
103   1         2        2016-01-09 06:35:29.023

Idealmente, preciso retornar um valor BIT para cada ID na tabela.

A condição do valor BIT é retornar 1 para cada ID quando PASSED é 1 e os NÍVEIS são diferentes por ordem CREATEDATE DESC.

Então, na minha tabela de exemplo, o valor BIT para

  • ID 100 retornaria 1 porque PASSED é 1 no primeiro e no quarto registro (o nível muda no quarto registro).
  • ID 101 retornaria um valor BIT de 0 porque há apenas 1 registro PASSED com valor 1.
  • ID 102 retornaria um valor de BIT de 0 porque os NÍVEIS são os mesmos para ambos os registros, embora PASSED fosse 1 em ambos os registros.
  • O ID 103 retornaria 0 porque ambos os registros não PASSARAM, embora os NÍVEIS sejam diferentes.

Esperando que uma consulta possa ser estruturada de forma que a resposta seja assim:

ID    STATUS_PASSED
100   1
101   0
102   0
103   0

Obrigado por qualquer ajuda fornecida.

sql-server t-sql

1 respostas

  1. Best Answer

    Isso parecia um problema de divisão relacional para mim:

    DECLARE @results TABLE ( ID INT NOT NULL, PASSED BIT NOT NULL, LEVEL INT NOT NULL, CREATEDATE DATE NOT NULL, PRIMARY KEY ( ID, LEVEL, PASSED, CREATEDATE ) )
DECLARE @levels TABLE ( LEVEL INT PRIMARY KEY )

INSERT INTO @levels
VALUES ( 1 ), ( 2 ) --, ( 3 )    
INSERT INTO @results
VALUES
    ( 100, 1, 2, '1 Oct 2016' ),
    ( 100, 0, 2, '1 Sep 2016' ),
    ( 100, 1, 2, '1 Aug 2016' ),
    ( 100, 1, 1, '1 Jul 2016' ),
    ( 101, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Sep 2016' ),
    ( 103, 0, 1, '1 Oct 2016' ),
    ( 103, 1, 2, '1 Sep 2016' )
    ,
    ( 200, 0, 1, '1 Sep 2016' ),        -- No pass at all
    ( 201, 1, 1, '1 Sep 2016' ),        -- 1 pass at level 1 only
    ( 202, 1, 1, '1 Sep 2016' ),        -- 1 pass at each level 
    ( 202, 1, 2, '1 Sep 2016' ),        -- 1 pass at each level 
    ( 203, 1, 2, '1 Sep 2016' ),        -- 1 pass at level 2 only 
    ( 204, 0, 1, '1 Sep 2016' ),        -- No pass at either level
    ( 204, 0, 2, '1 Sep 2016' )         -- No pass at either level

    SELECT ID, 
        CASE 
            WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
              AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
            THEN 1
            ELSE 0 
        END overallPass
    FROM @results
    GROUP BY ID

    Não incluí a CREATEDATEcoluna no cálculo porque não parece acrescentar muito. Além disso, realmente faz sentido alguém passar em um curso no mesmo nível duas vezes (conforme ID 100 em seus dados de amostra)? A ordem em que eles passam nos cursos realmente importa? Nesse caso, isso pode causar um bug no meu código onde Sum of PASSED >= COUNT ( DISTINCT LEVEL ). Pré-agregar os dados resolveria isso, por exemplo

    SELECT ID,
    CASE 
        WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
          AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
        THEN 1
        ELSE 0 
    END overallPass
FROM ( SELECT ID, LEVEL, MAX( CAST( PASSED AS INT ) ) PASSED FROM @results GROUP BY ID, LEVEL ) x
GROUP BY ID

    Se a data for realmente importante, poste novamente e precisaremos de uma consulta um pouco mais complicada, basicamente o PASSEDstatus associado ao max date per ID.

    HTH

    • 0

relate perguntas