在Amazon Quicksight自定义SQL中按类别计算7日百分比变化

问题背景 #

我正在用Amazon Quicksight可视化音乐流数据，已经通过自定义SQL把流媒体服务的源分类转换成内部类别，现在能展示各源类别随时间的播放量。现在想给每个类别加7日百分比变化列，但不知道怎么实现。

分类播放量的原SQL #

(SELECT
    count(*) as streams,
    internal.isrc_map.isrc as isrc,
    service.streams.country as country,
    service.source as original_source,
CASE
        WHEN service.streams.source = 'radio' THEN 'Category 1'
        WHEN service.streams.source IN ('queue', 'other') THEN 'Category 2'
        WHEN service.streams.source = 'collection' THEN 'Category 3'
        WHEN service.streams.source IN ('release', 'search', 'artist') THEN 'Category 4'
        WHEN service.streams.source = 'top20' THEN 'Category 5'
        WHEN service.streams.source = 'others' AND service.streams.source_url = '' THEN 'Category 5' 
        WHEN service.streams.source = 'others' AND service.streams.source_url LIKE '%playlist%' THEN 'Category 5'
        WHEN service.streams.source = 'others' AND service.streams.source_url != '' AND service.streams.source_url NOT LIKE '%playlist%'  THEN 'Category 1'
        ELSE 'Unidentified'
    END as source,
    service.streams.a_data_date as date
FROM
    service.streams
JOIN service.tracks ON
    service.streams.track_id = service.tracks.track_id
JOIN internal.isrc_map ON
    internal.isrc_map.isrc = service.tracks.isrc
WHERE
    client_id = 'client23'
GROUP BY
    service.streams.a_data_date,
    internal.isrc_map.isrc,
    service.streams.country,
    service.streams.source_url,
    service.streams.source
ORDER BY
    internal.isrc_map.isrc,
    service.streams.a_data_date)

尝试代码及报错 #

我是SQL新手，尝试计算总播放量的简单百分比变化时，Quicksight一直报语法错误：

错误：在"7"附近存在语法错误，位置：192

尝试的SQL代码：

WITH last_7_days AS (
  SELECT 
    count(*) as streams, 
    DATE_TRUNC('day', service.streams.a_data_date) as date
  FROM 
    service.streams
  WHERE 
    date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
  GROUP BY date
)
prev_7_days AS (
  SELECT 
    count(*) as streams, 
    DATE_TRUNC('day', service.streams.a_data_date) as date
  FROM 
    service.streams
  WHERE 
    date >= DATE_SUB(NOW(), INTERVAL 14 DAY)
    AND date < DATE_SUB(NOW(), INTERVAL 7 DAY)
  GROUP BY 
    date
) SELECT 
  last_7_days.streams,
  last_7_days.streams / prev_7_days.streams - 1 as pct_change
FROM 
  last_7_days

解决方案 #

1. 报错原因分析 #

• 语法错误来自INTERVAL写法不符合Quicksight常用引擎（Athena/Redshift）的规范，且DATE_SUB并非所有引擎支持
• WHERE子句中直接使用SELECT的别名date，但SQL执行顺序中WHERE早于SELECT，别名未生成
• 两个CTE未关联，会产生笛卡尔积，无法正确匹配对应周期的数据
• 未保留分类维度，无法按每个类别计算变化

2. 完整实现SQL（按类别+日期计算7日同比） #

基于你的原分类逻辑，用窗口函数实现高效的7日百分比变化计算：

WITH categorized_streams AS (
    SELECT
        count(*) as streams,
        CASE
            WHEN service.streams.source = 'radio' THEN 'Category 1'
            WHEN service.streams.source IN ('queue', 'other') THEN 'Category 2'
            WHEN service.streams.source = 'collection' THEN 'Category 3'
            WHEN service.streams.source IN ('release', 'search', 'artist') THEN 'Category 4'
            WHEN service.streams.source = 'top20' THEN 'Category 5'
            WHEN service.streams.source = 'others' AND service.streams.source_url = '' THEN 'Category 5' 
            WHEN service.streams.source = 'others' AND service.streams.source_url LIKE '%playlist%' THEN 'Category 5'
            WHEN service.streams.source = 'others' AND service.streams.source_url != '' AND service.streams.source_url NOT LIKE '%playlist%'  THEN 'Category 1'
            ELSE 'Unidentified'
        END as source_category,
        DATE_TRUNC('day', service.streams.a_data_date) as stream_date,
        service.streams.country
    FROM
        service.streams
    JOIN service.tracks ON
        service.streams.track_id = service.tracks.track_id
    JOIN internal.isrc_map ON
        internal.isrc_map.isrc = service.tracks.isrc
    WHERE
        client_id = 'client23'
    GROUP BY
        DATE_TRUNC('day', service.streams.a_data_date),
        service.streams.country,
        service.streams.source_url,
        service.streams.source
),
stream_with_prev AS (
    SELECT
        source_category,
        stream_date,
        country,
        streams,
        -- 获取7天前同类别同国家的播放量
        LAG(streams, 7) OVER (PARTITION BY source_category, country ORDER BY stream_date) AS prev_7day_streams
    FROM categorized_streams
)
SELECT
    source_category,
    stream_date,
    country,
    streams,
    prev_7day_streams,
    -- 计算百分比变化，处理除数为0的情况
    CASE 
        WHEN prev_7day_streams = 0 THEN NULL
        ELSE ROUND((streams::FLOAT / prev_7day_streams - 1) * 100, 2) 
    END AS pct_change_7day
FROM stream_with_prev
ORDER BY source_category, country, stream_date;

关键说明 #

• 使用LAG()窗口函数，按类别+国家分组、日期排序，直接取7天前的播放量，比CTE关联更高效
• 用CASE处理除数为0的场景，避免报错
• 将整数转换为FLOAT确保除法得到小数结果，再乘以100得到百分比并保留两位小数
• 保留country维度，不需要可直接删除对应分组和字段

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