如何用SQL按客户出行类型（混合/飞机/汽车）统计指定指标？

现有数据表 #

1. ticket_history（票务历史表） #

2. company_vehicle（企业交通工具对应表） #

客户分类规则 #

• 同时乘坐过Plane和Car的客户：mixed类型
• 仅乘坐Plane的客户：plane类型
• 仅乘坐Car的客户：car类型

目标统计需求 #

需生成包含以下指标的分类统计结果：

SQL实现代码 #

WITH customer_category AS (
    -- 标记每个客户的分类类型
    SELECT 
        customer_id,
        CASE 
            WHEN COUNT(DISTINCT transportation) = 2 THEN 'mixed'
            WHEN MAX(transportation) = 'Plane' THEN 'plane'
            ELSE 'car'
        END AS customer_type
    FROM ticket_history
    GROUP BY customer_id
),
customer_ticket_stats AS (
    -- 统计每个客户的Shuttle乘坐次数、总票价
    SELECT 
        t.customer_id,
        COUNT(CASE WHEN cv.vehicle = 'Shuttle' THEN 1 END) AS shuttle_count,
        SUM(CAST(REPLACE(t.ticket_price, '$', '') AS DECIMAL(10,2))) AS total_ticket_price
    FROM ticket_history t
    JOIN company_vehicle cv ON t.company_id = cv.company_id
    GROUP BY t.customer_id
)
-- 按客户分类聚合最终统计结果
SELECT 
    cc.customer_type,
    SUM(cts.shuttle_count) AS "# shuttle took",
    ROUND(SUM(cts.total_ticket_price) / COUNT(DISTINCT cc.customer_id), 2) AS "Avg ticket price per customer",
    COUNT(DISTINCT cc.customer_id) AS "# of customers"
FROM customer_category cc
JOIN customer_ticket_stats cts ON cc.customer_id = cts.customer_id
GROUP BY cc.customer_type
ORDER BY 
    -- 按指定顺序排序结果
    CASE cc.customer_type 
        WHEN 'mixed' THEN 1 
        WHEN 'plane' THEN 2 
        WHEN 'car' THEN 3 
    END;

最终统计结果 #

执行上述SQL后，得到的具体数据如下：

结果说明 #

• mixed类型：包含客户1、4，其中仅客户1乘坐过1次Shuttle；两人总票价合计822.18，平均单客户总票价为411.09。
• plane类型：仅客户3，未乘坐过Shuttle；总票价705.27，平均单客户总票价即该值。
• car类型：包含客户2、5，其中仅客户2乘坐过1次Shuttle；两人总票价合计141，平均单客户总票价为70.50。

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