PostgreSQL-提取最大时间戳小于特定日期

Question

我有一个问题试图查询任务。因此，我需要为竞赛平台实现一个评分系统，但是要计算和更新用户的评级，我需要提取用户的最后一个评级（因为表保留了整个历史记录）。

现在，这些是负责此任务的表（我仅保留相关的列）：

基于“ ID”的商店竞赛：

CREATE TABLE IF NOT EXISTS contests (
    id TEXT NOT NULL,
    date_end TIMESTAMP NOT NULL,
    
    PRIMARY KEY (id)
);

存储所有注册特定竞赛的用户：

CREATE TABLE IF NOT EXISTS contest_participants (
    username TEXT NOT NULL,
    contest_id TEXT NOT NULL,
    
    PRIMARY KEY (username, contest_id),
    FOREIGN KEY (contest_id) REFERENCES contests (id)
);

根据他们参与的竞赛存储所有用户的评分在：

CREATE TABLE IF NOT EXISTS contest_ratings (
    id INT AUTO_INCREMENT,
    contest_id TEXT NOT NULL,
    username TEXT NOT NULL,
    rating INT NOT NULL,
    
    PRIMARY KEY (id),
    FOREIGN KEY (contest_id) REFERENCES contests (id)
);

现在，由于能够一次计算超过1个竞赛评级，因此我无法使用ID列vonty>竞赛 。

我能找到的最好的选择是在竞赛中使用date_end列。基本上，如果我寻找最大data_end低于当前竞赛（ex：max（date_end）＆lt;'current_timestamp'），我可以找到最后的评分用户。但是我真的不知道该怎么做。

假设我们有以下数据：

--------------------------------------|
             contests                 |
----------------+---------------------|
       id       |     date_end        |
----------------+------------------+--|
 test-contest-1 | 2022-05-10 15:55:00 |
 test-contest-2 | 2022-05-10 17:00:00 |
 test-contest-3 | 2022-05-12 17:09:00 | 

-----------------------------|
      contest_participants   |
------------+----------------|
  username  |   contest_id   |  
------------+----------------|
 KeepCoding | test-contest-1 |
 TestUser   | test-contest-1 | 
 KeepCoding | test-contest-2 |
 KeepCoding | test-contest-3 |
 TestUser   | test-contest-3 |

-------------------------------------------|
                contest_ratings            |
----+----------------+------------+--------|
 id |   contest_id   |  username  | rating |
----+----------------+------------+--------|
 1  | test-contest-1 | KeepCoding |    100 |
 2  | test-contest-1 | TestUser   |     10 |
 3  | test-contest-2 | KeepCoding |    200 |

我们要计算test-contest-3竞赛的评级。为此，我们需要从test-contest-2竞赛中提取keepscoding用户的评分，以及test-contest-1 对于tastuser用户。

尝试自己解决这个问题，这几乎是我能想到的，但无法正常工作，因为正在为同一用户返回更多评级列，我只是不知道为什么。

因此，首先，我们需要最大date_end：

SELECT id, max(date_end) as date_end FROM contests WHERE date_end < 'current_timestamp'

然后提取额定值

SELECT cr.username, cr.rating FROM (...) s JOIN contest_ratings cr ON s.id = cr.id

，然后添加我写的过滤器

SELECT * FROM (...) t JOIN contest_participants cp ON t.username = cp.username AND cp.contest = 'current-contest-id';

（...）我的意思是括号中的上一个查询