时间戳

首页 > 解决方案 > SQL / Postgresql如何对列进行分组但根据某些条件找到另一列的平均值

sql - SQL / Postgresql如何对列进行分组但根据某些条件找到另一列的平均值

问题描述

我有一个名为sites的现有表，其中包含一个或多个具有相同 site_id 的条目。我需要使用以下条件创建一个新表：

1）如果有多个具有相同site_id的条目，我必须检查具有相同site_id的两个条目的方位角值，如果差异小于10，则获取方位角值和单个条目的平均值在新表中为他们创建。（请参阅现有表中的 site_id 5，在新表中获取 id 6）

2）如果同一site_id的2个条目超过10度，那么每个条目在新表中得到不同的id。（请参阅现有表中的 site_id 4，在新表中获得 2 个 id 的 4 和 5）

3) 具有不同 site_id 的所有其他条目可以按原样复制，并且每个条目在新表中都获得一个新 id。（现有表中除 4 和 5 外的所有 site_id）

现有的表站点：

            site_id azimuth longitude latitude  
            ------- ------- --------- --------- 
            1       10     -10.93    10.22  
            2       20      5.937    60.43  
            3       30     -7.942    53.47 
            4       70      57.94    13.14  ---> A) Difference of more than 10 degrees with entry below
            4       10      57.94    13.14  ---> A) Difference of more than 10 degrees with entry above

            5       45     -7.92     56.88 --> B) Diff of less than 10 deg with below entry
            5       55     -7.92     56.88 --> B) Diff of less than 10 deg with above entry

带有附加 id 列的预期表：

            id      site_id azimuth longitude latitude  
            ------- ------- ------- --------- --------- 
             1         1       10     -10.93    10.22  
             2         2       20      5.937    60.43  
             3         3       30     -7.942    53.47 

             4         4       70      57.94    13.14  // A) Since the difference in azimuth between the 2 entries in above table is more than 10 degrees, each entry goes as separate entries in the new table 

             5         4       10      57.94    13.14   // A) Since the difference in azimuth between the 2 entries in above table is more than 10 degrees, each entry goes as separate entries in the new table

             6         5       50     -7.92     56.88   // B) The azimuth was within 10 degrees with the other entry, so the average of 45+55/2=50 is taken as azimuth for site_id 5

由于我必须根据 10 度差异标准找到方位角的平均值，因此我的聚合 GROUP BY 不适用于所有条目。我是 SQL 的新手，如果我能得到任何帮助，我将不胜感激。

标签： sqlpostgresqlpostgresql-9.5

解决方案

我们可以分两步进行：

  • 第 1 步：创建一个按 site_id 分组的表，确定是否应合并具有该 site_id 的站点

  • 第 2 步：将其与原始表连接，以在必要时拉入非组合数据

结果如下：

select row_number() over () AS id
 , s2.site_id
 , case when t.close_azimuths then avg_azimuth else s2.azimuth end as azimuth
 , s2.longitude
 , s2.latitude
from 
  (select site_id
   , max(azimuth) - min(azimuth) <= 10 as close_azimuths
   , avg(azimuth) as avg_azimuth
  from sites
  group by site_id ) t
join sites s2 on s2.site_id = t.site_id

group by s2.site_id
 , case when t.close_azimuths then avg_azimuth else s2.azimuth end
 , s2.longitude
 , s2.latitude

请注意，新的方位角列不是整数，因为它是整数行的平均值。如果方位角读数应该是整数，您可以使用 ::integer 四舍五入并强制返回整数

推荐阅读