基于单个分组事件构建具有累计和的时间序列
首先,我需要强调的是,我对SQL非常陌生,当前状态是在堆栈溢出的帮助下实现的.
我有一个表格,其中包含发生的不同类型的事件.我想将这些数据转换成时间序列,填补事件之间的时间间隔,并累积不同类型事件的数字.
因此,这一切都是为了从单个事件创建时间序列,并计算跨事件组的运行/累积总和
下面是源数据的示例:
资料来源:
event_timestamp type value
01.01.2023 10:00 1 10
03.01.2023 10:00 2 10
05.01.2023 10:00 2 10
07.01.2023 10:00 1 10
预期结果:
event_timestamp type value cumulative_sum
01.01.2023 10:00 1 10 10
02.01.2023 10:00 1 0 10
03.01.2023 10:00 1 0 10
03.01.2023 10:00 2 10 10
04.01.2023 10:00 1 0 10
04.01.2023 10:00 2 0 10
05.01.2023 10:00 1 0 10
05.01.2023 10:00 2 10 20
06.01.2023 10:00 1 0 10
06.01.2023 10:00 2 0 20
07.01.2023 10:00 1 10 20
07.01.2023 10:00 2 0 20
我达到了一个点,在那里我可以创建以下内容(仅限于单个事件类型):
time type value cumulative_sum
01.01.2023 10:00 1 10 10
02.01.2023 10:00 1 0 10
03.01.2023 10:00 1 10 20
04.01.2023 10:00 1 0 20
05.01.2023 10:00 1 0 20
06.01.2023 10:00 1 0 20
07.01.2023 10:00 1 0 20
使用以下SQL语句(PostgreSQL):
SELECT
generate_series AS timestamp,
-- hard coded event type below
COALESCE(events.type, 1) AS type,
COALESCE(events.value, 0) AS value,
COALESCE(SUM(td.value) OVER (ORDER BY generate_series), 0) AS cumulative_sum
FROM
generate_series('2023-01-01'::timestamp, '2023-01-07'::timestamp, '1 day') AS generate_series
LEFT JOIN
-- hard coded event type below
events ON generate_series = events.event_timestamp AND event.type = 1
ORDER BY
generate_series;
现在我在问,我是否应该try 找到以更好的方式执行所有操作的SQL语句(不知道如何执行),或者是否应该使用一个python脚本来针对每个事件类型运行此语句,然后将数据插入到表中.
问题摘要:
- 使用SQL和Python的组合来进行这样的计算是否可取?
- 把时间系列的创作和累计总和分开会不会更好?
- 如果建议使用纯SQL方式,考虑到组,如何做到这一点