如何统计一周内会员与临时用户使用频次及SQL报错排查

需求说明 #

现有2022-01至2023-05的数据集，需从started_at时间戳提取星期信息，关联member_casual会员类型，统计一周内会员（member）与临时用户（casual）的产品使用频次。

示例数据 #

原代码错误分析 #

你提供的SQL存在以下几个问题导致报错：

• 子查询的CASE语句中直接引用了同层SELECT定义的day_of_week，SQL中同层SELECT的字段无法互相引用，必须先计算出星期数再进行转换；
• 子查询没有选择member_casual字段，外层GROUP BY时无法识别该字段；
• 外层使用SELECT *配合GROUP BY member_casual不符合聚合逻辑，需要使用聚合函数（如COUNT）统计频次；
• CASE语句的ELSE分支引用了未定义的weekday字段。

修正后的SQL代码 #

SELECT
  day_name,
  member_casual,
  COUNT(*) AS usage_count
FROM (
  SELECT
    member_casual,
    CASE EXTRACT(DAYOFWEEK FROM started_at)
      WHEN 1 THEN 'Sunday'
      WHEN 2 THEN 'Monday'
      WHEN 3 THEN 'Tuesday'
      WHEN 4 THEN 'Wednesday'
      WHEN 5 THEN 'Thursday'
      WHEN 6 THEN 'Friday'
      WHEN 7 THEN 'Saturday'
      ELSE 'Unknown'
    END AS day_name
  FROM `your_table_name` -- 替换为实际表名
  WHERE started_at BETWEEN '2022-01-01' AND '2023-05-31' -- 过滤目标时间范围
)
GROUP BY day_name, member_casual
ORDER BY 
  CASE day_name
    WHEN 'Monday' THEN 1
    WHEN 'Tuesday' THEN 2
    WHEN 'Wednesday' THEN 3
    WHEN 'Thursday' THEN 4
    WHEN 'Friday' THEN 5
    WHEN 'Saturday' THEN 6
    WHEN 'Sunday' THEN 7
  END, member_casual;

代码说明 #

1. 子查询部分：同步提取member_casual字段，直接基于EXTRACT的结果做星期名称转换，避免同层字段引用问题；添加时间过滤条件，确保仅统计2022-01至2023-05的数据；
2. 外层查询：按星期名称和会员类型分组，用COUNT(*)统计每组的使用频次；
3. 排序部分：通过CASE语句定义星期排序规则，让结果按周一到周日的逻辑顺序展示，更符合日常阅读习惯。

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