如何将一个复杂的SQL查询拆分为多个查询？

Question

我正在尝试运行一个查询，以便获取每个数据点对应的地理位置（多边形）的ID。但是我的数据量太大，查询执行得非常慢，几乎是一直在运行（我觉得可能是崩溃了）。

当我设置限制为200000时，查询可以正常工作，但我的数据总共有300万条，其中只有110万条与我的请求相关。

我使用的是PostgreSQL 15.5。

不工作的代码：

UPDATE alerts
SET ogc_fid = subquery.ogc_fid
FROM (
    SELECT a.uuid, ct.ogc_fid
    FROM alerts a
    JOIN competences_territoriales ct ON ST_Intersects(ST_SetSRID(a.location::geometry, 4326), ct.wkb_geometry)
    WHERE ct.competence = 'GN'
    
) AS subquery
WHERE alerts.uuid = subquery.uuid
AND alerts.ogc_fid IS NULL ;```

工作的代码：

UPDATE alerts
SET ogc_fid = subquery.ogc_fid
FROM (
    SELECT a.uuid, ct.ogc_fid
    FROM alerts a
    JOIN competences_territoriales ct ON ST_Intersects(ST_SetSRID(a.location::geometry, 4326), ct.wkb_geometry)
    WHERE ct.competence = 'GN'
    limit 200 000
    
) AS subquery
WHERE alerts.uuid = subquery.uuid
AND alerts.ogc_fid IS NULL ;

Answer 1

你的数据库可能内部就支持循环操作。不过，使用itertools可以实现一个通用的解决方案：

import itertools

limit = 200000
offset =  itertools.count(start=0, step= 200000)

cursor = conn.cursor()

while True:
    query = your_query.format(limit, next(offset))
    cursor.execute(query)
    result = cursor.fetchall()
    if len(result) > 0:
        do_something(result)
    else:
        break

需要注意的是，你必须更新你在图片中使用的查询语句，确保同时包含限制和偏移量，比如“UPDATE .. SET .. WHERE .. LIMIT .. OFFSET ..”。