最大値の集計を取得したい

Question

実現したいこと

横持ちのデータに対して、最大値の集計値TOP3を取得したいです。

名前　要素①　要路②　要素③　要素④
太郎　 40　　　30　　 20　　　 10
次郎 30 20 10 40
三郎 40 30 20 10
四郎 40 30 20 10
五郎 30 20 10 40
六郎 10 40 30 20

例えば、上記の場合は、
・太郎さんにとって最も得点の高い要素は①
・次郎さんにとって最も得点の高い要素は④
・三郎さんにとって最も得点の高い要素は①
・四郎さんにとって最も得点の高い要素は①
・五郎さんにとって最も得点の高い要素は④
・六郎さんにとって最も得点の高い要素は②
となるため、「要素①　3件」「要素④　2件」「要素②　1件」と出力したいです。

発生している問題

group by句、count関数を使用するイメージは湧きましたが、どのように取得すれば良いか、具体的なSQLが書けません。
要素名と件数は別々に求める方法でもOKですので、ご教示ください。
よろしくお願いいたします。

Answer 1

複数のカラムを対象に最大値を求めるには、greatest()を用います。
各々の要素と最大値を比較して、一致していればその要素が最大値です。
※実行可能な様に、from句は質問のデータを展開しています。
尚、同点があると各々の要素でカウントされます。

select  sum(case when 要素① = greatest(要素①,要素②,要素③,要素④) then 1 else 0 end) as 要素① 
      , sum(case when 要素② = greatest(要素①,要素②,要素③,要素④) then 1 else 0 end) as 要素② 
      , sum(case when 要素③ = greatest(要素①,要素②,要素③,要素④) then 1 else 0 end) as 要素③ 
      , sum(case when 要素④ = greatest(要素①,要素②,要素③,要素④) then 1 else 0 end) as 要素④ 
from (values
       ('太郎',40,30,20,10)
      ,('次郎',30,20,10,40)
      ,('三郎',40,30,20,10)
      ,('四郎',40,30,20,10)
      ,('五郎',30,20,10,40)
      ,('六郎',10,40,30,20)
      ) as w(名前,要素①,要素②,要素③,要素④)

追記

TOP3に対応。
集計したものをunnest()を使用して、横を縦に変換(配列を行集合に展開)し、limitします。

with tbl as (
  select *
  from (values
         ('太郎',40,30,20,10)
        ,('次郎',30,20,10,40)
        ,('三郎',40,30,20,10)
        ,('四郎',40,30,20,10)
        ,('五郎',30,20,10,40)
        ,('六郎',10,40,30,20)
        ) as w(名前,要素①,要素②,要素③,要素④)
)
select 要素No, 要素件数
from (
        select  sum(case when 要素① = greatest(要素①,要素②,要素③,要素④) then 1 else 0 end) as 要素① 
              , sum(case when 要素② = greatest(要素①,要素②,要素③,要素④) then 1 else 0 end) as 要素② 
              , sum(case when 要素③ = greatest(要素①,要素②,要素③,要素④) then 1 else 0 end) as 要素③ 
              , sum(case when 要素④ = greatest(要素①,要素②,要素③,要素④) then 1 else 0 end) as 要素④ 
        from tbl
      ) t
      , unnest(array[要素①,要素②,要素③,要素④]::integer[]) with ordinality as u(要素件数, 要素No)
order by 要素件数 desc limit 3

元々は正規化されていない状態が、集計し辛い状況を作っているので、unnestを使用して一旦正規化し
て集計した場合は以下。

with tbl as (
  select *
  from (values
         ('太郎',40,30,20,10)
        ,('次郎',30,20,10,40)
        ,('三郎',40,30,20,10)
        ,('四郎',40,30,20,10)
        ,('五郎',30,20,10,40)
        ,('六郎',10,40,30,20)
        ) as w(名前,要素①,要素②,要素③,要素④)
)
select 要素NO, count(*) as 件数
from (
      select *, max(要素) over(partition by 名前)=要素 as 最大要素 
      from (
          select 名前, 要素No, 要素
          from  tbl, unnest(array[要素①,要素②,要素③,要素④]::integer[]) with ordinality as u(要素, 要素No)
      ) nomalize
) pick_max
where 最大要素
group by 要素No
order by count(*) desc limit 3

正規化された状態のテーブルだった場合で記述すると、以下の様にシンプルになります。

select 要素NO, count(*) as 件数
from (
      select *, max(要素) over(partition by 名前)=要素 as 最大要素 
      from tbl
) pick_max
where 最大要素
group by 要素No
order by count(*) desc limit 3

Comments

[sazi]

> 最大値の集計値TOP3
を見落としていましたので、追記しました。

[Fur0]

返信が遅くなり申し訳ありません。
greatest関数というものがあるのですね。
ご回答ありがとうございました！