Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
774 views
in Technique[技术] by (71.8m points)

postgresql - Window functions and more "local" aggregation

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...