如何在单条SQL查询中统计上周与本周的独立用户数？

核心思路 #

通过条件聚合（结合COUNT(DISTINCT)和CASE语句），在同一条查询中分别统计两周的独立用户数，同时可通过日期函数动态计算周范围，避免硬编码日期。

方法1：固定日期范围（匹配你原有的硬编码方式） #

直接指定两周的日期区间，用CASE分别筛选对应周的用户并去重计数：

SELECT
  COUNT(DISTINCT CASE WHEN f.date BETWEEN '2023-11-06' AND '2023-11-12' THEN f.user_key END) AS 上周独立用户数,
  COUNT(DISTINCT CASE WHEN f.date BETWEEN '2023-11-13' AND '2023-11-19' THEN f.user_key END) AS 本周独立用户数
FROM
  `Analytics.warehouse.Fact_view` f
WHERE
  f.date BETWEEN '2023-11-06' AND '2023-11-19' -- 仅扫描两周数据，提升查询效率

方法2：动态计算周范围（无需手动修改日期） #

利用BigQuery的日期函数自动计算上周和本周的区间，适合定期自动执行的场景：

SELECT
  -- 上周：当前日期往前推14天到8天（假设本周是近7天）
  COUNT(DISTINCT CASE WHEN f.date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) THEN f.user_key END) AS 上周独立用户数,
  -- 本周：当前日期往前推7天到今天
  COUNT(DISTINCT CASE WHEN f.date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() THEN f.user_key END) AS 本周独立用户数
FROM
  `Analytics.warehouse.Fact_view` f
WHERE
  f.date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE()

方法3：按标准业务周统计（如周一到周日） #

如果你的业务周有固定起始日（比如周一），用DATE_TRUNC生成标准周的起止日期，确保统计范围准确：

WITH date_ranges AS (
  SELECT
    -- 上周的周一到周日
    DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK), WEEK(MONDAY)) AS 上周开始,
    DATE_ADD(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK), WEEK(MONDAY)), INTERVAL 6 DAY) AS 上周结束,
    -- 本周的周一到周日
    DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)) AS 本周开始,
    DATE_ADD(DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)), INTERVAL 6 DAY) AS 本周结束
)
SELECT
  COUNT(DISTINCT CASE WHEN f.date BETWEEN dr.上周开始 AND dr.上周结束 THEN f.user_key END) AS 上周独立用户数,
  COUNT(DISTINCT CASE WHEN f.date BETWEEN dr.本周开始 AND dr.本周结束 THEN f.user_key END) AS 本周独立用户数
FROM
  `Analytics.warehouse.Fact_view` f
CROSS JOIN date_ranges dr
WHERE
  f.date BETWEEN dr.上周开始 AND dr.本周结束

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