ANSI SQLには1999以降 EVERY
, SOME
, ANY
といったbooleanの集約関数がある。
しかしMySQL 8.0やOracle 21cではこれらの関数はサポートされていないようだし、PostgreSQLには EVERY
はあるものの、 ANY
や SOME
は非標準の別名の bool_or
としてしか使えない。どうしたものか……。
先に答えを言ってしまうと、 MIN
や MAX
が代わりに使える。
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 y
は MIN(x, y)
に対応し、 x OR y
は MAX(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
- MySQL :: MySQL 8.0 Reference Manual :: 12.20.1 Aggregate Function Descriptions
- Oracle Database / Release 21 - Aggregate Functions
- PostgreSQL 12 - 9.20. 集約関数