使子查询适用于每个独立 ID，而不是从多个 ID 中获取值的 SQL

Question

这是用于查找排名前 10 的广告客户应用"（基于 # 安装次数）平均有效每千次展示费用（以下查询中的计算结果）。如果我仅使用一个发布者 ID 运行它，它可以准确工作。

问题：使用多个发布应用 ID 时，子查询 （C2） 会读取该 ID，就好像排名前 10 的广告客户应用来自多个发布 ID 一样，从而产生误导性计算。我怎样才能以每个发布商应用可以读取的方式运行它（并获取每个发布商应用前 10 名广告客户的 CPPM）？

 SELECT
    d.app_name AS publisher_app_name,           
    a.publisher_app AS publisher_app_id,            
    (   SELECT 
            CASE WHEN SUM(b.impressions) > 0
                THEN ROUND((1000*SUM(b.money_spent))/SUM(b.impressions),3)
                    ELSE 0
                        END AS top_10_adv_ecpm
        FROM warehouse.daily_uber_aggr b
        WHERE publisher_app IN ('983234fb17ba470008','2b3ed6ff4d0e48463')
        AND b.dt BETWEEN '2018-12-26' AND '2019-01-25'
        AND b.advertiser_app IN (   SELECT advertiser_app
                                  FROM warehouse.daily_uber_aggr
                                  WHERE publisher_app IN ('983234fb17ba470008','2b3ed6ff4d0e48463')
                                  AND dt BETWEEN '2018-12-26' AND '2019-01-25'
                                  GROUP BY advertiser_app
                                  ORDER BY SUM (converted_installs) DESC
                                  LIMIT 10
    ) AS top_10_adv_ecpm,
    SUM(a.converted_installs) AS installs_publisher
 FROM warehouse.daily_uber_aggr a
LEFT JOIN dimensions.apps d ON d.app_id = a.publisher_app
WHERE a.publisher_app IN ('983234fb17ba470008','2b3ed6ff4d0e48463')
AND a.dt BETWEEN '2018-12-26' AND '2019-01-25'
 GROUP BY 
    d.app_name,
    a.publisher_app

Answer 1

我很确定你正在使它比它需要的更复杂。将聚合放入结果集中的子选择中确实会弄乱您要执行的操作。这（未经数据测试，确认语法）难道不能正常工作吗？

SELECT top 10 d.app_name AS publisher_app_name, a.publisher_app AS publisher_app_id, 
    CASE WHEN SUM(a.impressions) > 0
        THEN ROUND((1000*SUM(a.money_spent))/SUM(a.impressions),3)
        ELSE 0
        END AS top_10_adv_ecpm,
    SUM(a.converted_installs) AS installs_publisher
FROM warehouse.daily_uber_aggr a 
LEFT JOIN dimensions.apps d ON d.app_id = a.publisher_app
WHERE a.publisher_app IN ('983234fb17ba470008','2b3ed6ff4d0e48463')
AND a.dt BETWEEN '2018-12-26' AND '2019-01-25'
GROUP BY d.app_name, a.publisher_app
ORDER BY SUM (converted_installs) DESC

注意 我将LIMIT 10更改为top 10，因为它被标记为 SQL Server，如果它是另一个数据库品牌（如 MySQL），请使用适当的方法限制为前 10 个结果。

FWIW 原始查询的问题是您的查询和结果集中的子查询之间没有链接，因此对于主查询返回的每一行，您将运行子查询并返回其完整结果。