Oracle でグループ毎に条件に合致する値の有無を確認できるか？
経緯
-
GROUP BYのグループ毎に、条件に合致するレコードの存在有無を調べたい。 - 言葉尻だけで考えれば
EXISTSだが、この用途における記述方法があるのか無いのか含めわからない。 - 集計関数
COUNTには、「列を渡すとその列のNULL以外の値の個数を返す」性質があることに思い立ち、これに「条件に合致したらNULL以外、合致しなかったらNULL」を渡すことで集計できるのではと考えた。
謎:→ 集計自体は無事できたっぽいものの、COUNTの結果にしては「条件に合致する行の数」にはならない
解決: テストデータをDUAL表から作っていたが、UNION ALL じゃなく UNION で連結したせいで DISTINCTされた表をベースに集計してました。(ドアホウ)
実験
Oracleの集計関数 count は、引数に列を与えるとGroup byのグループ毎に引数に与えられた値がnull以外であるような値の個数を返す(?)
ここで下記のような、IDがATRを持ってたらATR毎に行ができるようなテーブルを定義する。
ITEMS 表
| ID | ATR |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
| 4 | 3 |
これに対し、条件式でNULLかNULL以外の値になるように式を組みcountに渡してやると、その属性を持っているかいないかの判定になる？
SELECT ID
, COUNT(CASE ATR WHEN 1 THEN 1 END) AT1 -- ATRが1の行以外は NULL を返す(CASE式のELSE省略時の返り値)ので、1個も無ければ0になる(?)
, COUNT(DECODE(ATR, 2, 1)) AT2 -- 当然、DECODEでNULLを返させてもよい
, COUNT(DECODE(ATR, 1, 1, 2, 1)) AT1_OR_2 -- OR条件を再現
FROM ITEMS
ORDER BY ID;
結果
| ID | AT1 | AT2 | AT1_OR_2 |
|---|---|---|---|
| 1 | 1 | 1 | 2 |
| 2 | 0 | 1 | 1 |
| 3 | 1 | 0 | 1 |
| 4 | 0 | 0 | 0 |
予想してた絵
| ID | AT1 | AT2 | AT1_OR_2 |
|---|---|---|---|
| 1 | 1 | 2 | 3 |
| 2 | 0 | 1 | 1 |
| 3 | 1 | 0 | 1 |
| 4 | 0 | 0 | 0 |
※再現用のクエリをおいておく
WITH ITEMS AS (
SELECT 1 ID, 1 ATR FROM DUAL
UNION SELECT 1, 2 FROM DUAL
UNION SELECT 1, 2 FROM DUAL
UNION SELECT 2, 2 FROM DUAL
UNION SELECT 3, 1 FROM DUAL
UNION SELECT 4, 3 FROM DUAL
)
SELECT ID
, COUNT(CASE ATR WHEN 1 THEN 1 END) AT1
, COUNT(DECODE(ATR, 2, 1)) AT2
, COUNT(DECODE(ATR, 1, 1, 2, 1)) AT1_OR_2
FROM ITEMS
GROUP BY ID
ORDER BY ID
;
疑問点
(id,atr)=(1,2)な列を2行用意しているため、DECODE(ATR, 2, 1)の結果が1になる行は2行あるはずであり、COUNT(DECODE(ATR, 2, 1)) AT2の結果が2になると予想されたが、こうならない。
また、id=1に対するCOUNT(DECODE(ATR, 1, 1, 2, 1)) AT1_OR_2の結果は2になった。
(3行あるのに！)
ATRでもグループ化されている...?(なわけないよな...)
という謎
疑問点サマリ
-
COUNT(条件式)で条件式がNULL以外である行数が複数あっても1が返る理由(Oracle技術ページ?) - 謎が残るので、もともとの目的(グループ毎に、条件に合致するレコードの有無を抽出する)が達せられているのかも若干不安。
この方法が適切であるのか? よりよい方法があるか?
0
