gpt4 book ai didi

sql - 如何改进这个 SELECT 查询链?

转载 作者:行者123 更新时间:2023-12-04 15:27:07 25 4
gpt4 key购买 nike

我有这个功能查询,它在与主表中的多边形相交的引用表中构建多边形的加权平均值。

有没有比一组嵌套选择更好的构造查询的方法?无论是在性能还是可读性方面。我考虑过使用一系列临时表,但这似乎更复杂而不是更少。

UPDATE tmp_master_geom AS master
SET weighted_value = sub_query.weighted_average
FROM
-- calc weighted average column
(SELECT
the_id,
(weighted_sum / total_area) AS weighted_average
-- the_whole_intersection AS geom
FROM
-- group intersection pieces by original id
(SELECT
the_id,
st_union(the_intersection) AS the_whole_intersection,
SUM(the_area) AS total_area,
SUM(the_area * ref_value) AS weighted_sum
FROM
-- get all intersections between master and reference
(SELECT
tmg.ID AS the_id,
st_astext(trgl.geom) AS the_original,
st_astext(st_intersection(tmg.geom, trgl.geom)) AS the_intersection,
st_area(st_intersection(tmg.geom, trgl.geom)) AS the_area,
trgl.ref_value AS ref_value
FROM
tmp_master_geom tmg,
tmp_ref_geoms_larger trgl
where st_intersects(tmg.geom, trgl.geom)
) AS intersection_table
GROUP BY
the_id
) AS sum_table
) AS sub_query
WHERE
master.id = sub_query.the_id
;

我的下一步是将其从查询转换为可重用函数,因此特别感谢考虑到这一点的建议。

最佳答案

Is there a better way to structure the query than a set of nested selects? Both in terms of performance and readability.

如果您担心代码的可读性,我会说您不必担心。但如果您主要关心的是性能,您可能想要 EXPLAIN您的查询以确定可能的瓶颈。

I considered using a series of temporary tables but that seems like it would be more complicated rather than less.

根据结果集的大小,临时表可能会为您的代码增加一些性能!如果您正在处理非常大的表,那么创建一个临时表并为您正在搜索的列建立索引可能比通过子选择或 CTE 查询更快。

My next step is to convert this from a query into a reusable function so suggestions with that in mind are especially appreciated.

如果你打算把整个东西打包成一个函数,你可以使用这个结构:

CREATE OR REPLACE FUNCTION gimme_the_geom() 
RETURNS GEOMETRY AS $BODY$
DECLARE geom GEOMETRY;
BEGIN
--your huge query goes here + INTO geom;
SELECT 'POINT(1 2)'::GEOMETRY INTO geom;
RETURN geom;
END;
$BODY$
LANGUAGE plpgsql;

用法

SELECT gimme_the_geom();

gimme_the_geom
--------------------------------------------
0101000000000000000000F03F0000000000000040
(1 Zeile)

关于sql - 如何改进这个 SELECT 查询链?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/62031459/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com