基于出行类型的行程分段统计分析技术问询

基础统计：按行程和出行类型分组的分段数汇总 #

首先生成按trip_id和type分组的分段数统计，这是后续需求的基础：

SELECT 
    trip_id,
    type,
    COUNT(segment_id) AS segment_count
FROM segments
GROUP BY trip_id, type
ORDER BY trip_id, segment_count DESC;

查询结果示例：

trip_id  type    segment_count
533521   car     4
533526   walk    4
533528   car     4
533531   walk    1
533532   car     1
533536   subway  4
533537   bus     2

需求1：每个行程中分段数最多的出行类型 #

利用窗口函数RANK()（支持并列情况，同一行程多个类型分段数相同时都会被保留）实现：

WITH trip_type_counts AS (
    SELECT 
        trip_id,
        type,
        COUNT(segment_id) AS segment_count,
        RANK() OVER (PARTITION BY trip_id ORDER BY COUNT(segment_id) DESC) AS rnk
    FROM segments
    GROUP BY trip_id, type
)
SELECT 
    trip_id,
    type AS top_type,
    segment_count
FROM trip_type_counts
WHERE rnk = 1
ORDER BY trip_id;

该查询会返回每个行程中分段数最多的出行类型及对应分段数，单一类型的行程直接返回该类型。

需求2：每种出行类型的平均每行程分段数 #

先统计每个行程下各类型的分段数，再对每个类型求平均值：

WITH trip_type_counts AS (
    SELECT 
        trip_id,
        type,
        COUNT(segment_id) AS segment_count
    FROM segments
    GROUP BY trip_id, type
)
SELECT 
    type,
    AVG(segment_count) AS avg_segments_per_trip
FROM trip_type_counts
GROUP BY type
ORDER BY avg_segments_per_trip DESC;

查询结果示例：

type    avg_segments_per_trip
subway  4.0000
car     3.0000
walk    2.5000
bus     2.0000

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