postgresql - 在服务器端使用 over table 计算表列

Question

假设，db中有两个表:

表注册表:

       Column       |            Type             |                           
--------------------+-----------------------------+---------
 registry_id        | integer                     | not null 
 name               | character varying           | not null
 ...
 uploaded_at        | timestamp without time zone | not null

表行:

    Column     |            Type             | Modifiers 
---------------+-----------------------------+-----------
 row_id        | character varying           | not null
 registry_id   | integer                     | not null
 row           | character varying           | not null

在现实世界中，registries 只是一个 csv 文件，rows 是文件的行。在我的 scala-slick 应用程序中，我想知道每个文件中有多少行。

注册表:

1,foo,...
2,bar,...
3,baz,...

行数:

aaa,1,...
bbb,1,...
ccc,2,...

期望的结果:

1,foo,... - 2
2,bar,... - 1
3,baz,... - 0

我现在的代码是(slick-3.0):

def getRegistryWithLength(rId: Int) = {
    val q1 = registries.filter(_.registryId===rId).take(1).result.headOption
    val q2 = rows.filter(_.registryId===rId).length.result
    val registry = Await.result(db.run(q1), 5.seconds)
    val length = Await.result(db.run(q2), 5.seconds)
    (registry, length)
}

(Await 是个坏主意，我知道)

如何使用单个 sql 查询执行 getRegistryWithLength？

我可以将列 row_n 添加到表 registries 中，但是在删除/插入查询后我将被迫更新列 row_n 行表。

如何在数据库服务器端自动计算表 registries 中的列 row_n？

Answer 1

基本查询可以是:

SELECT r.*, COALESCE(n.ct, 0) AS ct
FROM   registry r
LEFT   JOIN (
   SELECT registry_id, count(*) AS ct
   FROM   rows
   GROUP  BY registry_id
   ) n USING (registry_id);

LEFT [OUTER] JOIN是必不可少的，因此您不会在 rows 中没有相关行的情况下从注册表中过滤行.

COALESCE 在没有找到相关行的情况下返回 0 而不是 NULL。

SO上有很多相关的回答。一个在这里:

• SQL: How to save order in sql query?

您可以将其包装在 VIEW 中为了方便起见:

CREATE VIEW reg_rn AS
SELECT ...

...您可以像查询表格一样查询它。

旁白:使用 reserved SQL key words 是不明智的作为标识符。 row不能使用列名(即使在 Postgres 中允许)。