betweenの範囲をサブクエリから取得して最頻値を計算したい

Question

前提

サブクエリで得たタイムスタンプの開始と終了時刻を使って、別のテーブルのカラムの最頻値を計算して、先のサブクエリの結果に追加したいのですが、できません。

実現したいこと

質問用に２つテーブルを用意しました。
t_test_seisanテーブルはタイムスタンプとその時刻の生産品目名を記録

CREATE TABLE IF NOT EXISTS public.t_test_seisan
(
    id integer,
    ts timestamp without time zone,
    product_nm character varying COLLATE pg_catalog."default"
)

t_test_inspectテーブルは検査タイムスタンプと計測値v_1とV_2を記録

CREATE TABLE IF NOT EXISTS public.t_test_inspect
(
    id integer,
    ts timestamp without time zone,
    v_1 integer,
    v_2 integer
)

サンプルデータ１ for t_test_seisan
1,"2022-12-20 14:15:00","品名1"
2,"2022-12-20 14:16:00","品名1"
3,"2022-12-20 14:17:00","品名1"
4,"2022-12-20 14:18:00","品名1"
5,"2022-12-20 14:19:00","品名1"
6,"2022-12-20 14:20:00","品名2"
7,"2022-12-20 14:21:00","品名2"
8,"2022-12-20 14:22:00","品名2"
9,"2022-12-20 14:23:00","品名3"
10,"2022-12-20 14:24:00","品名3"
11,"2022-12-20 14:25:00","品名3"
12,"2022-12-20 14:26:00","品名3"

サンプルデータ２ for t_test_inspect
1,2022/12/20 14:15,11 ,112
2,2022/12/20 14:16,11 ,111
3,2022/12/20 14:17,12 ,111
4,2022/12/20 14:18,12 ,111
5,2022/12/20 14:19,11 ,112
6,2022/12/20 14:20,21 ,122
7,2022/12/20 14:21,21 ,121
8,2022/12/20 14:22,22 ,121
9,2022/12/20 14:23,33 ,131
10,2022/12/20 14:24,31 ,133
11,2022/12/20 14:25,31 ,133
12,2022/12/20 14:26,32 ,131

尚、サンプルデータでは両テーブルのタイムスタンプが完全一致してますが、実際のデータではその限りではありません。
ここで、以下のQueryで生産品目毎の開始/終了時刻が得られます。

SELECT
  min(ts) AS from_tm,
  max(ts) AS to_tm,
  product_nm
FROM t_test_seisan
GROUP BY product_nm
ORDER BY 1

実行結果1：

また、最頻値を求める方法ですが、上図の1行目の開始/終了時刻を見て、以下のQueryを書けば、品名1の最頻値は得られるところまではできました。

SELECT 
  mode() WITHIN GROUP (ORDER BY v_1) AS m_v_1,
  mode() WITHIN GROUP (ORDER BY v_2) AS m_v_2
FROM t_test_inspect
WHERE
  ts BETWEEN '2022-12-20 14:15:00' AND '2022-12-20 14:19:00'

実行結果2：

最終的に得たいのは実行結果1の表に最頻値のm_v1とm_v2カラムを付けた表です。

試したこと

相関副問合せ(相関サブクエリ)という機能を使えば、できそうなのでネット検索して、色々試したのですが、どうしても書き方が分かりません。

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

PostgreSQL 10
ここにより詳細な情報を記載してください。

Answer 1

もう少しスマートな書き方がありそうですが、たとえば以下のようなクエリで実現できそうです。

SELECT 
  MIN(A.ts) AS from_tm, 
  MAX(A.ts) AS to_tm, 
  A.product_nm,
  (SELECT mode() WITHIN GROUP (ORDER BY B.v_1) FROM t_test_inspect AS B WHERE B.ts BETWEEN MIN(A.ts) AND MAX(A.ts)) AS m_v_1,
  (SELECT mode() WITHIN GROUP (ORDER BY B.v_2) FROM t_test_inspect AS B WHERE B.ts BETWEEN MIN(A.ts) AND MAX(A.ts)) AS m_v_2
FROM t_test_seisan AS A
GROUP BY product_nm
ORDER BY product_nm

Comments

[hideki.]

早々の回答ありがとうございます。
なるほど、このように書けば良いのですね。
WITH句やJOINを使ったり試行錯誤しましたが、エラーで実行できませんでした。
ベストアンサーにさせて頂きます。