継続期間中に1度でもid変更があった場合除外したい

Question

前提・実現したいこと

ユーザーが一定期間、同一端末を使用しているか調べるクエリを書こうと思っています。

条件としては、

・端末使用開始日はstart_dateでdataテーブルの中に入っています。
・今回起算日となるtrouble_dateが存在した2週間の期間の中で１日後,２日後,3日後・・・14日後いずれかの日にも期間中に端末を変更する事があれば、データとして除外する形にしたいです。

クエリとしては機能しませんがニュアンスとしては下記のようなものを想定しています。
14日間に一度でも、の条件の入れ方が分からず詰まってます。よろしくお願いします。

該当のソースコード

SELECT　user_id,serial_number,start_date,trouble_date
FROM data
WHERE start_date <= trouble_date
AND serial_number=serial_number AND trouble_date interval -14

Comments

[dodox86]

説明が少し分からないのですが、言い方を変えると、trouble_dateに値があったとして、start_date からtrouble_date までの期間が14日を超えていたら除外しない、ということでしょうか。

[sou10]

間違えて関係の無い記述をしてしまい申し訳ありません。
trouble_dateの直後14日間であり、start_dateとの差分は特に関係がありません。

[dodox86]

すみません、相変わらず分からないのですが、trouble_dateの直後14日間に何があると除外する（あるいは除外しない）のでしょうか。
trouble_dateの直後14日間の間に、別のserial_numberのstart_dateがある、つまり別の端末を使ったか否か、なのでしょうか。それもstart_dateは関係ないのですか。

※この質問を読んだ他の閲覧者、回答者の方も恐らく分からないのではないか？と懸念してのコメントです。

[dodox86]

ああ、質問件名で「継続期間中に1度でもid変更があった場合除外したい」とありますね。

つまり、trouble_dateの直後14日間の間に、同じユーザーのidで別のserial_numberのstart_dateがある（＜別の端末を使い始めた？）ものは除外する、というのが正しいでしょうか。

[sou10]

はい、別の端末を使い始めるとserial_numberが変わる形です。ユーザーidに対しtrouble_dateが存在し、直後14日間に端末変更があった場合（serial_number）が切り替わった場合除外したい形です…

[yuxki]

start_dateとtrouble_dateには、どういった型の、どのような値が入りますか？

[sou10]

こちらも漏れていて申し訳ないです。start_date、trouble_date共にdate型になります。2019-01-01のような形です。

[dodox86]

正確に言うと、SQLiteはカラムの型にDate相当は無いので、"YYYY-MM-DD"な日付の文字列でしょうね。

Answer 1

想像を働かせて書いてみましたが、こんな感じのイメージでしょうか？
副問い合わせの中でtrouble_dateの存在するデータをとって、
serial_numberが異なるidのデータと内部結合しています。
14日の判定はbetween句を使と、datetime関数を使っています。

SELECT 
　　　before_data.user_id
,	before_data.serial_number	AS serial_number_before
,	after_data.serial_number	AS serial_number_after
FROM
	(
	SELECT　
		user_id
	,	serial_number	
	,	start_date
	,	trouble_date
	FROM 
		data
	WHERE start_date <= trouble_date
	) AS before_data // trouble_dateが存在する
INNER JOIN data AS after_data
ON     before_data.user_id	    = after_data.user_id
AND	before_data.serial_number != after_data.serial_number
AND	before_data.start_date	<= after_data.start_date
WHERE 
after_data.start_date BETWEEN before_data.trouble_date  and  datetime(before_data.trouble_date, '+14 days') 

検証ができておりませんこと、ご承知ください...

Comments

[sou10]

急用でお礼が遅くなりすみません。
JOIN後でこのような書き方をすれば良かったのかと勉強になりました。
有難うございました！