SELECT結果から同じ条件のデータが5行以上あるものをSELECTしたい

Question

下記のコードとデータを確認してください。

SELECT a.患者コード,MAX(b.基準日) as 基準日,a.測定日,COUNT(*)
FROM A a 
LEFT JOIN B b ON a.患者コード = b.患者コード AND a.測定日 >= b.基準日
GROUP BY a.患者コード,a.測定日

患者コード	基準日	測定日	COUNT(*)
1111	20141220	20150821	7
1111	20141220	20150902	2
1111	20141220	20150911	3
1111	20141220	20150916	4
1111	20141220	20150918	3
1111	20141220	20150925	3
1111	20141220	20150929	2
1111	20141220	20151003	3
4989	20181112	20181227	2
15296	20180109	20180221	3
15296	20180109	20180307	3
34711	20200805	20201226	3
34711	20200805	20210116	2
35212	20151120	20160105	2
35212	20151120	20160120	4
35212	20151120	20160212	2
35212	20151120	20160309	3
35212	20151120	20160408	2

テーブルAとテーブルBは患者コードで紐づいています。
基準日に対して測定日があり、重複した測定データがあります。（COUNT(*)は同じ測定日のデータの個数を表しています。）
グループ化することで同じ測定日のレコードを1行にまとめています。

追記：また、基準日は1人の患者に対して2つ以上ある場合もあります。

このSELECT結果より、同じ患者コードかつ同じ基準日のレコードが5行以上あるものだけ
SELECTするにはどうしたらよろしいでしょうか。

追記：最終的には下記のようなSELECT結果になることを望んでいます。

患者コード	基準日	測定日	COUNT(*)
1111	20141220	20150821	7
1111	20141220	20150902	2
1111	20141220	20150911	3
1111	20141220	20150916	4
1111	20141220	20150918	3
1111	20141220	20150925	3
1111	20141220	20150929	2
1111	20141220	20151003	3
35212	20151120	20160105	2
35212	20151120	20160120	4
35212	20151120	20160212	2
35212	20151120	20160309	3
35212	20151120	20160408	2

Comments

[hoshi-takanori]

欲しい結果 (同じ患者コードかつ同じ基準日のレコードが5行以上あるもの) がどのようなものか、いまいち分かりづらいので、具体的にお示しください。

[mather]

> テーブルAとテーブルB
具体的なレコードのサンプルを提示しましょう。
同じ患者コードには基準日が一つしか無いようですが、Bテーブルはそういった構成になっているのでしょうか。

> 基準日に対して測定日があり
「a.測定日 >= b.基準日」とあるので測定日よりあとであることが条件なのですね？

> このSELECT結果より、同じ患者コードかつ同じ基準日のレコードが5行以上あるものだけSELECTする
「5行以上あるものだけ」とは、例えば「患者コード 1111, 35212 の行のみ」ということでしょうか？
具体的な期待される結果をレコードの形で示したほうが良いと思います。

Answer 1

Oracle なら WITH が使えると思うので、こんな感じでしょうか。

WITH t AS (
  SELECT a.患者コード,MAX(b.基準日) as 基準日,a.測定日,COUNT(*)
  FROM A a 
  LEFT JOIN B b ON a.患者コード = b.患者コード AND a.測定日 >= b.基準日
  GROUP BY a.患者コード,a.測定日
)
SELECT *
FROM t
WHERE 患者コード IN
  (SELECT 患者コード FROM t GROUP BY 患者コード HAVING COUNT(*) >= 5)

ちなみに、元のクエリーの LEFT JOIN って単に JOIN の方が良いのでは。

Answer 2

このSELECT結果より、同じ患者コードかつ同じ基準日のレコードが5行以上あるものだけ
SELECTするにはどうしたらよろしいでしょうか。

質問のSELECTを、患者コードと基準日でgroup by して件数が5件以上のものを条件にする。

select *
FROM A a 
     LEFT JOIN B b ON a.患者コード = b.患者コード AND a.測定日 >= b.基準日
where (a.患者コード, b.基準日) in (
        select 患者コード, max_基準日
        from (
        	-- 質問のSELECT
          SELECT a.患者コード,MAX(b.基準日) as max_基準日,a.測定日
          FROM A a 
          LEFT JOIN B b ON a.患者コード = b.患者コード AND a.測定日 >= b.基準日
          GROUP BY a.患者コード,a.測定日
        ) b
        group by 患者コード, max_基準日
        having count(*) >= 5
      )

Comments

[JaguarSugar]

すみません、エラーになります。

ORA-00904: "基準日": 無効な識別子です。
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
行4 列4でエラー

[sousuke]

Oracleはちょっとわからないんですがcount(*)の列にエイリアスつけなくても大丈夫なんです？

あとこれはそもそも論になってしまいますが「select max(基準日) ~~ GROUP BY a.患者コード,a.測定日」で基準日をカウントするとなるとmaxの中に基準日が消え失せてる可能性があるんでSQL見ても正直何がしたいのかわかりにくいですね…

[sazi]

@JuguarSugarさん
SQLを変更しました。これでも同じエラーが出るなら、参照しているテーブルに基準日が無いという事になります。
@sousukeさん
参照していないからエラーにはならないと思いますが、使用しないので回答からは消しました。
基準日はcount()ではなくMax()ですので、気になると言われているポイントが良く分かりません。

[sazi]

@JuguarSugarさん
訂正。基準日はbテーブルですね。
SQL修正します。

[JaguarSugar]

@sazi さん
おお、いきました。
ありがとうございました。

[sousuke]

@sazi氏　仕組みがわからないので想像ですが、あくまで「max(基準日)の数」なので
『患者コード:35212、基準日:20151121、測定日:20160105』
っていう同一測定日の基準日違いのデータを足すとmax(基準日)が容易にずれて
中のselectの結果が

患者コード:35212、基準日:20151121、測定日:20160105
患者コード:35212、基準日:20151120、測定日:20160120
患者コード:35212、基準日:20151120、測定日:20160212
患者コード:35212、基準日:20151120、測定日:20160309
患者コード:35212、基準日:20151120、測定日:20160408

となっての元のデータに一切の変更はなくても「基準日:20151120」の
count(*)は４になって消えるのでそもそもSQL的に微妙では？ってことです。

maxせずにBに対して患者コードと基準日の数だけ直接数える等できないのかなあ？って思います。

[JaguarSugar]

@sousukeさん
説明不足でしたが、
基準日と測定日の間に別の基準日が入ることはないため sazi さんの回答で特に問題ありませんでした。

[sousuke]

@JuguarSugar氏
であれば多分大丈夫かな。あとは基準日と測定日の間に基準日が入ることがなく、
基準日1<測定日x<基準日2<測定日yと来た場合
（つまり同一患者コードで次の基準と測定が来た場合）は基準日2以降のcount(*)
(同じ測定日のデータの個数と言っている部分)は
倍々に増えていくことは覚えておいたほうがいいと思います。