如果零日不生成行/数据，如何获得每人每天的平均活动?

Question

我正在做一些关于网站订阅者活动的研究。具体来说，我希望看到2018年每个订阅者每天的平均点击次数。不幸的是，提供给我的数据表没有考虑订阅者根本不与网站互动的天数，但我需要考虑零日。

如果我以以下内容开始查询：

SELECT SubscriberID, date_trunc('Day', Date_of_Activity), count(*) as Clicks 
FROM WSD.Clicks 
WHERE Date_Of_Activity between date('2018-01-01') and date('2019-01-01') 
GROUP BY 1,2

。每个订阅者只有在该日期确认点击时，才会有一个特定日历日的条目;否则，不会在源数据中生成任何行。这会导致平均通货膨胀，因为它只在潜艇活跃时考虑它们;一个人在一年中的一天使用该网站并获得 2 次点击现在等于一个人一年中有 300 天每天点击两次。如何使查询为每个订阅者声明一个日历日，并在他们实际上没有记录活动的情况下将值归为"0"？

作为参考，此表只有几列：

订阅者 ID(字符串)、Date_of_Activity(时间戳)、Type_of_Activity (字符串)

我正在查询雅典娜(AWS)中的数据。

Answer 1

您可以根据日期列表加入。这在Postgres中使用generate_series()很容易完成：

select c.subscriberid, 
d.day::date as date_of_activity, 
count(c.date_of_activity) as clicks 
from generate_series(date '2018-01-01', date '2018-12-31', interval '1' day) as d(day)
left join clicks c on c.date_of_activity::date = d.day::date 
group by 1,2
order by 1,2;

count()忽略null值，当不匹配时，左联接将在clicks列中返回null。因此，count(c.Date_Of_Activity)将在这些日子中返回零。

但是，这也将显示subscriberid的空值。如果要为每个订阅者/date_of_activity组合创建假行，则需要交叉联接到所有订阅者的列表。请注意，这会在结果中为您提供(number of subscribers) * 365行！如果您有数百万订阅者，这可能不是您想要的：

select s.subscriberid, 
d.day::date as date_of_activity, 
count(c.date_of_activity) as clicks 
from generate_series(date '2018-01-01', date '2018-12-31', interval '1' day) as d(day)
cross join subscribers s
left join clicks c on c.date_of_activity::date = d.day::date 
group by 1,2
order by 1,2;

在线示例：https://rextester.com/QIGI84548