如何按用户邮件接收状态统计各项目平均存款次数？

可以通过一次查询完成分组统计，避免重复扫描数据。核心思路是先计算每个用户在各项目中的存款次数，再基于这些统计结果按项目和receive_emails分组计算平均值：

SELECT 
  project_id,
  receive_emails,
  AVG(deposit_count) AS avg_deposit_count
FROM (
  -- 子查询：统计每个用户在对应项目中的成功存款次数
  SELECT 
    a.project_id,
    b.receive_emails,
    a.player_external_id,
    COUNT(*) AS deposit_count
  FROM gamestorage.exchanged_orders a
  INNER JOIN gameanalytics.users_analytics b 
    ON a.player_external_id = b.external_id
  WHERE TYPE = 'deposit'
    AND status = 'success'
    AND payment_type = 'normal'
    AND amount >= 0
  GROUP BY a.project_id, b.receive_emails, a.player_external_id
) AS user_deposit_stats
GROUP BY project_id, receive_emails;

优化说明： #

• 仅需扫描一次原始数据，避免两次查询的重复计算，大幅提升处理效率
• 子查询先聚合出每个用户的存款次数，外层查询再计算分组平均值，逻辑清晰且符合SQL优化原则
• 自动包含receive_emails=true和false两种状态的统计结果，无需手动拆分查询

内容的提问来源于stack exchange，提问作者Neon L