EXISTSと集合

投稿日：2016年11月30日

前回に引き続きEXISTSの問題です。

下記のようなテーブル(projects)があり、

project_id | step_nbr | status
————+———-+——–
AA100 | 0 | 完了
AA100 | 1 | 待機
AA100 | 2 | 待機
B200 | 0 | 待機
B200 | 1 | 待機
CS300 | 0 | 完了
CS300 | 1 | 完了
CS300 | 2 | 待機
CS300 | 3 | 待機
DY400 | 0 | 完了
DY400 | 1 | 完了
DY400 | 2 | 完了

1番まで完了しているproject_idを求めるとします。回答として下記のデータが得られればOKです。

project_id
————
CS300
DY400

※本ではstp_nbr=2が完了しているもの(上記の例でいうとDY400)を除いていましたが、私の場合含めました。

この場合、EXISTSの考え方を使うと下記のようになるでしょう。

この場合も、step_nbrが0と１をともに満たしていないといけないのでEXISTSではなく逆のNOT EXISTSを使います。

SELECT
  DISTINCT p2.project_id
FROM
  projects p2
WHERE
  NOT EXISTS
  (
  SELECT
     p1.*
  FROM
    projects p1
  WHERE
    1 = CASE WHEN p1.step_nbr <= 1 AND p1.status <> '完了' THEN 1 ELSE NULL END
    AND
    p2.project_id = p1.project_id
);

SELECT

DISTINCT p2.project_id

FROM

projects p2

WHERE

NOT EXISTS

(

SELECT

p1.*

FROM

projects p1

WHERE

1 = CASE WHEN p1.step_nbr <= 1 AND p1.status <> '完了' THEN 1 ELSE NULL END

AND

p2.project_id = p1.project_id

);

なおHAVINGの考えを使って下記のように解くこともできます。こっちのほうがイメージはしやすいかも・・・・

SELECT
  p.*,
  CASE WHEN p.step_nbr <=1 AND p.status ='完了' THEN 1 ELSE 0 END  AS add_point
FROM
 projects p

SELECT

p.*,

CASE WHEN p.step_nbr <=1 AND p.status ='完了' THEN 1 ELSE 0 END AS add_point

FROM

projects p

イメージとしては上記のSQLから下記のようなテーブルをイメージしGRUOP BYしてadd_pointの累積を考えます。

project_id | step_nbr | status | add_point
————+———-+——–+———–
AA100 | 0 | 完了 | 1
AA100 | 1 | 待機 | 0
AA100 | 2 | 待機 | 0
B200 | 0 | 待機 | 0
B200 | 1 | 待機 | 0
CS300 | 0 | 完了 | 1
CS300 | 1 | 完了 | 1
CS300 | 2 | 待機 | 0
CS300 | 3 | 待機 | 0
DY400 | 0 | 完了 | 1
DY400 | 1 | 完了 | 1
DY400 | 2 | 完了 | 0

SELECT
  p.project_id
FROM
 projects p
GROUP BY
 p.project_id
HAVING SUM( CASE WHEN p.step_nbr <=1 AND p.status ='完了' THEN 1 ELSE 0 END )  >= 2 ;

SELECT

p.project_id

FROM

projects p

GROUP BY

p.project_id

HAVING SUM( CASE WHEN p.step_nbr <=1 AND p.status ='完了' THEN 1 ELSE 0 END ) >= 2 ;