Window functions and more "local" aggregation

common-table-expression, postgresql, window-functions

Solution

This returns your desired result with the sample data. Not sure if it will work for real world data:

select k, 
       min(v) over (partition by group_nr) as min_v,
       max(v) over (partition by group_nr) as max_v
from (
    select *,
           sum(group_flag) over (order by v,k) as group_nr
    from (
    select *,
           case
              when lag(k) over (order by v) = k then null
              else 1
            end as group_flag
    from window_test
    ) t1
) t2
order by min_v;

I left out the `DISTINCT` though.

Problem

Suppose I have this table: ``` select * from window_test; k | v ---+--- a | 1 a | 2 b | 3 a | 4 ``` Ultimately I want to get: ``` k | min_v | max_v ---+-------+------- a | 1 | 2 b | 3 | 3 a | 4 | 4 ``` But I would be just as happy to get this (since I can easily filter it with `distinct`): ``` k | min_v | max_v ---+-------+------- a | 1 | 2 a | 1 | 2 b | 3 | 3 a | 4 | 4 ``` Is it possible to achieve this with PostgreSQL 9.1+ window functions? I'm trying to understand if I can get it to use separate partition for the first and last occurrence of `k=a` in this sample (ordered by `v`).

Original source