时间戳

首页 > 解决方案 > “combined_lead_score”列的平均值

sql - “combined_lead_score”列的平均值

问题描述

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) as varchar(10)) AS avg_combined_lead_score,

完整代码

SELECT *
FROM (
   SELECT DISTINCT
      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) as varchar(10)) AS avg_combined_lead_score,
      l_i.created_date AS date,
      'Inbound' AS category,
      l_i.lead_id AS id,
      COUNT(l_i.lead_id)
   FROM {{ref('leads')}} l_i
   WHERE l_i.lead_status != 'Unqualified' AND l_i.lead_source_type IN ('Events', 'Inbound')

我如何查看 combine_lead_score 的平均值，我现在所做的只是为两者抛出相同的数字

标签： sql

解决方案

AVG 是一种聚合操作，但您不是聚合，只是选择不同的行。你想要的是一个 GROUP BY。

我不知道您的数据的确切形状/状态，但假设您想要所有具有相同, ,等的条目的总数，以及相同的平均数和计数...combined_lead_scorelead_namelead_sourcelead_source_detail

你可能想要更像这样的东西：

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,

  /* fetch TOTAL for lead score as 'combined'*/
  CAST(SUM(l_i.combined_lead_score) as varchar(10)) AS combined_lead_score,

  /* fetch AVG for lead score as average */
  CAST(AVG(l_i.combined_lead_score) as varchar(10)) AS avg_combined_lead_score,
  l_i.created_date AS date,
  'Inbound' AS category,
  l_i.lead_id AS id,
   COUNT(l_i.lead_id)
FROM 
  {{ref('leads')}} l_i
WHERE 
  l_i.lead_status != 'Unqualified' 
  AND l_i.lead_source_type IN ('Events', 'Inbound')

GROUP BY
  l_i.lead_name,
  l_i.lead_source,
  l_i.lead_source_detail,
  l_i.campaign_source_name,
  l_i.title,
  l_i.industry,  
  l_i.created_date,

推荐阅读