Início / dba / Perguntas / 191005
Accepted
GWR
Asked: 2017-11-16 09:39:02 +0800 CST

Agrupando subconjuntos de linhas com valores nulos em um conjunto ordenado

  • 772

Digamos que temos uma tabela onde cada linha é um dia, e é ordenada por esta coluna de dia. Em seguida, deixamos um conjunto de dados de associação que mostra em que dia os membros estavam ativos (e não).

Digamos que nosso conjunto de dados atual seja assim... A associação estava ativa do dia 3 ao 5, inativa do dia 5 ao 8 e ativa do dia 9 em diante, etc.

DAY     DATE        MEMBER  ACTIVE
 1      2017-01-01  123     null
 2      2017-01-02  123     null
 3      2017-01-03  123     2017-01-03
 4      2017-01-04  123     2017-01-04
 5      2017-01-05  123     2017-01-05
 6      2017-01-06  123     null
 7      2017-01-07  123     null
 8      2017-01-08  123     null
 9      2017-01-09  123     2017-01-09
10      2017-01-10  123     2017-01-10

...então ACTIVE=nullsignifica que a associação não estava ativa naqueles dias.

Com esta estrutura de dados, gostaria de chegar a um conjunto "recolhido", mostrando "spans" de tempo inativo/ativo:

MEMBER  MIN(DATE)   MAX(DATE)   STATUS
123,    2017-01-01, 2017-01-02  INACTIVE
123,    2017-01-03, 2017-01-05  ACTIVE
123,    2017-01-06, 2017-01-08  INACTIVE
123,    2017-01-09, 2017-01-10  ACTIVE

Eu tentei usar row_number() para de alguma forma particionar os subconjuntos de um determinado status, mas neste caso, usando min()/ max()sobre as linhas em que ACTIVE é nulo, trata-as como um único grupo, quando na realidade existem vários intervalos distintos de "associação inativa".

Como posso distinguir os períodos de associação inativa uns dos outros para fins de agrupamento? Que técnica posso usar para obter essa saída acima?

Aqui está o script para gerar os dados de origem fictícios:

CREATE TABLE ##SRC (ID INT, D DATE, MEMBER INT, ACTIVE DATE);

INSERT INTO ##SRC (ID, D, MEMBER, ACTIVE)
SELECT 1, '2017-01-01', 123, NULL UNION 
SELECT 2, '2017-01-02', 123, NULL UNION 
SELECT 3, '2017-01-03', 123, '2017-01-03' UNION 
SELECT 4, '2017-01-04', 123, '2017-01-04' UNION 
SELECT 5, '2017-01-05', 123, '2017-01-05' UNION 
SELECT 6, '2017-01-06', 123, NULL UNION 
SELECT 7, '2017-01-07', 123, NULL UNION 
SELECT 8, '2017-01-08', 123, NULL UNION 
SELECT 9, '2017-01-09', 123, '2017-01-09' UNION 
SELECT 10, '2017-01-10',    123, '2017-01-10' 
;
sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Seus dados de amostra não correspondem à sua descrição e me confundiram no início. Como sp_BlitzErik aponta, este é um problema de ilha e lacuna. A solução é bastante simples se você tiver acesso às funções da janela. Primeiro, podemos enumerar a tabela por membro sozinho, vamos chamar isso de full_order (isso é o mesmo que dia, mas vou adicioná-lo para generalidade). Segundo, podemos enumerar a tabela por membro e se eles estavam ativos naquele dia, vamos chamar isso de partial_order

     select day, active, date, member
      , row_number() over (partition by member 
                           order by day) as fullorder
      , row_number() over (partition by member
                          ,case when active is null then 0 else 1 end
                          order by day) as partialorder
 from src

DAY         ACTIVE     MEMBER      FULLORDER            PARTIALORDER        

----------- ---------- ----------- -------------------- --------------------
      1 -                  123                    1                    1
      2 -                  123                    2                    2
      3 01/03/2017         123                    3                    1
      4 01/04/2017         123                    4                    2
      5 01/05/2017         123                    5                    3
      6 -                  123                    6                    3
      7 -                  123                    7                    4
      8 -                  123                    8                    5
      9 01/09/2017         123                    9                    4
     10 01/10/2017         123                   10                    5

    Se a diferença entre full_order e partial_order for alterada, isso significa que active mudou de null para um valor ou vice-versa. Portanto, podemos formar um grupo com essa diferença. Dentro de cada grupo, podemos escolher o min(active) e max(active) para formar um intervalo:

    select member, grp, min(date), max(active) 
from (
    select day, active, date, member
         , row_number() over (partition by member order by day) 
         - row_number() over (partition by member
                             ,case when active is null then 0 else 1 end 
                              order by day) as grp  
    from src
) 
group by member, grp

MEMBER      GRP                  3          4         
----------- -------------------- ---------- ----------
    123                    0 01/01/2017 -         
    123                    2 01/03/2017 01/05/2017
    123                    3 01/05/2017 -         
    123                    5 01/08/2017 01/10/2017

    Provavelmente é mais fácil adicionar outro nível de aninhamento para obter o resultado desejado:

    select member, min_active
     , coalesce(max_active, min_active) as max_active
     , case when max_active is null then 'INACTIVE' else 'ACTIVE' end as status 
from (
    select member, grp, min(date) as min_active, max(active) as max_active 
    from (
        select day, active, date, member
             , row_number() over (partition by member order by day) 
             - row_number() over (partition by member
                                 ,case when active is null then 0 else 1 end 
                                 order by day) as grp  
        from src
    ) 
    group by member, grp)

MEMBER      MIN_ACTIVE MAX_ACTIVE STATUS  
----------- ---------- ---------- --------
    123 01/01/2017 01/01/2017 INACTIVE
    123 01/03/2017 01/05/2017 ACTIVE  
    123 01/05/2017 01/05/2017 INACTIVE
    123 01/08/2017 01/10/2017 ACTIVE
    • 3

  2. Em seguida, deixamos um conjunto de dados de associação que mostra em que dia os membros estavam ativos (e não).

    Desculpe Se entendi errado.você deveria ter postado ambos os dados da tabela então mencione o seu problema.Dessa forma é garantido obter a melhor consulta.

    Estou usando o CTE recursivo na minha consulta, o que pode ser evitado se você tiver a tabela.

    de qualquer forma o script é muito curto

    CREATE TABLE #SRC (ID INT, D DATE, MEMBER INT, ACTIVE DATE);

INSERT INTO #SRC (ID, D, MEMBER, ACTIVE)
SELECT 1, '2017-01-01', 123, NULL UNION 
SELECT 2, '2017-01-02', 123, NULL UNION 
SELECT 3, '2017-01-03', 123, '2017-01-03' UNION 
SELECT 4, '2017-01-04', 123, '2017-01-04' UNION 
SELECT 5, '2017-01-05', 123, '2017-01-05' UNION 
SELECT 6, '2017-01-06', 123, NULL UNION 
SELECT 7, '2017-01-07', 123, NULL UNION 
SELECT 8, '2017-01-08', 123, NULL UNION 
SELECT 9, '2017-01-09', 123, '2017-01-09' UNION 
SELECT 10, '2017-01-10',    123, '2017-01-10' 

;with CTE as
(
select *, 1 flg  from #SRC where id=1

union ALL

select s.*
,case when s.active is null and c.active is null then  flg 
when s.active is not null and c.active is not null then  flg 
else flg+1 end   
from #SRC S 
inner JOIN cte c on s.member=c.member
and s.id=c.id +1
)
select member,flg,MIN(D) MinD ,max(D)MaxD
from cte
group by member,flg

drop table #SRC
    • 0

relate perguntas