时间戳

首页 > 解决方案 > 使用 SQL 汇总数据

sql - 使用 SQL 汇总数据

问题描述

我有一个问题，我正在尝试使用 SQL 来解决，我需要您对方法的输入来解决它。

这是输入数据和预期输出的样子：

container_edits - This is the input table

container | units | status   |  move_time
-------------------------------------------------
XYZ       | 5     | Start    | 2018-01-01 00:00:15
XYZ       | 2     | Add      | 2018-01-01 00:01:10
XYZ       | 3     | Add      | 2018-01-01 00:02:00
XYZ       | null  | Complete | 2018-01-01 00:03:00
XYZ       | 5     | Start    | 2018-01-01 00:04:15
XYZ       | 3     | Add      | 2018-01-01 00:05:10
XYZ       | 4     | Add      | 2018-01-01 00:06:00
XYZ       | 5     | Add      | 2018-01-01 00:07:10
XYZ       | 6     | Add      | 2018-01-01 00:08:00
XYZ       | null  | Complete | 2018-01-01 00:09:00

Expected summarized output

container | loop_num | units | start_time          | end_time
------------------------------------------------------------------------
XYZ       | 1        |  10   | 2018-01-01 00:00:15 | 2018-01-01 00:03:00
XYZ       | 2        |  23   | 2018-01-01 00:04:15 | 2018-01-01 00:09:00

本质上，我需要根据状态标签对数据进行分区，提取分区内的最小和最大时间，并获取该分区内的单元总数。我知道窗口函数和 partition by 子句的使用，但是当我需要根据列的值（在这种情况下为“状态”）进行分区时，我不清楚如何应用它。

关于如何解决这个问题的任何线索都会非常有帮助。谢谢！

标签： sqlgaps-and-islands

解决方案

您可以使用的累积总和分配一个组starts-- 这是您loop_num 的其余是聚合：

select container, loop_num, sum(units),
       min(move_time), max(move_time)
from (select ce.*,
             sum(case when status = 'Start' then 1 else 0 end) over (partition by container order by move_time) as loop_num
      from container_edits ce
     ) ce
group by container, loop_num;

这是一个 db<>fiddle（它恰好使用 Postgres，但语法是标准 SQL）。

推荐阅读