在原始sql查询的结果中填充空的缺失日期

am46iovg 于 2021-07-26 发布在 Java

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

我有一个类似以下的查询，需要 min_date 以及 max_date .

Select TO_CHAR(date_trunc('hour', table.date), 'YYYY-MM-DD HH24:00') as formatted_date,
count(DISTINCT table.order_id) as unique_orders, sum(table.price) as total_price from table
where table.date>=min_date and table.date<=max_date

result = ActiveRecord::Base.connection.execute(query).values.to_a

这将给我一个订单数和每“小时”的总价。例如，假设这是我的table

<id: 1, order_id: 1, date: 2020-06-10 10:18:01+00, price:10>
<id: 2, order_id: 1, date: 2020-06-10 10:43:19+00, price:14>
<id: 3, order_id: 2, date: 2020-06-10 12:10:00+00, price:15>
<id: 4, order_id: 3, date: 2020-06-10 12:20:21+00, price:10>
<id: 5, order_id: 3, date: 2020-06-10 14:10:54+00, price:20>

还有我的 min_date = 2020-06-10 09:00:00+00 以及 max_date = 2020-06-10 14:00:00+00 . 这就是我的结果：

[[2020-06-10 10:00, 1, 24], [2020-06-10 12:00, 2, 25], [2020-06-10 14:00, 1, 20]]

我想把最短日期和最长日期之间的错失时间包括在内，如下所示：

[[2020-06-10 09:00, 0, 0], [2020-06-10 10:00, 1, 24], [2020-06-10 11:00, 0, 0], [2020-06-10 12:00, 2, 25], [2020-06-10 13:00, 0, 0] [2020-06-10 14:00, 1, 20]]

所以我还需要表中不存在但包含在最小日期和最大日期之间的时间范围内的所有小时数。在这个场景中，这是我想要的所有时间，但可能是所有的“周”或“月”，这取决于我传递给的论点 date_trunc .

sql postgresql ruby-on-rails

来源：https://stackoverflow.com/questions/62400953/fill-empty-missing-dates-in-result-from-raw-sql-query

1条答案

按热度按时间

pgx2nnw81#

使用 generate_series() 计算你想要的所有日期。然后使用 left join 要引入数据：

select to_char(gs.dte, 'YYYY-MM-DD HH24:00') as formatted_date,
       count(distinct t.order_id) as unique_orders, sum(t.price) as total_price
from generate_series(min_date, max_date, interval '1 hour') gs(dte) left join
     table t
     on t.date >= gs.dte and
        t.date < gs.dte + interval '1 hour' and table.date<=max_date
group by gs.dte
order by gs.dte;

赞(0）回复(0）举报 2021-07-26

我来回答

在原始sql查询的结果中填充空的缺失日期

1条答案

相关问题

热门标签

最新问答