sql - 将表中的匹配行除以其他表中的匹配行

Question

我的 BigQuery 数据库为在线游戏建模。它有以下表格：

• raid，发生在 中的游戏实例channel。例子：

  +--------------------------------------+---------------+
  |                  id                  |    channel    |
  +--------------------------------------+---------------+
  | 4dc0fd49-3d13-4271-b5fb-2fc8a3bfa0c6 | #antonidas    |
  | d400e1cd-10af-4cb4-8a24-1a3f7ed994c0 | #anvilmar     |
  | 532c95b6-9398-4ae4-bd97-d9a0aed3b9cc | #antonidas    |
  | b7779b4e-5042-45ab-a356-5f2e08b51f1f | #antonidas    |
  | 41160b3b-bcd5-4f00-b3c4-972054e64cab | #antonidas    |
  +--------------------------------------+---------------+
  

• entry，进入突袭的人。例子：

  +--------------------------------------+-----------+
  |               raid_id                |  raider   |
  +--------------------------------------+-----------+
  | 41160b3b-bcd5-4f00-b3c4-972054e64cab | Blackrock |
  | 41160b3b-bcd5-4f00-b3c4-972054e64cab | Coilfang  |
  | 4dc0fd49-3d13-4271-b5fb-2fc8a3bfa0c6 | Blackrock |
  | d400e1cd-10af-4cb4-8a24-1a3f7ed994c0 | Blackrock |
  | b7779b4e-5042-45ab-a356-5f2e08b51f1f | Blackrock |
  | b7779b4e-5042-45ab-a356-5f2e08b51f1f | Gilneas   |
  | 532c95b6-9398-4ae4-bd97-d9a0aed3b9cc | Blackrock |
  +--------------------------------------+-----------+
  

• win，赢得突袭的人。例子：

  +--------------------------------------+-----------+
  |               raid_id                |  raider   |
  +--------------------------------------+-----------+
  | d400e1cd-10af-4cb4-8a24-1a3f7ed994c0 | Blackrock |
  | 4dc0fd49-3d13-4271-b5fb-2fc8a3bfa0c6 | Blackrock |
  | 41160b3b-bcd5-4f00-b3c4-972054e64cab | Coilfang  |
  +--------------------------------------+-----------+
  

我正在尝试确定在特定频道中发生的突袭的总体胜率。如果获胜/进入raid的raid是通道C，则获胜率是总行数win除以总行数。类似于：entry

我最接近的是运行 2 个子选择查询，但我觉得必须有更好的方法，我不必重复自己并且总共运行 3 个查询。

SELECT
  (
  SELECT
    COUNT(*)
  FROM
    win
  JOIN
    raid
  ON
    win.raid_id = raid.id
  WHERE
    raid.channel = "#antonidas") / (
  SELECT
    COUNT(*)
  FROM
    entry
  JOIN
    raid
  ON
    entry.raid_id = raid.id
  WHERE
    raid.channel = "#antonidas") AS `win_rate`

标签： sqlgoogle-bigquery

Answer 1

此查询应为您提供所需的结果。它LEFT JOIN是raid，entry然后win计算每个表中不同行的数量，以确定每个通道的条目数和获胜次数：

SELECT r.channel,
       COUNT(DISTINCT r.id) AS raids,
       COUNT(DISTINCT w.raid_id) AS wins, 
       COUNT(DISTINCT e.raid_id) AS entries,
       CAST(COUNT(DISTINCT w.raid_id) AS FLOAT) / COUNT(DISTINCT e.raid_id) AS win_rate
FROM raid r
LEFT JOIN entry e ON e.raid_id = r.id
LEFT JOIN win w ON w.raid_id = r.id
GROUP BY r.channel

输出（用于您的样本数据）：

channel     raids   wins    entries     win_rate
#antonidas  4       2       4           0.5
#anvilmar   1       1       1           1

dbfiddle 上的演示

请注意，您可以在WHERE channel = ...之前添加GROUP BY以将结果限制到一个特定的通道。您也可以删除您实际上不需要的任何列，我将它们全部放入以演示从您的示例数据计算的数字。