【SQL】同一行の複数列を使ってapply的な処理をする

例題. 7人の審査員のうち4人以上が「合格」の札を上げた商品は合格とするSQLは？

データセットはこちら

答えはこちら

select 
	*,
	(
	select
		sum(case when j = '合格' then 1 else 0 end) as gokaku --合格を上げた審査員の数
	from
		unnest(array[judgement1,
		judgement2,
		judgement3,
		judgement4,
		judgement5,
		judgement6,
		judgement7]) as j
	),
	(
	select
		case
			when sum(case when j = '合格' then 1 else 0 end) >= 4 then '合格'
			else '不合格'
		end as final_judgement -- 最終評価
	from
		unnest(array[judgement1,
		judgement2,
		judgement3,
		judgement4,
		judgement5,
		judgement6,
		judgement7]) as j
	)
from
	jobtune;

結果

解説

このSQLの肝は、SELECT句の相関サブクエリの中で集約関数を使うということ

さて例題を振り返りましょう。データセットは、商品ごとに審査員7名がどちらの札を上げたのかが、7つの列にそれぞれ格納されています。このデータセットを使って、4名以上の審査員が合格の札を上げていた商品は最終評価を合格として、新しい列に最終評価を表示したいです。反対に、3名以下の審査員しか合格の札を上げていなかった商品は、最終評価を不合格としたいです。

まず思い浮かぶ実装方針として、SELECTの結果に新しい列を追加するということになるため、SELECT句に計算列を加えればいいと発想します。
手続き型の考え方でいくと、7つの値を入力として受け取り、最終評価を1つ出力する関数を書けばいいということになります。
そうすると、judgement1から7の列を使って1つの値を返すSELECT文を書いて、サブクエリとしてSELECT句の中に埋め込めばいいと考えます。
ここでサブクエリについての復習を。知っている人は読み飛ばしてください。

サブクエリの復習

SQLの中に書かれるSQLのこと。入れ子になるSQL（サブクエリ）は、FROM句、WHERE句、SELECT句などの中で書くことができる。サブクエリはUPDATE,INSERT,SELECT文などで使えるが、ここでは特に、SELECT文の中で入れ子にされるSELECT文を指すとする。

相関サブクエリ/非相関サブクエリ

内側のSELECT文が外側のSELECT文の結果や列を参照する場合、相関サブクエリと呼ばれる。この場合、サブクエリだけでは返却値がもとまらないため、外側のクエリの行ごとに、内側のサブクエリは再評価される。
したがって、内側のサブクエリがどういう結果を返すかイメージするためには、外側のクエリの結果、1行1行がどういうデータになっているかイメージすることが重要である。
相関サブクエリでないサブクエリは非相関サブクエリである。

テーブルサブクエリ/列サブクエリ/スカラサブクエリ

サブクエリを出力形態によって分類すると、

• 複数行複数列を返す場合はテーブルサブクエリ
• 複数行1列を返す場合は列サブクエリ
• 1行1列を返す場合はスカラサブクエリ
  と呼ばれる。テーブルサブクエリは、FROM句のテーブル名の代わりに、必要な行や列だけに絞る場合によく使われる。
  列サブクエリはIN句の右項に使われることが多い。
  スカラサブクエリはSELECT句の中、もしくはWHERE句の中で使われることが多い。

今回の問題を解くためには、SELECT句の中に、相関サブクエリでスカラサブクエリを書くのが適しています。

解説続き

実際にSQLを書こうとすると意外とつまづきます。
まずサブクエリのFROM句に、unnest(array[judgement1, ... を書くという発想が難しい。
これは複数の列を縦持ちに変換すると考えると良いです。
複数の列を縦持ちに変換して行を増やし、GROUP BYで1行に集約することでスカラサブクエリにすれば良いですね。

サブクエリの中のSELECT句の書き方も意外とつまづきます。
FROM句に書いたunnestは複数行1列になっているのはわかるが、どうやってその列名を指定すれば良いのか。
これはPostgresの仕様によると思うのですが、FROM句の中のunnestはテーブル関数と呼ばれ、そのunnest(...) as xxxという書き方でエイリアスを与えると、SELECT句の中で参照することができる模様。
もしエイリアスを与えなかった場合、サブクエリのSELECT句中ではunnestとして参照できることから分かるように、FROM句の中のunnestの返り値は複数行1列になることが自明なので、そのエイリアスが列名として認識されるのではと思います。
PostgreSQL ドキュメント参照

汎化

話を戻すと、複数の列を使ってapply的な処理をしたいというのが本題です。
apply的な処理というのは何かというと、今回のデータセットをPythonのPandasに渡したとして、最終評価を求める場合には、DataFrameに対してapplyメソッドを使って、合格が4つ以上あれば合格を返す関数を行ごとに適用すると思います。このapplyをイメージしています。

例題を通して、SELECT句の中に相関サブクエリを書けばいいということはわかっていただけたと思いますので、これを汎化させてみましょう。
簡単にいうと、サブクエリが1行1列を返すように、サブクエリのSELECT句を書けばなんでも良いわけです。

例えば、judgement1から7の最頻値を求めるには、unnestで縦持ちにしたサブテーブルに対して集約をかけ、カウントをとり、降順にして1番目を取るように書けます。

select 
	*,
	(
	select
		j as final_judgement
	from
		unnest(array[judgement1,
		judgement2,
		judgement3,
		judgement4,
		judgement5,
		judgement6,
		judgement7]) as j
	group by j
	order by count(*) desc
	limit 1
	)
 from
     jobtune;

もし、審査員の評価が点数形式であれば、平均値や最大値、最小値、四則演算の結果なども取得できます。

まとめると、同一行の複数列を使ってapply処理をするためには、SELECT句の中にスカラサブクエリを書いて、そのFROM句には入力となる列をunnestで縦持ちにし、あとは1行1列返すよう煮るなり焼くなりしてあげれば良い、ということでした！

例題の別解①

FROM句にunnestなんて思いつかないよという人のために。

SELECT
    *,
    (
        CASE WHEN judgement1 = '合格' THEN 1 ELSE 0 END +
        CASE WHEN judgement2 = '合格' THEN 1 ELSE 0 END +
        CASE WHEN judgement3 = '合格' THEN 1 ELSE 0 END +
        CASE WHEN judgement4 = '合格' THEN 1 ELSE 0 END +
        CASE WHEN judgement5 = '合格' THEN 1 ELSE 0 END +
        CASE WHEN judgement6 = '合格' THEN 1 ELSE 0 END +
        CASE WHEN judgement7 = '合格' THEN 1 ELSE 0 END
    ) AS gokaku,
    CASE
        WHEN (
            CASE WHEN judgement1 = '合格' THEN 1 ELSE 0 END +
            CASE WHEN judgement2 = '合格' THEN 1 ELSE 0 END +
            CASE WHEN judgement3 = '合格' THEN 1 ELSE 0 END +
            CASE WHEN judgement4 = '合格' THEN 1 ELSE 0 END +
            CASE WHEN judgement5 = '合格' THEN 1 ELSE 0 END +
            CASE WHEN judgement6 = '合格' THEN 1 ELSE 0 END +
            CASE WHEN judgement7 = '合格' THEN 1 ELSE 0 END
        ) >= 4 THEN '合格'
        ELSE '不合格'
    END AS final_judgement
FROM
    jobtune;

ここまで説明してきて、別の回答を書くのはどうなのかと思いますが、unnestやarrayが使えない場合にはこういう書き方になると思います。
しかも割と直感的に書けますね。
ちょっと汎用性が落ちるので、この回答は二番手としました。

例題の別解②

with CTE_final_judge as (
	select
		item,
		count(*) as gokaku,
		case when count(*) >= 4 then '合格' else '不合格' end as final_judge
	from (
		select
			item,
			unnest(array[judgement1,
			judgement2,
			judgement3,
			judgement4,
			judgement5,
			judgement6,
			judgement7]) as judge
		from
			jobtune
	)temp
	where
		judge = '合格'
	group by item
)
SELECT
	jobtune.*,
	cfj.gokaku,
	cfj.final_judge
FROM
	jobtune
inner join CTE_final_judge cfj
on jobtune.item = cfj.item;

おそらく一番多くの人が思いつくであろうSQLがこれだと思います。
一旦、縦横変換してGROUP BYして最終評価を求めて、最後に元データに結合するというやり方です。
この方法も悪くはないと思いますし、パフォーマンス次第ではこちらを選ぶべきかもしれません。
ただ慣れてくると冒頭のSQLがスッキリしてて分かりやすいと個人的に思います。