每月排行榜的數據庫設計

我有一個系統，使用者可以在其中對事件進行預測。每周大約有兩個活動。當事件結束時，系統對所有預測進行評分並在數據庫中更新。這是我目前數據庫結構的近似表示：

使用者表

+----+-----------+--------+
| ID |  username |  score |
+----+-----------+--------+
|  1 |  alice    |     12 |
|  2 |  bob      |     22 |
+----+-----------+--------+

遊戲桌

+----+-----------------+----------------------+------------+----------+
| ID |    gameTitle    |       gameTime       |  questions |  answers |
+----+-----------------+----------------------+------------+----------+
|  1 |  Football match |  2019-08-11 14:00:00 |  {json}    |  {json}  |
|  2 |  Hockey game    |  2019-07-11 15:00:00 |  {json}    |  {json}  |
+----+-----------------+----------------------+------------+----------+

預測表：

+----+--------------+--------------+-------------+--------+-------+
| ID |  gameID (FK) |  userID (FK) |  prediction |  score |  rank |
+----+--------------+--------------+-------------+--------+-------+
|  1 |            1 |            1 |  {json}     |      6 |     1 |
|  2 |            1 |            2 |  {json}     |      4 |     2 |
|  3 |            2 |            1 |  {json}     |      2 |     2 |
+----+--------------+--------------+-------------+--------+-------+

因此，最初預測中的“分數”和“排名”為 NULL，然後在事件結束後，我執行一個腳本，該腳本遍歷該遊戲的所有條目並為使用者評分並生成排名。

使用這種結構，我可以執行以下操作：

• 列出所有時間得分最高的使用者（從使用者中選擇，得分遞減）
• 列出每個事件的排名（從 PREDICTIONS 中選擇排名，其中 gameID = X，降序）

但是，我現在想做的是能夠獲得時間有限的排名。所以我希望能夠查看哪些使用者在當月或二月份等月份的所有事件中得分最高。

我知道我可以通過查看 Games 表並獲取給定月份中的所有 gameID 手動執行此操作，然後通過 Predictions 表查看所有具有這些 ID 的預測，並將每個使用者的分數相加，然後發送這個. 然而，這似乎是非常低效和資源密集型的。特別是考慮到這將是一個 API 呼叫

因此，我想知道如何在我目前的數據庫中適應這一點 - 如果可能和/或建議，或者我是否應該使用其他技術。

• 一個想法是為每個月生成一個新表，並在相關時更新並從中提取。
• 另一種方法是在使用者表中有一個“每月得分”列，每個月都會重置。但是，這不會有歷史數據，這會很好（儘管我猜總是可以生成）

我覺得我可能缺少一些數據庫範例技巧，所以很高興聽到為此提出的解決方案。

在考慮了更多之後，我認為可行的解決方案是創建一個新的monthlyScores 表，其結構如下：

+----+--------------+----------+--------+
| ID |  userID (FK) |   month  |  score |
+----+--------------+----------+--------+
|  1 |            1 |  03/2019 |     12 |
|  2 |            1 |  04/2019 |     23 |
|  3 |            2 |  03/2019 |      3 |
|  4 |            1 |  05/2019 |     22 |
|  5 |            2 |  06/2019 |     11 |
+----+--------------+----------+--------+

其中月份是當月，我可以為當月的遊戲增加這個值，這意味著我可以提供當月的領導者排行榜。同樣，我可以顯示每個使用者前幾個月的分數。

在這裡發布這個作為答案，因為我覺得它可以工作。也就是說，您可能會發現我對數據庫非常陌生，因此有興趣了解其他解決方案，和/或是否建議這樣做！

SELECT ...
   FROM ( getting all gameIDs that are in the given month ) AS x
   JOIN ... ON ...

看看如何使用“派生表”來完成鏈的第一步？這是一個簡單的SELECT gameIDs FROM ... WHERE ....

你最終可能會得到

SELECT ...
   FROM ( SELECT ...
              FROM ( SELECT ...
                   ) AS x
        ) AS y

甚至更深。

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