提示問題
撰寫一個查詢來識別回傳的活躍用戶。回訪活躍用戶是指在進行任何其他購買后 7 天內進行了第二次購買的用戶。輸出這些回傳的活躍用戶的 user_id 串列。
CREATE TABLE amazon_transactions(
id int,
user_id int,
itemvar char,
created_at datetime,
revenue int
)
我的解決方法如下。我得到了部分正確的答案。有許多用戶 ID 不應該在那里。有人可以告訴我我的解決方案有什么問題嗎？
select
user_id
from
amazon_transactions t1
where
7 < ANY(
select
ABS( DATEDIFF( t1.created_at, t2.created_at ) )
from
amazon_transactions t2
where
t2.user_id = t1.user_id
);
uj5u.com熱心網友回復：
這是解決問題的方法
select DISTINCT user_id
from amazon_transaction a
where exists (select null
from amazon_transaction b
where a.user_id=b.user_id
and a.id <> b.id
and abs(datediff(b.created_at,a.created_at))<=7
)
uj5u.com熱心網友回復：
利用
select
t1.user_id
from
amazon_transactions t1
join amazon_transactions t2
on t1.user_id = t2.user_id and ABS(DATEDIFF(t1.created_at, t2.created_at )) <= 7 and t1.id <> t2.id
或者
select
user_id
from
amazon_transactions t1
where
7 >= ANY(
select
ABS( DATEDIFF( t1.created_at, t2.created_at ) )
from
amazon_transactions t2
where
t2.user_id = t1.user_id
);
uj5u.com熱心網友回復：
SELECT DISTINCT user_id
FROM amazon_transaction t1
WHERE EXISTS ( SELECT NULL
FROM amazon_transaction t2
WHERE t1.id < t2.id
AND t1.user_id = t2.user_id
AND t2.created_at < t1.created_at INTERVAL 7 DAY )
任務有點模糊。也許您必須調整查詢 - 例如，使用t1.id <> t2.id, t2.created_at <= t1.created_at, INTERVAL 6 DAY...
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/399382.html
上一篇：從x行的右側或左側訪問n行