如何为基于经纬度计算距离的CTE结果计算平均值？

当然可以在现有CTE的基础上添加聚合函数计算平均值，但你当前的查询逻辑存在问题——它是将所有出现过的起点站点和终点站点做笛卡尔积来计算距离，而非基于实际骑行记录中的起点终点对，这会生成大量不存在的骑行路线，导致统计的平均值不符合实际需求。

方案一：统计实际骑行记录的平均距离（推荐） #

这个方案针对真实的骑行记录计算每条记录的距离，再求平均值，更符合业务场景：

WITH name AS (
    SELECT
        id,
        latitude,
        longitude,
        name,
        docks
    FROM
        santander_stations
),
ride_data AS (
    SELECT
        startstationid,
        endstationid
    FROM
        public.santander_2016

    UNION ALL  -- 用UNION ALL保留所有骑行记录，UNION会去重导致统计失真
    SELECT
        startstationid,
        endstationid
    FROM
        public.santander_2017

    UNION ALL
    SELECT
        startstationid,
        endstationid
    FROM
        public.santander_2018
),
ride_distances AS (
    SELECT
        calculate_distance(a.latitude, a.longitude, b.latitude, b.longitude, 'K') AS calculated_distance
    FROM
        ride_data rd
    JOIN name a ON rd.startstationid = a.id
    JOIN name b ON rd.endstationid = b.id
)
SELECT AVG(calculated_distance) AS average_ride_distance
FROM ride_distances;

关键说明：

• 替换UNION为UNION ALL：避免丢失重复的骑行记录，确保平均值基于真实的骑行次数计算。
• 通过JOIN关联骑行记录与站点信息：准确匹配每条骑行的起点和终点经纬度，计算真实的骑行距离。
• 新增ride_distances CTE存储计算后的距离，最后用AVG()函数直接聚合得到平均值。

方案二：统计所有可能起点终点组合的平均距离（仅特殊场景使用） #

如果你确实需要统计所有出现过的起点站点和终点站点之间所有组合的距离平均值，可以修改原查询如下：

WITH name AS (
    SELECT
        id,
        latitude,
        longitude,
        name,
        docks
    FROM
        santander_stations
),
ride_data AS (
    SELECT
        startstationid,
        endstationid
    FROM
        public.santander_2016

    UNION
    SELECT
        startstationid,
        endstationid
    FROM
        public.santander_2017

    UNION
    SELECT
        startstationid,
        endstationid
    FROM
        public.santander_2018
),
station_pairs AS (
    SELECT
        calculate_distance(a.latitude, a.longitude, b.latitude, b.longitude, 'K') AS calculated_distance
    FROM
        name AS a,
        name AS b
    WHERE
        a.id IN (SELECT startstationid FROM ride_data)
        AND b.id IN (SELECT endstationid FROM ride_data)
)
SELECT AVG(calculated_distance) AS average_pair_distance
FROM station_pairs;

该方案会生成所有起点站点与终点站点的笛卡尔积，计算所有可能组合的距离平均值，仅适用于特定分析场景。

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