条件に合致したデータをうまくとりたい

Question

###以下の条件に合うデータをユーザーIDでグループ化して取りたい
前日の hoge が1以上で
2日前から4日前の hoge が 0のときのデータ

テーブル名
user_data

SELECTするカラム
ユーザーID
ユーザー名

テーブルのカラム構成は以下

rp_date date NOT NULL,
user_id int(10) UNSIGNED NOT NULL,
user_name varchar(1024) NOT NULL,
hoge int(11) NOT NULL,

カラム名などは適当です

必要なカラムだけ書いてます

rp_dateに日付が入ってます

よろしくお願いします。

###発生している問題

hoge が０のときデータがうまく取れない

###該当のソースコード

 UNION SELECT user_id AS ユーザーID, user_name AS ユーザー名
 FROM `user_data`
 WHERE (SELECT SUM(hoge) FROM user_data WHERE rp_date <= DATE_SUB(CURRENT_DATE(),INTERVAL 4 DAY) AND rp_date >= DATE_SUB(CURRENT_DATE(),INTERVAL 2 DAY)) = 0 
 AND 
 (SELECT SUM(hoge) FROM user_data WHERE rp_date = DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)) > 0 
 GROUP BY user_id;```

Answer 1

user_idの条件が無く相関問い合わせになっていないからだと思います。

SELECT user_id AS ユーザーID, user_name AS ユーザー名
FROM user_data as tgt
WHERE 
    (SELECT SUM(hoge) FROM user_data 
     WHERE rp_date <= DATE_SUB(CURRENT_DATE(),INTERVAL 4 DAY) 
       AND rp_date >= DATE_SUB(CURRENT_DATE(),INTERVAL 2 DAY)
       AND user_id=tgt.user_id) = 0 
AND (SELECT SUM(hoge) FROM user_data 
     WHERE rp_date = DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
       AND user_id=tgt.user_id) > 0 
     ) > 0 
GROUP BY user_id, user_name

Comments

[noob_noob]

なるほど。ありがとうございます。試してみます。

[noob_noob]

すいません。上手くとることができませんでした。

[sazi]

元々のSQLに相関問い合わせになるように条件を追加しただけなので、
どのように上手くいっていないかをコメントもらえるとアドバイスできるかもしれません。

Answer 2

前日の hoge が1以上で

2日前から4日前の hoge が 0のときのデータ

前日の hoge が1以上「か」
2日前から4日前の hoge が 0のときのデータ
でよろしいでしょうか？

select * from user_data where 0
or (rp_date = curdate() - interval 1 day and hoge=1)
or (rp_date between curdate() - interval 4 day and curdate() - interval 2 day
and hoge=0) ;

追記

※以下修正します。1ではなく1以上でした

質問者さんから提示された追加仕様を元に、判断基準を4つ用意しました
(1)1日前でhoge>=1のデータが最低1つ必要
(2)1日前でhoge<1のデータは存在してはいけない
(3)24日前でhoge=0のデータが最低1つ必要
(4)24日前でhoge=0以外のデータは存在してはいけない

その上で本日を'2017-12-14'として計算します

create table user_data (id int primary key,rp_date date,user_id int,hoge int);
insert into user_data values
(1,'2017-12-13',1,0),
(2,'2017-12-13',1,1),
(3,'2017-12-13',2,1),
(4,'2017-12-12',2,0),
(5,'2017-12-12',2,0),
(6,'2017-12-12',2,1),
(7,'2017-12-13',3,1),
(8,'2017-12-13',3,1),
(9,'2017-12-12',3,0),
(10,'2017-12-12',3,0),
(11,'2017-12-12',3,0),
(12,'2017-12-11',3,0),
(13,'2017-12-10',3,0);

• user_id=1は条件2と条件3違反
• user_id=2は条件4違反
• user_id=3は全ての条件をクリア

具体的にはこう

select user_id
,sum((rp_date=curdate()-interval 1 day)*(hoge>=1)) as j1
,sum((rp_date=curdate()-interval 1 day)*(hoge<1)) as j2
,sum((rp_date between curdate()-interval 4 day and curdate()-interval 2 day)*(hoge=0)) as j3
,sum((rp_date between curdate()-interval 4 day and curdate()-interval 2 day)*(hoge!=0)) as j4
from user_data
group by user_id

これを元にuser_idのみ抽出するのでhavingでまとめます

select user_id
from user_data
group by user_id
having sum((rp_date=curdate()-interval 1 day)*(hoge>=1))>0
and sum((rp_date=curdate()-interval 1 day)*(hoge<1))=0
and sum((rp_date between curdate()-interval 4 day and curdate()-interval 2 day)*(hoge=0))>0
and sum((rp_date between curdate()-interval 4 day and curdate()-interval 2 day)*(hoge!=0))=0;

上記本日が'2017-12-14'でない場合は「curdate()」の部分を「'2017-12-14'」に書き換えてください

Comments

[noob_noob]

すいません。質問がわかりにくかったです。

前日の hoge が1以上「かつ」
2日前から4日前の hoge が 0のときのデータ
がほしいです。

[yambejp]

user_idとrp_dateの組み合わせはユニークですか
つまり今日が2017-12-14だとして
user_id=1のユーザーがrp_date=2017-12-13となるデータを
複数持つのか？ということです、その上で

> 前日の hoge が1

とは、複数持つデータの１つが1ならいいのか、全てが1でなくてはならないのか仕様をきめてください。また、どうように

> 2日前から4日前の hoge が 0のとき
これは2日前から4日前のhoge一つで0を含む時なのか全てが0のときなのか
定義が必要です。また全てが0である必要あるとき
2日前=0、3日前データなし、4日前=0
のようなデータは条件と合致するのか提示が必要です

[noob_noob]

>user_idとrp_dateの組み合わせはユニークですか
いえユニークではありません。複数持ちます。
そのうえで全ての hoge が1以上でないといけません。

>2日前から4日前のhoge一つで0を含む時なのか全てが0のときなのか
定義が必要です。
全てが0のときです。2日前=0、3日前データなし、4日前=0という条件も合致します。
つまり 2日前から4日前の hoge が０又はデータなしの時になります。

説明不足ですいません。
よろしくお願いいたします

[yambejp]

もう1つだけ、1日前のhoge=1および2~4日前のhoge=0は
各最低でも1つは必要というでいいですよね？

[noob_noob]

1日前の hoge > 0 かつ 2~4日前のhoge=0(又はデータなし)
のデータを取得したいということになります。

1日前の条件と2~4日前の条件両方に合致するものがとりたいという意味です

お答えになっているでしょうか。

[yambejp]

サンプルを追記しておきました
ちなみにuser_nameは正規化して別テーブルで管理するものとし
省略してあります

[noob_noob]

ありがとうございます！とても参考になりました！

[noob_noob]

すいません。やりたいことと若干ずれていました。

[yambejp]

そうですか、お役に立てなくて残念です
仕様が固まったらサンプルをつけて再提示されるとよろしいかと