时间戳

首页 > 解决方案 > Postgres 按值更改分区/分组

sql - Postgres 按值更改分区/分组

问题描述

我想构建一个查询，它不是按值本身对数据进行分组，而是按行之间的值变化这一事实来分组数据。我将不胜感激任何帮助！这是一个例子：

假设我有一个具有这种结构的表：

create table events (
  event_time timestamp, 
  source_id integer references source(id),
  metric integer,
  flagged boolean
);

和以下样本数据：

source_id | event_time | metric | flagged
-----------------------------------------
1         | 8:00       | 1      | t
1         | 8:10       | 15     | f
1         | 8:20       | 7      | t
1         | 8:30       | 3      | t
1         | 8:40       | 6      | f
1         | 8:50       | 10     | t 
2         | 9:00       | 5      | t

我想查询metric组的子组的平均值，但如果数据是有序的，则根据不会从一行更改为下一行source_id的事实对这些子组进行分组。flagged也就是说，在上面的示例中，我的意思不是group by source_id, flagged，因为这只会为每个 提供两个组source_id，而是将连续行分组，按时间排序，其中flagged相邻行中的值相同。也就是说，我想有以下组：

group_number | source_id | flagged | avg(metric)
------------------------------------------------
1            | 1         | t       | 1            -- group includes entry for 8:00 only
2            | 1         | f       | 15           -- entry for 8:15 only
3            | 1         | t       | 5            -- entries on 8:20 & 8:30, (7+3)/2
4            | 1         | f       | 6            -- entry for 8:40 only
5            | 1         | t       | 10           -- entry for 8:50 only
6            | 2         | t       | 5            -- 9:00 from source_id = 2

group_number输出中不需要，这里只是为了使大小写更清楚。此外，我不需要那些不是的条目flagged——基本上它们只用于拆分组。

我试图用分区和ranks和来做到这一点lags，但到目前为止无济于事。将不胜感激任何帮助！谢谢你。

标签： sqlpostgresqlgroup-by

解决方案

这是一个差距和孤岛问题。一种解决方案使用行号的差异。

select source_id, flagged, avg(metric)
from (select e.*,
             row_number() over (partition by source_id order by event_time) as seqnum_1,
             row_number() over (partition by source_id, flagged order by event_time) as seqnum_2
      from events e
     ) e
group by source_id, flagged, (seqnum_1 - seqnum_2);

这个工作的原因有点难以解释。我发现如果您运行子查询，您将看到行号之间的差异如何识别具有相同标志的相邻行组。

推荐阅读