我必須在我的資料庫中使用表。Table_A 包含軌跡段的統計資訊 ( segment_id, start/end coordinates, length)。
CREATE TABLE table_A(segment_id INT NOT NULL, session_id INT NOT NULL,
segment_start_sec INT NOT NULL, lat_start DOUBLE PRECISION, lon_start DOUBLE PRECISION,
lat_end DOUBLE PRECISION, lon_end DOUBLE PRECISION, length DOUBLE PRECISION)
Table_B 包含采樣率GPS下所有段的行日志。1 second
CREATE TABLE table_B(session_id INT NOT NULL,seconds INT NOT NULL,
lat DOUBLE PRECISION, lon DOUBLE PRECISION)
現在我想選擇GPS每個段的日志，從開始到結束。所以我做查詢：
SELECT a.segment_id,
b.seconds,
b.lat,
b.lon
FROM table_A a
LEFT JOIN table_B b
ON a.session_id = b.session_id
問題：
這將回傳最長段的從開始到結束的所有內容。所以：
-- check segment 0 total records
SELECT COUNT(*)
FROM table_A a
LEFT JOIN table_B b
ON a.session_id = b.session_id
WHERE a.segment_id=0
289
和
-- check segment 12 total records
SELECT COUNT(*)
FROM table_A a
LEFT JOIN table_B b
ON a.session_id = b.session_id
WHERE a.segment_id=12
289
-- same as segment_id=16
顯然這是GPS最長段的日志，但所有段都以相同的行數結束。
GPS在這種情況下，如何根據每個段的開始和結束時間過濾每個段的日志？
我在這個dbfiddle中重現了這些表（完整），包括上述所有查詢。
uj5u.com熱心網友回復：
因此，您希望GPS一一獲取所有軌跡段。
由于table_A不存盤段結束的秒數，您可以使用該LEAD函式從下一個段的 中獲取它segment_start_sec。但是現在你對最后一段有問題，因為它沒有另一個段可以從中獲取結束秒值，所以它的結束秒值將等于table_Bseconds中相同的最大值。session_id
我應該注意，在這個解決方案中，每對段都有“公共”行，這是當前行的結尾和下一行的開始。
最后查詢看起來像這樣
SELECT a.segment_id,
b.seconds,
b.lat,
b.lon
FROM (
SELECT
*,
LEAD(segment_start_sec) OVER (PARTITION BY session_id ORDER BY segment_id) segment_end_sec
FROM table_A
) a
LEFT JOIN table_B b
ON a.session_id = b.session_id
AND b.seconds BETWEEN a.segment_start_sec AND COALESCE(a.segment_end_sec,
(SELECT MAX(seconds)
FROM table_B b1
WHERE b1.session_id = b.session_id))
它為您的樣本資料回傳 291 行。
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/504846.html
標籤：sql PostgreSQL 加入
上一篇：如何在單個查詢中連接多個查詢？