sql - Referencing current row in FILTER clause of window function -
in postgresql 9.4 window functions have new option of filter select sub-set of window frame processing. documentation mentions it, provides no sample. online search yields samples, including 2ndquadrant found rather trivial examples constant expressions. looking filter expression includes value of current row.
assume have table bunch of columns, 1 of of date type:
col1 | col2 | dt ------------------------ 1 | | 2015-07-01 2 | b | 2015-07-03 3 | c | 2015-07-10 4 | d | 2015-07-11 5 | e | 2015-07-11 6 | f | 2015-07-13 ...
a window definition processing on date on entire table trivially constructed: window win (order dt)
i interested in knowing how many rows present in, say, 4 days prior current row (inclusive). want generate output:
col1 | col2 | dt | count -------------------------------- 1 | | 2015-07-01 | 1 2 | b | 2015-07-03 | 2 3 | c | 2015-07-10 | 1 4 | d | 2015-07-11 | 3 5 | e | 2015-07-11 | 3 6 | f | 2015-07-13 | 4 ...
the filter clause of window functions seems obvious choice:
count(*) filter (where current_row.dt - dt <= 4) on winbut how specify current_row.dt (for lack of better syntax)? possible?
if not possible, there other ways of selecting date ranges in window frame? frame specification no row-based.
i not interested in alternative solutions using sub-queries, has based on window processing.
you not aggregating rows, new aggregate filter clause not right tool. window function more it, problem remains, however: frame definition of window cannot depend on values of current row. can count given number of rows preceding or following rows clause.
to make work, aggregate counts per day , left join full set of days in range. can apply window function:
select t.*, ct.ct_last4days ( select *, sum(ct) on (rows 3 preceding) ct_last4days ( select generate_series(min(dt), max(dt), interval '1 day')::date dt tbl t1 ) d left join (select dt, count(*) ct tbl group 1) t using (dt) ) ct join tbl t using (dt); related:
Comments
Post a Comment