如何将DROP、CREATE AS SELECT与CTE语法结合使用？

1. 定义查询参数的临时表 #

这部分用于配置查询的核心参数，可根据需求直接修改对应值：

-- 可修改变量的临时表
DROP TABLE IF EXISTS tmp_vars;
CREATE TEMPORARY TABLE tmp_vars AS
SELECT
TO_TIMESTAMP('2023-10-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.MS') AS startDateTime, -- 查询起始时间
TO_TIMESTAMP('2023-10-14 23:59:59.999', 'YYYY-MM-DD HH24:MI:SS.MS') AS endDateTime, -- 查询结束时间
'Sackey' AS tenantName, -- 指定单个租户名称
ARRAY['Bright'] AS groupNameArray, -- 指定分组名称数组
'week' AS groupBy; -- 分组维度，可选值为'day'(按天)、'week'(按周)、'month'(按月)、'year'(按年)

2. 计算中位数平均值的主SQL #

通过CTE、窗口函数实现按指定维度分组，计算已解决工单的首次专家响应时间中位数，并将结果转换为秒级平均值：

-- 创建存储首次专家响应时间中位数的临时表
DROP TABLE IF EXISTS tmp_first_response;
CREATE TEMPORARY TABLE tmp_first_response AS
WITH tmp AS (
    SELECT * FROM tmp_vars -- 引用参数配置临时表
)
SELECT
    CASE
        WHEN mm.groupBy = 'day' THEN mm.created
        WHEN mm.groupBy = 'week' THEN EXTRACT('WEEK' FROM mm.created)
        WHEN mm.groupBy = 'month' THEN EXTRACT('MONTH' FROM mm.created)
        ELSE EXTRACT('YEAR' FROM mm.created)
    END AS "day_week_month_year", -- 分组维度结果字段
    CAST(AVG(mm.expert_response_time)/1000 AS DECIMAL(10,0)) AS first_expert_response_secs_median_avg_resolved -- 中位数平均值（转换为秒）
FROM (
    SELECT
        m.created,
        m.expert_response_time,
        -- 按分组维度对响应时间排序并生成行号
        ROW_NUMBER() OVER (
            PARTITION BY
                CASE
                    WHEN tmp.groupBy = 'day' THEN m.created::varchar
                    WHEN tmp.groupBy = 'week' THEN EXTRACT('WEEK' FROM m.created)::varchar
                    WHEN tmp.groupBy = 'month' THEN EXTRACT('MONTH' FROM m.created)::varchar
                    ELSE EXTRACT('YEAR' FROM m.created)::varchar
                END
            ORDER BY m.expert_response_time
        ) rn,
        -- 统计每个分组的工单总数
        COUNT(*) OVER (
            PARTITION BY
                CASE
                    WHEN tmp.groupBy = 'day' THEN m.created::varchar
                    WHEN tmp.groupBy = 'week' THEN EXTRACT('WEEK' FROM m.created)::varchar
                    WHEN tmp.groupBy = 'month' THEN EXTRACT('MONTH' FROM m.created)::varchar
                    ELSE EXTRACT('YEAR' FROM m.created)::varchar
                END
        ) cnt,
        tmp.groupBy
    FROM message m
    JOIN tmp
        ON m.tenant_name = tmp.tenantName
        AND m.group_name = ANY(tmp.groupNameArray)
        AND m.created >= tmp.startDateTime
        AND m.created <= tmp.endDateTime
        AND m.lifecycle_state = 'resolved' -- 仅统计已解决状态的工单
        AND m.expert_response_time IS NOT NULL -- 排除无响应时间记录的工单
) AS mm
-- 筛选中位数对应的行（偶数取中间两行，奇数取中间一行）
WHERE rn IN (FLOOR((cnt + 1)/2), FLOOR((cnt + 2)/2))
GROUP BY
    CASE
        WHEN mm.groupBy = 'day' THEN mm.created
        WHEN mm.groupBy = 'week' THEN EXTRACT('WEEK' FROM mm.created)
        WHEN mm.groupBy = 'month' THEN EXTRACT('MONTH' FROM mm.created)
        ELSE EXTRACT('YEAR' FROM mm.created)
    END;

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