SQL查询获取combined_lead_score列平均值返回相同值问题解决

你当前的SQL存在两个核心逻辑错误：

1. 使用了AVG()、COUNT()这类聚合函数，但没有声明GROUP BY子句，数据库会默认将每一条独立的行作为一个分组聚合，单一行的平均值自然等于该行本身的combined_lead_score值，所以两个字段输出完全一致。
2. 子查询内同时混用了DISTINCT和聚合函数，逻辑冲突，且你当前贴出的代码语法不完整，缺少子查询的闭合括号和外层查询逻辑。

根据你的使用场景可以选择两种修改方案：

场景1：保留所有线索明细，每行附带全局总平均值 #

这种情况用窗口函数替代普通聚合函数，不需要加GROUP BY，修改后代码示例如下：

SELECT *
FROM (
   SELECT
      l_i.lead_name AS name,
      l_i.lead_source AS lead_source,
      l_i.lead_source_detail AS lead_source_detail,
      l_i.campaign_source_name AS campaign_source,
      l_i.title AS title,
      l_i.industry AS industry,
      CAST(l_i.combined_lead_score as varchar(10)) AS combined_lead_score,
      -- 窗口函数计算全量数据的平均值
      CAST(AVG(l_i.combined_lead_score) OVER() as varchar(10)) AS avg_combined_lead_score,
      l_i.created_date AS date,
      'Inbound' AS category,
      l_i.lead_id AS id,
      -- 窗口函数计算全量线索总数，需要分组计数可在OVER内加PARTITION BY指定维度
      COUNT(l_i.lead_id) OVER() AS total_lead_count
   FROM {{ref('leads')}} l_i
   WHERE l_i.lead_status != 'Unqualified' AND l_i.lead_source_type IN ('Events', 'Inbound')
) t
-- 有去重需求可以在这里加DISTINCT，不要和内层聚合逻辑混用

场景2：按指定维度分组，计算各分组的平均值 #

如果不需要明细，只需要看比如按行业、线索来源分组的平均值，就补充GROUP BY子句，把所有非聚合字段都放到GROUP BY列表中：

SELECT
  l_i.industry AS industry,
  l_i.lead_source AS lead_source,
  CAST(AVG(l_i.combined_lead_score) as varchar(10)) AS avg_combined_lead_score,
  COUNT(l_i.lead_id) AS group_lead_count
FROM {{ref('leads')}} l_i
WHERE l_i.lead_status != 'Unqualified' AND l_i.lead_source_type IN ('Events', 'Inbound')
GROUP BY l_i.industry, l_i.lead_source

内容的提问来源于stack exchange，提问作者Karthikcharan Suresh