明确题意

统计每天的日活数及新用户占比

步骤拆解

1. 得到客户最早进入时间表 tb_early

• 最早活跃时间:MIN(DATE(in_time))

			SELECT uid,MIN(DATE(in_time)) early_time
			FROM tb_user_log ul
			GROUP BY uid

2. 得到客户所有的活跃时间表 tb_uv

SELECT 
  uid,
  DATE(in_time) uv_time -- 客户进入时间
FROM
  tb_user_log ul 
UNION -- 将两个表UNION，去重得到客户的所有活跃时间（跨天按两天算）
SELECT 
  uid,
  DATE(out_time) uv_time -- 客户退出时间 
FROM
  tb_user_log ul 

3. 将表 tb_early 与表 tb_uv 左连得到新表 tb 包括客户最早活跃时间(即哪天为新用户)，活跃时间

SELECT 
    tb_early.uid,
    uv_time,
    early_time 
FROM tb_early 
LEFT JOIN tb_uv 
ON tb_early.uid = tb_uv.uid -- 将最早进入时间与所有活跃时间连接起来

4. 对 tb 按活跃时间进行分组，计算每天的活跃数，以及新用户数

• 按照活跃时间分组：GROUP BY uv_time
• 每天的活跃数： COUNT( DISTINCT uid ) dau
• 新用户数：SUM(IF( early_time=uv_time,1,0))
• 新用户占比 = 新用户数 / 每天的活跃数
• 保留两位小数：ROUND(uv_new,2)

 SELECT 
	tb.uv_time dt,
    COUNT(DISTINCT uid) dau, -- 计算每天的用户数
    ROUND(SUM(IF( early_time=uv_time,1,0))/COUNT(DISTINCT uid),2) nu_new_ratio
    -- 如果最早进入时间=活跃时间，则认为是新用户
 FROM tb -- tb表有客户id，客户最早进入时间，客户活跃时间
 GROUP BY uv_time -- 按活跃时间分组
 ORDER BY dt -- 按日期升序

最终代码

 SELECT 
	tb.uv_time dt,
    COUNT(DISTINCT uid) dau, -- 计算每天的用户数
    ROUND(SUM(IF( early_time=uv_time,1,0))/COUNT(DISTINCT uid),2) nu_new_ratio
    -- 如果最早进入时间=活跃时间，则认为是新用户
  FROM 
  (
	  SELECT tb_early.uid,uv_time,early_time
	  FROM ( -- 客户最早进入时间
			SELECT uid,MIN(DATE(in_time)) early_time
			FROM tb_user_log ul
			GROUP BY uid
	  ) tb_early
	  LEFT JOIN 
	  (   
		  SELECT uid,DATE(in_time) uv_time
		  FROM tb_user_log ul -- 客户进入时间
		  UNION -- 将两个表UNION，去重得到客户的所有活跃时间（跨天按两天算）
		  SELECT uid,DATE(out_time) uv_time
		  FROM tb_user_log ul -- 客户退出时间
		) tb_uv
		ON tb_early.uid=tb_uv.uid  -- 将最早进入时间与所有活跃时间连接起来
  ) tb -- tb表有客户id，客户最早进入时间，客户活跃时间
  GROUP BY uv_time -- 按活跃时间分组
  ORDER BY dt -- 按日期升序

优化代码1.0

可以把最早进入时间利用窗口函数直接计算，就不用连接俩表了

  SELECT 
    tb.uv_time dt,
    COUNT(DISTINCT uid) dau,
    -- 计算每天的用户数
    ROUND(
      SUM(IF(early_time = uv_time, 1, 0)) / COUNT(DISTINCT uid),
      2
    ) nu_new_ratio -- 如果最早进入时间=活跃时间，则认为是新用户
  FROM
    (SELECT 
      uid,
      uv_time,
      (MIN(uv_time) over (PARTITION BY uid)) early_time  -- 最早进入时间
    FROM
      (SELECT 
        uid,
        DATE(in_time) uv_time 
      FROM
        tb_user_log ul -- 客户进入时间
      UNION
      -- 将两个表UNION，去重得到客户的所有活跃时间（跨天按两天算）
      SELECT 
        uid,
        DATE(out_time) uv_time 
      FROM
        tb_user_log ul -- 客户退出时间
      ) tb_uv -- 将最早进入时间与所有活跃时间连接起来
    ) tb -- tb表有客户id，客户最早进入时间，客户活跃时间
  GROUP BY uv_time -- 按活跃时间分组
  ORDER BY dt -- 按日期升序