如何使用PostgreSQL将开始/结束时间列分割成离散块？

Question

我们有一些表，它们的结构如下：

开始，-日期时间

结束--日期时间

成本-十进制

因此，例如，可能会有这样的一行：

01/01/201010:08，01/01/2010 1:56 01 135.00

01/01/201011:01上午11:01，01/01/2010 3:22,118.00

01/01/201006:19 02 01/02/2010-1:43,167.00

等等..。

我想把它转换成一种格式(有函数吗？)返回数据的格式如下：

上午10:00，上午10:15，X，Y，Z

上午10:15，上午10:30，X，Y，Z

上午10:30，上午10:45，X，Y，Z

上午10:45，上午11:00，X，Y，Z

上午11:00，上午11:15，X，Y，Z

……

其中：

X=匹配的行数

Y=那一段时间的成本/费用

Z=此期间的总时间

对于上述数据，我们可能有：

上午10:00，上午10:15，1，(135/228分钟*7)，7

• 第一行从上午10点08分开始，所以从10:00到10:15只使用7分钟。
• ->结束时间有228分钟。

……

上午11:00，上午11:15，2，((135+118)/(228+261) minutes*(15+14))，29

• 第二行在上午11:00之后就开始了，所以我们需要15分钟才能到达第一行，再加上从第二排到
• ，第二行有261分钟->结束时间

。

……

我相信我已经在这里做了数学计算，但需要弄清楚如何把它变成PG函数，这样它就可以在报告中使用。

理想情况下，我希望能够调用任意持续时间的函数，即15分钟、30分钟或60分钟，并据此将其拆分。

有什么想法吗？

Answer 1

这是我的尝试。考虑到本表的定义：

CREATE TABLE interval_test
(
  "start" timestamp without time zone,
  "end" timestamp without time zone,
  "cost" integer
)

这个查询似乎可以做您想做的事情。不过，不确定这是否是最好的解决方案。还请注意，它需要Postgres 8.4才能工作，因为它使用窗口函数和查询。

WITH RECURSIVE intervals(period_start) AS (
    SELECT 
    date_trunc('hour', MIN(start)) AS period_start
      FROM interval_test

  UNION ALL
    SELECT intervals.period_start + INTERVAL '15 MINUTES'
      FROM  intervals
      WHERE (intervals.period_start + INTERVAL '15 MINUTES') < (SELECT MAX("end") FROM interval_test)
  )
  SELECT DISTINCT period_start, intervals.period_start + INTERVAL '15 MINUTES' AS period_end, 
  COUNT(*) OVER  (PARTITION BY period_start ) AS record_count,
SUM (LEAST(period_start + INTERVAL '15 MINUTES', "end")::timestamp - GREATEST(period_start, "start")::timestamp)
  OVER  (PARTITION BY period_start ) AS total_time,

  (SUM(cost) OVER  (PARTITION BY period_start ) /  


 (EXTRACT(EPOCH FROM SUM("end" - "start") OVER  (PARTITION BY period_start )) / 60)) * 

 ((EXTRACT (EPOCH FROM SUM (LEAST(period_start + INTERVAL '15 MINUTES', "end")::timestamp - GREATEST(period_start, "start")::timestamp)
  OVER  (PARTITION BY period_start )))/60)

   AS expense

FROM  interval_test
INNER JOIN intervals ON (intervals.period_start, intervals.period_start + INTERVAL '15 MINUTES') OVERLAPS (interval_test.start, interval_test.end)

ORDER BY period_start ASC