横持ち→縦持ちに変更しつつ、集計した結果をJSONで取得したい

Question

前提・実現したいこと

以下のような横持ちのテーブルデータを縦持ちに変更後、平均点の高い上位3教科の教科名と平均点を取得して一時テーブルに格納したいです。

【横持ちテーブル：成績】
名前　　　国語　　　算数　　　社会　　　理科　　　英語
太郎　　　　90　　　　50　　　 100　　　　70　　　　90
二郎　　　　70　　　 100　　　　80　　　　90　　　 100
三郎　　　 100　　　　70　　　　60　　　　40　　　　90

【縦持ち】※平均点は小数点以下第2位を四捨五入します。
教科　　　平均点
国語　　　86.7
算数　　　73.4
社会　　　80.0
理科　　　66.7
英語　　　93.4

上記の場合は、「英語　93.4」、「国語　86.7」、「社会　80.0」を取得し、教科と平均点の列が定義された一時テーブルに格納したいです。

発生している問題

集計、持ち方の向き変更、リネームなどを一気に行うSQLが思いつかないために、一時テーブルに格納するデータが取得できず手が止まっています。

補足情報（FW/ツールのバージョンなど）

Amazon AuroraのPostgreSQL

Comments

[Orlofsky]

回答ではありません。SQLをシンプルにパフォーマンス良く実行できるように、データベースのテーブルは
データベースの正規化 https://oss-db.jp/measures/dojo_info_04.shtml に沿って設計します。通常第3正規化まで行います。今回は第1正規化で繰り返し(今回は教科)を排除します。

Answer 1

やり方はいろいろあると思いますが、UNION ALLを使う方法があります。

SELECT
  教科,
  TO_CHAR(ROUND(AVG(点数), 1), '99.9') AS 平均点
FROM (
            SELECT '国語', 国語 FROM 成績
  UNION ALL SELECT '算数', 算数 FROM 成績
  UNION ALL SELECT '社会', 社会 FROM 成績
  UNION ALL SELECT '理科', 理科 FROM 成績
  UNION ALL SELECT '英語', 英語 FROM 成績
) AS T (教科, 点数)
GROUP BY 教科
ORDER BY AVG(点数) DESC
FETCH FIRST 3 ROWS ONLY;

あるいは配列を使う方法もあります。

SELECT
  教科,
  TO_CHAR(ROUND(平均点, 1), '99.9') AS 平均点
FROM UNNEST(
  ARRAY['国語', '算数', '理科', '社会', '英語'],
  (SELECT ARRAY[AVG(国語), AVG(算数), AVG(理科), AVG(社会), AVG(英語)] FROM 成績)
) AS T (教科, 平均点)
ORDER BY T.平均点 DESC
FETCH FIRST 3 ROWS ONLY;

Comments

[Fur0]

回答ありがとうございます！
ちなみに何ですが、カラム数の多い横持ちテーブルを縦持ちに変更する場合、UNION ALLを使うよりも一時テーブルに格納するなどのやり方の方が可読性も保守性も高くて良いでしょうか。
というのも、今回は私の好みで一気にSQLで取得する方針にしましたが、一般的にはどうかと思いまして。

[neko_the_shadow]

①複雑なSQLで行列を入れ替える
②簡単なSQLと何らかのプログラミング言語を組み合わせて行列を入れ替える
↑のどちらが良いか? ですが、環境によって良い悪いは変わりますし、どちらかの選択肢しか取れないということもあると思います。状況によって適切な選択ができるのが一番良いですね。
ただ保守性や可読性の観点を考えると②のほうが良いかなと個人的には考えます。複雑なSQLはSQLに詳しい人でないと手に負えなかったり、パフォーマンス問題を引き起こしやすかったりします。またSQLは単体テストが難しいことが多く、自動テストにうまく組み込めないこともあります。あくまで個人的なものの見方で恐縮ですが、デメリットを考えると②のほうがよいかなという感じですね。

[Fur0]

大変ありがとうございました！