Postgresql
使用 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,處理的邏輯順序都是相同的。