为何在SQL子查询中自引用同表列？WHERE关联逻辑解析

先贴出你提供的查询代码：

SELECT
    starttime,
    start_station_id,
    tripduration,
    (SELECT
        ROUND(AVG(tripduration),2)
    FROM bigquery-public-data.new_york_citibike.citibike_trips
    WHERE start_station_id = outer_trips.start_station_id
    ) AS avg_duration_for_station,
    ROUND(tripduration -
        (SELECT
            ROUND(AVG(tripduration),2)
        FROM bigquery-public-data.new_york_citibike.citibike_trips
        WHERE start_station_id = outer_trips.start_station_id
        ),2) AS difference_from_avg
FROM bigquery-public-data.new_york_citibike.citibike_trips AS outer_trips
LIMIT 25;

针对你的两个疑问，解答如下：

1. WHERE column = alias.column 语句的作用 #

这里的outer_trips是外层主查询给表起的别名，这个条件用来绑定外层行和子查询的筛选范围。主查询每取出一条骑行记录（对应某个站点的某次骑行），子查询就会匹配这条记录里的start_station_id，只计算该站点下所有骑行的平均时长——简单说，就是让子查询返回「当前行所属站点」的平均，而非全表所有站点的总平均。如果去掉这个条件，子查询会直接算出全表所有骑行的平均时长，根本没法得到单个站点的专属平均值。

2. 为何需要在SQL子查询中自引用同表的列 #

你的需求是要对比「单次骑行时长和它所在站点的平均时长」，这就需要把同一张表当成两个独立数据集来用：

• 外层表：取出每一条具体的骑行记录，提供单次骑行的时长和所属站点ID；
• 子查询：针对外层当前行的站点ID，从同一个表中筛选出该站点的所有记录，计算这个站点的平均骑行时长。

如果不做自引用，子查询只会计算全表的总平均，没法区分不同站点的差异，也就达不到你要的“按站点计算平均差值”的目的。你之前觉得无需自引用就能得到相同结果，其实是混淆了「全表总平均」和「单个站点平均」这两个完全不同的统计维度。

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