Início / dba / Perguntas / 156068
Accepted
maxTrialfire
Asked: 2016-11-23 12:42:48 +0800 CST

Usando a função de janela para levar adiante o primeiro valor não nulo em uma partição

  • 772

Considere uma tabela que registra visitas

create table visits (
  person varchar(10),
  ts timestamp, 
  somevalue varchar(10) 
)

Considere estes dados de exemplo (timestamp simplificado como contador)

ts| person    |  somevalue
-------------------------
1 |  bob      |null
2 |  bob      |null
3 |  jim      |null
4 |  bob      |  A
5 |  bob      | null
6 |  bob      |  B
7 |  jim      |  X
8 |  jim      |  Y
9 |  jim      |  null

Estou tentando levar adiante o último somevalue não nulo da pessoa para todas as suas visitas futuras até que esse valor mude (ou seja, torne-se o próximo valor não nulo).

O conjunto de resultados esperados se parece com isto:

ts|  person   | somevalue | carry-forward 
-----------------------------------------------
1 |  bob      |null       |   null
2 |  bob      |null       |   null
3 |  jim      |null       |   null
4 |  bob      |  A        |    A
5 |  bob      | null      |    A
6 |  bob      |  B        |    B
7 |  jim      |  X        |    X
8 |  jim      |  Y        |    Y
9 |  jim      |  null     |    Y

Minha tentativa fica assim:

 select *, 
  first_value(somevalue) over (partition by person order by (somevalue is null), ts rows between UNBOUNDED PRECEDING AND current row  ) as carry_forward

 from visits  
 order by ts

Observação: o (somevalue is null) é avaliado como 1 ou 0 para fins de classificação, para que eu possa obter o primeiro valor não nulo na partição.

O acima não me dá o resultado que estou procurando.

postgresql window-functions

2 respostas

  1. Best Answer

    A consulta a seguir atinge o resultado desejado:

    select *, first_value(somevalue) over w as carryforward_somevalue
from (
  select *, sum(case when somevalue is null then 0 else 1 end) over (partition by person order by id ) as value_partition
  from test1

) as q
window w as (partition by person, value_partition order by id);

    Observe a instrução de caso nulo - se IGNORE_NULL fosse suportado pelas funções de janela do postgres, isso não seria necessário (como mencionado por @ypercubeᵀᴹ)

    • 19

  2. O problema está na categoria de problemas de lacunas e ilhas. É uma pena que o Postgres ainda não tenha implementado IGNORE NULLfunções de janela como FIRST_VALUE(), caso contrário seria trivial, com uma simples alteração em sua consulta.

    Provavelmente, existem muitas maneiras de resolver isso usando funções de janela ou CTEs recursivos.

    Não tenho certeza se é a maneira mais eficiente, mas um CTE recursivo resolve o problema:

    with recursive 
    cf as
    (
      ( select distinct on (person) 
            v.*, v.somevalue as carry_forward
        from visits as v
        order by person, ts
      ) 
      union all
        select 
            v.*, coalesce(v.somevalue, cf.carry_forward)
        from cf
          join lateral  
            ( select v.*
              from visits as v
              where v.person = cf.person
                and v.ts > cf.ts
              order by ts
              limit 1
            ) as v
            on true
    )
select cf.*
from cf 
order by ts ;
    • 6

relate perguntas