sql - 在同一列上迭代时计算持续时间

Question

是否可以根据同一列中存在的时间之间的差异来计算每日总小时数？

<表类=“s-表”><标题>日期时间 <正文>2017-11-0808:25:572017-11-0812:31:592017-11-0813:28:422017-11-0817:34:132017-11-0908:28:082017-11-0912:31:152017-11-0913:20:13

我可以使用此查询获取 enditime 和 starttime 的单独数据:

SELECT MAX(time) AS "endtime",
       MIN(time) AS "startime",
       MAX(time)-MIN(time) as "totalneedtocalculatepause"
WHERE (date = '2017-11-08'::date )
FROM history ts

但是缺少 startpause 和 endpause:

<表类=“s-表”><标题>结束时间开始时间总需要计算暂停 <正文>17:34:1308:25:5709:08:16

正确的顺序应该是:

day         startpause   starttime    endtime     endpause
2017-11-08: (12:31:59 - 08:25:57) + (17:34:13 - 13:28:42)

我使用的是 PostgreSQL 13。

Answer 1

鉴于您的记录是耦合的并且连续值代表开始结束时间段，您可以使用 ROW_NUMBER函数，除以 2，并取上限，以识别对(分区)。然后使用您的查询，同时对新生成的列进行分区。

WITH cte AS (
    SELECT *, CEIL((ROW_NUMBER() OVER(PARTITION BY date ORDER BY time)+1)/2) AS rn 
    FROM history
)
SELECT MAX(time) AS "endtime",
       MIN(time) AS "startime",
       CAST(MAX(time)-MIN(time) AS time) AS "totalneedtocalculatepause"
FROM cte
WHERE (date = '2017-11-08'::date)
GROUP BY rn

“输出”:

<表类=“s-表”><标题>结束时间开始时间总需要计算暂停 <正文>12:31:5908:25:5704:06:0217:34:1313:28:4204:05:31

查看演示 here .

---

如果您想要整体摘要，您可以应用进一步的聚合:

WITH cte AS (
    SELECT *, CEIL((ROW_NUMBER() OVER(PARTITION BY date ORDER BY time)+1)/2) AS rn 
    FROM history
), cte2 AS (
    SELECT MAX(time) AS "endtime",
           MIN(time) AS "startime",
           CAST(MAX(time)-MIN(time) AS time) AS "totalneedtocalculatepause"
    FROM cte
    WHERE (date = '2017-11-08'::date)
    GROUP BY rn
)
SELECT MAX(endtime) AS "endtime",
       MIN(startime) AS "startime",
       CAST(SUM(totalneedtocalculatepause) AS TIME) AS "totalneedtocalculatepause"
FROM cte2

“输出”:

<表类=“s-表”><标题>结束时间开始时间总需要计算暂停 <正文>17:34:1308:25:5708:11:33

查看演示 here .