计算每个用户的交互总数

Question

我试图按用户显示每天的"Censored"交互和按用户显示的总Censored。

select
date("PostedAt"),
"UserId",
sum(count(*)) over(order by("PostedAt")) as "Total by User"
FROM
"CommunityPost"
WHERE
"Censored"=true
GROUP BY "PostedAt","UserId"
ORDER BY ("PostedAt") DESC

它输出该组中所有用户的总数，而不是按UserId的总数。

Date.         UserID Total by User
"2020-04-22"    41  12
"2020-04-22"    41  11
"2020-04-22"    32  10
"2020-04-21"    32  8

如何按UserId获取总数？

Answer 1

我认为您需要添加PARTITION BY。示例如下：

SELECT
DATE("PostedAt"),
"UserId",
SUM(count(*)) OVER(PARTITION BY "UserId", "PostedAt") AS "Total by User"
FROM
"CommunityPost"
WHERE
"Censored"=true
GROUP BY "PostedAt","UserId"
ORDER BY ("PostedAt") DESC