sql - 依靠 GROUP BY 以及结果总数

Question

我有三个表:

users 只有两列:id: INT, name: TEXT

houses 三列:id: INT, user_id: INT, sold_at: DATE

users_with_house_permissions 有两列:id: INT, user_id: INT

我有这个查询可以找到用户出售的房屋数量(只要它有 house_permissions):

SELECT users.id as user_id, COUNT(*) as houses_sold
FROM users
JOIN users_with_house_permissions hp ON hp.user_id = users.id
LEFT JOIN houses on houses.user_id = users.id AND houses.sold_at IS NOT NULL
GROUP BY users.id

现在，我怎样才能得到返回的总行数？我正在尝试进行分页，我需要知道此查询将返回的总行数。

例如，如果我有以下内容:

users (id, name):

1, John
2, Carla
3, Bula

users_with_house_permissions (id, user_id):

1, 1
2, 2

houses (id, user_id, sold_at):

1, 1, '2018-10-10'

我希望查询的结果是:

user_id: 1
houses_sold: 1
total_count: 2

user_id: 2
houses_sold: 0
total_count: 2

因此，total_count 将表示此查询将返回的行数。

Answer 1

您可以使用没有任何分区的分析/窗口函数计数:

SELECT users.id as user_id, COUNT(*) as houses_sold,

  COUNT(*) OVER() as total_count -- count of rows returned by query

FROM users
JOIN users_with_house_permissions hp ON hp.user_id = users.id
LEFT JOIN houses on houses.user_id = users.id AND houses.sold_at IS NOT NULL
GROUP BY users.id

它像任何其他分析函数一样工作；它计算一个分区，但如果没有指定分区，则它计算整个数据集。在这种情况下，countover 是在分组完成后进行的，因此当 count(*) 计算组中的项目数时，count(*) over() 计算数据集中的组数

其他人发布了一个 sum(count(*)) over()，这相当于在分组之前对行进行计数。如果你有一个“谁卖了房子”的数据集，它是这样的:

john
john
john
mary

已售出 4 栋房屋，约翰售出 3 栋，玛丽售出 1 栋。该机构有 2 名销售代表。

COUNT(*) FROM ... GROUP BY name 给出了“john sold 3, mary sold 1”的数据集:

john, 3
mary, 1

如果我们对这个计数求和，我们会得到 4，即 3+1。这实际上是分组完成之前的房屋数量。 SUM(COUNT(*)) OVER() 因此是我们在进行分组之前的行数。请务必记住，COUNT(*) 属于 GROUP BY，并且将成为一个整数计数，然后由 SUM OVER 对其求和。如果我们使用子查询，可能会更容易看出:

SELECT name, the_count, SUM(the_count) OVER()
FROM (SELECT name, count(*) as the_count FROM sales GROUP BY name) subquery

但是因为分析是在分组完成后计算的，所以实际上没有必要像这样呈现它；数据库将以与它相同的方式执行此操作:

SELECT name, count(*), sum(count(*)) over() FROM sales GROUP BY name

因此，我们到了这样的地步，您明白在执行分组后应用分析，这意味着 COUNT(*) OVER() 是行数的计数分组操作完成后的数据集。分组产生了 john,3|mary,1 所以 COUNT(*) OVER() 这产生了 2 - 数据集中的行数

如果您想阅读更多内容，文档标题为“Windows 函数”，可以在此处找到:https://www.postgresql.org/docs/9.1/tutorial-window.html

这是针对 PG 9.1 的；记得将 View 更改为您特定版本的 PG