如何更优雅地实现用户订单总数统计及订单数用户分布计算？

我的数据集包含用户ID（prsn_id）、下单日期（order_date）、下单方式、产品描述（product_desc）字段，没有唯一订单ID列，每行对应一条订单记录。我目前用三层嵌套SQL实现统计：第一层子查询用Row_Number() OVER (PARTITION BY prsn_id ORDER BY order_date, product_desc ASC)给每个用户的订单分配行号；第二层子查询取每个用户的最大行号作为其订单总数；最外层统计不同订单总数对应的用户数量，原SQL代码如下：

select distinct
max_nbr_of_devices_ordered
, count(distinct prsn_id) as numer
from ( select distinct
        prsn_id
        , max(nbr_of_devices_ordered) as max_nbr_of_devices_ordered
        from ( select distinct
               prsn_id
               , Row_Number() Over (PARTITION BY prsn_id ORDER BY order_date, product_desc ASC) AS nbr_of_devices_ordered
               from hsd_orders
               ) as a
        group by 1
        ) as b
group by 1

现在想知道有没有更优雅的方式实现每位用户订单总数的计算及对应订单数的用户分布统计？

优化方案 #

完全不需要用ROW_NUMBER()绕路统计订单数，直接通过两次分组就能完成需求，逻辑更简洁直观：

方法1：使用CTE（公共表表达式）提升可读性

-- 先统计每个用户的订单总数
WITH user_order_stats AS (
    SELECT 
        prsn_id,
        COUNT(*) AS order_count
    FROM hsd_orders
    GROUP BY prsn_id
)
-- 再统计不同订单数对应的用户数量
SELECT 
    order_count AS max_nbr_of_devices_ordered,
    COUNT(DISTINCT prsn_id) AS numer
FROM user_order_stats
GROUP BY order_count
ORDER BY order_count;

方法2：嵌套子查询（兼容不支持CTE的SQL方言）

SELECT 
    order_count AS max_nbr_of_devices_ordered,
    COUNT(DISTINCT prsn_id) AS numer
FROM (
    SELECT 
        prsn_id,
        COUNT(*) AS order_count
    FROM hsd_orders
    GROUP BY prsn_id
) AS user_order_stats
GROUP BY order_count
ORDER BY order_count;

优化点说明 #

• 去掉冗余逻辑：原写法用行号最大值等价订单数是绕路操作，COUNT(*)可以直接精准统计每个用户的订单总数
• 消除多余DISTINCT：原SQL中多处DISTINCT是不必要的——既然每行对应一条唯一订单记录，分组统计时无需额外去重
• 代码结构更清晰：CTE或简单嵌套子查询的分层逻辑比三层嵌套更易读，维护成本更低

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