我在SQL查询中使用sum()寻找帮助:

SELECT links.id,
       count(DISTINCT stats.id) as clicks,
       count(DISTINCT conversions.id) as conversions,
       sum(conversions.value) as conversion_value
FROM links
LEFT OUTER JOIN stats ON links.id = stats.parent_id
LEFT OUTER JOIN conversions ON links.id = conversions.link_id
GROUP BY links.id
ORDER BY links.created desc;

我使用DISTINCT是因为我正在进行“分组依据”,这可以确保同一行不会被重复计数。

问题是SUM(conversions.value)对每行的“值”多次计数(由于分组依据)

我基本上想为每个DISTINCT conversions.id做SUM(conversions.value)

那可能吗?

最佳答案

我可能是错的,但据我了解

  • conversions.id 是表中的主键 转换
  • stats.id 是表的主键 统计信息

  • 因此,对于每个conversions.id,您最多影响一个link.id。

    您的要求有点像做2组的笛卡尔积:
    [clicks]
    SELECT *
    FROM links
    LEFT OUTER JOIN stats ON links.id = stats.parent_id
    
    [conversions]
    SELECT *
    FROM links
    LEFT OUTER JOIN conversions ON links.id = conversions.link_id
    

    对于每个链接,您将获得sizeof([clicks])x sizeof([conversions])行

    如前所述,您可以通过以下方式获得请求中唯一身份转化的次数:
    count(distinct conversions.id) = sizeof([conversions])
    

    这种独特的方法可以删除笛卡尔积中的所有[clicks]行

    但显然
    sum(conversions.value) = sum([conversions].value) * sizeof([clicks])
    

    就您而言,
    count(*) = sizeof([clicks]) x sizeof([conversions])
    count(*) = sizeof([clicks]) x count(distinct conversions.id)
    

    你有
    sizeof([clicks]) = count(*)/count(distinct conversions.id)
    

    所以我会用
    SELECT links.id,
       count(DISTINCT stats.id) as clicks,
       count(DISTINCT conversions.id) as conversions,
       sum(conversions.value)*count(DISTINCT conversions.id)/count(*) as conversion_value
    FROM links
    LEFT OUTER JOIN stats ON links.id = stats.parent_id
    LEFT OUTER JOIN conversions ON links.id = conversions.link_id
    GROUP BY links.id
    ORDER BY links.created desc;
    

    让我发布!
    杰罗姆

    07-24 21:06