postgresql 如何在表中对每天每个小时的数据进行分组？

5us2dqdw 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(173)

**大家好！****请告诉我如何将每天每个小时的数据分组到表中？**我有一个名为sales的表。此表包含以下数据：sale_id-销售的id。sale_start-销售过程开始的时间（时间戳）。sale_canceled-何时取消销售（时间戳）。sale_completed-销售时间（时间戳）。table

...我需要计算一周内每个小时有多少销售额。
一般来说，现在我有一个postgresql-query，当我使用“LIMIT”时它就可以工作了。否则，请求崩溃。Postgresql-query：

WITH s AS (SELECT date_trunc('hour', '2022-04-22'::date) - generate_series(0, 24 * 7) * interval '1 hour' AS h_slot
FROM sales),
dt AS (SELECT DATE_TRUNC('hour', "sale_start") AS sale_start,
       DATE_TRUNC('hour', "sale_canceled") AS sale_canceled,
       DATE_TRUNC('hour', "sale_completed") AS sale_completed,
       FROM sales
       WHERE "sale_start" >= '2022-08-10'::date - INTERVAL '1 week'
       AND "sale_start" <= '2023-08-10'),
SELECT s.h_slot,
       COUNT(*) FILTER (WHERE dt.sale_completed = s.h_slot) AS completed_count,
       COUNT(*) FILTER (WHERE s.h_slot >= dt.sale_start 
       AND s.h_slot <= dt.sale_canceled) AS available_count
FROM s CROSS JOIN dt
GROUP BY s.h_slot
ORDER BY s.h_slot

字符串

postgresql

来源：https://stackoverflow.com/questions/76803992/how-i-can-group-the-data-for-each-hour-of-each-day-in-a-table

1条答案

按热度按时间

vyswwuz21#

注意片段：

WITH s AS (
  SELECT date_trunc('hour', '2022-04-22'::date) 
            - generate_series(0, 24 * 7) * interval '1 hour' AS h_slot
  FROM sales
)

字符串
在那里，您生成大小为7 * 24 *（表sales中的记录计数）的系列。
然后cross加入dt (sales)。记录总数为
7 * 24 （表销售中的记录计数）（销售间隔（周）中的记录计数）
首先，删除FROM Sales。
这可能会解决你的问题。

WITH s AS (
  SELECT date_trunc('hour', '2022-04-22'::date) 
             - generate_series(0, 24 * 7) * interval '1 hour' AS h_slot
)

型
例如，试试这个。

WITH s AS (SELECT date_trunc('hour', '2022-8-10'::date) - generate_series(0, 24 * 7) * interval '1 hour' AS h_slot
)
,avlbl AS (
  SELECT DATE_TRUNC('hour', "sale_start") intrvl
      ,count(*) as cnt_availible 
       FROM sales
       WHERE "sale_start" >= '2022-08-10'::date - INTERVAL '1 week'
       AND "sale_start" <= '2023-08-10'
  group by DATE_TRUNC('hour', "sale_start")
)
,dtgr AS (
  SELECT coalesce(DATE_TRUNC('hour', "sale_cancelled")
                   ,DATE_TRUNC('hour', "sale_completed")) dthour
         ,count(sale_cancelled) cnt_cancelled
         ,count(sale_completed) cnt_completed
       FROM sales
       WHERE "sale_start" >= '2022-08-10'::date - INTERVAL '1 week'
       AND "sale_start" <= '2023-08-10'
  group by coalesce(DATE_TRUNC('hour', "sale_cancelled")
                   ,DATE_TRUNC('hour', "sale_completed"))
)
SELECT * 
FROM s 
left JOIN dtgr d on d.dthour=s.h_slot
left join avlbl a on a.intrvl=s.h_slot
ORDER BY s.h_slot

型

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 如何在表中对每天每个小时的数据进行分组？

1条答案

相关问题

热门标签

最新问答