経緯

• GROUP BYのグループ毎に、条件に合致するレコードの存在有無を調べたい。
• 言葉尻だけで考えればEXISTSだが、この用途における記述方法があるのか無いのか含めわからない。
• 集計関数 COUNT には、「列を渡すとその列のNULL以外の値の個数を返す」性質があることに思い立ち、これに「条件に合致したらNULL以外、合致しなかったらNULL」を渡すことで集計できるのではと考えた。

謎:→ 集計自体は無事できたっぽいものの、COUNTの結果にしては「条件に合致する行の数」にはならない

解決: テストデータをDUAL表から作っていたが、UNION ALL じゃなく UNION で連結したせいで DISTINCTされた表をベースに集計してました。(ドアホウ)

実験

Oracleの集計関数 count は、引数に列を与えるとGroup byのグループ毎に引数に与えられた値がnull以外であるような値の個数を返す(?)

ここで下記のような、IDがATRを持ってたらATR毎に行ができるようなテーブルを定義する。

ITEMS 表

これに対し、条件式で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;

結果

予想してた絵

※再現用のクエリをおいておく

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技術ページ?)
• 謎が残るので、もともとの目的(グループ毎に、条件に合致するレコードの有無を抽出する)が達せられているのかも若干不安。
  この方法が適切であるのか?　よりよい方法があるか?