sql—如何改进这个select查询链？

oogrdqng 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(437)

我有一个功能强大的查询，它构建了一个参考表中与主表中的多边形相交的多边形的加权平均值。
有没有比嵌套选择集更好的方法来构造查询？在性能和可读性方面。我曾考虑过使用一系列临时表，但这似乎更复杂而不是更少。

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
;

我的下一步是将它从一个查询转换成一个可重用的函数，因此特别感谢有此想法的建议。

sql postgresql postgis

来源：https://stackoverflow.com/questions/62031459/how-to-improve-this-chain-of-select-queries

1条答案

按热度按时间

woobm2wo1#

有没有比嵌套选择集更好的方法来构造查询？在性能和可读性方面。
如果你担心你的代码的可读性，我会说你没有什么好担心的。但如果你最关心的是表现，你可能会 EXPLAIN 您的查询以确定可能的瓶颈。
我曾考虑过使用一系列临时表，但这似乎更复杂而不是更少。
根据结果集的大小，临时表可能会为代码增加一些性能！如果您处理的是非常大的表，那么创建临时表并为正在搜索的列编制索引可能比在子选择或CTE上查询要快。
我的下一步是将它从一个查询转换成一个可重用的函数，因此特别感谢有此想法的建议。
如果您计划将整个内容打包为一个函数，可以使用以下结构：

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)

赞(0）回复(0）举报 2021-07-26

我来回答

sql—如何改进这个select查询链？

1条答案

相关问题

热门标签

最新问答