PostgreSQL如何新增分区合并多查询 按商家频次与库存排序输出

实现方案 #

完全可以通过窗口函数合并两条查询的逻辑，无需额外关联GROUP BY的结果集，直接在单层子查询内计算出排序所需的两个指标即可，参考SQL如下：

SELECT business, product
FROM (
    SELECT 
        business, 
        product,
        -- 统计每个商家的出现频次
        COUNT(*) OVER (PARTITION BY business) AS business_freq,
        -- 统计每个商家的总库存余量 SUM(Inventory) - SUM(Total_Sold)
        SUM(Inventory - Total_Sold) OVER (PARTITION BY business) AS stock_remain
    FROM your_table_name
) t
ORDER BY 
    business_freq DESC, -- 第一优先级：商家出现频次降序
    stock_remain DESC, -- 第二优先级：库存余量降序
    business, product -- 可选：同商家同库存的情况下按商家名、商品名排序，保证结果稳定

逻辑说明 #

1. 用窗口函数COUNT(*) OVER (PARTITION BY business)实现原第一条查询的分区频次统计，得到每个商家的记录出现次数
2. 新增窗口函数SUM(Inventory - Total_Sold) OVER (PARTITION BY business)，直接按商家分区计算总库存余量，避免了GROUP BY之后再关联回原表取商品列的操作
3. 排序时先按频次降序，频次相同的情况下按库存余量降序，完全匹配需求的排序规则，代入示例数据可以直接得到预期输出结果

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