时间戳

首页 > 解决方案 > 依靠 GROUP BY 以及结果总数

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

问题描述

我有三张桌子：

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 将表示此查询将返回的行数。

标签： sqlpostgresqlgroup-bycount

解决方案

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

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

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

其他人发布了一个sum(count(*)) over()等效于在分组之前计算行数的方法。如果你有一个“谁卖了房子”的数据集，它是这样的：

john
john
john
mary

已售出 4 套房屋，john 已售出 3 套，mary 已售出 1 套。该机构有 2 名销售代表。

COUNT(*) FROM ... GROUP BY name给出“约翰卖了 3，玛丽卖了 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

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

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；请记住将视图更改为您的特定 PG 版本

推荐阅读