PostgreSQL：查询不能使用count [英] PostgreSQL: Query doesn't work using count

查看：450
本文介绍了PostgreSQL：查询不能使用count的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

  WITH WITH AS（
 SELECT ps.id，ps.brgy_locat，ps.municipali，ps.bldg_name，fh.gridcode，ps.bldg_type 
 FROM evidensapp_polystructures ps 
 JOIN evidensapp_floodhazard fh ON fh.gridcode = 3 
 AND ST_Intersects（fh.geom，ps.geom）
），med AS（
 SELECT ps.id，ps.brgy_locat ，ps.municipali，ps.bldg_name，fh.gridcode，ps.bldg_type 
 FROM evidensapp_polystructures ps 
 JOIN evidensapp_floodhazard fh ON fh.gridcode = 2 
 AND ST_Intersects（fh.geom，ps.geom ）
 EXCEPT * FROM hi 
），low AS（
 SELECT ps.id，ps.brgy_locat，ps.municipali，ps.bldg_name，fh.gridcode，ps.bldg_type 
 FROM evidensapp_polystructures ps 
 JOIN evidensapp_floodhazard fh ON fh.gridcode = 1 
 AND ST_Intersects（fh.geom，ps.geom）
 EXCEPT SELECT * FROM hi 
 EXCEPT SELECT * FROM med 
）
 SELECT brgy_locat，municipali，bldg_name，bldg_type， gridcode，count（bldg_name）
 FROM（SELECT brgy_locat，municipali，bldg_name，gridcode，bldg_type 
 FROM hi 
 GROUP BY 1,2,3,4,5）cnt_hi 
 FULL JOIN（选择brgy_locat，citiesi，bldg_name，gridcode，bldg_type 
从med 
 GROUP BY 1,2,3,4,5）cnt_med USING（brgy_locat，citiesi，bldg_name，gridcode，bldg_type）
 full join（选择brgy_locat，citiesi，bldg_name，gridcode，bldg_type 
 FROM low 
 GROUP BY 1,2,3,4,5）cnt_low USING（brgy_locat，citiesi，bldg_name，gridcode，bldg_type）

上面的查询返回错误：


错误：列cnt_hi.brgy_locat必须出现在GROUP BY子句
中或用于集合函数
**********错误** ********

错误：列cnt_hi.brgy_locat必须出现在GROUP BY子句
中或用于聚合函数SQL州：42803


但是，如果我省略 count（bldg_name），它会起作用。但我需要根据 bldg_name 。



编辑：
我想获得与危险值（网格代码）相交的建筑物数量：高（3），中（2）和低（1）。但是，如果某个几何图形已经在High中相交，请排除其中的Medium查询，并且与Low一起排除那些在High和Medium中相交的几何。

PostgreSQL ：9.4，PostGIS：2.1.7



表格详情： 

  id integer NOT NULL DEFAULT nextval（'evidensapp_floodhazard_id_seq':: regclass），
 gridcode integer NOT NULL，
 date_field character varying（60），
几何几何（MultiPolygon，32651），
 CONSTRAINT evidensapp_floodhazard_pkey PRIMARY KEY（id）
）; 
 
 CREATE INDEX evidensapp_floodhazard_geom_id 
 ON evidensapp_floodhazard USING gist（geom）; 
 
 ALTER TABLE evidensapp_floodhazard CLUSTER ON evidensapp_floodhazard_geom_id; 
 
 CREATE TABLE evidensapp_polystructures（
 id serial NOT NULL，
 bldg_name character varying（100）NOT NULL，
 bldg_type character varying（50）NOT NULL，$ b $ （50）NOT NULL，
 cityi字符变化（50）NOT NULL，
省字符变化（50）NOT NULL，
几何几何（MultiPolygon，32651），
 CONSTRAINT evidensapp_polystructures_pkey PRIMARY KEY（id）
）; 
 
 CREATE INDEX evidensapp_polystructures_geom_id 
 ON evidensapp_polystructures USING gist（geom）; 
 
 ALTER TABLE evidensapp_polystructures CLUSTER ON evidensapp_polystructures_geom_id;

预期的输出是这样的，但有正确的计数：



编辑2：
尽我所能解释预期的输出是什么，无论如何：




  • 统计 bldg_name 不是 id code>，其中 floodhazard 与编辑1 中提到的条件相交的网格代码。

  • 然后将它分组为 brgy_locat ， brgy_municipali 和 gridcode 和 bldg_type 它属于。

    • / b>

    请看看在上面的图片。

    解决方案

    您可能想要这个：

      WITH WITH AS（
     SELECT ps.brgy_locat，ps.municipali，ps.bldg_na me，ps.bldg_type，fh.gridcode 
    ，count（*）OVER（PARTITION BY ps.bldg_name，ps.bldg_type）AS building_count 
     FROM evidensapp_polystructures ps 
     JOIN evidensapp_floodhazard fh ON fh.gridcode = 3 
    和ST_Intersects（fh.geom，ps.geom）
    ）
    ，med AS（
     SELECT ps.brgy_locat，ps.municipali，ps.bldg_name，ps.bldg_type ，fh.gridcode 
    ，count（*）OVER（PARTITION BY ps.bldg_name，ps.bldg_type）AS building_count 
     FROM evidensapp_polystructures ps 
     JOIN evidensapp_floodhazard fh ON fh.gridcode = 2 
     AND ST_Intersects（fh.geom，ps.geom）
     LEFT JOIN hi使用（bldg_name，bldg_type）
     WHERE hi.bldg_name IS NULL 
    ）
     TABLE hi 
     
     UNION ALL 
     TABLE MED 
     
     UNION ALL 
     SELECT ps.brgy_locat，ps.municipali，ps.bldg_name，ps.bldg_type，fh.gridcode 
     ，计数（*）OVER（PARTITION BY ps.bldg_name， ps.bldg_type）AS building_count 
     FROM evidensapp_polystructures ps 
     JOIN evidensapp_floodhazard fh ON fh.gridcode = 1 
     AND ST_Intersects（fh.geom，ps.geom）
     LEFT JOIN hi用法bldg_name，bldg_type）
     LEFT JOIN med USING（bldg_name，bldg_type）
     WHERE hi.bldg_name IS NULL 
     AND med.bldg_name IS NULL;

    根据您对问题和聊天的评论，此计数按照 （bldg_name，bldg_type） 现在 - 不包括已经在更高层上相交的建筑物 - 再次基于（bldg_name，bldg_type）

    所有其他列都是不同的（ id ， geom ）或计数的功能相关噪声（ brgy_locat ， municipali ，...）。 如果不是，则添加更多列 PARTITION BY 子句以消除建筑物的歧义。



    如果一栋建筑物与 evidensapp_floodhazard 与 相同 gridcode ，它会被多次计数 。由于你实际上并不想聚合行，而只是依赖分区，所以关键特性是使用 count（） code> as 窗口功能 ，而不是像原来那样的聚合函数。基本解释：



    count（*）在这里做的更好：

    code> LEFT JOIN / IS NULL 而不是 EXCEPT 。详细信息：





    我在外部查询中未能看到 FULL JOIN 的用途。使用 UNION ALL 来代替。



    备选查询



    无论它在同一个网格代码级别与 evidensapp_floodhazard 相交多少次， 一次

    另外，这个变体（不同于第一个！）假定同一个（bldg_name，bldg_type）的所有行匹配相同gridcode级别，可能是也可能不是这种情况：

      SELECT brgy_locat，municipali，bldg_name，bldg_type，3 AS gridcode 
    ，count（*）OVER（PARTITION BY bldg_name，bldg_type）AS building_count 
     FROM evidensapp_polystructures ps 
     WHERE EXISTS（
     SELECT 1 FROM evidensapp_floodhazard fh 
     WHERE fh.gridcode = 3 AND ST_Intersects（fh.geom，ps.geom）
    ）
     
     UNION ALL 
     SELECT brgy_locat，municipali，bldg_name，bldg_type，2 AS gridcode 
    ，count（* ）OVER（PARTITION BY bldg_name，bldg_type）AS building_count 
     FROM evidens app_polystructures ps 
     WHERE EXISTS（
     SELECT 1 FROM evidensapp_floodhazard fh 
     WHERE fh.gridcode = 2 AND ST_Intersects（fh.geom，ps.geom）
    ）
     AND NOT EXISTS（
     SELECT 1 FROM evidensapp_floodhazard fh 
     WHERE fh.gridcode> 2  - 排除** all **更高的网格代码上的匹配
    和ST_Intersects（fh.geom，ps.geom）
    ）
     
     UNION ALL 
     SELECT brgy_locat， （
    ，count（*）OVER（PARTITION BY bldg_name，bldg_type）AS building_count 
     FROM evidensapp_polystructures ps 
     WHERE EXISTS（
     SELECT 1 FROM evidensapp_floodhazard fh 
     WHERE fh.gridcode = 1 AND ST_Intersects（fh.geom，ps.geom）
    ）
     AND NOT EXISTS（
     SELECT 1 FROM evidensapp_floodhazard fh 
     where fh .gridcode> 1 AND ST_Intersects（fh.geom，ps.geom）
    ）;

    同时演示一个没有CTE的变体，根据数据分布，这可能会或可能不会更好。 / p>

    索引



    将 gridcode 添加到索引可能可以提高性能。 （未使用PostGis进行测试）：

    您需要首先安装附加模块 btree_gist 。详细信息：





    整数和范围/ 20908766＃20908766>相当于由整数和范围组成的排除约束 

      CREATE INDEX evidensapp_floodhazard_geom_id 
     ON evidensapp_floodhazard USING gist（gridcode，geom）; 
          


    WITH hi AS (
  SELECT ps.id, ps.brgy_locat, ps.municipali, ps.bldg_name, fh.gridcode, ps.bldg_type
  FROM evidensapp_polystructures ps
  JOIN evidensapp_floodhazard fh ON fh.gridcode=3
                                 AND ST_Intersects(fh.geom, ps.geom)
), med AS (
  SELECT ps.id, ps.brgy_locat, ps.municipali ,ps.bldg_name, fh.gridcode, ps.bldg_type
  FROM evidensapp_polystructures ps
  JOIN evidensapp_floodhazard fh ON fh.gridcode=2
                                 AND ST_Intersects(fh.geom, ps.geom)
  EXCEPT SELECT * FROM hi
), low AS (
  SELECT ps.id, ps.brgy_locat, ps.municipali,ps.bldg_name, fh.gridcode, ps.bldg_type
  FROM evidensapp_polystructures ps
  JOIN evidensapp_floodhazard fh ON fh.gridcode=1
                                 AND ST_Intersects(fh.geom, ps.geom)
  EXCEPT SELECT * FROM hi
  EXCEPT SELECT * FROM med
)
SELECT brgy_locat, municipali, bldg_name,  bldg_type, gridcode, count( bldg_name)
FROM (SELECT brgy_locat, municipali, bldg_name, gridcode, bldg_type
      FROM hi
      GROUP BY 1, 2, 3, 4, 5) cnt_hi
FULL JOIN (SELECT brgy_locat, municipali,bldg_name, gridcode, bldg_type
      FROM med
      GROUP BY 1, 2, 3, 4, 5) cnt_med USING (brgy_locat, municipali, bldg_name,gridcode,bldg_type)
FULL JOIN (SELECT brgy_locat, municipali,bldg_name,gridcode, bldg_type
      FROM low
      GROUP BY 1, 2, 3, 4, 5) cnt_low USING (brgy_locat, municipali, bldg_name, gridcode, bldg_type)

    The query above returns an error:

    ERROR: column "cnt_hi.brgy_locat" must appear in the GROUP BY clause or be used in an aggregate function ********** Error **********

    ERROR: column "cnt_hi.brgy_locat" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803

    But if I omit the count(bldg_name) it works. But I need to count based on bldg_name.

    EDIT: I wanted to get the number of buildings that intersect with the hazard value(gridcode): High(3), Medium(2) and Low(1). But, if a certain geometry intersects already in High,exclude in it Medium query and same goes with Low exclude those geometry that intersects in High and Medium.

    PostgreSQL: 9.4, PostGIS: 2.1.7

    Table Details:

    CREATE TABLE evidensapp_floodhazard (
  id integer NOT NULL DEFAULT nextval('evidensapp_floodhazard_id_seq'::regclass),
  gridcode integer NOT NULL,
  date_field character varying(60),
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_floodhazard_pkey PRIMARY KEY (id)
);

CREATE INDEX evidensapp_floodhazard_geom_id
  ON evidensapp_floodhazard USING gist (geom);

ALTER TABLE evidensapp_floodhazard CLUSTER ON evidensapp_floodhazard_geom_id;

CREATE TABLE evidensapp_polystructures (
  id serial NOT NULL,
  bldg_name character varying(100) NOT NULL,
  bldg_type character varying(50) NOT NULL,
  brgy_locat character varying(50) NOT NULL,
  municipali character varying(50) NOT NULL,
  province character varying(50) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_polystructures_pkey PRIMARY KEY (id)
);

CREATE INDEX evidensapp_polystructures_geom_id
  ON evidensapp_polystructures USING gist (geom);

ALTER TABLE evidensapp_polystructures CLUSTER ON evidensapp_polystructures_geom_id;

    Intended output is like this but with correct count:

    EDIT 2: As much as I try my best to explain what the intended output is, anyway:

    • count the bldg_name not the id in which what gridcode it intersects in floodhazard with the condition as mentioned above on the EDIT 1.
    • then group it to what brgy_locat,brgy_municipali and what gridcode and bldg_type it belong.

    Kindly take a look at the image above.

    解决方案

    You probably want this instead:

    WITH hi AS (
   SELECT ps.brgy_locat, ps.municipali, ps.bldg_name, ps.bldg_type, fh.gridcode
        , count(*) OVER(PARTITION BY ps.bldg_name, ps.bldg_type) AS building_count
   FROM   evidensapp_polystructures ps
   JOIN   evidensapp_floodhazard    fh ON fh.gridcode = 3
                                      AND ST_Intersects(fh.geom, ps.geom)
   )
, med AS (
   SELECT ps.brgy_locat, ps.municipali, ps.bldg_name, ps.bldg_type, fh.gridcode
        , count(*) OVER(PARTITION BY ps.bldg_name, ps.bldg_type) AS building_count
   FROM   evidensapp_polystructures ps
   JOIN   evidensapp_floodhazard    fh ON fh.gridcode = 2
                                      AND ST_Intersects(fh.geom, ps.geom)
   LEFT   JOIN hi USING (bldg_name, bldg_type)
   WHERE  hi.bldg_name IS NULL
   )
TABLE hi

UNION ALL
TABLE med

UNION ALL 
   SELECT ps.brgy_locat, ps.municipali, ps.bldg_name, ps.bldg_type, fh.gridcode
        , count(*) OVER(PARTITION BY ps.bldg_name, ps.bldg_type) AS building_count
   FROM   evidensapp_polystructures ps
   JOIN   evidensapp_floodhazard    fh ON fh.gridcode = 1
                                      AND ST_Intersects(fh.geom, ps.geom)
   LEFT   JOIN hi USING (bldg_name, bldg_type)
   LEFT   JOIN med USING (bldg_name, bldg_type)
   WHERE  hi.bldg_name IS NULL
   AND    med.bldg_name IS NULL;

    Based on your comments to the question and the chat, this counts per (bldg_name, bldg_type) now - excluding buildings that already intersect on a higher level - again based on (bldg_name, bldg_type).

    All other columns are either distinct (id, geom) or functionally dependent noise for the count (brgy_locat, municipali, ...). If not, add more columns the PARTITION BY clause to disambiguate buildings. And add the same columns to the USING clause of the JOIN condition.

    If a building intersects with multiple rows in evidensapp_floodhazard with the same gridcode it is counted that many times. See alternative blow.

    Since you do not actually want to aggregate rows but just count on partitions, the key feature is using count() as window function, not as aggregate function like in your original. Basic explanation:

    count(*) does a better job here:

    Using LEFT JOIN / IS NULL instead of EXCEPT. Details:

    And I failed to see the purpose of FULL JOIN in the outer query. Using UNION ALL instead.

    Aternative query

    This counts building once, no matter how many times it intersects with evidensapp_floodhazard on the same gridcode level

    Also, this variant (unlike the first!) assumes that all rows for the same (bldg_name, bldg_type) match on the same gridcode level, which may or may not be the case:

    SELECT brgy_locat, municipali, bldg_name, bldg_type, 3 AS gridcode
     , count(*) OVER(PARTITION BY bldg_name, bldg_type) AS building_count
FROM   evidensapp_polystructures ps
WHERE  EXISTS (
   SELECT 1 FROM evidensapp_floodhazard fh
   WHERE  fh.gridcode = 3 AND ST_Intersects(fh.geom, ps.geom)
   )

UNION ALL
SELECT brgy_locat, municipali, bldg_name, bldg_type, 2 AS gridcode
     , count(*) OVER(PARTITION BY bldg_name, bldg_type) AS building_count
FROM   evidensapp_polystructures ps
WHERE  EXISTS (
   SELECT 1 FROM evidensapp_floodhazard fh
   WHERE  fh.gridcode = 2 AND ST_Intersects(fh.geom, ps.geom)
   )
AND    NOT EXISTS (
   SELECT 1 FROM evidensapp_floodhazard fh
   WHERE  fh.gridcode > 2  -- exclude matches on **all** higher gridcodes
   AND    ST_Intersects(fh.geom, ps.geom)
   )

UNION ALL 
SELECT brgy_locat, municipali, bldg_name, bldg_type, 1 AS gridcode
     , count(*) OVER(PARTITION BY bldg_name, bldg_type) AS building_count
FROM   evidensapp_polystructures ps
WHERE  EXISTS (
   SELECT 1 FROM evidensapp_floodhazard fh
   WHERE  fh.gridcode = 1 AND ST_Intersects(fh.geom, ps.geom)
   )
AND    NOT EXISTS (
   SELECT 1 FROM evidensapp_floodhazard fh
   WHERE  fh.gridcode > 1 AND ST_Intersects(fh.geom, ps.geom)
   );

    Also demonstrating a variant without CTEs, which may or may not perform better, depending on data distribution.

    Index

    Adding gridcode to the index might improve performance. (Not tested with PostGis):

    You need to install the additional module btree_gist for this first. Details:

    CREATE INDEX evidensapp_floodhazard_geom_id
  ON evidensapp_floodhazard USING gist (gridcode, geom);

    这篇关于PostgreSQL：查询不能使用count的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆