在postgresql中获取特定时间段内的每日查询结果

Question

我在 postgresql 数据库中有一个名为 order 的表。所有订单相关信息都存储在其中。现在，如果订单被拒绝，则某些订单行将从订单表中移出并存储在rejected_orders 表中。因此，计数函数无法提供正确的订单数。

现在，如果我想获取某一天的订单请求数量。我必须减去当天最后一个订单和当天第一个订单之间的 ID 号。下面，我查询了 2022 年 3 月 1 日的总请求数。 遗憾的是，前任员工忘记在数据库中正确保存时区。数据以UTC+00时区保存在数据库中，获取的数据需要在GMT+06时区。

select 
(select id from orders
 where created_at<'2022-03-02 00:00:00+06' 
order by created_at desc limit 1
) 
- 
(select id from orders 
where created_at>='2022-03-01 00:00:00+06' 
order by created_at limit 1
) as march_1st;



march_1st 
-----------
185

现在， 如果我想获取特定时间段内每天的总请求数（假设为 2021 年 3 月）。如何在一个 sql 查询中做到这一点，而不必每天编写一个查询？

总结一下，

total_request_per_day = 当日最后一个订单的 ID - 第一个订单的 ID 当天的顺序。

如何根据该逻辑编写查询，该逻辑将为我提供特定月份中每一天的total_request_per_day。 像这样，

|Date       | total requests|
|01-03-2022 | 187           |
|02-03-2022 | 202           |
|03-03-2022 | 227           |
................
................

Answer 1

恕我直言，使用 id 数字来确定一段时间内的行数是不正确的。删除行会在 id 编号序列中留下间隙；它们不是为此目的而设计的。

这是 date_trunc()、COUNT(*) 和 GROUP BY 的工作。

date_trunc('day',created_at) 函数将任意时间戳转换为当天的午夜。例如，它将“2022-03-02 16:41:00转换为2022-03-02 00:00:00”。使用它我们可以这样编写查询。

SELECT COUNT(*) order_count, 
       date_trunc('day', created_at) day
  FROM orders
 WHERE created_at >= date_trunc('day', NOW()) - INTERVAL '7 day'
   AND created_at <  date_trunc('day', NOW())
 GROUP BY date_trunc('day', created_at)

此查询给出过去 7 天内每天的订单数。

您花在学习如何使用这样的 SQL 数据运算上的每一分钟都将在您节省的工作时间中得到回报。

Answer 2

试试这个：

SELECT  d.ref_date :: date AS "date"
     , count(*) AS "total requests"
  FROM generate_series('20220301' :: timestamp, '20220331' :: timestamp, '1 day') AS d(ref_date)
  LEFT JOIN orders
    ON date_trunc('day', d.ref_date) = date_trunc('day', created_at)
 GROUP BY d.ref_date

• generate_series() 生成您可以在其中的参考日期列表
  想要统计订单数量

• 然后，您只需将参考日期与年/月/日的 created_at 日期进行比较即可加入 orders 表。 LEFT JOIN 允许您选择没有现有订单的参考日。

• 最后，您通过按参考日分组来计算每天的订单数。