如何通过SQL计算不同user_type每周各天的平均骑行次数？

要得到包含day_of_week、avg_num_member_trips、avg_num_casual_trips的结果表，可通过先统计每日骑行次数，再按星期几求均值的方式实现，以下是适配主流数据库的SQL语句：

WITH daily_trips AS (
    SELECT
        -- 根据使用的数据库选择对应的星期提取函数，以下是常见示例：
        -- PostgreSQL: TO_CHAR(started_at, 'Day') AS day_of_week, EXTRACT(DOW FROM started_at) AS day_num
        -- MySQL: DAYNAME(started_at) AS day_of_week, WEEKDAY(started_at) AS day_num
        -- SQL Server: DATENAME(weekday, started_at) AS day_of_week, DATEPART(weekday, started_at) AS day_num
        DAYNAME(started_at) AS day_of_week,
        WEEKDAY(started_at) AS day_num, -- 用于排序，确保星期顺序符合习惯
        COUNT(CASE WHEN user_type = 'member' THEN trip_id END) AS member_trips,
        COUNT(CASE WHEN user_type = 'casual' THEN trip_id END) AS casual_trips
    FROM bike_trip_2019
    GROUP BY DATE(started_at), day_of_week, day_num
)
SELECT
    day_of_week,
    ROUND(AVG(member_trips), 2) AS avg_num_member_trips, -- 保留两位小数，可按需调整
    ROUND(AVG(casual_trips), 2) AS avg_num_casual_trips
FROM daily_trips
GROUP BY day_of_week, day_num
ORDER BY day_num;

语句说明： #

1. CTE daily_trips：按自然日分组，统计当天会员和临时用户的骑行总次数，同时提取星期几的名称和排序用的数字（避免名称排序导致星期顺序混乱，比如"Friday"排在"Monday"前面）。
2. 外层查询：对每个星期几，计算所有对应日期的日均骑行次数，并用ROUND函数控制小数位数（可选），最后按星期数字排序输出。

注意事项： #

不同数据库的日期处理函数有差异，需根据实际使用的数据库替换注释中的对应函数：

• PostgreSQL：用TO_CHAR(started_at, 'Day')获取星期名称，EXTRACT(DOW FROM started_at)获取星期数字（0=周日，1=周一…6=周六）
• MySQL：DAYNAME(started_at)获取星期名称，WEEKDAY(started_at)获取星期数字（0=周一，1=周二…6=周日）
• SQL Server：DATENAME(weekday, started_at)获取星期名称，DATEPART(weekday, started_at)获取星期数字（1=周日，2=周一…7=周六）

内容的提问来源于stack exchange，提问作者Abi Rizki Alviandri