R语言处理超大数据集需聚合时如何获取未聚合维度的统计结果

你不需要先按企业单维度聚合后再二次计算，所有指标可以直接在传入domo_get_query的MySQL语句中完成聚合计算，计算过程全部跑在数据源侧，仅返回聚合后的小数据量结果，不需要全量拉取2000万条原始数据。

按企业+月份统计月度线索量 #

调整GROUP BY维度，新增月份统计维度即可，不需要保留原始明细数据：

inbound_leads_monthly <- domo_get_query('6d969e8b-fe3e-46ca-9ba2-21106452eee2',
                                auto_limit = TRUE,
                                query = "select org_id,
                                          -- 把inserted_at转成YYYY-MM格式的月份
                                          DATE_FORMAT(STR_TO_DATE(inserted_at, '%m/%d/%Y'), '%Y-%m') as lead_month,
                                          COUNT(*) as monthly_lead_count
                                        from table
                                        GROUP BY org_id, lead_month
                                        ORDER BY org_id, lead_month"
)

同时获取企业总线索量+月度线索量 #

如果需要一次查询同时拿到单企业总线索和各月线索，可以用窗口函数实现，不需要分两次查询：

select 
  org_id,
  DATE_FORMAT(STR_TO_DATE(inserted_at, '%m/%d/%Y'), '%Y-%m') as lead_month,
  COUNT(*) as monthly_lead_count,
  -- 按org_id分组统计总线索量
  COUNT(*) over (partition by org_id) as total_lead_per_org
from table
GROUP BY org_id, lead_month
ORDER BY org_id, lead_month

转化率计算 #

用条件聚合即可在同一句SQL中完成转化率统计，逻辑是统计lead_converted_at非空的线索数除以总线索数：

select 
  org_id,
  DATE_FORMAT(STR_TO_DATE(inserted_at, '%m/%d/%Y'), '%Y-%m') as lead_month,
  COUNT(*) as monthly_lead_count,
  COUNT(*) over (partition by org_id) as total_lead_per_org,
  -- 月度转化数
  COUNT(CASE WHEN lead_converted_at IS NOT NULL THEN 1 END) as monthly_converted_count,
  -- 月度转化率
  ROUND(COUNT(CASE WHEN lead_converted_at IS NOT NULL THEN 1 END)/COUNT(*),4) as monthly_conversion_rate,
  -- 企业总转化率
  ROUND(COUNT(CASE WHEN lead_converted_at IS NOT NULL THEN 1 END) over (partition by org_id)/COUNT(*) over (partition by org_id),4) as total_conversion_rate_per_org
from table
GROUP BY org_id, lead_month
ORDER BY org_id, lead_month

如果需要筛选指定客户群，直接在WHERE子句中加org_id in (xxx)的筛选条件即可，会进一步缩小返回的结果集大小。

内容的提问来源于stack exchange，提问作者Justin Benfit