我有一個包含 的游戲日志表handDate，如下所示：
| ID | 手日期 |
|---|---|
| 1 | 2019-06-30 16:14:02.000 |
| 2 | 2019-07-12 06:18:02.000 |
| 3 | ... |
鑒于以下情況，我想從此表（開始和結束）計算游戲會話：
- 如果 1 小時后沒有活動，則考慮新會話。
- 一個會話可以存在 2 天
所以我想要這樣的結果：
| 日 | 會話開始 | 會話結束 |
|---|---|---|
| 2019-06-30 | 2019-06-15 16:14:02.000 | 2019-06-15 16:54:02.000 |
| 2019-07-02 | 2019-07-02 16:18:02.000 | 2019-07-02 17:18:02.000 |
| 2019-07-02 | 2019-07-02 23:18:02.000 | 2019-07-03 03:18:02.000 |
| 2019-07-03 | 2019-07-03 06:18:02.000 | 2019-07-03 08:28:02.000 |
目前我正在使用以下代碼，但無法實作我想要的：
SELECT *
FROM (
SELECT *,
strftime( '%s', handDate) - strftime( '%s', prev_event) AS inactivity
FROM (
SELECT handDate,
date( handDate) as day,
FIRST_VALUE( handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate) AS first_event,
MIN(handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate),
MAX(handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate),
LAG( handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS prev_event,
LEAD( handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate) AS next_event
FROM hands
) last
) final
我正在使用SQLite。
uj5u.com熱心網友回復：
我找到了以下解決方案：
SELECT day,
sessionId,
MIN(handDate) as sessionStart,
MAX(handDate) as sessionEnd
FROM(
SELECT day,
handDate,
sum(is_new_session) over (
order by handDate rows between unbounded preceding and current row
) as sessionId
FROM (
SELECT *,
CASE
WHEN prev_event IS NULL
OR strftime('%s', handDate) - strftime('%s', prev_event) > 3600 THEN true
ELSE false
END AS is_new_session
FROM (
SELECT handDate,
date(handDate) as day,
LAG(handDate) OVER (
PARTITION BY date(handDate)
ORDER BY handDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS prev_event
FROM hands
)
)
)
GROUP BY sessionId
uj5u.com熱心網友回復：
DROP TABLE IF EXISTS hands;
CREATE TABLE hands(handDate TIMESTAMP);
INSERT INTO hands(handDate)
VALUES ('2021-10-29 10:30:00')
, ('2021-10-29 11:35:00')
, ('2021-10-29 11:36:00')
, ('2021-10-29 11:37:00')
, ('2021-10-29 12:38:00')
, ('2021-10-29 12:39:00')
, ('2021-10-29 12:39:10')
;
SELECT start_period, end_period
FROM (
SELECT is_start, handDate AS start_period
, CASE WHEN is_start AND is_end THEN handDate
ELSE LEAD(handDate) OVER (ORDER BY handDate)
END AS END_period
FROM (
SELECT *
FROM (
SELECT *
,CASE WHEN (event-prev_event) * 1440.0 > 60 OR prev_event IS NULL THEN true ELSE FALSE END AS is_start
,CASE WHEN (next_event-event) * 1440.0 > 60 OR next_event IS NULL THEN true ELSE FALSE END AS is_end
FROM (
SELECT handDate
, juliANDay(handDate) event
, juliANDay(LAG(handDate) OVER (ORDER BY handDate)) AS prev_event
, juliANDay(LEAD(handDate) OVER (ORDER BY handDate)) AS next_event
FROM hands
) t
) t
WHERE is_start OR is_end
)t
)t
WHERE is_start
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/346785.html