Início / dba / Perguntas / 130218
Accepted
obautista
Asked: 2016-02-24 09:59:42 +0800 CST

Como comparar e agregar dados em uma tabela

  • 772

Existe uma maneira de avaliar os dados em uma tabela para derivar se em um conjunto de ProductIDs um PRODUCTID passou ou falhou? Aqui está um exemplo dos meus dados:

DECLARE @example TABLE (ID INT NOT NULL, PRODUCTID INT, PASSED BIT, LEVEL INT, CREATEDATE DATETIME, PRIMARY KEY (ID))
INSERT INTO @example VALUES
 (  1, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  2, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  3, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  4, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  5, 101, 1, 1, '2016-01-10 07:35:02.123' )
,(  6, 102, 0, 1, '2016-01-10 07:35:02.123' )
,(  7, 102, 1, 2, '2016-01-10 07:35:02.123' )
,(  8, 103, 1, 1, '2016-01-10 10:35:02.123' )
,(  9, 103, 1, 2, '2016-01-10 09:35:02.123' )  
,( 10, 103, 1, 2, '2016-01-10 08:35:02.123' ) 
,( 11, 103, 1, 3, '2016-01-10 07:35:02.123' )

É possível estruturar uma consulta que retorne:

PRODUCTID    ISPASSED
100          0
101          0
102          0
103          1

As condições são: 2 APROVADOS para 2 NÍVEIS diferentes para um determinado PRODUCTID.

  • 100 é um 0 porque todos os 4 registros são para NÍVEL 1
  • 101 é um 0 porque há apenas 1 PASS e para apenas 1 NÍVEL
  • 102 é um 0 porque um registro PASSOU e o outro falhou
  • 103 é 1 porque existem 2 registros PASSED para NÍVEIS diferentes.

Muito obrigado!

EDITAR:

Eu criei esta consulta, mas ela é curta porque só consigo qualificar a primeira parte dos requisitos:

WITH cte(PRODUCTID, ISPASSED) AS
(
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) = 2 THEN 1 ELSE 0 END AS ISPASSED
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC), LEVEL, PASSED
    FROM @example
    GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
  WHERE RowNumber <=2
  GROUP BY PRODUCTID
)
select * from cte
sql-server sql-server-2014

4 respostas

  1. Como você deseja contar valores diferentes em um conjunto que pode conter valores idênticos, eu diria que COUNT(DISTINCT ...)é a ferramenta perfeita para usar. Por exemplo, o seguinte contará níveis distintos por produto:

    SELECT
  PRODUCTID,
  COUNT(DISTINCT LEVEL)
FROM
  @example
GROUP BY
  PRODUCTID
;

    Esta será a saída para o seu exemplo:

    PRODUCTID
---------  ---
100        1
101        1
102        2
103        3

    Agora, como você pode contar apenas os valores distintos que correspondem a uma condição específica? A resposta é, usando agregação condicional, ou seja, uma que usa uma expressão CASE como argumento da função de agregação. Portanto, para contar apenas os níveis aprovados distintos , você pode usar uma consulta como esta:

    SELECT
  PRODUCTID,
  COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END)
FROM
  @example
GROUP BY
  PRODUCTID
;

    E os resultados serão agora

    PRODUCTID
---------  ---
100        1
101        1
102        1
103        3

    Os números parecem corretos agora, então como você pode transformar esses 1, 3em 0, 1? Por que, usando outra expressão CASE, é claro:

    SELECT
  PRODUCTID,
  ISPASSED = CASE
                WHEN COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END) >= 2
                THEN 1
                ELSE 0
              END
FROM
  @example
GROUP BY
  PRODUCTID
;

    Agora você pode aproveitar a saída esperada de

    PRODUCTID  ISPASSED
---------  --------
100        0
101        0
102        0
103        1
    • 2

  2. Isso lhe dará a resposta desejada com base nos dados de exemplo que você forneceu. Você não respondeu à minha pergunta sobre o número de níveis, então isso pressupõe que não haja mais de 3 níveis.

    select ProductID, case when [1]+ [2] + [3] >=2  then 1 else 0 end as ispassed
FROM 
(Select cast(PASSED as int) PASSED, Level, Productid
FROM @example) e 
PIVOT 
( 
Max(Passed) 
FOR [Level] in ([1], [2], [3]) )
AS pvt 
ORDER BY pvt.PRODUCTID
    • 1
  3. Best Answer

    Isso é o que eu entendo... Não tenho certeza se isso cobre todos os seus casos...

    Avise-me se houver algum caso específico que não esteja coberto.

    reeditado: só notei

    As condições são: 2 APROVADOS para 2 NÍVEIS diferentes para um determinado PRODUCTID.

        Select  productid, 
        Case when minpass = 1 and maxpass = 1 and nbOfLevels>1 and NbOfPass>1 then 1 else 0 end as passed
From (Select Productid, 
            min(convert(int,passed)) minPass,
            max(convert(int,passed)) maxpass, 
            -- max(Level) MaxLevel,
            sum(convert(int,passed)) NbOfPass,
            count(distinct Level) nbOfLevels
            from @example
        group by productid ) X
    • 0

  4. Isso parece estar funcionando:

    WITH cte(ProductID, IsPassed) AS
{
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) >= 2 THEN 1 ELSE 0 END AS IsPassed
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID, LEVEL ORDER BY CREATEDATE DESC), LEVEL, PASSED
        FROM @example
        GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
WHERE RowNumber = 1
GROUP BY PRODUCTID
)
select * from cte
    • 0

relate perguntas