PostgreSQL中RANK与DENSE_RANK的选择及查询优化咨询

1. 用RANK出现6月无第27名结果是否正常？ #

完全正常。RANK()函数的核心特性就是：当多个行并列同一排名时，下一个排名会跳过对应数量的位次。比如6月有2款产品并列第26名，那么下一个可用的排名就是26+2=28，自然不会出现第27名的结果。

2. 是否应该改用DENSE_RANK？ #

取决于你的业务需求：

• 如果希望排名是连续序列（比如并列第26名后，下一个是第27名），那么应该用DENSE_RANK()。它会让并列的行共享同一排名，且后续排名不跳跃，始终保持连续。
• 如果需要严格遵循“按销量排序后，跳过并列数量的位次”的真实排行榜逻辑（比如并列第2就没有第3名），那继续用RANK()完全没问题。

3. 查询结构优化建议 #

• 预聚合销量数据：先单独计算1997年每个月各产品的总销量，再基于这个结果做排名，避免重复计算销量，提升可读性和性能：

  WITH monthly_product_sales AS (
      SELECT
          DATE_TRUNC('month', o.order_date) AS sale_month,
          p.product_id,
          p.product_name,
          SUM(od.quantity) AS total_sold
      FROM orders o
      JOIN order_details od ON o.order_id = od.order_id
      JOIN products p ON od.product_id = p.product_id
      WHERE o.order_date BETWEEN '1997-01-01' AND '1997-12-31'
      GROUP BY sale_month, p.product_id, p.product_name
  ),
  ranked_products AS (
      SELECT
          *,
          RANK() OVER (PARTITION BY sale_month ORDER BY total_sold DESC) AS sales_rank
      FROM monthly_product_sales
  )
  SELECT * FROM ranked_products WHERE sales_rank = 27;
  

• 提前过滤数据：在最外层查询先筛选1997年的订单，减少后续处理的数据量，避免全表扫描不必要的历史数据。
• 添加针对性索引：针对过滤、连接、排序的核心字段创建索引，提升查询效率：
  • orders(order_date, order_id)：加速按日期筛选订单，以及和order_details的连接
  • order_details(order_id, product_id, quantity)：加速订单明细与订单、产品的连接，以及销量求和计算
• 参数化查询（可选）：如果需要频繁查询不同名次的产品，可将排名值设为参数，避免重复修改SQL：

  PREPARE get_monthly_top_n(int) AS
  WITH monthly_product_sales AS (
      -- 同上预聚合逻辑
  ),
  ranked_products AS (
      SELECT *, RANK() OVER (PARTITION BY sale_month ORDER BY total_sold DESC) AS sales_rank
      FROM monthly_product_sales
  )
  SELECT * FROM ranked_products WHERE sales_rank = $1;
  
  -- 调用时传入参数
  EXECUTE get_monthly_top_n(27);
  

• 精简返回字段：只选择业务需要的字段，减少数据传输和内存占用。

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