使用 OVER(PARTITION BY) 限制分區中的結果數量

在下面的查詢中，為什麼我們必須通過使用WHERE foo.row_num < 3子查詢外部的子句foo而不是子查詢內部的子句來限制從每個分區返回的結果WHERE row_num < 3？

詢問

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
   SELECT ROW_NUMBER() 
   OVER (
       PARTITION by loc.pid
       ORDER BY ST_Distance(r.the_geom, loc.the_geom)
   ) as row_num,
   loc.pid, loc.land_type, r.road_name, 
   ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
   FROM ch05.land AS loc
   LEFT JOIN ch05.road AS r
   ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
   WHERE loc.land_type = 'police station'
) AS foo

WHERE foo.row_num < 3
ORDER BY pid, row_num;

不工作的查詢

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
   SELECT ROW_NUMBER() 
   OVER (
       PARTITION by loc.pid
       ORDER BY ST_Distance(r.the_geom, loc.the_geom)
   ) as row_num,
   loc.pid, loc.land_type, r.road_name, 
   ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
   FROM ch05.land AS loc
   LEFT JOIN ch05.road AS r
   ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
   WHERE loc.land_type = 'police station'
   AND row_num < 3
) AS foo

ORDER BY pid, row_num;

錯誤： ERROR: column "row_num" does not exist

列“row_num”不存在，因為處理的邏輯順序要求 dbms在評估 SELECT 子句*之前應用 WHERE 子句。*視窗函式是 SELECT 子句的一部分，因此在同一語句的 WHERE 子句中無法訪問其別名。

FROM 子句是要評估的語句的第一部分。這就是為什麼您在 FROM 子句中聲明的別名可以在同一語句的 WHERE 子句中訪問的原因。

在此頁面中搜尋“邏輯處理順序”。儘管這連結到 SQL Server 文件，但對於每個符合 SQL 標準的 dbms，處理的邏輯順序都是相同的。

引用自：https://dba.stackexchange.com/questions/38959