如何创建具有多个计算的表?

Question

如果我使用一个连接执行一次计算:

SELECT
SUM(friends_made) as calc1, table2.group_id
FROM
friends_made_table as table1
INNER JOIN
grouped_users as table2 ON table1.user_id = table2.user_id
GROUP BY 
table2.group_id

我得到的结果是:

calc1 | group_id
-----------------
400   |   1
320   |   2
330   |   3

但我还需要另一个计算(calc2)与table1相同的内部连接，但与不同的表(table3)

SELECT
SUM(request_accept) AS calc2, table1.group_id
FROM
friends_accept_table AS table3
INNER JOIN
grouped_users as table1 ON table1.user_id = table3.user_id
GROUP BY
table1.group_id

结果是:

calc2 | group_id
-----------------
100   |   1
150   |   2
120   |   3

我如何连接这两个查询并创建一个显示两个计算(calc1, calc2)的新表?

calc1 |calc2 | group_id
-----------------------
400   | 100  | 1
320   | 150. | 2
330   | 120. | 3

编辑显示表格/结果，去掉四舍五入

Answer 1

只要在两个结果之间存在一组共同的group_ids，一个连接就足够了。否则，您可能需要左/右连接或全连接。

with data1 as (
SELECT SUM(friends_made) as calc1, table2.group_id
FROM friends_made_table as table1 INNER JOIN grouped_users as table2
ON table1.user_id = table2.user_id
GROUP BY table2.group_id
), data2 as (
SELECT SUM(request_accept) as calc2, table1.group_id
FROM friends_accept_table as table3 INNER JOIN grouped_users as table1
ON table1.user_id = table3.user_id
GROUP BY table1.group_id
)
select calc1, calc2, d1.group_id
from data1 d1 inner join data2 d2 on d2.group_id = d1.group_id;

这里假定您的平台支持CTE语法。如果没有，可能会有类似的重写。