我查询 YouTube Data Api 以获得频道上最受欢迎的视频列表,然后获取它们的统计数据,每小时 4 次(每 15 分钟一次,由 cron)。数据存储在 Postgres 中,但将其转储并加载到另一个 SQL 数据库中不会有问题。现在我有以下数据表:
video_id| views_count | likes_count | timestamp
---------+-------------+-------------+---------------------
foo | 100 | 1 | 2018-12-01 12:01:03
foo | 101 | 1 | 2018-12-01 12:16:06
foo | 105 | 1 | 2018-12-01 12:31:01
bar | 199 | 0 | 2018-12-01 12:01:02
bar | 200 | 0 | 2018-12-01 12:16:08
bar | 301 | 5 | 2018-12-01 12:31:02
... | ...
UPD:这是架构(粘贴到sqlfiddle):
CREATE TABLE video_statistics
(
video_id TEXT not null,
views_count INTEGER not null,
likes_count INTEGER not null,
timestamp TIMESTAMPTZ not null
);
我应该如何查询该数据以便按小时view_counts
和likes_count
按视频分组的列获得增量?澄清我想要得到的东西:
hour_of_day|video_id|views_increment|likes_increment
-----------+--------+---------------+---------------
... | ...
11 | foo | 4 | 0
12 | foo | 5 | 1
... | ...
11 | bar | 73 | 0
12 | bar | 102 | 5
... | ...
换句话说,这是一个基于历史数据的“发布视频的最佳时间”,并考虑了数周和数月的数据。我应该将数据转储到一些时间序列数据库或其他更适合这种情况的数据库中,然后在那里查询吗?还是我应该求助于用代码计算这个?
一种可能性是首先
row_number()
获取记录以获得每个视频、日期和小时的第一个和最后一个值。然后加入两组first和last值,得到各自的差值。对视频和小时的结果进行分组,并获得每天每个视频的总和或平均值。架构:
猜猜是这样的:
请注意,您必须决定如何处理没有滞后行的行,即每个分区中的第一行。