search
LoginSignup
1

More than 1 year has passed since last update.

posted at

updated at

SQLでbooleanを集計する

ANSI SQLには1999以降 EVERY, SOME, ANY といったbooleanの集約関数がある。

しかしMySQL 8.0やOracle 21cではこれらの関数はサポートされていないようだし、PostgreSQLには EVERY はあるものの、 ANYSOME は非標準の別名の bool_or としてしか使えない。どうしたものか……。

先に答えを言ってしまうと、 MINMAX が代わりに使える。

MIN, MAX を使う方法

boolean = {false, true}, bit = {0,1} に関して、 (boolean, AND, true), (boolean, OR, false), (bit, MIN, 1), (bit, MAX, 0) はそれぞれモノイドを成し、以下のような関係が成り立つ。

x y x AND y x OR y
false false false false
false true false true
true false false true
true true true true
x y MIN(x, y) MAX(x, y)
0 0 0 0
0 1 0 1
1 0 0 1
1 1 1 1

false = 0, true = 1 と読み替えると、ちょうど x AND yMIN(x, y) に対応し、 x OR yMAX(x, y) に対応する。bitで集計をしたあと、結果をbooleanに読み替えればbooleanの集計ができる。

すなわち、

SELECT
  EVERY(x),
  ANY(y)
FROM hoge

SELECT
  MIN(CASE WHEN x THEN 1 ELSE 0 END) = 1,
  MAX(CASE WHEN y THEN 1 ELSE 0 END) = 1
FROM hoge

と言い換えられる。

和や積を使う方法

また、 (integer, +, 0) もモノイドを成し、以下のような関係が成り立つ。

x y x + y
0 0 0
0 >0 >0
>0 0 >0
>0 >0 >0

OR と同じような関係が成り立つため、

SELECT ANY(x) FROM hoge

SELECT SUM(CASE WHEN x THEN 1 ELSE 0 END) > 0 FROM hoge

とも言い換えられる。

AND についても (bit, *, 1) で MIN と同様のことが言えるが、

x y x * y
0 0 0
0 1 0
1 0 0
1 1 1

SQLでは積による集計に一工夫必要なため(参考: SQLである列の要素すべての積を求める方法 - Qiita)、 MIN, MAX による言い換えの方が対称性があってよいと思う(SUM を使った書き方は count-if 的な使用法もあるけど)。

参考URL

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
1