我有兩個表如下：
T1
event_id device_id event_time var1 var2
1 A 2021-01-01 5 6
2 C 2021-01-02 8 7
3 B 2021-01-05 1 6
4 C 2021-01-07 7 7
5 D 2021-01-12 8 9
6 C 2021-01-18 3 4
7 B 2021-01-21 7 1
T2
device_id incident_time
B 2021-01-06
C 2021-01-17
我希望輸出是
device_id incident_time event_id event_time var1 var2
B 2021-01-06 3 2021-01-05 1 6
C 2021-01-17 6 2021-01-18 3 4
所以，我想找到event_id，event_time，var1和var2在一個單一的事件T1，其中的事件時間是最接近incident_time于每個設備T2，無論是否event_time是大于或小于incident_time。到目前為止，我丑陋（且不正確）的代碼：
select T2.device_id, T2.incident_time, (select distinct on (device_id) event_id
from T1
where device_id = T2.device_id
and event_time <= T2.incident_time
order by device_id, event_time desc) as event_id
from T2;
如何使用 Join 陳述句更優雅地完成此操作？或者括號中的 select as 陳述句是否正確？如何包含所有必需的列？謝謝
uj5u.com熱心網友回復：
跳過子查詢，改為執行連接。就像是：
select distinct on (T2.device_id) T2.device_id, T2.incident_time,
T1.event_id, T1.event_time, T1.var1, T1.var2
from T2
left join T1 ON T1.device_id = T2.device_id
order by T2.device_id, ABS(T2.incident_time - T1.event_time)
uj5u.com熱心網友回復：
架構和插入陳述句：
create table T1(event_id int, device_id varchar(2), event_time date, var1 int, var2 int);
insert into T1 values(1 ,'A' ,'2021-01-01', 5, 6);
insert into T1 values(2 ,'C' ,'2021-01-02', 8, 7);
insert into T1 values(3 ,'B' ,'2021-01-05', 1, 6);
insert into T1 values(4 ,'C' ,'2021-01-07', 7, 7);
insert into T1 values(5 ,'D' ,'2021-01-12', 8, 9);
insert into T1 values(6 ,'C' ,'2021-01-18', 3, 4);
insert into T1 values(7 ,'B' ,'2021-01-21', 7, 1);
create table T2 (device_id varchar(2), incident_time date);
insert into T2 values('B', '2021-01-06');
insert into T2 values('C', '2021-01-17');
查詢 1（使用 cte 和 row_number() 視窗函式）：
with cte as
(
select T2.device_id, T2.incident_time,T1.event_id,T1.event_time,T1.var1, T1.var2,
row_number()over(partition by T2.device_id order by ABS(T2.incident_time-T1.event_time))rn
from T2 left join T1 on T2.device_id=T1.device_id
)
select device_id, incident_time, event_id, event_time, var1, var2 from cte
where rn=1
輸出：
| 設備編號 | 事件時間 | 事件編號 | 事件時間 | 變數1 | 變數2 |
|---|---|---|---|---|---|
| 乙 | 2021-01-06 | 3 | 2021-01-05 | 1 | 6 |
| C | 2021-01-17 | 6 | 2021-01-18 | 3 | 4 |
查詢 2（使用 distinct on ）：
select distinct on (T2.device_id) T2.device_id, T2.incident_time,
T1.event_id, T1.event_time, T1.var1, T1.var2
from T2
left join T1 ON T1.device_id = T2.device_id
order by T2.device_id, ABS(T2.incident_time - T1.event_time)
輸出：
| 設備編號 | 事件時間 | 事件編號 | 事件時間 | 變數1 | 變數2 |
|---|---|---|---|---|---|
| 乙 | 2021-01-06 | 3 | 2021-01-05 | 1 | 6 |
| C | 2021-01-17 | 6 | 2021-01-18 | 3 | 4 |
db<>在這里擺弄
uj5u.com熱心網友回復：
我會通過“橫向交叉連接”使用“獲取前 1 行并列”來獲取每個事件的所有合適的事件。
Select T1.event_id, T1.device_id, T1.event_time, T2.incident_time, T1.var1, T1.var2
From T2 Cross Join Lateral
(Select T1.event_id, T1.device_id, T1.event_time, T1.var1, T1.var2
From T1
Where T1.device_id=T2.device_id
Order by Abs(T1.event_time-T2.incident_time)
Fetch First 1 Rows With Ties) As T1
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/359843.html
標籤：sql PostgreSQL的
上一篇：如何使用查詢連接sql中的兩行