SQLで、日付が1秒ずれたデータを、同一データとして扱いたい

Question

前提・実現したいこと

SQLで、以下のようなテーブルから、合格した製品の数を求めようとしています。

＜列の説明＞
・productは、製品のidです。
・orderは、productのうちで最初に作った製品が１、次の製品が２となります。
(productが変わった場合、orderは再度1から始まります）
・timeは、判定した時刻です
・okは、trueが合格、falseが不合格です
・machineは判定装置のidです。１と２があります。

＜データの説明＞
・一つの製品につき2回検査があります。
・2回とも結果がtrueなら合格です。
・「productとorderが同じでtimeも同じ」または「productとorderが同じでtimeのずれが1秒」なら同じ製品です。
・1と2の検査の順番は決まってません。

＜テーブル＞
product|order|time|ok|machine
|:--|:--:|--:|--:|
1|1|2018-09-10 12:00:00|true|1
1|1|2018-09-10 12:00:01|false|2
1|2|2018-09-10 12:00:09|true|2
1|2|2018-09-10 12:00:10|true|1
1|3|2018-09-10 12:00:20|false|2
1|3|2018-09-10 12:00:20|true|1
2|1|2018-09-10 12:00:35|false|1
2|1|2018-09-10 12:00:35|true|2
1|1|2018-09-11 12:00:00|false|1
1|1|2018-09-11 12:00:00|false|2

＜出力したいデータ＞
|合格数|
|1|

発生している問題

以下が、作成したSQLです。

SELECT count(*)
FROM(
    SELECT order
    FROM table
    GROUP BY product,order,time 
    HAVING SUM(CASE is_ok WHEN 'true' THEN 1 WHEN 'false' THEN 0 END) in (2))as t1
)

「GROUP BY」で製品ごとのレコードを抽出し、「HANING」で2回とも結果がtrueのレコードを抽出しています。
しかし、これでは『「orderが同じでtimeのずれが1秒程度」なら同じ製品』という条件を実現できてません。
どのようにSQLを書けばいいでしょうか。

補足情報（FW/ツールのバージョンなど）

PostgreSQL10を使ってます。

Comments

[sazi]

条件が不明確です。１秒程度とありますが「程度」とはどのような範囲でしょう。また、「１秒程度」の範囲にない場合は、どう扱うのですか？（共に対象にしないのか、小さい方を基準にするとか）

[m.ts10806]

PostgreSQLをタグに追加してください

Answer 1

GROUP BY order,(timeの秒が奇数なら1秒引く)
でいいのでは？
と思ったけど駄目ですね。

律儀にlagを使えば良さそうです。

with tmp as(
SELECT ord,time,
lag(time) over(partition by product,ord order by time,machine) as ltime
FROM t
where ok)
select count(*)
  from tmp
 where time = ltime
    or time = ltime + interval '1 second'

Comments

[user968]

ありがとうございます。lag関数は知りませんでした。とても便利ですね。
この方法でうまくいきました。

Answer 2

orderは予約語なので、もし実際の項目名なら別な項目名への変更をお薦めします。

SELECT count(*)
FROM(
    SELECT product, "order"
    FROM table1
    GROUP BY product, "order"
    HAVING (max(time)- min(time)= interval '1 second' or max(time)= min(time)) 
       and sum(case when ok then 1 else 0 end)=2
) tmp

Comments

[user968]

ありがとうございます。実際の項目名は別なので大丈夫です。
「GROUP BY product, "order"」とすると「productとorderが同じでtimeが違っているレコード」を同じ製品に対する検査としてしまうので、結果が異なると思われます。

[sazi]

そうなんですか。では、サンプルのorder=3は実際には無いということですか？

[sazi]

「一つの製品につき2回検査があります。」とは同一のmachineでの話ですか？
それで、同一のmachineで２回OKだったら？
それとも別々なmachineで都合４回行って、その内２回がOKなら合格ってことですか？

[user968]

machine1での検査と、machine2での検査を1回ずつ行い、両方がOKなら合格です。
orderは製品を作った数（識別番号）なので、3以上もあります。

[sazi]

分かりました。では他の回答の方が適当ですね。

[sazi]

ただ、「「productとorderが同じでtimeが違っているレコード」を同じ製品に対する検査としてしまう」というのは、havingの条件で除外されていると思うんですが、結果的には正しくなかったと云うことなんですね。
ああ、別なものが含まれて正しい結果が得られないという事か

[user968]

「同じ製品に対する検査」という集合を作る条件は、group byで指定します。
havingは集合を作った後の操作です。

Answer 3

・productは、製品のidです。
・「productとorderが同じでtimeも同じ」または「productとorderが同じでtimeのずれが1秒」なら同じ製品です。

⇒パッと見、productが同じなら、同じ製品にも見えますが、
データの説明を見る限り、
productというよりproject id や　cycle idみたいな捉え方になりそうですね。

なお、SQLを作る際の考え方として、
machine 1の結果テーブル
machine 2の結果テーブルが存在し、
同じ製品の結果を抽出・カウントするイメージで十分じゃないですかね。

※実際の利用状況を考えると、
machine 1 の結果のOKが２つあっても、
合格ではなさそうなので。

SELECT	COUNT( 'X' )
FROM	table T1
			INNER JOIN table T2 ON(
					T2.machine	= 2
				AND	T2.product	= T1.product
				AND	T2.order	= T1.order
				AND	T2.time		<= T1.time + interval '1 second'
				AND	T2.time		>= T1.time - interval '1 second'
			)
WHERE	T1.machine	= 1
	AND	T1.ok		= 'true'
	AND	T2.ok		= 'true'

※見て分かると思いますが、
上記は、以下のような状況には対応していません。

product	order	time	ok	machine	コメント
1	1	2018-09-18 12:00:00	true	1	同一マシンの合格結果が１秒以内に複数存在
1	1	2018-09-18 12:00:01	true	2
1	1	2018-09-18 12:00:01	true	1
1	2	2018-09-18 12:00:00	true	1	同一マシンの合格結果が２秒以内に存在し
1	2	2018-09-18 12:00:01	true	2	間に異なるマシンの合格結果
1	2	2018-09-18 12:00:02	true	1
1	2	2018-09-18 12:00:03	true	2
1	3	2018-09-18 12:00:00	true	1	同一マシンの合格が２つ
1	3	2018-09-18 12:00:00	false	2
1	3	2018-09-18 12:00:01	true	1

Comments

[user968]

ありがとうございます。説明がわかりずらくて申し訳ございません。
「同じ製品」ではなく「同じ製品に対する検査結果」という意図で書きました。
machineごとの結果テーブルを抽出してカウントするのは思いつきませんでした。

Answer 4

こんなかな？

SELECT COUNT(1)
FROM table
WHERE ok = True
GROUP BY order, CAST(time AS date)
HAVING COUNT(1) >= 2 AND MAX(time) - MIN(time) <= '00:00:01'

sql_lover　さんの指摘により日付もグループピング

上は題意を間違えてました。
平文(サブクエリを使わない)にこだわって書き換えました。
実行速度はおそらく遅いです。

SELECT COUNT(1)
FROM table AS t1 
     INNER JOIN table AS t2
     ON t1.product = t2.product
         AND
        t1.order = t2.order
WHERE t1.ok
       AND
      t2.ok
       AND
      t1.machine = 1
       AND
      t2.machine = 2
       AND
      t1.time - t2.time BETWEEN '-00:00:01' AND '00:00:01'

Comments

[退会済みユーザー]

下記データの場合に期待した結果を返さないのでは？
order time ok machine
1 2018-09-10 12:00:00 true 1
1 2018-09-10 12:00:01 true 2
1 2018-09-11 12:00:00 true 1
1 2018-09-11 12:00:00 true 2

[hihijiji]

日付違い　抜けてました
追記します。

[退会済みユーザー]

CAST(time AS date)を集約キーに追加したら
下記データの場合に期待した結果を返さないのでは？
order time ok machine
1 2018-09-10 23:59:59 true 1
1 2018-09-11 00:00:00 true 2

[hihijiji]

そもそもその場合はproductが変更になるので、同一製品とはみなされません。

[hihijiji]

上productではなくorderでした。

[hihijiji]

ごめんなさい何か勘違いしてました。そんなことは書いてませんね。

[user968]

ありがとうございます。自己結合して、同じ製品への検査結果を同じレコードに入れればwhere文が使えるのですね。