通过列值的差异将SQL中的gregregroup在SQL中

Question

我在Postgres表中的日志表中有以下数据：

1. logid => int(自动增量(
2. start_time => bigint(存储时代值(
3. inserted_value => int

以下是存储在表中的数据(其中 start Time实际不是列，仅以24小时格式显示UTC格式的start_time值(

logid   user_id    start_time       inserted_value       start time actual
 1      1          1518416562       15                   12-Feb-2018 06:22:42
 2      1          1518416622       8                    12-Feb-2018 06:23:42 
 3      1          1518417342       9                    12-Feb-2018 06:35:42 
 4      1          1518417402       12                   12-Feb-2018 06:36:42 
 5      1          1518417462       18                   12-Feb-2018 06:37:42
 6      1          1518418757       6                    12-Feb-2018 06:59:17 
 7      1          1518418808       11                   12-Feb-2018 07:00:08

我想根据start_time

的差进行分组和总和值

对于上述数据，总和应分为三组：

  user_id         sum
   1              15 + 8
   1              9 + 12 + 18
   1              6 + 11

因此，每个组中的值都有1分钟的差异。该1可以视为任何X分钟的差异。

我也在尝试LAG功能，但无法完全理解它。我希望我能够解释我的问题。

Answer 1

您可以使用普通的group by实现所需的目标。只需使所有属于同一分钟的start_time值相等。例如

select user_id, start_time/60, sum(inserted_value)
from log_table
group by user_id, start_time/60

我假设您的start_time列包含代表毫秒的整数，因此/60将其正确截断为几分钟。如果值是浮子，则应使用floor(start_time/60)。

如果您还想选择您分组分钟的人类可读日期，则可以将to_timestamp((start_time/60)*60)添加到选择列表中。

Answer 2

您可以使用LAG检查当前行是否比上一行多> 60秒，并设置group_changed(虚拟列((虚拟列(。

在下一步中，使用该列上的运行总和。这将创建一个group_number，您可以将其用于将其分组为第三步。

WITH cte1 AS (
    SELECT
        testdata.*,
        CASE WHEN start_time - LAG(start_time, 1, start_time) OVER (PARTITION BY user_id ORDER BY start_time) > 60 THEN 1 ELSE 0 END AS group_changed
    FROM testdata
), cte2 AS (
    SELECT
        cte1.*,
        SUM(group_changed) OVER (PARTITION BY user_id ORDER BY start_time) AS group_number
    FROM cte1
)
SELECT user_id, SUM(inserted_value)
FROM cte2
GROUP BY user_id, group_number

SQL小提琴