如何按状态优先级统计订单数量？PostgreSQL查询求助

表结构 #

line_item表结构及数据如下：

状态判定规则 #

• 状态优先级列表：['pending', 'reserved', 'to-ship', 'confirmed', 'unavailable', 'canceled']
• 订单状态取其所有条目里优先级最高的状态：只要有pending条目，订单状态就是pending；若无pending但有reserved，则取reserved，以此类推。

单个订单状态查询（已实现） #

通过以下查询可正确获取每个订单的最终状态：

WITH statuses_rank AS (
  SELECT ARRAY['pending', 'reserved', 'to-ship', 'confirmed', 'unavailable', 'canceled'] AS statuses_order
)
SELECT DISTINCT ON (order_id) order_id, status
FROM line_item, statuses_rank
ORDER BY order_id, array_position(statuses_order, status::text);

查询结果：

需求：统计各状态的订单数量 #

期望得到如下统计结果：

尝试的错误查询 #

以下查询无法得到正确结果：

WITH statuses_rank AS (
  SELECT ARRAY['pending', 'reserved', 'to-ship', 'confirmed', 'unavailable', 'canceled'] AS statuses_order
)
SELECT  status, COUNT(DISTINCT order_id) as count_order
FROM line_item, statuses_rank
GROUP BY status, statuses_rank.statuses_order
ORDER BY array_position(statuses_order, status::text) ASC;

解决方案 #

正确思路是先获取每个订单的最终状态，再对这些状态分组统计。将单个订单状态查询作为子CTE，再基于此统计：

WITH statuses_rank AS (
  SELECT ARRAY['pending', 'reserved', 'to-ship', 'confirmed', 'unavailable', 'canceled'] AS statuses_order
),
order_final_status AS (
  -- 先确定每个订单的最终状态
  SELECT DISTINCT ON (order_id) order_id, status
  FROM line_item, statuses_rank
  ORDER BY order_id, array_position(statuses_order, status::text)
)
-- 对最终状态分组统计订单数量
SELECT 
  status AS statuses, 
  COUNT(order_id) AS "count(order_id)"
FROM order_final_status
GROUP BY status
-- 按状态优先级排序结果
ORDER BY array_position((SELECT statuses_order FROM statuses_rank), status::text);

该查询会先通过order_final_status确定每个订单的唯一最终状态，再统计各状态对应的订单数，最终得到符合预期的结果。

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