SQLで重複しているデータの件数をカウントしたい。

Question

解決したいこと

重複した列[商品番号]がある場合、その件数を下記SQLで算出しました。

テーブル：商品

商品番号	登録日時	区分
A001	2021/03/02	2
A001	2019/06/06	3
A001	2020/05/06	4
### 使用したSQL

SELECT
  商品番号,
  COUNT(商品番号) 
FROM
  商品 X
WHERE
  EXISTS ( 
    SELECT
      *
    FROM
      商品 Y 
    WHERE
      X.商品番号 = Y.商品番号
    GROUP BY
      Y.商品番号
    HAVING
      COUNT(Y.商品番号) > 1
  ) 
GROUP BY 商品番号

そこで、算出する条件を列[区分]が 1 であるデータを除く　と設定し、
更に下記パターン別で条件を加えたうえでそれぞれ件数を算出したいです。
条件：
①重複データの登録日時が全て過去日を持つ商品番号と、そのぶら下がってる件数。
例）

商品番号	登録日時	区分
A001	2021/03/02	2
A001	2019/06/06	3
A001	2020/05/06	4

結果

商品番号	COUNT(商品番号)
A001	3

②重複データの登録日時が全て未来日を持つ商品番号と、そのぶら下がっている件数。
例）

商品番号	登録日時	区分
A001	2022/03/02	2
A001	2023/06/06	3
A001	2025/05/06	4

結果

商品番号	COUNT(商品番号)
A001	3

③重複データの登録日時が未来日が複数(2<=)ある かつ NULLを含まない商品番号と、
そのぶら下がっている件数。※未来日のみカウント。
例）

商品番号	登録日時	区分
A001	2023/03/02	2
A001	2019/06/06	3
A001	2025/05/06	4

結果

商品番号	COUNT(商品番号)
A001	2

④重複データの登録日時が未来日 かつ NULLを持つ商品番号と、そのぶら下がっている件数。
※未来日とNULLのみカウント。
例）

商品番号	登録日時	区分
A001	2023/03/02	2
A001	NULL	3
A001	2018/05/06	4

結果

商品番号	COUNT(商品番号)
A001	2

⑤重複データの登録日時が全てNULLを持つ商品番号と、そのぶら下がっている件数。
例）

商品番号	登録日時	区分
A001	NULL	1
A001	NULL	2
A001	NULL	3

結果

商品番号	COUNT(商品番号)
A001	2

列[区分]が 1 であるデータを除く条件＋上記5パターンの条件で件数を出す場合、
EXISTSの中のWHEREにその条件を入れるのか、それともEXISTSの外に入れるのが適切なのかをお聞きしたいのが1点と、
パターン別の条件の後ろに※でカウントする際の条件を記載しており、その条件に関してもEXISTの中か外どちらが適切かという点についても教えていただきたく思います。
また、その際、一例としてSQLに書き加えてご提示して頂けると助かります。

使用したSQL

SELECT
  商品番号,
  COUNT(商品番号) 
FROM
  商品 X
WHERE
  EXISTS ( 
    SELECT
      *
    FROM
      商品 Y 
    WHERE
      X.商品番号 = Y.商品番号
    GROUP BY
      Y.商品番号
    HAVING
      COUNT(Y.商品番号) > 1
  ) 
GROUP BY 商品番号

Comments

[sazi]

未来日とか過去日というのは何を基準にしたものですか？
また、重複データを前提にしているのに商品番号がNullとはどういう状態ですか？
（商品番号がNullのものも重複のパターンとするという事ですか？）
↑見直すと、Nullというのは登録日時の事ですね。
それを前提に見直すと、
> ④重複データの登録日時が未来日 かつ NULLを持つ
というのは未来日のデータがあり、且つ、登録日時がNullのものもあるという状態でしょうか？

[MM71]

>未来日とか過去日というのは何を基準にしたものですか？
記載漏れしてました。SYSDATEを基準としています。
> ④重複データの登録日時が未来日 かつ NULLを持つ
というのは未来日のデータがあり、且つ、登録日時がNullのものもあるという状態でしょうか？
はい。その状態ということで合っております。

Answer 1

①登録日時が全て過去日を持つ商品番号と、そのぶら下がってる件数。
②登録日時が全て未来日を持つ商品番号と、そのぶら下がっている件数。
③登録日時が未来日が複数(2<=)ある かつ NULLを含まない商品番号と、そのぶら下がっている件数。※未来日のみカウント。
④登録日時が未来日 かつ NULLを持つ商品番号と、そのぶら下がっている件数。※未来日とNULLのみカウント。
⑤登録日時が全てNULLを持つ商品番号と、そのぶら下がっている件数。

上記条件の件数をそれぞれ求めて、その内の重複データのものという条件にしています。
※検証はしていませんので、あくまでイメージとして考えて下さい。

SELECT  商品番号
      , sum(case when not exists(select 1 from 商品 where 商品番号=x.商品番号 and (登録日時 > sysdate or 登録日時 is null) then 1 else 0 end) as ①
      , sum(case when not exists(select 1 from 商品 where 商品番号=x.商品番号 and (登録日時 < sysdate or 登録日時 is null) then 1 else 0 end) as ②
      , sum(case when 登録日時 > sysdate
                  and (select count(*) from 商品 where 商品番号=x.商品番号 and 登録日時 > sysdate) > 1 
                  and not exists(select 1 from 商品 where 商品番号=x.商品番号 and 登録日時 is null) 
            then 1 else 0 end) as ③
      , sum(case when (登録日時 > sysdate or 登録日時 is null)
                  and exists(select 1 from 商品 where 商品番号=x.商品番号 and 登録日時 > sysdate)
                  and exists(select 1 from 商品 where 商品番号=x.商品番号 and 登録日時 is null) 
            then 1 else 0 end) as ④
      , sum(case when not exists(select 1 from 商品 where 商品番号=x.商品番号 and 登録日時 is not null) then 1 else 0 end) as ⑤
FROM  商品 x
GROUP BY 商品番号
HAVING COUNT(商品番号) > 1