給定一個包含完整記錄的每小時表 A，例如：
User Hour Purchase
Joe 1 0
Joe 2 0
Joe 3 0
Joe 4 1
Joe 5 0
Joe 6 0
Joe 7 0
Joe 8 1
Joe 9 1
Joe 10 0
以及它的一個子集 B，例如
User Hour Purchase
Joe 3 0
Joe 9 1
Joe 10 0
我只想保留那些來自 A 的記錄，這些記錄在 B 中或在 B 子集后面最多 2 小時，沒有重復，例如
User Hour Purchase
Joe 1 0
Joe 2 0
Joe 3 0
Joe 7 0
Joe 8 0
Joe 9 1
Joe 10 0
如何通過內部連接實作結果，而不重復（在本例中為 8 和 9 小時）并為 B 中的小時保留正確的購買值？（這是一個 MWE，假設多個用戶和時間戳而不是小時）
uj5u.com熱心網友回復：
這是在子句INNER中具有適當條件的簡單連接：ON
SELECT DISTINCT a.*
FROM a INNER JOIN b
ON b.User = a.User AND a.Hour BETWEEN b.Hour - 2 AND b.Hour
WHERE如果您想要特定用戶的結果，您可以添加一個子句：
WHERE a.User = 'Joe'
uj5u.com熱心網友回復：
考慮以下簡單的方法
select * from tableA a
where exists (
select 1 from tableB b
where a.hour between b.hour - 2 and b.hour
and a.user = b.user
)
如果應用于您的問題中的樣本 - 輸出是
我希望在你的真實情況下你有datetime或timestamp而不是hour列，所以在這種情況下你需要稍微修改where a.hour between b.hour - 2 and b.hour上面的部分。它看起來像
where a.datetime between datetime_sub(b.datetime, interval 2 hour) and b.datetime
uj5u.com熱心網友回復：
嘗試這個
with _data as
(
select 'Joe' as _user, 1 as _hour,0 as purchase union all
select 'Joe' as _user, 2 as _hour,0 as purchase union all
select 'Joe' as _user, 3 as _hour,0 as purchase union all
select 'Joe' as _user, 4 as _hour,1 as purchase union all
select 'Joe' as _user, 5 as _hour,0 as purchase union all
select 'Joe' as _user, 6 as _hour,0 as purchase union all
select 'Joe' as _user, 7 as _hour,0 as purchase union all
select 'Joe' as _user, 8 as _hour,1 as purchase union all
select 'Joe' as _user, 9 as _hour,1 as purchase union all
select 'Joe' as _user,10 as _hour,0 as purchase
)
,subset as
(
select 'Joe' as _user, 3 as _hour,0 as purchase union all
select 'Joe' as _user, 9 as _hour,1 as purchase union all
select 'Joe' as _user,10 as _hour,0 as purchase
)
select a._user,a._hour,any_value(b.purchase) from _data a join subset b on
(
a._user = b._user and
(
a._hour = b._hour
or
case when b._hour > a._hour then (case when (b._hour - a._hour) <=2 then 1=1 end) end)
)
group by a._user,a._hour
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/428255.html
上一篇：條件連接(Oracle)