PostgreSQL如何按国家、城市分组随机抽取指定行数并解决语法报错

问题原因分析 #

• 语法报错的直接原因：你在SELECT列表的最后一个字段osm_version和后续的ROW_NUMBER()窗口函数之间缺少逗号，PostgreSQL解析时识别不到字段分隔，所以抛出语法错误。
• 原有脚本逻辑不满足随机抽样需求：你写的ROW_NUMBER()没有加随机排序规则，同一个国家+城市分组内的行号是固定的，没法实现随机抽取的效果。

正确实现脚本 #

固定每个分组抽2条随机数据 #

如果需求是每个国家的每个城市固定抽2条数据，可以直接用以下语句：

WITH ranked_buildings AS (
    SELECT 
        osm_id, way, tags, way_centroid, way_area, calc_way_area, 
        area_diff, area_prct_diff, calc_perimeter, calc_count_vertices, 
        building, "building:part", "type", amenity, landuse, tourism, 
        office, leisure, man_made, "addr:flat", "addr:housename", 
        "addr:housenumber", "addr:interpolation", "addr:street", 
        "addr:city", "addr:postcode", "addr:country", length, width, 
        height, osm_uid, osm_user, osm_version,
        -- 按国家+城市分组，组内随机排序生成行号
        ROW_NUMBER() OVER (
            PARTITION BY "addr:country", "addr:city" 
            ORDER BY random()
        ) AS cell_rn
    FROM osm_qa.buildings
    WHERE "addr:city" IS NOT NULL
      AND "addr:country" IS NOT NULL
)
-- 每个分组取前2行
SELECT * FROM ranked_buildings WHERE cell_rn <= 2;

每个分组随机抽1-2条数据 #

如果需要每个分组有概率抽1条、有概率抽2条，可以把最后一行的过滤条件修改为：

SELECT * FROM ranked_buildings WHERE cell_rn <= 1 + (random() < 0.5)::int;

这个写法会让每个分组有50%概率抽1条，50%概率抽2条，符合你要求的1-2行随机抽取规则。

大数据量优化建议 #

你总数据量达5亿行，直接跑全表窗口函数性能会比较差，可以参考以下优化方案：

• 提前创建addr:country、addr:city的联合部分索引，大幅提升分组排序的性能：

  CREATE INDEX CONCURRENTLY idx_buildings_addr_country_city 
  ON osm_qa.buildings ("addr:country", "addr:city") 
  WHERE "addr:country" IS NOT NULL AND "addr:city" IS NOT NULL;
  

• 如果不需要完全严格的随机，可先用PostgreSQL内置的TABLESAMPLE方法先抽样减少数据量，再做窗口排序，比如先抽10%的样本再分组取数，只要抽样比例高于你最终要取的总行数比例就不会出现分组遗漏。

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