如何编写SQL统计user_id在多周内的出现频次分布？

背景 #

我有一张简化后的events表，结构如下：

event_time timestamp with time zone NOT NULL,
user_id character varying(100) NOT NULL,
... -- 其他字段

事件为用户点击网页内容，样本约10万用户，使用行为各异：仅使用一次、频繁使用或间歇性爆发式使用（如连续2周频繁使用，之后2周无操作，再连续2周使用）。

示例数据：

user_id | event_time
      1 | 2022-06-20 00:00:00+00
      2 | 2022-06-21 00:01:00+00
      1 | 2022-06-24 00:00:00+00
      1 | 2022-07-01 00:02:34+00
      3 | 2022-07-01 00:03:45+00
      1 | 2022-07-18 00:00:00+00
      3 | 2022-07-19 01:00:00+00

问题 #

如何编写SQL查询，统计user_id在多周内的出现频次分布？

需求示例 #

查询需返回至少在1周、2周、3周、4周及4周以上出现的user_id数量，输出格式如下：

one_occurrence | two_occurrences | three_occurrences | four_occurrences | more_than_four
             1 |               1 |                 1 |                0 |              0     

例如user_id1有4条事件记录，但仅计为3次出现，原因：

• 1 - 6/20所在周内两次点击，计1次
• 1 - 7/01所在周内1次点击，计1次
• 1 - 7/18所在周内1次点击，计1次
  = user_id 1共在3个不同周内至少点击1次。

所有出现周数超过4的user_id将归为一组，其余用户的统计逻辑同理。

解决方案 #

可以通过分两步的SQL逻辑实现需求：

1. 先统计每个用户有行为记录的不同周数
2. 对统计结果进行分类计数，转换为要求的列格式

具体SQL（以PostgreSQL为例）

SELECT
    SUM(CASE WHEN week_count = 1 THEN 1 ELSE 0 END) AS one_occurrence,
    SUM(CASE WHEN week_count = 2 THEN 1 ELSE 0 END) AS two_occurrences,
    SUM(CASE WHEN week_count = 3 THEN 1 ELSE 0 END) AS three_occurrences,
    SUM(CASE WHEN week_count = 4 THEN 1 ELSE 0 END) AS four_occurrences,
    SUM(CASE WHEN week_count > 4 THEN 1 ELSE 0 END) AS more_than_four
FROM (
    SELECT
        user_id,
        COUNT(DISTINCT DATE_TRUNC('week', event_time)) AS week_count
    FROM events
    GROUP BY user_id
) AS user_week_counts;

逻辑说明

• 内层子查询user_week_counts：用DATE_TRUNC('week', event_time)将事件时间截断到周级别，再通过COUNT(DISTINCT ...)统计每个用户有行为的不同周数。
• 外层查询：用CASE语句对每个用户的周数进行分类，通过SUM统计每一类的用户数量，最终输出要求的列格式。

注意：不同数据库的周处理函数有差异，比如MySQL用WEEK(event_time)，SQL Server用DATEPART(week, event_time)，需根据实际使用的数据库调整。

内容的提问来源于stack exchange，提问作者Switch386