如何通过添加子查询按客户端id对SQL中的所有平均值求和

Question

我在SQL中编写了按月计算平均值的查询，但现在我也想按client_id将所有平均值相加。我试图添加子查询，但它不起作用，这是我的代码：

SELECT to_char(cd.date, 'YYYYMM') AS date_format_m,
cd.client_id,
csd.owner_email,
AVG(mrr)
FROM client_data AS cd
LEFT JOIN client_static_data AS csd ON cd.client_id = csd.client_id
WHERE cd.date > '2021-09-01' and mrr > 0
GROUP by date_format_m, cd.client_id, csd.owner_email

Answer 1

也许您可以在这里使用SUM()作为分析函数：

SELECT
TO_CHAR(cd.date, 'YYYYMM') AS date_format_m,
cd.client_id,
csd.owner_email,
AVG(mrr),
SUM(AVG(mrr)) OVER (PARTITION BY cd.client_id) mrr_avg_sum
FROM client_data AS cd
LEFT JOIN client_static_data AS csd ON cd.client_id = csd.client_id
WHERE cd.date > '2021-09-01' AND mrr > 0
GROUP by date_format_m, cd.client_id, csd.owner_email;

如果以上不是你想要的，而是你只想为每个所有者的电子邮件和日期记录一条，那么只需子查询你目前拥有的内容：

SELECT
date_format_m,
owner_email,
SUM(mrr_avg) AS mrr_avg_sum        
FROM
(
SELECT
TO_CHAR(cd.date, 'YYYYMM') AS date_format_m,
cd.client_id,
csd.owner_email,
AVG(mrr) AS mrr_avg
FROM client_data AS cd
LEFT JOIN client_static_data AS csd ON cd.client_id = csd.client_id
WHERE cd.date > '2021-09-01' AND mrr > 0
GROUP by date_format_m, cd.client_id, csd.owner_email
) t
GROUP BY date_format_m, owner_email;