我有一個用戶活動表：
CREATE TABLE public.user_session_activity_table (
id integer NOT NULL,
"userId" integer NOT NULL,
"creationDate" timestamp without time zone DEFAULT now() NOT NULL
);
INSERT INTO public.user_session_activity_table
(
id,
"userId",
"creationDate"
)
VALUES
(1, 1, '2021-11-06 10:54:23.891327'),
(2, 1, '2021-11-06 10:59:56.616956'),
(3, 1, '2021-11-06 10:59:57.680751'),
(4, 1, '2021-11-06 10:59:58.857336'),
(5, 1, '2021-11-06 11:36:47.112812'),
(6, 1, '2021-11-06 11:36:49.049485'),
(7, 1, '2021-11-06 11:36:50.931315')
期望的輸出：
id userId sessionLenght
1 1 123s -- row 1
2 1 123s -- row 2-4 grouped together
3 1 123s -- row 4-7 grouped together
解釋：
我正在創建用戶會話的視圖，形成一個包含已保存用戶活動行的表。我想對創建日期之間經過的時間增量進行 GROUP BY。如果時間過長（假設閾值為 1 分鐘），則當前組結束，新組開始。這將導致此樣本資料與 3 個組對齊：
- 編號：1
- id:2, id:3, id:4
- 編號：5，編號：6，編號：7
如您所見，最顯著的時間差異是在 id:1 <-> id:2 和 id:4 <-> id:5 之間，這就是為什么它應該分成 3 個獨立的組。
我正在使用最新版本的 PostgreSQL。“sessionLength”不是那么重要，我可以自己找到解決方案，主要問題是創建這些組。
一個重要的事實是：將日期四舍五入是行不通的，一個會話可能會持續幾分鐘或幾小時。應該結束和開始組的事情是活動之間的時間差（例如，當用戶注銷時，或離開鍵盤一個小時）。
謝謝，非常感謝您的幫助！（如果問題不清楚，請告訴我，我會盡量澄清一點！:)）
uj5u.com熱心網友回復：
視窗函式允許您指定范圍。您可以使用它來獲取尋址記錄集的第一個值和最后一個值。[我需要omg子查詢來實際使用值（減去它們）并僅過濾（偽）聚合。只需省略該WHERE條款，看看它是如何作業的...]
SELECT
first_id, user_id
, ze_first, ze_last
, (ze_last-ze_first) AS timespan
, 1 (last_id -first_id) AS nrecords
FROM (
SELECT
id, user_id
, first_value(id) OVER www AS first_id
, last_value(id) OVER www AS last_id
, first_value(creation_date) OVER www AS ze_first
, last_value(creation_date) OVER www AS ze_last
FROM user_session_activity_table
WINDOW www AS (
PARTITION BY user_id
ORDER BY creation_date
-- This is the magic ...
RANGE BETWEEN '1 min' PRECEDING AND '1 min' FOLLOWING
)
) omg
WHERE id=first_id -- anything goes ...
;
uj5u.com熱心網友回復：
我知道這并沒有給你一個完整的解決方案，但它可能會幫助你到達那里，使用 row_number 來識別要在 60 秒的持續時間內組合在一起的行：
with u as (
select *,
id - row_number() over (partition by userid, round(extract('epoch' from creationdate) / 60) * 60 order by creationdate) gp
from t
)
select
row_number() over(partition by max(userId) order by max(creationdate)) GroupNo,
max(userid) UserId,
min(creationdate) StartOfRange, max(CreationDate) EndOfRange,
round(max(date_part('second',creationdate::time))- min(date_part('second',creationdate::time))) duration
from u
group by gp
uj5u.com熱心網友回復：
你需要在 postgresql 中使用 STRING_AGG 函式；諸如此類，我對 postgresql 不熟悉
SELECT id,userId,STRING_agg(id,';'),to_char(creationDate, 'HH:MI') FROM public.user_session_activity_table
GROUP BY
userId,to_char(creationDate, 'HH:MI')
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/350379.html