-- 第一步:新用户临时表
select user_id, min(date) as m_date
from login
group by user_id;
-- 第二步:根据日期,统计人数,得出有新用户登录时的日期及其数量
select m_date, count(distinct user_id) as new
from (select user_id, min(date) as m_date
from login
group by user_id) as n_user
group by m_date
order by m_date;
-- 第三步:抽取所有日期临时表
select DISTINCT date
from login;
-- 第四步:两个表联结,并设置空值为0
select date, ifnull ( new, 0 )
from (
select m_date, count(distinct user_id) as new
from (select user_id, min(date) as m_date
from login
group by user_id) as n_user
group by m_date
order by m_date
) as new_user_date
right outer join (select DISTINCT date from login) as total_date
on total_date.date = new_user_date.m_date
order by total_date.date;
这道题最大的收获,大概是一个从来没有注意过的函数ifnull(data1,data2)
。
这个函数的含义就是,如果第一个位置的值为空值,则此处填写位置2的值。