复杂SQL查询案例分析：计算每个月的累积唯一用户数

复杂SQL查询案例分析：计算每个月的累积唯一用户数

题目要求

在这道SQL面试题中，我们需要解决一个关于用户行为数据的查询问题。具体要求如下：

1. 元数据格式：数据由三列组成，分别是年份(year)、月份(month)和用户ID(userid)。每一行数据代表某个用户在某年某月的行为记录。给出的示例数据如下：

   2020 1 a
   2020 2 a
   2020 2 b
   2020 2 c
   2020 3 a
   2020 3 d
   

2. 目标：根据上述数据，计算每个月的累积唯一用户数。累积唯一用户数指的是从开始月份到当前月份为止，所有出现过的唯一用户的数量。例如，用户a在2020年1月首次出现，那么从2020年1月开始的所有月份中，都要将用户a计入累积的唯一用户数中。

3. 期望的输出格式：输出应包含三列，分别是年份(year)、月份(month)、以及累积的唯一用户数。根据给定的元数据，期望的SQL查询结果如下：

   2020 1 1
   2020 2 3
   2020 3 4
   

在这个结果中：

• 2020年1月有1个唯一用户，即a。
• 2020年2月累积的唯一用户数为3个，分别是a、b和c。
• 2020年3月累积的唯一用户数增加到4个，新增了用户d。

建表语句

要执行以上SQL查询，需要首先创建一个数据表并插入给定的数据。建表语句如下：

CREATE TABLE src (
    year INT,
    month INT,
    userid VARCHAR(10)
);

INSERT INTO src (year, month, userid) VALUES
(2020, 1, 'a'),
(2020, 2, 'a'),
(2020, 2, 'b'),
(2020, 2, 'c'),
(2020, 3, 'a'),
(2020, 3, 'd');

题目解析

1. 数据结构和需求

给定的数据包含三列：年份(year)、月份(month)和用户ID(userid)。我们需要按月计算累积的唯一用户数量。累积的意思是前几个月出现的所有唯一用户都要包含在当前月的统计中。

2. SQL逻辑分解

为了实现目标查询，SQL分为两个主要部分：

1. monthly_unique_users CTE (Common Table Expression)：
   这个CTE用于生成一个带有行号(rn)的表。ROW_NUMBER函数按照用户ID进行分区（PARTITION BY userid），并根据年份和月份进行排序（ORDER BY year, month）。行号rn = 1表示每个用户第一次出现的记录。

   WITH monthly_unique_users AS (
       SELECT
           year,
           month,
           userid,
           ROW_NUMBER() OVER (PARTITION BY userid ORDER BY year, month) AS rn
       FROM
           src
   )
   

2. unique_user_counts CTE：
   这个CTE用于统计每个月的唯一用户数。由于我们只关心每个用户首次出现的月份，所以在计算时我们仅选择rn = 1的记录。然后，通过GROUP BY year, month来统计每个月的唯一用户数量。

   , unique_user_counts AS (
       SELECT
           year,
           month,
           COUNT(DISTINCT userid) AS unique_users
       FROM
           monthly_unique_users
       WHERE
           rn = 1
       GROUP BY
           year,
           month
   )
   

3. 最终查询：
   最终查询使用窗口函数SUM() OVER来计算累积的唯一用户数量。这个函数按年份和月份排序，并将每个月的唯一用户数加起来。

   SELECT
       year,
       month,
       SUM(unique_users) OVER (ORDER BY year, month) AS cumulative_unique_users
   FROM
       unique_user_counts
   ORDER BY
       year,
       month;
   

参考链接

• SQL ROW_NUMBER() 函数
• SQL 窗口函数