如何用SQL查询提取PostgreSQL中Table1的连续时序分组数据？

当然可以实现！你这个需求属于典型的连续相同分组（岛屿问题）——也就是要把连续出现的同一个address归为一组，而不是把所有相同address的数据都混在一起统计。咱们用PostgreSQL的窗口函数就能轻松搞定。

思路拆解 #

核心是给每一段连续的相同address打上唯一的分组标记，然后基于这个标记做聚合统计：

1. 用LAG()函数获取当前行的上一行address，判断两者是否相同，生成一个"分组切换标识"；
2. 对这个标识做累加，得到每个连续组的唯一ID；
3. 按分组ID聚合，计算每组的记录数、最早时间和最晚时间。

完整SQL代码 #

WITH grouped_data AS (
    SELECT
        address,
        time,
        -- 生成分组切换标识：如果当前address和上一行不同，标记为1，否则0
        CASE WHEN LAG(address) OVER (ORDER BY time) != address THEN 1 ELSE 0 END AS group_switch
    FROM Table1
),
group_ids AS (
    SELECT
        address,
        time,
        -- 累加切换标识，得到每个连续组的唯一ID
        SUM(group_switch) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM grouped_data
)
SELECT
    address,
    COUNT(*) AS count,
    MIN(time) AS start_time,
    MAX(time) AS end_time
FROM group_ids
GROUP BY group_id, address
ORDER BY start_time;

结果验证 #

执行上面的SQL后，你会得到和预期完全一致的结果：

A, 3, 2018-01-03-11:25:30, 2018-01-08-06:25:36
B, 3, 2018-01-08-11:14:30, 2018-01-10-10:18:50
A, 2, 2018-01-12-23:17:30, 2018-01-13-06:24:40
C, 2, 2018-01-14-15:18:10, 2018-01-18-13:44:20

补充说明 #

• LAG(address) OVER (ORDER BY time)：必须按time排序，这样才能正确获取上一行的address，保证分组是按时序连续的；
• SUM(group_switch) OVER (...)：这里的窗口范围是从第一行到当前行，所以每遇到一次分组切换（group_switch=1），分组ID就会递增，从而把连续的相同address归为同一个组；
• 最后按group_id和address分组，是因为理论上同一个group_id只会对应一个address，加上address是为了更严谨。

内容的提问来源于stack exchange，提问作者Farvardin