如何动态连接多个聚合查询的输出? [英] How to concatenate outputs of multiple aggregate queries dynamically?

问题描述

我正在构建一个 Postgres 查询，该查询需要对数组中的每个条目调用聚合函数，然后将所有这些聚合值连接在一起.

I'm building a Postgres query that needs to call an aggregate function on each entry in an array, then concat all of those aggregate values together.

这是对其中一个聚合的查询:

Here's the query for one of the aggregates:

WITH mvtdata AS
(
  SELECT ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, name, description
  FROM feature
  WHERE feature_set = 'river'
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
)
SELECT ST_AsMVT(mvtdata.*, 'river')
FROM mvtdata;

我有一个单独的表来定义不同的数据层.每个数据层都需要调用上述查询并将其结果连接起来.

I have a separate table that defines the different data layers. Each data layer needs to call the above query and concatenate its results.

假设我有一个带有以下值的 layer 表(如果更容易，我也可以使用数组):

Say I have a layer table with the following values (I could also use an array if easier):

|  name        |
|  'river'     |
|  'building'  |

我想为表中的每个条目调用一次上述查询并连接值.类似的东西:

I want to call the above query once for each entry in the table and concatenate the values. Something like:

WITH riverdata AS
(
  SELECT ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, name, description
  FROM feature
  WHERE feature_set = 'river'
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
),
buildingdata AS
(
  SELECT ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, name, description
  FROM feature
  WHERE feature_set = 'building'
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
)
SELECT (ST_AsMVT(riverdata.*, 'river') || ST_AsMVT(buildingdata.*, 'building')) mvt
FROM riverdata, buildingdata;

如何编写查询以使用此类表(或数组)来动态连接任意数量的数据层?每层有一个单独的 ST_AsMVT() 调用很重要.

How can I write my query to use such a table (or array) to dynamically concatenate an arbitrary number of data layers? It is important to have a separate ST_AsMVT() call per layer.

推荐答案

您可以简单地将键值放入 IN clasue 中，这样您就可以通过单个查询获得所有所需的记录.之后，获取CTE检索到的记录，并应用ST_AsMVT函数.

You could simply put the key values into a IN clasue, so that you can get all desired records a single query. After that, get the records retrieved by the CTE and apply the ST_AsMVT function.

WITH mvtdata AS
(
  SELECT 
    ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, 
    name, description,feature_set
  FROM feature
  WHERE feature_set IN ('river','building')
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
)
SELECT 
  ST_AsMVT(mvtdata.*,feature_set)
FROM mvtdata

如果您更喜欢使用数组，请将 IN 替换为 ANY/SOME，例如:

In case you prefer to work with arrays, replace the IN with ANY/SOME, e.g:

WHERE feature_set = ANY ('{river,building}')

如果它仍然没有帮助你，看看plpgsql可能会很有趣:

If it still does not help you, it might be interesting to take a look at plpgsql:

CREATE OR REPLACE FUNCTION public.concat_mvt(arr_feature TEXT[]) 
RETURNS BYTEA LANGUAGE plpgsql 
AS $BODY$
DECLARE 
  i INTEGER;
  res BYTEA DEFAULT '';
  rec BYTEA;
BEGIN 
FOR i IN 1..array_length(arr_feature,1) LOOP
  WITH mvtdata AS (
  SELECT 
    ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, 
    name, description,feature_set
  FROM feature
  WHERE feature_set = arr_feature[i]
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
  ) SELECT ST_AsMVT(mvtdata.*,arr_feature[i])
    FROM mvtdata INTO rec;
  res := res || rec;
END LOOP;
RETURN res;
END $BODY$;

.. 并像这样调用函数

.. and call the function like this

SELECT concat_mvt('{river,building}')

这篇关于如何动态连接多个聚合查询的输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！