窗口功能和更多“本地”功能聚合 [英] Window functions and more "local" aggregation

问题描述

假设我有这个表：

select * from window_test;

 k | v
---+---
 a | 1
 a | 2
 b | 3
 a | 4

我最终想要得到：

 k | min_v | max_v
---+-------+-------
 a | 1     | 2
 b | 3     | 3
 a | 4     | 4

但是我也会很高兴得到这个（因为我可以轻松地用<$过滤掉它c $ c>与众不同）：

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

是否可以使用PostgreSQL 9.1+窗口函数来实现？我试图了解是否可以让它在此示例中第一次和最后一次出现 k = a 使用单独的分区（按排序） v ）。

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

推荐答案

这将返回所需的结果以及示例数据。不确定是否适用于现实世界的数据：

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;

虽然我省略了 DISTINCT 。

这篇关于窗口功能和更多“本地”功能聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！