基于TRAVELS表创建派生表，计算用户骑行均值、最大值及总量的SQL问题

需求说明 #

• 使用TRAVELS表创建派生表，计算用户的平均骑行次数、单个用户的最大骑行次数以及所有用户的总骑行次数

TRAVELS表结构 #

当前错误查询语句 #

SELECT
  COUNT(TRAVEL_ID) AS NUM,
  DISTINCT USER_ID  
FROM 
  (SELECT 
          AVG(NUM) AS Average,
          MAX(NUM) AS Maximum,
          NUM AS Total
   FROM TRAVELS) AS a;

查询存在的问题 #

• 子查询引用了原表不存在的NUM字段，逻辑不成立；
• 外层查询中DISTINCT关键字位置错误，不符合SQL语法规范；
• 整体逻辑未按需求先统计单个用户的骑行次数，无法得到目标统计指标。

正确查询语句 #

-- 先统计每个用户的骑行次数，生成派生表
WITH user_travel_stats AS (
    SELECT 
        USER_ID,
        COUNT(TRAVEL_ID) AS user_travel_count
    FROM TRAVELS
    GROUP BY USER_ID
)
-- 基于派生表计算整体统计指标
SELECT
    ROUND(AVG(user_travel_count), 4) AS 平均骑行次数,
    MAX(user_travel_count) AS 单用户最大骑行次数,
    SUM(user_travel_count) AS 总骑行次数
FROM user_travel_stats;

预期结果 #

内容的提问来源于stack exchange，提问作者Devin Culp Corasair