每个日期时间用多行填充历史postgresql表所需的建议

Question

我有一个SQL查询，它每小时生成多个行，其中包含市场信息(每个市场一行)，例如当时从一个表中累积的唯一客户数量，该表如下所示，后面跟着查询:

tbody> <<tr>

organization_id	transaction_id	transaction_date_time	market
6789	80031	2021-06-07 33	3
6789	80032	2021-06-07 15:34	3
6789	80033	2021-06-07 44	3
6789	80034	2021-06-07 15分	3
1234	80035	2021-06-17 14:07	1
1234	80036	2021-06-17 14:07	1
4321	80037	2021-07-05 11:51	2
4321	80038	2021-07-05 11:51	2
1234	80039	2021-07-13 15:41	1
1234	80040	2021-07-14 09:41	1
1234	80041	2021-07-14 09:55	1

如果这是您的原始查询:

select date_trunc('hour', current_timestamp) as date_time,
ct.country_code as market,
count(distinct ct.organization_id) as acc_alltime_organizations,
count(distinct case when ct.transaction_date_time >= current_date - interval '30' day then ct.organization_id end) as acc_1m_organizations
from customer_transactions ct
group by ct.country_code;

然后您可以使用

修改它的小时范围:

select h.date_time,
t.country_code as market,
count(distinct ct.organization_id) as acc_alltime_organizations,
count(distinct case when ct.transaction_date_time >= h.date_time - interval '30' day then ct.organization_id end) as acc_1m_organizations
from (select generate_series(min(ct.transaction_date_time), current_date, interval '1 hour') as date_time
from customer_transactions ct
) h left join
customer_transactions ct
on ct.transaction_date_time <= h.date_time
group by h.date_time, ct.country_code

可能有更有效的方法来计算你想要的，但这回答了你所问的问题。您可能想要问一个新的问题，其中包含示例数据、期望的结果以及对所需逻辑的清晰解释。