Użycie funkcji okna do przeniesienia pierwszej niepustej wartości w partycji

Rozważ tabelę, która rejestruje wizyty

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

Rozważ te przykładowe dane (znacznik czasu uproszczony jako licznik)

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

Usiłuję przenieść ostatnią inną niż zerową wartość osoby na wszystkie jego przyszłe wizyty, dopóki ta wartość się nie zmieni (tj. Stanie się następną wartością inną niż zero).

Oczekiwany zestaw wyników wygląda następująco:

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

Moja próba wygląda następująco:

 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

Uwaga: (gdzieś wartość jest pusta) w celu sortowania przyjmuje wartość 1 lub 0, dzięki czemu mogę uzyskać pierwszą wartość inną niż null w partycji.

Powyższe nie daje mi rezultatu, którego szukam.

Następujące zapytanie osiąga pożądany wynik:

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);

Zwróć uwagę na instrukcję null case - jeśli IGNORE_NULL był obsługiwany przez funkcje okna postgres, nie byłoby to konieczne (jak wspomniano w @ ypercubeᵀᴹ)

Problem dotyczy kategorii luk i wysp. Szkoda, że ​​Postgres jeszcze nie wdrożyłIGNORE NULL funkcji okna, takich jak FIRST_VALUE(), w przeciwnym razie byłoby to proste, z prostą zmianą w zapytaniu.

Prawdopodobnie istnieje wiele sposobów rozwiązania tego za pomocą funkcji okna lub rekurencyjnych CTE.

Nie jestem pewien, czy jest to najbardziej skuteczny sposób, ale rekurencyjne CTE rozwiązuje problem:

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 ;