时间戳

首页 > 解决方案 > 加入两个表后立即查询一半的结果

sql - 加入两个表后立即查询一半的结果

问题描述

以下查询仅对内部查询（post_engagement、网站购买）产生了正确的结果，而所有其他数字都错误地增加了许多倍。有任何想法吗？谢谢。

两个表的架构：

收藏广告（id、campaign_id、campaign_name、目标、展示次数、支出）

操作（id、ads_id、action_type、值）

SELECT
  f.campaign_id,
  f.campaign_name,
  f.objective,
  SUM(f.impressions) AS Impressions,
  SUM(f.spend) AS Spend,
  SUM(a.post_engagement) AS "Post Engagement",
  SUM(a.website_purchases) AS "Website Purchases"
FROM
favorite_ads f
LEFT JOIN (
     SELECT
          ads_id,
          CASE WHEN action_type = 'post_engagement' THEN SUM(value) END AS 
post_engagement,
      CASE WHEN action_type = 'offsite_conversion.fb_pixel_purchase' THEN SUM(value) END AS website_purchases
 FROM Actions a
 GROUP BY ads_id, action_type
) a ON f.id = a.ads_id
WHERE date_trunc('month',f.date_start) = '2018-04-01 00:00:00' AND 
date_trunc('month',f.date_stop) = '2018-04-01 00:00:00' --only get campaigns 
that ran in April, 2018
GROUP BY f.campaign_id, campaign_name, objective
Order by campaign_id

标签： sqlpostgresqlamazon-redshift

解决方案

如果不知道实际的表结构、约束、依赖关系和数据，就很难判断问题可能是什么。

您已经在评论中有一些线索，您应该首先考虑这些线索。

例如，您写道，此子查询返回正确的结果：

SELECT ads_id,
       CASE
         WHEN action_type = 'post_engagement'
         THEN SUM(value)
       END AS post_engagement,
       CASE
         WHEN action_type = 'offsite_conversion.fb_pixel_purchase'
         THEN SUM(value)
       END AS website_purchases
  FROM Actions a
 GROUP BY ads_id, action_type

这是否也给出了正确的结果：

SELECT ads_id,
       SUM(
         CASE
           WHEN action_type = 'post_engagement'
           THEN value
         END
       ) AS post_engagement,
       SUM(
         CASE
           WHEN action_type = 'offsite_conversion.fb_pixel_purchase'
           THEN value
         END
       ) AS website_purchases
  FROM Actions
 GROUP BY ads_id

如果是这样，然后尝试用那个替换您的子查询。

如果您仍然有问题，那么我会调查您的加入条件是否正确，看起来，对于一个活动 ( campaign_id)，您可能有多个相同的条目id，这将使子查询结果成倍增加 -取决于favorite_ads.

推荐阅读