分组查询时获取商品价格最值对应unique_id的方法咨询

针对5000万条数据的大表，优先推荐窗口函数方案，仅需一次全表扫描即可完成计算，性能最优；同时提供其他备选方案及优化建议：

方案一：窗口函数（推荐） #

利用ROW_NUMBER()窗口函数为每个product分组内的行按价格排序，筛选出价格最小/最大的行后聚合，避免多次扫描表：

WITH ranked_data AS (
    SELECT 
        product,
        price,
        unique_id,
        -- 按价格升序排序，标记最小价格的行
        ROW_NUMBER() OVER (PARTITION BY product ORDER BY price ASC) AS rn_min,
        -- 按价格降序排序，标记最大价格的行
        ROW_NUMBER() OVER (PARTITION BY product ORDER BY price DESC) AS rn_max
    FROM mytable
)
SELECT 
    product,
    MIN(CASE WHEN rn_min = 1 THEN price END) AS min_price,
    MIN(CASE WHEN rn_min = 1 THEN unique_id END) AS min_price_unique_id,
    MAX(CASE WHEN rn_max = 1 THEN price END) AS max_price,
    MAX(CASE WHEN rn_max = 1 THEN unique_id END) AS max_price_unique_id
FROM ranked_data
GROUP BY product;

说明： #

• 如果同一product存在多条价格相同的最小/最大价格记录，ROW_NUMBER()会随机取其中一条的unique_id；若需保留所有符合条件的unique_id，可替换为RANK()，并结合STRING_AGG(unique_id, ',')来拼接多个ID。
• 该方案仅需扫描一次原表，适合超大规模数据集。

方案二：聚合+关联子查询（不推荐大数据量） #

先聚合得到每个product的最小/最大价格，再通过子查询匹配对应的unique_id：

WITH product_price_summary AS (
    SELECT 
        product,
        MIN(price) AS min_price,
        MAX(price) AS max_price
    FROM mytable
    GROUP BY product
)
SELECT 
    pps.product,
    pps.min_price,
    (SELECT unique_id FROM mytable WHERE product = pps.product AND price = pps.min_price LIMIT 1) AS min_price_unique_id,
    pps.max_price,
    (SELECT unique_id FROM mytable WHERE product = pps.product AND price = pps.max_price LIMIT 1) AS max_price_unique_id
FROM product_price_summary pps;

说明： #

• 子查询会对每个product单独执行查找，数据量较大时性能会显著下降，仅适合小数据集使用。

性能优化建议 #

无论采用哪种方案，创建复合索引都能大幅提升查询速度：

CREATE INDEX idx_product_price ON mytable (product, price);

该索引可加速分组、排序及价格匹配的过程，减少磁盘IO开销。

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