2023年7月31日 21:12:52go评论89阅读模式

英文:

How I can group the data for each hour of each day in a table?

问题

大家好！
请告诉我如何在表格中为每天的每个小时分组数据？
我有一个名为 sales 的表。该表包含以下数据：
sale_id - 销售的ID。
sale_start - 销售流程开始的时间（时间戳）。
sale_canceled - 销售取消的时间（时间戳）。
sale_completed - 销售完成的时间（时间戳）。

...
我需要计算每周每个时间小时内有多少销售和可用销售数量。

总的来说，现在我有一个 PostgreSQL 查询，当我使用 "LIMIT" 时它能够工作。否则，请求会崩溃。
PostgreSQL 查询：

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

英文:

Hi everybody!
Please tell me how I can group the data for each hour of each day in a table?
I have a table with the name sales. This table contains the data:
sale_id - id of the sale.
sale_start - when the sale process was started (timestamp).
sale_canceled - when the sale was canceled(timestamp).
sale_completed - when the sale was made (timestamp).
table

...
I need to calculate how many sales have been made and are available at every hour of time during the week.

In general, now I have a postgresql-query and it works when I use "LIMIT". Otherwise, the request crashes.
Postgresql-query:

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

答案1

得分: 1

请注意以下片段：

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表中记录数）的系列。

然后与dt (sales)进行“cross join”。

总记录数为7 * 24 （sales表中记录数）（sales中销售周的时间间隔的记录数）。

首先，移除“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_available 
       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

英文:

Pay attention to the fragment:

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

There you generate series of size= 7 * 24 *(count of records in table sales).

Then cross join with dt (sales).
Total number of records is
7 * 24 *(count of records in table sales) * (count of records in interval (week) of sales)

First, remove FROM Sales.
This may solve your problem.

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

For example, try this.

WITH s AS (SELECT date_trunc(&#39;hour&#39;, &#39;2022-8-10&#39;::date) - generate_series(0, 24 * 7) * interval &#39;1 hour&#39; AS h_slot
)
,avlbl AS (
  SELECT DATE_TRUNC(&#39;hour&#39;, &quot;sale_start&quot;) intrvl
      ,count(*) as cnt_availible 
       FROM sales
       WHERE &quot;sale_start&quot; &gt;= &#39;2022-08-10&#39;::date - INTERVAL &#39;1 week&#39;
       AND &quot;sale_start&quot; &lt;= &#39;2023-08-10&#39;
  group by DATE_TRUNC(&#39;hour&#39;, &quot;sale_start&quot;)
)
,dtgr AS (
  SELECT coalesce(DATE_TRUNC(&#39;hour&#39;, &quot;sale_cancelled&quot;)
                   ,DATE_TRUNC(&#39;hour&#39;, &quot;sale_completed&quot;)) dthour
         ,count(sale_cancelled) cnt_cancelled
         ,count(sale_completed) cnt_completed
       FROM sales
       WHERE &quot;sale_start&quot; &gt;= &#39;2022-08-10&#39;::date - INTERVAL &#39;1 week&#39;
       AND &quot;sale_start&quot; &lt;= &#39;2023-08-10&#39;
  group by coalesce(DATE_TRUNC(&#39;hour&#39;, &quot;sale_cancelled&quot;)
                   ,DATE_TRUNC(&#39;hour&#39;, &quot;sale_completed&quot;))
)
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

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

如何在表格中按每天的每个小时分组数据？

问题

答案1

我在安装PostgreSQL时遇到了错误，我使用的是Ubuntu 22。

在Golang中向预处理语句传递字符串

在AGE中，是否有一种方法可以查询单个列中的顶点和边缘？

如何从表中获取与查询选择的记录相关的查询条件的数据

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。