时间戳

首页 > 解决方案 > Postgres 每小时按数据分组，然后找到 max 和 min createdAt 的时间差并计算它们的总和

sql - Postgres 每小时按数据分组，然后找到 max 和 min createdAt 的时间差并计算它们的总和

问题描述

我有一张这样的桌子：

p_id |      createdat      | pagetitle | sessionid |      text       | device  | deviceserial
------+---------------------+-----------+-----------+-----------------+---------+--------------
      | 2020-09-03 06:59:18 |           |           | Approve         | Android | 636363636890
      | 2020-09-03 08:40:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 08:40:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 08:45:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 08:43:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 08:50:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 08:47:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 08:53:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 08:50:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 08:55:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 08:52:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 09:00:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 08:55:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 09:05:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 08:59:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 09:07:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 09:01:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 09:09:11 |           |           | hi              | Android | 6625839827
      | 2020-09-03 09:03:10 |           |           | launchComponent | Android | 636363636890
      | 2020-09-03 09:09:11 |           |           | hi              | Android | 6625839828
      | 2020-09-03 09:03:10 |           |           | launchComponent | Android | 636363636891
      | 2020-09-03 09:13:11 |           |           | hi              | Android | 6625839828

我想按小时和设备序列对数据进行分组。然后从每小时 createdAt 中找到最大值和最小值，然后计算总和。那是我尝试过的查询：

Select deviceserial,DATE_PART('minute', max(createdat)::timestamp - min(createdat)::timestamp) AS time_difference,date_part('hour', createdat) as hr FROM json_table2  GROUP BY deviceserial,hr;

这是我的结果：

deviceserial | time_difference | hr
--------------+-----------------+----
 636363636891 |               3 |  9
 6625839832   |               0 | 11
 636363636890 |               0 |  6
 636363636890 |              19 |  8
 6625839830   |               0 | 10
 6625839830   |               0 | 12
 6625839835   |               0 | 10
 6625839833   |               0 | 12
 6625839828   |               4 |  9
 6625839832   |               0 | 10
 6625839835   |               0 | 11
 636363636890 |               2 |  9
 6625839827   |               9 |  9
 6625839833   |               0 | 11
 6625839827   |              15 |  8

我想找到这样的东西。这将是时间差异的每小时总和（createdAt 差异）groupby 设备序列：

deviceserial | time_difference | 
--------------+-----------------+----
 636363636890 |              21 |   
 6625839827   |              24 | 

And for other values as well...

在这里，我试图找到不同 deviceSerial 的设备使用情况。

标签： sqlpostgresqldatetimeaggregate-functionsdate-arithmetic

解决方案

如果我正确地跟随您，那是您现有查询之上的另一个聚合级别：

select deviceserial, sum(time_difference) as time_difference
from (
    select deviceserial,
        date_part('minute', max(createdat)::timestamp - min(createdat)::timestamp) as time_difference,
        date_trunc('hour', createdat) as hr 
    from json_table2  
    group by deviceserial, hr
) t
group by deviceserial

我将查询更改为使用date_trunc()，而不是date_part(): 后者将不同日期同一时间发生的记录一起计算，我认为您不希望这样做。

虽然这可能会回答您的直接问题，但我想知道结果有多大用处。您可能想问另一个问题，从一开始就解释您要做什么。

推荐阅读