Metabase连接Redshift按月聚合数据排序异常问题求助

问题出在你用字符串格式的MM/YYYY来排序，字符串排序是按字符顺序比较的，比如'01/2023'会排在'02/2022'前面，因为'01'的字符顺序比'02'靠前，和实际时间顺序不符。

修改后的SQL语句 #

SELECT 
    combined.value,
    combined.time,
    combined.count
FROM (
    -- 第一个数据集的聚合
    SELECT 
        dataset.value AS value, 
        TO_CHAR(dataset.time::date, 'MM/YYYY') AS time, 
        COUNT(id) AS count,
        DATE_TRUNC('month', dataset.time::date) AS month_date
    FROM  
        dataset
    GROUP BY 
        dataset.value, 
        DATE_TRUNC('month', dataset.time::date),
        TO_CHAR(dataset.time::date, 'MM/YYYY')
    
    UNION ALL
    
    -- 第二个数据集的聚合
    SELECT 
        dataset_2.value_2 AS value, 
        TO_CHAR(dataset_2.time::date, 'MM/YYYY') AS time, 
        COUNT(id) AS count,
        DATE_TRUNC('month', dataset_2.time::date) AS month_date
    FROM 
        dataset_2
    GROUP BY 
        dataset_2.value_2, 
        DATE_TRUNC('month', dataset_2.time::date),
        TO_CHAR(dataset_2.time::date, 'MM/YYYY')
) combined
ORDER BY 
    combined.value ASC, 
    combined.month_date ASC;

关键修改点 #

• 新增month_date字段：用DATE_TRUNC('month', ...)将日期截断到月份，得到一个日期类型的值（例如2022-01-01），这个字段用于排序，保证时间顺序正确。
• 调整GROUP BY：同时包含month_date和字符串格式的time，两者是一一对应的，不会改变聚合结果，但能让我们拿到可靠的排序字段。
• 外层统一排序：子查询内的ORDER BY在UNION ALL时会被忽略，所以必须在合并后的结果集上执行最终排序，用日期类型的month_date代替字符串排序。

简化版（可选） #

如果Redshift支持函数依赖的GROUP BY（大部分情况下支持），可以简化GROUP BY子句，只按value和month_date分组，time直接由month_date转换生成，这样更高效：

SELECT 
    combined.value,
    combined.time,
    combined.count
FROM (
    SELECT 
        dataset.value AS value, 
        TO_CHAR(DATE_TRUNC('month', dataset.time::date), 'MM/YYYY') AS time, 
        COUNT(id) AS count,
        DATE_TRUNC('month', dataset.time::date) AS month_date
    FROM  
        dataset
    GROUP BY 
        dataset.value, 
        DATE_TRUNC('month', dataset.time::date)
    
    UNION ALL
    
    SELECT 
        dataset_2.value_2 AS value, 
        TO_CHAR(DATE_TRUNC('month', dataset_2.time::date), 'MM/YYYY') AS time, 
        COUNT(id) AS count,
        DATE_TRUNC('month', dataset_2.time::date) AS month_date
    FROM 
        dataset_2
    GROUP BY 
        dataset_2.value_2, 
        DATE_TRUNC('month', dataset_2.time::date)
) combined
ORDER BY 
    combined.value ASC, 
    combined.month_date ASC;

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