Postgresql 使用原始 sql 填写缺失的日期

Question

我有下表(简化如下(：

Orders:

<id: 1, shipping: 6.0, price: 20.0>
<id: 2, shipping: 10.0, price: 30.0>
<id: 3, shipping: 7.0, price: 12.0>
<id: 4, shipping: 5.0, price: 0.0> #0 dollars because it was updated after return

Sales：

<id: 1, order_id: 1, price:10.0, qty:2, date: "2020-06-01T01:16:15-04:00">
<id: 2, order_id: 1, price:9.0, qty: 1, date: "2020-06-01T01:16:15-04:00">
<id: 3, order_id: 2, price:15.0, qty:2, date: "2020-06-01T01:23:53-04:00">
<id: 4, order_id: 3, price:4.0, qty: 1, date: "2020-06-01T20:28:18-04:00">
<id: 5, order_id: 3, price:4.0, qty: 2, date: "2020-06-01T20:31:15-04:00">
<id: 6, order_id: 4, price:29.0, qty:1, date: "2020-06-03T20:16:15-04:00">

Refunds：

<id: 1, order_id: 1, qty:1, amount: 9.0, date: "2020-06-01T01:23:15-04:00">
<id: 2, order_id: 4, qty:1, amount: 29.0, date: "2020-06-04T03:34:53-04:00">

我正在编写原始sql来计算按天数分组的shipping(即sum(orders.shipping((，total orders(即COUNT(DISTINCT orders.id((和net sales(即sales.price * sales.qty - COALESCE(refunds.refund_amount，0((。搜索将采用min_date，并在格式上max_date：YYYY-MM-DDThh24:mi:ss过滤掉不在日期范围内的销售或退款。我遇到的问题是使用generate_series将表中不存在的所有日期都设置为 0 添加。因此，如果 min_date = 2020-06-01T00：00：00 且 max_date = 2020-06-05T23：59：59，则示例响应如下所示：

"2020-06-01": {shipping: 6, total_orders: 3, net_sales: 62.0},
"2020-06-02": {shipping: 0, total_orders: 0, net_sales: 0}, --> newly added
"2020-06-03": {shipping: 5, total_orders: 1, net_sales: 29},
"2020-06-04": {shipping: 0, total_orders: 1, net_sales: -29.0},
"2020-06-05": {shipping: 0, total_orders: 0, net_sales: 0} --> newly added.

谁能帮我收到上面想要的结果。我看过示例，但我无法让它适用于我的方案。谢谢！

Answer 1

我认为这可以做你想要的：

select 
d.dt, 
o.shipping,
s.total_orders,
coalesce(s.sales_amount, 0) - coalesce(r.refound_amount, 0) net_sales
from generate_series(?::timestamp, ?::timestamp, interval '1 day') d(dt)
left join lateral (
select 
count(distinct order_id) total_orders,
sum(price * quantity) sales_amount,
array_agg(order_id) order_ids
from sales s
where s.date >= d.dt and s.date < d.dt + interval '1 day'
) s on true
left join lateral (
select sum(o.shipping) shipping
from orders o
where o.id = any(s.order_ids)
) o on true
left join lateral (
select sum(r.amount) refound_amount
from refunds r
where r.order_id = any(s.order_ids)
) r on true

查询首先生成给定间隔内的所有日期(?表示两个日期参数(。

然后，我们使用带有聚合查询的lateral join来获取有关该时间段内发生的所有销售的信息。另一个later join带来与第一个横向连接选择的order_id相对应的运输，另一个带来相应的退款。