我有log_event一張桌子，里面有一張桌子，如下所示：
user_id|event_date_time| event
------------------------------
7494212|1535308430 | Opened app
7494212|1535308433 | Closed app
1475185|1535308444 | Registered
6946725|1535308475 | Opened app
6946725|1535308476 | Made a purchase
6946725|1535308477 | closed app
Create table log_event (
user_id int,
event_date_time bigint,
event varchar(70)
);
INSERT INTO log_event Values( 7494212,1535308430,'Opened app');
INSERT INTO log_event Values( 7494212,1535308433,'Closed app');
INSERT INTO log_event Values( 1475185,1535308444,'Registered');
INSERT INTO log_event Values( 6946725,1535308475,'Opened app');
INSERT INTO log_event Values( 6946725,1535308476,'Made a purchase');
INSERT INTO log_event Values( 6946725,1535308477,'closed app');
資料庫<>小提琴
我想根據列 Open 和 close查找average time花費。單個用戶在一天或一個月內可以有多個打開和關閉條目。有沒有出路，因為我不確定？提前致謝userEvent'show to find next close for every opened app event and then group by for single user
我已經在 stackoverflow 中尋找解決方案，但找不到最接近它的任何東西。
uj5u.com熱心網友回復：
您可以event = 'Closed app'使用event = 'Opened app'帶LEAD()視窗函式的每個帶行獲取行，并使用它來查找2個時間戳的差異。
然后您可以聚合以獲得平均值：
SELECT user_id, AVG(time_dif) average_time_spent
FROM (
SELECT user_id, event,
LEAD(event_date_time) OVER (PARTITION BY user_id ORDER BY event_date_time) - event_date_time time_dif
FROM log_event
WHERE event IN ('Opened app', 'Closed app')
) t
WHERE event = 'Opened app'
GROUP BY user_id;
請參閱演示。
uj5u.com熱心網友回復：
您可以使用 self- join：
with sessions(id, c, d) as (
select l.user_id, l.event_date_time, l.event_date_time - max(l1.event_date_time)
from log_event l join log_event l1 on l1.user_id = l.user_id and l1.event_date_time < l.event_date_time and lower(l1.event) = 'opened app' where lower(l.event) = "closed app"
group by l.user_id, l.event_date_time
)
select id, avg(d) from sessions group by id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/366225.html