一个列上的 SQL 聚合给出另一列的结果

Question

我正在尝试(但失败)加入 SQLite 数据库中的一些表。数据本身很复杂，但我认为我已将其归结为一个说明性示例。

这是我要加入的三个表。

表:事件

+----+---------+-------+-----------+
| id | user_id | class | timestamp |
+----+---------+-------+-----------+
|  1 | 'user1' |     6 |       100 |
|  2 | 'user1' |    12 |       400 |
|  3 | 'user1' |     4 |       900 |
|  4 | 'user2' |     6 |       400 |
|  5 | 'user2' |     3 |       800 |
|  6 | 'user2' |     8 |       900 |
+----+---------+-------+-----------+

表:游戏

+---------+---------+------------+-----------+
| user_id | game_id | game_class | timestamp |
+---------+---------+------------+-----------+
| 'user1' |       1 | 'A'        |       200 |
| 'user2' |       2 | 'A'        |       300 |
| 'user1' |       3 | 'B'        |       500 |
| 'user1' |       4 | 'A'        |       600 |
| 'user1' |       5 | 'A'        |       700 |
+---------+---------+------------+-----------+

表:A 分数

+---------+-------+
| game_id | score |
+---------+-------+
|       1 |     8 |
|       2 |     2 |
|       4 |     9 |
|       5 |     6 |
+---------+-------+

我想加入这些以在第一个表上提供一个附加列，其中包含事件发生时用户在游戏 A 类中的当前分数。 IE。我希望加入的结果如下所示:

想要的结果

+----+----------+-------+-----------+-----------------+
| id | user_id  | class | timestamp | current_a_score |
+----+----------+-------+-----------+-----------------+
|  1 |  'user1' |     6 |       100 | (null)          |
|  2 |  'user1' |    12 |       400 | 8               |
|  3 |  'user1' |     4 |       900 | 6               |
|  4 |  'user2' |     6 |       400 | 2               |
|  5 |  'user2' |     3 |       800 | 2               |
|  6 |  'user2' |     8 |       900 | 2               |
+----+----------+-------+-----------+-----------------+

下面的简单连接将两个表 AScores 和 Games 放在一起。

SELECT * FROM AScores
INNER JOIN Games
ON AScores.game_id = Games.game_id

所以我希望将它作为子查询加入到事件表中。像这样的东西:

SELECT Events.*, AScoredGames.time_stamp AS game_time_stamp, AScoredGames.score
FROM Events
LEFT OUTER JOIN (
    SELECT AScores.score, Games.* FROM AScores
    INNER JOIN Games
    ON AScores.game_id = Games.game_id
) AS AScoredGames
ON Events.user_id = AScoredGames.user_id 
AND Events.time_stamp >= AScoredGames.time_stamp
ORDER BY Events.time_stamp ASC

结果如下:

+----+---------+-------+------------+-----------------+-------+
| id | user_id | class | time_stamp | game_time_stamp | score |
+----+---------+-------+------------+-----------------+-------+
|  1 | user1   |     6 | 100        | NULL            | NULL  |
|  2 | user1   |    12 | 400        | 200             | 8     |
|  4 | user2   |     6 | 400        | 300             | 2     |
|  5 | user2   |     3 | 800        | 300             | 2     |
|  6 | user2   |     8 | 900        | 300             | 2     |
|  3 | user1   |     4 | 900        | 200             | 8     |
|  3 | user1   |     4 | 900        | 600             | 9     |
|  3 | user1   |     4 | 900        | 700             | 6     |
+----+---------+-------+------------+-----------------+-------+

因此，我需要按 Events.id 进行分组，以摆脱带有 Events.id 3 的三重行。但我想要做的是选择具有最大 game_time_stamp 的行，然后使用该行的分数。如果我将 MAX(game_time_stamp) 作为我的聚合，我仍然必须独立聚合分数。有没有办法将 score 列的聚合函数中的行选择与 game_time_stamp 列的聚合函数的结果联系起来？

(注意，对 Select first record in a One-to-Many relation using left join 和 SQL Server: How to Join to first row 等问题的现有答案似乎表明我不能并且说必须在子查询上使用 WHERE 子句。但我正在努力解决这个问题(我将发布另一个关于该问题的问题)和我能想到至少一种解决方案，我希望有更好的解决方案。)

Answer 1

以下查询应该这样做。它使用 NOT EXISTS条件与相关子查询来定位每个事件的相关游戏记录。

SELECT e.*, s.score current_a_score
FROM 
    events e
    LEFT JOIN games g 
        ON  g.user_id = e .user_id
        AND g.timestamp < e.timestamp
        AND NOT EXISTS (
            SELECT 1 
            FROM games g1
            WHERE 
                g1.user_id = e .user_id
                AND g1.timestamp < e.timestamp 
                AND g1.timestamp > g.timestamp
        )
    LEFT JOIN ascores s 
        ON  s.game_id = g.game_id
ORDER BY e.id

这个 DB Fiddle demo 随着您的测试数据返回:

| id  | user_id | class | timestamp | current_a_score |
| --- | ------- | ----- | --------- | --------------- |
| 1   | user1   | 6     | 100       |                 |
| 2   | user1   | 12    | 400       | 8               |
| 3   | user1   | 4     | 900       | 6               |
| 4   | user2   | 6     | 400       | 2               |
| 5   | user2   | 3     | 800       | 2               |
| 6   | user2   | 8     | 900       | 2               |