PostgreSQL按description分组计算订单升级率的SQL实现方法

• PostgreSQL数据库中存在orders表，表结构与示例数据如下：

order_id    creation_date                   product_id                           description
integer     timestamp                       character varying                    character varying

00001       2022-05-31 21:12:53.923341      {"id":12,"type":"Order"}             Mercedes
00002       2022-05-31 20:49:43.649024      {"id":14,"type":"Order"}             BMW
00002       2022-05-31 19:53:46.581882      {"id":23,"type":"Upgrade"}           Warranty
00003       2022-05-31 19:42:21.372392      {"id":12,"type":"Order"}             Mercedes
00003       2022-05-31 18:43:31.995706      {"id":39,"type":"Upgrade"}           Onsite Service
00004       2022-05-31 18:43:32.026072      {"id":12,"type":"Order"}             Mercedes
00005       2022-01-01 02:28:56.008328      {"id":105,"type":"Order"}            Audi

• 表数据规则：
  • 行记录product_id字段解析出的type值为Order时，代表一条主订单
  • 行记录product_id字段解析出的type值为Upgrade时，代表对应订单的升级服务
• 示例对应关系：order_id为00002的BMW订单、order_id为00003的Mercedes订单带有升级服务；00001、00004、00005号订单无升级服务。
• 现有查询仅能按升级服务维度统计关联订单数，代码如下：

SELECT description, COUNT(DISTINCT(order_id)) FROM orders
             WHERE product_id::json->>'type' IN ('Upgrade')
             GROUP BY description

• 目标需求：按主订单的description字段分组，计算带升级服务的订单数量/对应品类总订单数量的比率，期望输出结果如下：

description   upgrade_ratio
Mercedes      0.33
BMW           1.00
Audi          0.00

核心逻辑是先提取所有主订单作为统计基数，再逐个标记主订单是否绑定升级服务，最后分组计算比率，代码如下：

WITH main_orders AS (
    -- 提取所有主订单，排除升级服务行避免品类统计偏差
    SELECT DISTINCT
        order_id,
        description
    FROM orders
    WHERE product_id::jsonb->>'type' = 'Order'
),
order_with_upgrade_tag AS (
    -- 为每个主订单打是否有升级服务的标记
    SELECT
        mo.description,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM orders o
                WHERE o.order_id = mo.order_id
                  AND o.product_id::jsonb->>'type' = 'Upgrade'
            ) THEN 1
            ELSE 0
        END AS has_upgrade
    FROM main_orders mo
)
-- 分组计算升级率，保留2位小数
SELECT
    description,
    ROUND(SUM(has_upgrade)::NUMERIC / COUNT(*), 2) AS upgrade_ratio
FROM order_with_upgrade_tag
GROUP BY description
ORDER BY upgrade_ratio DESC;

逻辑说明 #

• 第一层CTE先过滤出所有主订单，用DISTINCT去重，避免主订单重复计数，同时排除升级服务行，防止Warranty、Onsite Service这类升级服务的描述被错误统计为商品品类。
• 第二层CTE通过EXISTS子查询关联判断每个主订单是否存在同订单号的升级服务记录，用CASE WHEN生成0/1的标记位，有升级记1，无升级记0。
• 最终聚合时，SUM(has_upgrade)就是对应品类下带升级服务的订单总数，除以该品类主订单总数量COUNT(*)，通过ROUND保留2位小数即可得到要求的升级率。
• 如果使用的PostgreSQL版本不支持CTE语法，可将两层CTE改写为嵌套子查询，逻辑完全一致。

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