json - Postgresql根据另一个表的组数据统计数据生成JSON

Question

我有两个表格，如下所示:

users
id     name     city           state
1      Bob      Los Angeles    California
2      Susan    Dallas         Texas
3      Joe      Los Angeles    California


activity       
type           user_id
Activation     1
Registration   1
Activation     2
Activation     3

我想计算事件类型 Activation 发生的次数以返回这样的数据集:

[
  {count: 2, city: 'Los Angeles', state: 'California'},
  {count: 1, city: 'Dallas', state: 'Texas'}
]

这是我试过的查询:

SELECT COUNT(*) AS count, user_id,
(SELECT json_agg(users.*) FROM users WHERE activity.user_id = users.id) as users
FROM activity
WHERE type='Activation'
GROUP BY users.city, users.state, user_id

我得到这个错误:错误:缺少表“users”的 FROM 子句条目

我不确定如何对来自多个表的分组数据进行这样的计数。

这是Postgresql的最新版本

Answer 1

我会首先创建一个提供正确数据的 View ，例如:

CREATE VIEW activations_on_locations AS
    SELECT count(*), U.city, U.state
    FROM users U
    INNER JOIN activity A
        ON U.id = A.user_id
        AND A.type='Activation'
    GROUP BY U.city, U.state;

反对 JSON 查询非常简单:

SELECT json_agg(AOL) FROM activations_on_locations AOL;