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

问题描述

我有从表单中收集的数据.并旋转"数据，使其看起来像这样:

I have data I have collect from a form. And have "pivoted" the data so it looks like this:

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

(这是使用 case 语句完成的，我使用的环境中不允许交叉表:cartodb)

(This was accomplished using case statements, crosstab is not allowed in the environment I am using: cartodb)

我现在需要一列列出具有最大值的 CITY.例如:

I now need a column that list the CITY with the max value. For example:

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

感谢您的回复.我想知道我是否可以在一个查询中实现这一点.例如，这是我用来将数据放入上面第一个表示例中的查询，它对数据进行了数据透视:

Thanks for the replies. I am wondering if I can make this happen in one query. For example, here is the query I used to get my data into the first table example above, whick pivoted the data:

SELECT counties.name, counties.state, counties.the_geom,
count(case when fandom_survey_one.favorite_team = 'Arizona Cardinals' then 'ari' end)                                       ari,
count(case when fandom_survey_one.favorite_team = 'Atlanta Falcons' then 'atl' end) atl,
count(case when fandom_survey_one.favorite_team = 'Baltimore Ravens' then 'bal' end) bal,
count(case when fandom_survey_one.favorite_team = 'Buffalo Bills' then 'buf' end) buf,
count(case when fandom_survey_one.favorite_team = 'Carolina
FROM fandom_survey_one, counties
WHERE ST_Intersects(fandom_survey_one.the_geom, counties.the_geom)
group by counties.name, counties.state, counties.the_geom
order by counties.name, counties.state

我想知道是否有办法将 Gordon 或 Erwin 提供的答案合并到第一个查询中，以便能够在一个查询中完成所有这些.谢谢.

I wondering if there is a way to incorporate the answers provided by Gordon or Erwin into this first query to be able to do this all in one query. THanks.

推荐答案

这是简单"或切换"CASE 语句避免代码重复.

That's a textbook example for a "simple" or "switched" CASE statement to avoid code repetition.

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;

如果出现平局，则列表中的第一个(从左到右)获胜.

The first in the list (from left to right) wins in case of a tie.

这篇关于在几列之间显示具有最大值的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！