编写高效Redshift查询获取年度最常用运输方式及数量

我有一个包含3.03亿行数据的Redshift表lineitem，其排序键为l_receiptdate，表中包含l_receiptdate与l_shipmode两个字段，示例数据如下：

我的需求是找出每年使用次数最多的运输方式，返回receiptyear（年份）、shipmode（运输方式）及ship_mode_count（对应次数），预期输出如下：

作为Redshift新手，我希望从一开始就遵循最佳查询实践。目前我写出了部分SQL，但不知道如何继续：

select DATE_TRUNC('year', l_receiptdate) as receiptyear,
       l_shipmode as shipmode,
       count(*) as ship_mode_count
FROM lineitem
group by 1,2

你的现有SQL已经完成了第一步：按年份和运输方式分组统计次数。接下来需要筛选出每年中次数最多的运输方式，同时结合Redshift的特性优化查询性能。

1. 完整SQL实现 #

可以使用窗口函数ROW_NUMBER()（若每年仅需返回一个最多的运输方式，即使有并列）或RANK()（若需返回所有并列第一的运输方式）来完成筛选：

方式一：使用ROW_NUMBER()（仅返回单个最多的运输方式） #

WITH yearly_ship_counts AS (
    SELECT 
        DATE_TRUNC('year', l_receiptdate)::DATE AS receiptyear,
        l_shipmode AS shipmode,
        COUNT(*) AS ship_mode_count
    FROM lineitem
    GROUP BY 1, 2
),
ranked_counts AS (
    SELECT 
        receiptyear,
        shipmode,
        ship_mode_count,
        ROW_NUMBER() OVER (PARTITION BY receiptyear ORDER BY ship_mode_count DESC) AS rn
    FROM yearly_ship_counts
)
SELECT 
    EXTRACT(YEAR FROM receiptyear) AS receiptyear,
    shipmode,
    ship_mode_count
FROM ranked_counts
WHERE rn = 1
ORDER BY receiptyear;

方式二：使用RANK()（返回所有并列第一的运输方式） #

如果存在某一年多个运输方式次数相同且都是最多的情况，用RANK()会保留所有并列项：

WITH yearly_ship_counts AS (
    SELECT 
        DATE_TRUNC('year', l_receiptdate)::DATE AS receiptyear,
        l_shipmode AS shipmode,
        COUNT(*) AS ship_mode_count
    FROM lineitem
    GROUP BY 1, 2
),
ranked_counts AS (
    SELECT 
        receiptyear,
        shipmode,
        ship_mode_count,
        RANK() OVER (PARTITION BY receiptyear ORDER BY ship_mode_count DESC) AS rk
    FROM yearly_ship_counts
)
SELECT 
    EXTRACT(YEAR FROM receiptyear) AS receiptyear,
    shipmode,
    ship_mode_count
FROM ranked_counts
WHERE rk = 1
ORDER BY receiptyear;

2. Redshift最佳实践说明 #

• 利用排序键减少数据扫描：你的表排序键是l_receiptdate，DATE_TRUNC('year', l_receiptdate)会利用排序键的有序性，Redshift可以快速按年份范围扫描数据，避免全表扫描的低效。
• 避免不必要的数据转换：将DATE_TRUNC的结果转为DATE类型（::DATE），可以减少后续窗口函数处理的数据量，提升性能。
• CTE的合理使用：Redshift对CTE的优化较好，将分组统计和排序筛选拆分为多个CTE，逻辑清晰且不会影响性能（Redshift会将CTE合并为执行计划）。
• 排序优化：最终结果按receiptyear排序，符合排序键的顺序，无需额外的排序开销。

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