SQLクエリを実行するたびに抽出されるレコードの件数が変わってしまう

Question

PostgreSQL 14で以下のクエリを実行したところ、実行するたびに抽出されるレコードの件数がおよそ1割前後の幅で変動し困っています。

テーブルの構成は (id1 date, id2 varchar, 計測時刻 timestamp, 属性A varchar, 条件1 integer) で、時系列で変化する属性Aが目的の順序ABCDで推移している個体を抽出することが目的です。
もちろん実行中にレコードの書き換えは行われておらず、サブクエリsb1の時点では件数の揺れは見られませんでした。

環境は Windows 10 Pro for Workstation, PostgreSQL 14.3, pgAdmin 4で、localhost上のDBに対して実行しています。

どのような原因が考えられるでしょうか。また可能なら、このような場合にどのような手段で原因に検討をつけるのが良いでしょうか。どうかご教示お願い致します。

WITH sb2 AS(
	SELECT id1, id2,
	LAG (属性A, 2) OVER 条件 AS 前2,
	LAG (属性A, 1) OVER 条件 AS 前1,
	属性A,
	LEAD (属性A, 1) OVER 条件 AS 後1
	FROM(
		-- 個体ごとに同一属性のレコードは1件を残して取捨する
		SELECT DISTINCT ON(id1, id2, 属性A) id1, id2, 属性A, 計測時刻
		FROM テーブル1
		WHERE 条件1 = 1
		AND 属性A is not null
		AND 計測時刻::time >= time '8:00:00' AND 計測時刻::time < time '20:00:00'
	)sb1
	WINDOW 条件 AS (PARTITION BY id1, id2 ORDER BY id1 ASC, id2 ASC, 計測時刻 ASC)
)
SELECT * FROM sb2
WHERE 前2 = 'A' AND 前1 = 'B' AND 属性A = 'C' AND 後1 = 'D'

Comments

[sazi]

無知で申し訳ないですが、
WINDOW 条件 AS (PARTITION BY id1, id2 ORDER BY id1 ASC, id2 ASC, 計測時刻 ASC)
とは何でしょう？
リファレンスなどで示してもらえると助かります。

[Orlofsky]

質問にCREATE TABLE文くらいは載せては？

Answer 1

件数については、DISTINCTを使用せずに、order by を明示してlimitで件数を限定するようにしてみてください。

Comments

[heji]

ORDER BYを使わない限りクエリの抽出される順序が一定に定まらないことが、続くクエリの抽出結果を不安定にする原因のようでした。
ORDER BYの使用を促してくださったことが助けになりました。

ご回答ありがとうございました。

Answer 2

不要かもしれませんが、DISTINCT ONを使用せず書き換えたクエリを残しておきます。

DISTINCT ONはLIMITのような最終的な取得件数を制限する目的ではなく、例として AAAnullnullBBnullCCCDnullnull という順で属性Aが推移した個体について、nullおよび属性Aが重複するレコードを除外し、ABCDという4件のレコードに整理するために考えたものでした。こうして整理した上で、LAGとLEADで時系列の推移を確認しました。
（AABBnullnullCD -> ABCD、BBCnullBAAnullBCCD -> BCBABCD、など）

推移の間にnullが挟まっても良い点や、2種類のIDの掛け合わせで個体を識別している点（WINDOW句の使用）など、私の説明の足りない点が多かったかと思います。テーブル自体についても情報が足りない中で原因を考えてくださったことは申し訳ありません。

またそもそも当初のクエリでは、たとえDISTINCT ONの対象になるレコードの順序が一定に定まったとしても、BABCDのような推移をした個体がBACDの時系列に整理されるため、ABCDの推移をしているにも関わらず正しく抽出されない初歩的な間違いがありましたので、その修正も含んでいます。まぎらわしい誤解を含む質問でした。ごめんなさい。

CREATE TABLE テーブル1(
id1 date, id2 varchar, 計測時刻 timestamp without time zone, 属性A varchar, 条件1 integer
);
-- id1は計測開始日。id2は計測開始時に再割り当てをする。
-- id2は計測開始日が異なると重複するおそれがあるのでid1とかけ合わせて識別する。
INSERT INTO テーブル1
VALUES ('2022-06-01', '0001', '2022-06-01 19:30:00', null, 1) 
VALUES ('2022-06-01', '0001', '2022-06-01 20:30:00', 'A', 1) 
VALUES ('2022-06-01', '0001', '2022-06-01 21:30:00', 'B', 1) 
VALUES ('2022-06-01', '0001', '2022-06-01 22:30:00', null, 1) 
VALUES ('2022-06-01', '0001', '2022-06-01 23:30:00', 'C', 1) 
VALUES ('2022-06-01', '0001', '2022-06-02 0:30:00', null, 1)
VALUES ('2022-06-01', '0001', '2022-06-02 1:30:00', 'D', 1)
VALUES ('2022-06-03', '0010', '2022-06-03 8:30:00', 'B', 1) 
VALUES ('2022-06-03', '0010', '2022-06-03 9:30:00', 'A', 1) 
VALUES ('2022-06-03', '0010', '2022-06-03 10:30:00', 'B', 1) 
VALUES ('2022-06-03', '0010', '2022-06-03 11:30:00', 'C', 1) 
VALUES ('2022-06-03', '0010', '2022-06-03 12:30:00', 'D', 1) 
VALUES ('2022-06-03', '0010', '2022-06-03 13:30:00', null, 1);

WITH wth AS(
	SELECT id1, id2,
	LAG (属性A, 2) OVER 条件 AS 前2,
	LAG (属性A, 1) OVER 条件 AS 前1,
	属性A,
	LEAD (属性A, 1) OVER 条件 AS 後1
	FROM(
		SELECT * FROM(
			SELECT id1, id2, 属性A, 属性A IS DISTINCT FROM LAG(属性A) OVER 条件 AS ここで変化, 計測時刻
			FROM テーブル1
			WHERE 条件1 = 1
			AND 属性A is not null
			AND 計測時刻::time >= time '8:00:00' AND 計測時刻::time < time '20:00:00'
			WINDOW 条件 AS (PARTITION BY id1, id2 ORDER BY id1 ASC, id2 ASC, 計測時刻 ASC)
		)sb1
		WHERE ここで変化 = TRUE
	)sb2
	WINDOW 条件 AS (PARTITION BY id1, id2 ORDER BY id1 ASC, id2 ASC, 計測時刻 ASC)
)
SELECT * FROM wth
WHERE 前2 = 'A' AND 前1 = 'B' AND 属性A = 'C' AND 後1 = 'D'