在给定时间内的Postgres和
在给定时间内的Postgres和
提问于 2020-11-18 17:49:22
我正试图用Postgres对谷歌在给定时间内的分析数据进行汇总。我不太清楚如何解决分组问题。下面看到的查询每天返回一列。我想把所有点击的次数加到给定的日期。因此，查询应该为每个活动返回1行，每一行上都有一列指示单击的总和。
SELECT
sa.id AS salesforce_account_id,
acp.campaignid,
acp.campaignname,
date,
SUM(clicks) as clicks
FROM
adwords_campaign_performance acp
INNER JOIN salesforce_account sa ON
sa.adwords_id = acp.adwords_customerid
WHERE acp.date >= '2020-10-01'
AND acp.date <= '2020-10-03'
GROUP BY sa.id, acp.campaignid, acp.campaignname, date我可以编写查询，以便返回整个月的数字，如下所示：
SELECT
sa.id AS salesforce_account_id,
acp.campaignid,
acp.campaignname,
date_trunc('month', date) AS MONTH,
SUM(clicks) as clicks
FROM
adwords_campaign_performance acp
INNER JOIN salesforce_account sa ON
sa.adwords_id = acp.adwords_customerid
WHERE MONTH = '2020-10-01 00:00:00'
AND sa.id = 3148
GROUP BY sa.id, acp.campaignid, acp.campaignname, MONTH任何帮助都将不胜感激。谢谢!
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-11-18 17:51:11
下面看到的查询每天返回一列。我想把所有点击的次数加到给定的日期。因此，查询应该为每个活动返回1行，每一行上都有一列指示单击的总和。
因此，只需将date从select和group by子句中删除：
SELECT
sa.id AS salesforce_account_id,
acp.campaignid,
acp.campaignname,
SUM(clicks) as clicks
FROM adwords_campaign_performance acp
INNER JOIN salesforce_account sa ON sa.adwords_id = acp.adwords_customerid
WHERE acp.date >= '2020-10-01'
AND acp.date <= '2020-10-03'
GROUP BY sa.id, acp.campaignid, acp.campaignnameStack Overflow用户
发布于 2020-11-18 17:55:10
我认为您希望generate_series()生成一个月中的所有日期：
SELECT gs.date, sa.id AS salesforce_account_id, acp.campaignid, acp.campaignname,
SUM(clicks) as clicks
FROM generate_series('2020-10-01'::date, '2020-10-31'::date, interval '1 day') gs(dte) LEFT JOIN
adwords_campaign_performance acp
ON acp.date >= gs.dte AND
acp.date < gs.dte + INTERVAL '1 DAY' LEFT JOIN
salesforce_account sa
ON sa.adwords_id = acp.adwords_customerid
GROUP BY gs.dte, sa.id, acp.campaignid, acp.campaignname, date页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64898537
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号