在Postgres中获取一个表的ID和它对同一表的行总数的模

Question

在尝试将一些数据映射到表时，我想获得表的ID及其对同一表中总行数的模。例如，给定这个表:

id
--
1
3
10
12

我想要这个结果:

id | mod
---+----
1 |   1    <-  1 mod 4
3 |   3    <-  3 mod 4
10 |   2    <- 10 mod 4
12 |   0    <- 12 mod 4

是否有一种简单的方法可以动态地实现这一点(例如，不事先计算行数或以原子方式执行)?

到目前为止，我已经尝试了这样做:

SELECT t1.id, t1.id % COUNT(t1.id) mod FROM tbl t1, tbl t2 GROUP BY t1.id;

这是有效的，但是你必须有GROUP BY和tbl t2，否则它会为mod列返回0，这是有意义的，因为我认为它通过将表本身乘以表来工作，因此每个ID获得表的完整集合。我想对于足够小的表，这是可以的，但我可以看到这如何成为大表的问题。

编辑:找到了另一个hack-ish方法:

WITH total AS (
SELECT COUNT(*) cnt FROM tbl
)
SELECT t1.id, t1.id % t2.cnt mod FROM tbl t1, total t2

它类似于前一个查询，但它"折叠"与前一个计数相乘到单行。

Answer 1

可以使用COUNT()窗口函数:

SELECT id, 
id % COUNT(*) OVER () mod 
FROM tbl;

我确信优化器足够聪明，只计算一次窗口函数的结果。