时间戳

首页 > 解决方案 > 在 Postgres 中加入 Range

sql - 在 Postgres 中加入 Range

问题描述

我有一个像这样的 PostgreSQL 数据库表：

ID | Meal            |Person
--------------------------------
1  | Early Beakfast  | Abc
2  | Breakfast       | Abc
3  | Lunch           | Abc
4  | Afternoon Snack | Abc
5  | Evening Snack   | Abc
7  | Dinner          | Abc
8  | late Night Snack| Abc
9  | Afternoon Snack | Def
10 | Evening Snack   | Def

我想要这样的输出：

Anchor          | Before          | After            | Person
----------------------------------------------------------------
Afternoon Snack | Early Breakfast | Evening Snack    | Abc
Afternoon Snack | Breakfast       | Dinner           | Abc
Afternoon Snack | Lunch           | Late Night Snack | Abc
Afternoon Snack | NULL            | Evening Snack    | Def

获得此结果的最佳方法是什么？

我曾尝试在 ID IN (ID+1, ID+2,...ID+N) 上使用自加入，但这不可行。注意：这只是一个示例数据。实际数据包含 2M+ 条记录。但是，锚点保持不变。

标签： sqlpostgresql

解决方案

根据下午点心的数量（累积总和）创建组，然后聚合：

select 'Afternoon Snack',
       max(case when seqnum = 0 then meal end) as before,
       max(case when seqnum = 1 then meal end) as after
from (select t.*,
             row_number() over (partition by person, grp, (meal <> 'Afternoon Snack') order by id) as seqnum
      from (select t.*,
                   count(*) filter (where meal = 'Afternoon Snack') over (partition by person order by id) as grp
            from t
           ) t
     ) t
where grp in (0, 1)
group by person, seqnum
order by person, seqnum;

推荐阅读