複数カラムの最大値のカラム名を取得する

Question

下記のようなイメージの、複数カラムの最大値を取る場合、どのようにしたらいいのでしょうか。

カラムの中のポイントを取得するには、greatest関数などでできるのかとも思うのですが、
果たして、どのように、カラム名をとればいいのか、ご存知の方、ご教示いただけますと幸いです。

どうぞよろしくお願いいたします。

Comments

[yambejp]

最大値が複数あった場合はどうするつもりでしょうか？greatestは要素を1つ返しますが、カラム名となると競合します

[tynko]

最大値が複数あった場合は、、、カラムを複数作ることができるのであれば、それが良いと考えます。

[tynko]

もしくは、どちらかを優先という形でも問題ございません。

[yambejp]

では、最大値が競合した場合は複数を列記するものとします

Answer 1

データを調整します

create table tbl(id int,col varchar(10),val int,unique key(id,col));
insert into tbl values
(111111,'CP1',5),(111111,'CP2',3),(111111,'CP3',2),(111111,'CP4',1),
(222222,'CP1',3),(222222,'CP2',4),(222222,'CP3',5),(222222,'CP4',6),
(333333,'CP1',1),(333333,'CP2',3),(333333,'CP3',3),(333333,'CP4',3);

• 表示

select id
,sum((col='CP1')*val) as CP1
,sum((col='CP2')*val) as CP2
,sum((col='CP3')*val) as CP3
,sum((col='CP4')*val) as CP4
from tbl
group by id

※上記mysqlのやりかたちゃんとやるなら

select id
,sum(case col when 'CP1' then val else 0 end) as CP1
,sum(case col when 'CP2' then val else 0 end) as CP2
,sum(case col when 'CP3' then val else 0 end) as CP3
,sum(case col when 'CP4' then val else 0 end) as CP4
from tbl
group by id

最大値のカラム名

select id
,sum((col='CP1')*val) as CP1
,sum((col='CP2')*val) as CP2
,sum((col='CP3')*val) as CP3
,sum((col='CP4')*val) as CP4
,(select group_concat(col) from tbl where val= max(t1.val) and id=t1.id) as MAX_CP
from tbl as t1
group by id

※mysql以外のgroup_concatは適宜そのSQLの文法に合わせてください

Comments

[tynko]

ありがとうございます。正規化処理をしたことがなく、基本的な質問で恐縮なのですが、
データ調整の
"(111111,'CP1',5),(111111,'CP2',3),(111111,'CP3',2),(111111,'CP4',1),
(222222,'CP1',3),(222222,'CP2',4),(222222,'CP3',5),(222222,'CP4',6),
(333333,'CP1',1),(333333,'CP2',3),(333333,'CP3',3),(333333,'CP4',3);"

の部分に関しては、全レコード分を実施するということになるのでしょうか？

[yambejp]

select * from tbl
とすると基本のデータ構造がわかります
その上で「表示」に書いたものが命題を表示するSQLです
tynkoさんが列記しているデータの箇所はあくまでinsertして
元データをつくるためのものです

[tynko]

ありがとうございます!!
プログラミングをはじめて間もないこともあり、yambejpさんのおっしゃることを理解しかねている状態です><　が、ひとまず上のデータ調整をやってみます。お忙しい中ありがとうございます!

Answer 2

ぱっと思いつくのは、greatest()で取得した値を、case式で比較して一致したカラム名を判定する。
しかし、そうゆう用途があるなら正規化すればもっと楽に取得できますね。

Comments

[tynko]

さっそくありがとうございます。　ここでおっしゃっていただいている、正規化すればということはどのようなイメージになるのでしょうか？
お手数おかけし恐縮ですが、ご教示いただけましたら幸いです。

[sazi]

yambejpさんが回答中で示されているテーブル構造が正規化されたものです。

[tynko]

ありがとうございます。ちなみに、greatest()でやる場合、「case式で比較して一致したカラム名を判定する。」といった処理はどのような処理になるのでしょうか？正規化に少してこずっていることもあり、
greatest()で実施し、nullだらけになってしまい、array_max(filter(ARRAY　でなんとか最大のポイント数は導くところまでたどり着けました。