Postgresql

按引用表中相關行數排序

  • February 26, 2016

假設有兩個表：

使用者

id [pk] |   name
--------+---------
     1 | Alice
     2 | Bob
     3 | Charlie
     4 | Dan

電子郵件

id | user_id | email 
----+---------+-------
 1 |       1 | a.1
 2 |       1 | a.2
 3 |       2 | a.3
 4 |       2 | b.1
 5 |       2 | a.4
 6 |       2 | a.5
 7 |       3 | b.2
 8 |       3 | a.6

通過一個查詢，我想檢索：

  • 使用者的 id 和名稱
  • 使用者的電子郵件計數
  • 使用者的電子郵件及其 ID

我希望輸出按電子郵件數量降序排列並過濾，僅包括以“a”開頭的電子郵件。沒有電子郵件的使用者也應包括在內 - 將他們的電子郵件計數視為0。

有我的查詢：

SELECT users.id AS user_id, users.name AS name,
      emails.id AS email_id, emails.email AS email,
      count(emails.id) OVER (PARTITION BY users.id) as n_emails
FROM users
LEFT JOIN emails on users.id = emails.user_id
WHERE emails.email LIKE 'a' || '%%'
ORDER BY n_emails DESC;

而（預期的）結果，看起來不錯：

user_id |  name   | email_id | email | n_emails 
---------+---------+----------+-------+----------
      2 | Bob     |        6 | a.5   |        3
      2 | Bob     |        5 | a.4   |        3
      2 | Bob     |        3 | a.3   |        3
      1 | Alice   |        2 | a.2   |        2
      1 | Alice   |        1 | a.1   |        2
      3 | Charlie |        8 | a.6   |        1

很明顯，這是一個簡單而小例子，而實際數據集可能足夠大，所以我想使用LIMIT/OFFSET進行分頁。例如，我想獲取第一對使用者（不僅僅是行）：

-- previous query ...
LIMIT 2 OFFSET 0;

而且……失敗。我只有關於 Bob 的不完整資訊：

user_id | name | email_id | email | n_emails 
---------+------+----------+-------+----------
      2 | Bob  |        6 | a.5   |        3
      2 | Bob  |        5 | a.4   |        3

因此問題是：如何將限制/偏移應用於對象，在這種情況下，使用者（邏輯實體，而不是行）？

我找到了這樣的解決方案：添加dense_rank()users.id 然後按排名過濾：

SELECT * FROM (
   SELECT users.id AS user_id, users.name AS name,
          emails.id AS email_id, emails.email AS email,
          count(emails.id) OVER (PARTITION BY users.id) as n_emails,
          dense_rank() OVER (ORDER BY users.id) as n_user
   FROM users
   LEFT JOIN emails on users.id = emails.user_id
   WHERE emails.email LIKE 'a' || '%%'
   ORDER BY n_emails DESC
   ) AS sq
WHERE sq.n_user <= 2; -- here it is

輸出看起來不錯：

user_id | name  | email_id | email | n_emails | n_user 
---------+-------+----------+-------+----------+--------
      2 | Bob   |        6 | a.5   |        3 |      2
      2 | Bob   |        5 | a.4   |        3 |      2
      2 | Bob   |        3 | a.3   |        3 |      2
      1 | Alice |        2 | a.2   |        2 |      1
      1 | Alice |        1 | a.1   |        2 |      1

但是如果您查看查詢計劃，您會發現最昂貴的步驟是子查詢掃描和排序。AFAIK 不可能在子查詢或 CTE 上建立索引，因此它將始終對 n_user 進行序列掃描/過濾，並且查詢將在大數據集上執行很長時間。

我看到的另一個解決方案是進行兩個查詢：

  1. 使用子查詢僅檢索過濾和排序數據集的使用者 ID 和電子郵件數量；
  2. 將第一個子查詢加入使用者和電子郵件

查詢是：

SELECT users.id AS user_id, users.name,
      emails.id AS email_id, emails.email,
      sq.n_emails
FROM
(SELECT users.id, count(emails.id) AS n_emails
   FROM users
   LEFT JOIN emails ON users.id = emails.user_id
   WHERE emails.email LIKE 'a' || '%%'
   GROUP BY users.id
   ORDER BY n_emails DESC
   LIMIT 2 OFFSET 0 -- here it is
   ) AS sq
JOIN users ON users.id = sq.id
LEFT JOIN emails ON emails.user_id = users.id
WHERE emails.email LIKE 'a' || '%%'
ORDER BY sq.n_emails DESC;

這似乎要快得多。但它看起來不是一個好的解決方案，因為我必須複製完全相同的查詢（SELECT...FROM部分除外），事實上，一個查詢執行兩次。有沒有更好的解決方案？

排除沒有電子郵件的使用者

假設我們只想要實際擁有電子郵件的使用者。沒有電子郵件的使用者將被忽略。我最初採用這個假設的原因是您的所有查詢都已經這樣做了：

LEFT JOIN emails on users.id = emails.user_id
WHERE emails.email LIKE 'a' || '%%'

通過添加WHERE條件，emails.email您可以有效地將您的內容轉換LEFT JOIN為純文字[INNER] JOIN並排除沒有電子郵件的使用者。詳細解釋：

重寫第二個查詢

您的第二個查詢不像宣傳的那樣工作，結果不是“按電子郵件數量降序”。您必須將結果嵌套count()在另一個 CTE 或子查詢中並dense_rank()在其上執行。您不能在同一查詢級別中嵌套視窗函式。

SELECT u.name, e2.*
FROM  (
  SELECT *, dense_rank() OVER (ORDER BY n_emails, users.id) AS rnk
  FROM  (
     SELECT user_id, id AS e_id, e_mail
          , count(*) OVER (PARTITION BY user_id) AS n_emails          
     FROM   emails
     WHERE  email LIKE 'a' || '%'  -- one % is enough
     ) e1
  ) e2
JOIN   users u ON u.id = e2.user_id
WHERE  rnk < 3
ORDER  BY rnk;

如果謂詞具有足夠的選擇性（僅選擇所有電子郵件的一小部分），這應該是最快的。行排序不同的兩個視窗函式也有其價格。

  • 一個要點是emails僅執行子查詢 - 如果初步假設成立，這是可能的。

第三次查詢改進

另一方面，如果謂詞WHERE e.email LIKE 'a' || '%'不是很有選擇性，那麼您的第三次查詢可能會更快，即使它從表中讀取兩次 - 但第二次只需要行。還改進了：

SELECT e.user_id, u.name,
      e.id AS e_id, e.e_mail, sq.n_emails
FROM  (
  SELECT user_id, count(*) AS n_emails
  FROM   emails
  WHERE  email LIKE 'a' || '%'
  GROUP  BY user_id
  ORDER  BY count(*) DESC, user_id  -- break ties
  LIMIT  2  OFFSET 0
  ) sq
JOIN   emails e USING (user_id)
JOIN   users  u ON u.id = e.user_id
WHERE  e.email LIKE 'a' || '%'
ORDER  BY sq.n_emails DESC;

包括沒有電子郵件的使用者

您可以再次在內部查詢中包含 users 表，類似於您之前的內容。但是您必須將電子郵件過濾器拉入加入條件！

SELECT u.name, e2.*
FROM  (
  SELECT *, dense_rank() OVER (ORDER BY n_emails, users.id) AS rnk
  FROM  (
     SELECT u.id AS user_id, u.name, e.id AS e_id
          , count(e.user_id) OVER (PARTITION BY u.id) AS n_emails          
     FROM   users u
     LEFT   JOIN emails e ON e.user_id = u.id
                         AND e.email LIKE 'a' || '%'  -- !!!
     ) e1
  ) e2
WHERE  rnk < 3
ORDER  BY rnk;

這會貴一點。

由於您首先檢索電子郵件最多的使用者，因此沒有電子郵件的使用者很少會出現在結果中。要優化性能，您可以使用UNION ALLwith LIMIT：

(  -- parentheses required
SELECT u.name, e2.user_id, e2.e_id, e2.e_mail, e2.n_emails
FROM  (
  SELECT *, dense_rank() OVER (ORDER BY n_emails, users.id) AS rnk
  FROM  (
     SELECT user_id, id AS e_id, e_mail
          , count(*) OVER (PARTITION BY user_id) AS n_emails          
     FROM   emails
     WHERE  email LIKE 'a' || '%'  -- one % is enough
     ) e1
  ) e2
JOIN   users u ON u.id = e2.user_id
WHERE  rnk < 3      -- adapt to paging!
ORDER  BY rnk
)
UNION ALL
(    
SELECT u.name, u.user_id, NULL AS e_id, NULL AS e_mail, 0 AS n_emails  
FROM   users       u
LEFT   JOIN emails e ON e.user_id = u.id
                   AND e.email LIKE 'a' || '%'
WHERE  e.e.user_id IS NULL
)
OFFSET 0      -- adapt to paging!
LIMIT  2      -- adapt to paging!

詳細解釋：

MATERIALIZED VIEW

我會考慮實現結果有兩個原因：

  • 後續查詢要快得多。
  • 您不必對移動的目標進行操作。您談到分頁，如果使用者在頁面之間收到新電子郵件，您的整個排序順序可能沒有實際意義。

從不帶( ) 的第二個查詢建構一個 MV ，然後返回第一頁等。這是一個政策問題，當您再次刷新 MV 時。LIMITREFRESH MATERIALIZED VIEW

引用自：https://dba.stackexchange.com/questions/86038

相關問答

Postgresql

將兩個事件表合併到一個時間線中

  • March 25, 2021
檢視問答
Postgresql

基於時間的採樣

  • February 12, 2021
檢視問答
Postgresql

Postgres Count 在同一個查詢中具有不同的條件

  • June 28, 2020
檢視問答
Postgresql

重疊範圍的最大 sum()

  • May 19, 2020
檢視問答
Postgresql

如何優化我的事務級別執行餘額笛卡爾連接？

  • April 28, 2020
檢視問答
Postgresql

當時間序列中的值為空時返回上一個執行總計

  • April 28, 2020
檢視問答