postgresql 在几列之间显示具有最大值的列名

xxls0lw8  于 2023-06-22  发布在  PostgreSQL
关注(0)|答案(2)|浏览(103)

我有从表格中收集的数据。并“旋转”数据,使其看起来像这样:

COUNTY     | denver  | seattle   | new_york | dallas   | san fran
-----------+---------+-----------+----------+----------+---------
ada        | 3       | 14        | 0        | 0        | 0    
slc        | 10      | 0         | 0        | 0        | 9    
canyon     | 0       | 5         | 0        | 0        | 0    
washington | 0       | 0         | 11       | 0        | 0    
bonner     | 0       | 0         | 0        | 2        | 0

(This是使用case语句完成的,在我使用的环境中不允许交叉表:cartodb)
我现在需要一个列来列出具有最大值的CITY。例如:

COUNTY     | CITY     | denver  | seattle   | new_york | dallas   | san fran
-----------+----------+---------+-----------+----------+----------+---------
ada        | seattle  | 3       | 14        | 0        | 0        | 0    
slc        | denver   | 10      | 0         | 0        | 0        | 9    
canyon     | seattle  | 0       | 5         | 0        | 0        | 0    
washington | new_york | 0       | 0         | 11       | 0        | 0    
bonner     | dallas   | 0       | 0         | 0        | 2        | 0
6za6bjd0

6za6bjd01#

这是一个“简单”或“切换”CASE语句的教科书示例,以避免代码重复。

SELECT CASE greatest(denver, seattle, new_york, dallas, "san fran")
          WHEN denver      THEN 'denver'
          WHEN seattle     THEN 'seattle'
          WHEN new_york    THEN 'new_york'
          WHEN dallas      THEN 'dallas'
          WHEN "san fran"  THEN 'san fran'
       END AS city, *
FROM   tbl;

第一个在列表中(从左到右)获胜的情况下,一个平局。

oknwwptz

oknwwptz2#

你可以用一个大的case语句来实现:

select t.*,
       (case when denver = greatest(denver, seattle, new_york, dallas, sanfran) then 'denver'
             when seattle = greatest(denver, seattle, new_york, dallas, sanfran) then 'seattle'
             when new_york = greatest(denver, seattle, new_york, dallas, sanfran) then 'new_york'
             when dallas = greatest(denver, seattle, new_york, dallas, sanfran) then 'dallas'
             when sanfran = greatest(denver, seattle, new_york, dallas, sanfran) then 'sanfran'
        end) as City                 
from table t;

编辑:
我会在最后把结果作为重点。就像这样:

SELECT name, state, the_geom,
       MAX(CASE WHEN seqnum = 1 THEN favorite_team END) as favorite_team,
       MAX(CASE WHEN favorite_team = 'Arizona Cardinals' THEN cnt ELSE 0 END) as ari,
       MAX(CASE WHEN favorite_team = 'Atlanta Falcons' THEN cnt ELSE 0 END) as atl,
       MAX(CASE WHEN favorite_team = 'Baltimore Ravens' THEN cnt ELSE 0 END) as bal,
       MAX(CASE WHEN favorite_team = 'Buffalo Bills' THEN cnt ELSE 0 END) as buf
FROM (SELECT c.name, c.state, c.the_geom, s.favorite_team, count(*) as cnt,
             ROW_NUMBER() OVER (PARTITION BY c.name, c.state, c.the_geom ORDER BY COUNT(*) desc) as seqnum
      FROM fandom_survey_one s JOIN
           counties c
           ON ST_Intersects(s.the_geom, c.the_geom)
      GROUP BY c.name, c.state, c.the_geom, s.favorite_team
     ) c
GROUP BY name, state, the_geom
ORDER BY name, state

相关问题