我正在嘗試創建一個查詢，該查詢將為我提供每個用戶每月登錄總時間的列。
username | auth_event_type | time | credential_id
Joe | 1 | 2021-11-01 09:00:00 | 44
Joe | 2 | 2021-11-01 10:00:00 | 44
Jeff | 1 | 2021-11-01 11:00:00 | 45
Jeff | 2 | 2021-11-01 12:00:00 | 45
Joe | 1 | 2021-11-01 12:00:00 | 46
Joe | 2 | 2021-11-01 12:30:00 | 46
Joe | 1 | 2021-12-06 14:30:00 | 47
Joe | 2 | 2021-12-06 15:30:00 | 47
auth_event_type 列指定事件是登錄 (1) 還是注銷 (2)，并且 credential_id 指示會話。
我正在嘗試創建一個具有如下輸出的查詢：
username | year_month | total_time
Joe | 2021-11 | 1:30
Jeff | 2021-11 | 1:00
Joe | 2021-12 | 1:00
我將如何在 postgres 中執行此操作？我在想它會涉及一個窗??口函式嗎？如果有人能指出我正確的方向，那就太好了。謝謝你。
uj5u.com熱心網友回復：
將視窗函式lag()與按credential_id排序的磁區一起使用time，例如
WITH j AS (
SELECT username, time, age(time, LAG(time) OVER w)
FROM t
WINDOW w AS (PARTITION BY credential_id ORDER BY time
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
)
SELECT username, to_char(time,'yyyy-mm'),sum(age) FROM j
GROUP BY 1,2;
注意：ROWS BETWEEN 1 PRECEDING AND CURRENT ROW在這種情況下，框架幾乎是可選的，但保持視窗函式盡可能明確被認為是一種很好的做法，這樣將來您就不必閱讀檔案來弄清楚您的查詢在做什么.
演示： db<>fiddle
uj5u.com熱心網友回復：
解決方案 1 部分作業
不確定視窗函式會在您的情況下為您提供幫助，但聚合函式將：
WITH list AS
(
SELECT username
, array_agg(DISTINCT to_char(time, 'YYYY-MM')) AS year_month
, array_agg(time ORDER BY auth_event_type) AS time_session
FROM your_table
GROUP BY username, credential_id
)
SELECT username
, year_month[1] AS year_month
, sum(time_session[2] - time_session[1]) AS total_time
FROM list
GROUP BY username, year_month
查詢的第一部分匯總了相同用戶名 credential_id 的登錄/注銷時間，第二部分計算了每個 year_month 登錄/注銷時間之間差異的總和。在登錄時間和注銷時間在同一個月之前，此查詢運行良好，但如果不是，則失敗。
解決方案 2 完全有效
為了計算total_time每個用戶名和每個月的登錄時間和注銷時間，我們可以使用時間范圍方法，該方法將時間范圍[login_time, logout_time)與每月時間范圍相交[monthly_start_time, monthly_end_time)：
WITH list AS
(
SELECT username
, array_agg(time ORDER BY auth_event_type) AS time_session
FROM your_table
GROUP BY username, credential_id
)
SELECT username
, to_char(t.month_start_date, 'YYYY-MM')
, sum(upper(f.period) - lower(f.period)) AS total_time
FROM list
INNER JOIN
( SELECT generate_series(min(date_trunc('month', time)), max(date_trunc('month', time)), '1 month') AS month_start_date
FROM your_table
) AS t
ON tsrange(t.month_start_date, t.month_start_date interval '1 month' - interval '1 second') && tsrange(time_session [1], time_session[2])
CROSS JOIN LATERAL (SELECT tsrange(t.month_start_date, t.month_start_date interval '1 month' - interval '1 second') * tsrange(time_session [1], time_session[2]) AS period) AS f
GROUP BY username, to_char(t.month_start_date, 'YYYY-MM')
在dbfiddle 中查看結果
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/380143.html
標籤：sql PostgreSQL的 窗函数
下一篇：將偽列舉模型重構為列舉