如何在Redshift中将状态值转为列名并重构聚合查询结果

问题背景 #

我编写了以下查询，旨在按路线、sla_min统计总业务量（vol）及sla_status。其中sla_status通过CASE WHEN语法计算，得到‘OVER SLA’和‘MEET SLA’两种状态：

with data_manifest as (
select no,
       concat(concat(origin,'-'),destination) as route_city, 
       sla_min,
       case
           when status>0 and datediff(day, sla_max_date_time_internal, last_valid_tracking_date_time) > 0 then 'OVER SLA'
           when status=0 and datediff(day, sla_max_date_time_internal, current_date) > 0 then 'OVER SLA' else 'MEET SLA'
        end as status_sla
from data
where trunc(tgltransaksi::date) between ('30 January,2023') and ('9 February,2023')
), data_vol as (
select
    route_city,
    count(distinct no) as volume,
    status_sla,
    sla_min,
from data_manifest
group by route_city, status_sla, sla_min
)

当前查询结果如下：

route_city     vol      status_sla      sla_min
A - B          20        MEET SLA          2
A - B          40        OVER SLA          2
B - C          30        MEET SLA          1
B - C          30        OVER SLA          1

需求是将‘MEET SLA’和‘OVER SLA’转为列名，得到如下结构的结果：

route_city    MEET SLA   OVER SLA   total_vol    sla_min
A - B          20           40         60           2
B - C          30           30         60           1 

解决方案 #

可以利用Redshift支持的条件聚合结合CASE WHEN实现行转列，同时直接计算总业务量，修改后的查询语句如下：

with data_manifest as (
select no,
       concat(origin,'-',destination) as route_city, -- 简化嵌套concat写法
       sla_min,
       case
           when status>0 and datediff(day, sla_max_date_time_internal, last_valid_tracking_date_time) > 0 then 'OVER SLA'
           when status=0 and datediff(day, sla_max_date_time_internal, current_date) > 0 then 'OVER SLA' 
           else 'MEET SLA'
        end as status_sla
from data
where trunc(tgltransaksi::date) between '2023-01-30' and '2023-02-09' -- 使用标准日期格式避免解析错误
), data_vol as (
select
    route_city,
    sla_min,
    count(distinct case when status_sla = 'MEET SLA' then no end) as "MEET SLA",
    count(distinct case when status_sla = 'OVER SLA' then no end) as "OVER SLA",
    count(distinct no) as total_vol
from data_manifest
group by route_city, sla_min
)
select * from data_vol;

关键说明 #

1. 行转列实现：通过count(distinct case ...)对不同状态的业务量分别统计，将行数据转换为列展示。
2. 语法优化：把嵌套的concat(concat(origin,'-'),destination)简化为concat(origin,'-',destination)，逻辑更清晰。
3. 日期格式规范：将非标准日期字符串改为'2023-01-30'这种ISO标准格式，避免Redshift在日期解析时出现异常。
4. 总业务量计算：直接用count(distinct no)统计单条路线的总业务量，无需额外子查询处理。

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