我實施的圖來存盤的排行榜資料頂部10的用戶是使用昂貴的計算COUNT(*)。我計劃在視圖上看起來像這樣：
id SERIAL PRIMARY KEY
user_id TEXT
type TEXT
rank INTEGER
count INTEGER
-- adding an index to user_id
-- adding a two-column unique index to user_id and type
我無法查看應如何創建此視圖以正確說明rank和type。本質上，我有一個像這樣的大表（約 3000 萬行）：
---- --------- --------- ----------------------------
| id | user_id | type | created_at |
---- --------- --------- ----------------------------
| 1 | 1 | Diamond | 2021-05-11 17:35:18.399517 |
| 2 | 1 | Diamond | 2021-05-12 17:35:17.399517 |
| 3 | 1 | Diamond | 2021-05-12 17:35:18.399517 |
| 4 | 2 | Diamond | 2021-05-13 17:35:18.399517 |
| 5 | 1 | Clay | 2021-05-14 17:35:18.399517 |
| 6 | 1 | Clay | 2021-05-15 17:35:18.399517 |
---- --------- --------- ----------------------------
使用上面的表格，我試圖實作這樣的目標：
---- --------- --------- ------ -------
| id | user_id | type | rank | count |
---- --------- --------- ------ -------
| 1 | 1 | Diamond | 1 | 3 |
| 2 | 2 | Diamond | 2 | 1 |
| 3 | 1 | Clay | 1 | 2 |
| 4 | 1 | Weekly | 1 | 5 | -- 3 diamonds 2 clay obtained between Mon-Sun
| 5 | 2 | Weekly | 2 | 1 |
---- --------- --------- ------ -------
通過Weekly我從最后一個星期日的時間進行計數即將到來的周日。
這僅使用 SQL 是否可行，還是需要某種腳本？如果可行，這將如何完成？值得一提的是，有數千種不同的型別，因此不必手動指定type將是首選。
如果有任何不清楚的地方，請告訴我，我會盡力澄清。謝謝！
uj5u.com熱心網友回復：
與“用戶”行相比，“每周”行的生成方式不同（我稱它們為兩個不同的“類別”）。要獲得您想要的結果，您可以使用UNION ALL.
例如：
select 'u' as category, user_id, type,
rank() over(partition by type order by count(*) desc) as rk,
count(*) as cnt
from scores
group by user_id, type
union all
select 'w', user_id, 'Weekly',
rank() over(order by count(*) desc),
count(*) as cnt
from scores
group by user_id
order by category, type desc, rk
結果：
category user_id type rk cnt
--------- -------- -------- --- ---
u 1 Diamond 1 3
u 2 Diamond 2 1
u 1 Clay 1 2
w 1 Weekly 1 5
w 2 Weekly 2 1
請參閱DB Fiddle 上的運行示例。
注意：為了簡單起見，我在查詢中保留了按時間戳過濾。如果您真的需要僅包含過去 7 天（或其他時間段）的行，則只需WHERE在兩個子查詢中添加一個子句即可。
uj5u.com熱心網友回復：
我想這就是你所說的，對吧？
WITH scores_plus_weekly AS ((
SELECT id, user_id, 'Weekly' AS type, created_at
FROM scores
WHERE created_at BETWEEN '2021-05-10' AND '2021-05-17'
)
UNION (
SELECT * FROM scores
))
SELECT
row_number() OVER (ORDER BY CASE "type" WHEN 'Diamond' THEN 0 WHEN 'Clay' THEN 1 ELSE 2 END, count(*) DESC) as "id",
user_id,
"type",
row_number() OVER (PARTITION BY count(*) DESC) as "rank",
count(*)
FROM scores_plus_weekly
GROUP BY user_id, "type"
ORDER BY "id";
我確定這不是唯一的方法，但我認為結果并不太復雜。該查詢首先將原始資料庫與本周的所有分數相結合。為了保持一致性，我選擇了一個與您的整個示例集相匹配的日期范圍。然后按user_id和分組type以獲取每個組合的計數。該row_numbers給你的整體排名和每個型別的排名。此查詢的很大一部分由排序依據組成type，因此如果您要加入另一個包含型別順序或優先級的表，CASE則可能可以簡化。
然后，最后，可以使用CREATE VIEW score_ranks AS ，然后是您的查詢在視圖中捕獲整個查詢。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/391472.html
標籤：sql PostgreSQL的