请求修改SQL实现按周统计指标并计算周期内周平均值

开发者阿泽

2026-7-8

问题翻译

我是SQL新手，现有一段SQL可以统计各organization_uuid的注册用户数、活跃用户数、活跃用户占比、**人均出行次数（TpR）**指标。现在需要修改这段SQL，先按周统计上述指标，再计算{{start_date}}至{{end_date}}周期内所有周的平均值，请帮忙修改。

原SQL：

WITH RegisteredUsers AS (
    SELECT
        e.organization_uuid,
        COUNT(DISTINCT e.user_uuid) AS TotalRegisteredUsers
    FROM
        u4b.dim_employee AS e
    GROUP BY
        e.organization_uuid
),
ActiveUsers AS (
    SELECT
        e.organization_uuid,
        COUNT(DISTINCT e.user_uuid) AS TotalActiveUsers
    FROM
        u4b.dim_employee AS e
    INNER JOIN uberpool.hcv_fact_trip AS t ON e.user_uuid = t.client_uuid
    WHERE
        fact_trip_status IN ('completed', 'fare_split')
        AND t.datestr BETWEEN '{{start_date}}' AND '{{end_date}}'
    GROUP BY
        e.organization_uuid
),
RiderTrips AS (
    SELECT
        e.organization_uuid,
        COUNT(*) AS RiderTripCount
    FROM
        u4b.dim_employee AS e
        INNER JOIN uberpool.hcv_fact_trip AS t ON e.user_uuid = t.client_uuid
    WHERE
        fact_trip_status IN ('completed', 'fare_split')
        AND t.datestr BETWEEN '{{start_date}}' AND '{{end_date}}'
    GROUP BY
        e.organization_uuid
)
SELECT
    ru.organization_uuid,
    COALESCE(TotalRegisteredUsers, 0) AS "Registered Users",
    COALESCE(TotalActiveUsers, 0) AS "Active Users",
    CASE
        WHEN COALESCE(TotalRegisteredUsers, 0) = 0 THEN 0
        ELSE (COALESCE(TotalActiveUsers, 0) * 100) / COALESCE(TotalRegisteredUsers, 0)
    END AS "Percentage of Active Users",
    COALESCE(RiderTripCount / TotalActiveUsers, 0) AS "Trips per Rider (TpR)"
FROM
    RegisteredUsers ru
LEFT JOIN ActiveUsers au ON ru.organization_uuid = au.organization_uuid
LEFT JOIN RiderTrips rt ON ru.organization_uuid = rt.organization_uuid
ORDER BY "Active Users" DESC;

原输出：

organization_uuid   Registered Users    Active Users    Percentage of Active Users  Trips per Rider (TpR)
23232               41650               2331            5                          44
32325               11662               2133            18                         30
32323               7920                1639            20                         23
56565               2012                773             38                         15
73847               9495                720             7                          16

修改后的SQL（按周统计+周期平均值）

下面的SQL会先输出每个组织的周度指标，再计算整个周期内的周平均指标（注：若使用MySQL等其他数据库，需调整DATE_TRUNC的语法，比如MySQL用DATE_FORMAT(t.datestr, '%Y-%u') AS week_start）：

WITH RegisteredUsers AS (
    -- 全量注册用户（和原逻辑一致，无时间过滤）
    SELECT
        e.organization_uuid,
        COUNT(DISTINCT e.user_uuid) AS TotalRegisteredUsers
    FROM
        u4b.dim_employee AS e
    GROUP BY
        e.organization_uuid
),
WeeklyActiveUsers AS (
    -- 按周统计各组织的活跃用户（当周有完成出行的用户）
    SELECT
        e.organization_uuid,
        DATE_TRUNC(t.datestr, WEEK) AS week_start, -- 提取周起始日，不同数据库语法可能调整
        COUNT(DISTINCT e.user_uuid) AS WeeklyActiveUsers
    FROM
        u4b.dim_employee AS e
    INNER JOIN uberpool.hcv_fact_trip AS t ON e.user_uuid = t.client_uuid
    WHERE
        fact_trip_status IN ('completed', 'fare_split')
        AND t.datestr BETWEEN '{{start_date}}' AND '{{end_date}}'
    GROUP BY
        e.organization_uuid,
        DATE_TRUNC(t.datestr, WEEK)
),
WeeklyRiderTrips AS (
    -- 按周统计各组织的出行总次数
    SELECT
        e.organization_uuid,
        DATE_TRUNC(t.datestr, WEEK) AS week_start,
        COUNT(*) AS WeeklyTripCount
    FROM
        u4b.dim_employee AS e
    INNER JOIN uberpool.hcv_fact_trip AS t ON e.user_uuid = t.client_uuid
    WHERE
        fact_trip_status IN ('completed', 'fare_split')
        AND t.datestr BETWEEN '{{start_date}}' AND '{{end_date}}'
    GROUP BY
        e.organization_uuid,
        DATE_TRUNC(t.datestr, WEEK)
),
WeeklyMetrics AS (
    -- 整合周度所有指标
    SELECT
        ru.organization_uuid,
        wau.week_start,
        COALESCE(ru.TotalRegisteredUsers, 0) AS "Registered Users",
        COALESCE(wau.WeeklyActiveUsers, 0) AS "Weekly Active Users",
        CASE
            WHEN COALESCE(ru.TotalRegisteredUsers, 0) = 0 THEN 0
            ELSE (COALESCE(wau.WeeklyActiveUsers, 0) * 100) / COALESCE(ru.TotalRegisteredUsers, 0)
        END AS "Weekly Percentage of Active Users",
        COALESCE(wrt.WeeklyTripCount / wau.WeeklyActiveUsers, 0) AS "Weekly Trips per Rider (TpR)"
    FROM
        RegisteredUsers ru
    LEFT JOIN WeeklyActiveUsers wau ON ru.organization_uuid = wau.organization_uuid
    LEFT JOIN WeeklyRiderTrips wrt ON ru.organization_uuid = wrt.organization_uuid
        AND wau.week_start = wrt.week_start
)
-- 先输出周度指标，再输出周期平均值（用UNION ALL合并，也可分开查询）
SELECT
    organization_uuid,
    "周度数据" AS metric_type,
    week_start AS period,
    "Registered Users",
    "Weekly Active Users",
    "Weekly Percentage of Active Users",
    "Weekly Trips per Rider (TpR)"
FROM WeeklyMetrics
UNION ALL
SELECT
    organization_uuid,
    "周平均数据" AS metric_type,
    NULL AS period,
    AVG("Registered Users") AS "Average Registered Users", -- 注册用户是全量，平均值和原值一致
    AVG("Weekly Active Users") AS "Average Active Users",
    AVG("Weekly Percentage of Active Users") AS "Average Percentage of Active Users",
    AVG("Weekly Trips per Rider (TpR)") AS "Average Trips per Rider (TpR)"
FROM WeeklyMetrics
GROUP BY organization_uuid
ORDER BY organization_uuid, metric_type;

关键改动说明

新增周维度分组：在WeeklyActiveUsers和WeeklyRiderTrips中，用DATE_TRUNC提取周起始日期作为分组字段，得到每个组织每周的活跃用户和出行数据。
整合周度指标：通过WeeklyMetricsCTE关联注册用户数据，计算每周的完整指标。
计算周平均值：对每个组织的所有周度指标，用AVG()函数计算周期内的平均值，并用UNION ALL将周度数据和平均数据合并展示（也可拆分为两个独立查询）。

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