获取周均评分持续提升的网约车司机SQL查询需求

假设我们有一个名为driver_rating的表，存储了以下出行评分数据：

需要编写SQL查询，获取每周平均评分持续提升的司机详情，要求计算司机评分的最近7天平均值，最终仅为符合条件的司机返回单行结果，格式示例：Driver ID: b, Trip Date: 23-07-19, Weekly Average: 3。

以下是实现需求的SQL查询：

WITH driver_weekly_avg AS (
    SELECT
        driverid,
        date,
        AVG(driver_rating) OVER (
            PARTITION BY driverid
            ORDER BY date
            RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
        ) AS weekly_avg,
        ROW_NUMBER() OVER (PARTITION BY driverid ORDER BY date DESC) AS rn
    FROM driver_rating
    -- 统一driverid大小写，避免a和A被识别为不同司机
    WHERE LOWER(driverid) = driverid
),
driver_trend_check AS (
    SELECT
        driverid,
        weekly_avg,
        -- 对比当前周平均与上一周平均，判断是否递增
        weekly_avg > LAG(weekly_avg) OVER (PARTITION BY driverid ORDER BY date) AS is_increasing
    FROM driver_weekly_avg
)
SELECT
    CONCAT(
        'Driver ID: ', driverid,
        ', Trip Date: ', TO_CHAR(date, 'DD-MM-YY'),
        ', Weekly Average: ', ROUND(weekly_avg)
    ) AS driver_result
FROM driver_weekly_avg
WHERE driverid IN (
    SELECT driverid
    FROM driver_trend_check
    -- 仅保留有多个评分记录且所有周平均都持续递增的司机
    GROUP BY driverid
    HAVING COUNT(*) > 1 AND MIN(is_increasing) = TRUE
)
AND rn = 1; -- 取司机的最新记录

逻辑说明 #

1. 计算滚动7天平均：通过driver_weekly_avg CTE，使用窗口函数AVG() OVER()实现按司机分组、按日期排序的滚动7天平均评分，同时标记每条记录是否为该司机的最新记录。
2. 判断评分趋势：driver_trend_check CTE利用LAG()函数获取前一周的平均评分，对比当前周平均是否更高，生成递增标记。
3. 筛选符合条件的司机：通过子查询筛选出所有评分记录均满足递增趋势的司机，最后取这些司机的最新记录并按指定格式输出。

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