我有 3 個表，一個用戶表，一個管理表和一個 cust 表。admin 和 cust 表都是 user_account 表的外鍵。基本上，每個用戶都有一個用戶記錄，他們的用戶型別取決于他們是否在 admin 或 cust 表中有記錄。
user admin cust
user_id user_id | admin_id user_id | cust_id
--------- ---------|---------- ---------|---------
1 1 | a 2 | dd
2 4 | b 3 | ff
3
4
然后我有一個 login_history 表，記錄用戶每次登錄應用程式時的 user_id 和登錄時間戳
login_history
user_id | login_on
---------|---------------------
1 | 2022-01-01 13:22:43
1 | 2022-01-02 16:16:27
3 | 2022-01-05 21:17:52
2 | 2022-01-11 11:12:26
3 | 2022-01-12 03:34:47
我想創建一個視圖，其中包含從 1 月 1 日開始的一年中每周第一天的所有日期，以及一個包含該周登錄的唯一管理員用戶計數和唯一客戶計數的計數列該周登錄的用戶。因此，結果視圖應包含以下 53 條記錄，每周一條。
login_counts_view
week_start_date | admin_count | cust_count
-----------------|-------------|------------
2022-01-01 | 1 | 1
2022-01-08 | 0 | 2
2022-01-15 | 0 | 0
.
.
.
2022-12-31 | 0 | 0
請注意，第一周（2022-01-01）只有 1 個 admin_count 計數，即使 user_id 為 1 的管理員在該周登錄了兩次。
以下是我對該視圖的當前查詢。但是，表非常大，從視圖中檢索所有記錄需要 10 多秒，主要是因為左連接日期比較。
CREATE VIEW login_counts_view AS
SELECT
week_start_dates.week_start_date::text AS week_start_date,
count(distinct a.user_id) AS admin_count,
count(distinct c.user_id) AS cust_count
FROM (
SELECT
to_char(i::date, 'YYYY-MM-DD') AS week_start_date
FROM
generate_series(date_trunc('year', NOW()), to_char(NOW(), 'YYYY-12-31')::date, '1 week') i
) week_start_dates
LEFT JOIN login_history l ON l.login_on::date BETWEEN week_start_dates.week_start_date::date AND (week_start_dates.week_start_date::date INTERVAL '6 day')::date
LEFT JOIN admin a ON a.user_id = l.user_id
LEFT JOIN cust c ON c.user_id = l.user_id
GROUP BY week_start_date;
有沒有人有關于如何使這個查詢更有效地執行的任何提示？
uj5u.com熱心網友回復：
主意
計算每個登錄日期的偽周：將年份劃分為 7 天切片并連續編號。給定日期的偽周將是它所屬切片的序號。
然后對表示偽周的整數而不是日期值和比較操作連接。
執行
實作這一點的觀點如下：
CREATE VIEW login_counts_view_fast AS
WITH RECURSIVE Numbers(i) AS ( SELECT 0 UNION ALL SELECT i 1 FROM Numbers WHERE i < 52 )
SELECT CAST ( date_trunc('year', NOW()) AS DATE) 7 * n.i week_start_date
, count(distinct lw.admin_id) admin_count
, count(distinct lw.cust_id) cust_count
FROM (
SELECT i FROM Numbers
) n
LEFT JOIN (
SELECT admin_id
, cust_id
, base
, pit
, pit-base delta
, (pit-base) / (3600 * 24 * 7) week
FROM (
SELECT a.user_id admin_id
, c.user_id cust_id
, CAST ( EXTRACT ( EPOCH FROM l.login_on ) AS INTEGER ) pit
, CAST ( EXTRACT ( EPOCH FROM date_trunc('year', NOW()) ) AS INTEGER ) base
FROM login_history l
LEFT JOIN admin a ON a.user_id = l.user_id
LEFT JOIN cust c ON c.user_id = l.user_id
) le
) lw
ON lw.week = n.i
GROUP BY n.i
;
一些備注：
- 紀元值是自絕對基準日期時間（特別是 1/1/1970 0h00）以來經過的秒數。
CASTS根據 postgresql 日期函式的簽名以及為了強制執行整數算術，有必要將雙精度數轉換為整數和時間戳到日期。- 遞回子查詢是連續整數的生成器。它可能會被一個
generate_series電話取代（未經測驗）
評估
在這個 db fiddle中看到它的實際效果
查詢計劃表明執行時間節省了 50-70%。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/463038.html
標籤：PostgreSQL