# 1.首先提取每个用户的最早登陆日期表m # 2.以login为左表左连接m表,两个条件user_id相等,l.date=m.min_date # 3.以date为分组统计m.user_id出现的次数 SELECT l.date, count( m.user_id ) FROM login l LEFT JOIN ( SELECT user_id, min( date ) min_date FROM login GROUP BY user_id ) m ON l.user_id = m.user_id AND l.date = m.min_date GROUP BY l.date; # 1. 首先提取每个用户的最早登陆日期表 # 2. 按照date分组计算( user_id, date )同时出现在“最早登陆日期表”中的次数 SELECT date, sum(( user_id, date ) IN ( SELECT user_id, min( date ) min_date FROM login GROUP BY user_id ) ) new FROM login GROUP BY date ORDER BY date; # 1.利用first_value窗口函数求第一次登陆的日期 # 2.以date分组用sum( date = f_date ) 为首次登陆的次数 SELECT date, sum( date = f_date ) FROM ( SELECT user_id, date, first_value( date ) over ( PARTITION BY user_id ORDER BY date ) f_date FROM login ) f GROUP BY date;