ruby-on-rails - 生成连接多个表并对某些列求和的 PostgreSQL 查询

Question

我正在尝试通过 PostgreSQL 查询返回下表:

container length | terminal 1 | terminal 2 | terminal 3 | Cumulative
----------------------------------------------------------------------
10               |   12       |     12     |     12     |     36     |
20               |   34       |     34     |     34     |     102    |
30               |   56       |     56     |     56     |     168    |
40               |   78       |     78     |     78     |     234    |

数字是编造的，但本质上我希望能够看到:

• 第一列容器长度
• 我的所有码头以及每个容器长度的容器数量
• 第 4 列中每一行的这些值(第 2-4 列)的总和。

这可以通过 SQL 查询实现吗？

表格如下:

Organization
 - terminal_id
 - container_id

Container
 - organization_id
 - current_location_type
 - current_location_id

Terminal
 - organization_id

也就是说，一个Organization有很多容器，有很多终端。一个终端属于一个组织。一个容器属于一个组织。

我可以从容器派生终端的方式是:containers.current_location_type = 'Terminal' 和 containers.current_location_id = terminals.id

我有以下 SQL 查询，但它没有按照我希望在上表中返回的方式返回数据。

SELECT containers.container_length AS container_length,
  terminals.name AS terminal_name,
  COUNT(*) AS group_count
FROM containers
LEFT JOIN terminals
  ON terminals.organization_id = organization.id
  AND terminals.id = containers.current_location_id
WHERE containers.organization_id = organization.id
  AND containers.current_location_type = 'Terminal'
GROUP BY containers.container_length, terminals.id
ORDER BY container_length ASC

此查询返回 3 列:container_length、terminal_name 和 group_count。好像我在正确的道路上，但我无法找到上表中的方法。关于如何实现这一目标的任何想法？谢谢!

Answer 1

自 9.4 以来，postgres 能够使用 FILTER 子句选择性地应用聚合。

你可以这样做:

SELECT
  containers.container_length,
  count(*) FILTER (WHERE terminals.name = 'terminal_1') AS terminal_1,
  count(*) FILTER (WHERE terminals.name = 'terminal_2') AS terminal_2,
  count(*) FILTER (WHERE terminals.name = 'terminal_3') AS terminal_3,
  count(*) AS cumulative
FROM containers -- ...

您必须将分组调整为仅按 container_length。