sql笔记（1）

隆哥当时给我出的几个sql的题

---

访问总量

SELECT DISTINCT from_unixtime(view_time,'%Y-%m-%d')as datetime,
uid
from house.member_record
ORDER BY datetime

结果是

---

访问总人数

SELECT DISTINCT unix_timestamp(from_unixtime(view_time,'%Y-%m-%d'))as datetime,
uid
from house.member_record
ORDER BY datetime

结果是

---

访问新用户数量

select a.min_date , count(distinct a.uid) from
( select uid , from_unixtime(min(view_time), '%Y%m%d') as min_date
from member_record
group by uid )a
group by a.min_date

结果是

---

某天的用户留存

select c.pdate , count(distinct c.uid) , count(distinct d.uid) from
( select distinct from_unixtime(view_time,'%Y%m%d') as pdate , uid from member_record )c left outer join
( select a.pdate , a.uid from
( select distinct from_unixtime(view_time,'%Y%m%d') as pdate , uid from member_record )a join
( select distinct from_unixtime(view_time,'%Y%m%d') as pdate , uid from member_record )b on a.uid = b.uid where b.pdate > a.pdate and datediff(b.pdate,a.pdate) <= 7 )d
on c.uid = d.uid and c.pdate = d.pdate
group by c.pdate

---

一周的用户留存

select c.pdate , count(distinct c.uid) , count(distinct d.uid) from 
( select distinct from_unixtime(view_time,'%Y%m%d') as pdate , uid from member_record )c left outer join 
( select a.pdate , a.uid from 
( select distinct from_unixtime(view_time,'%Y%m%d') as pdate , uid from member_record )a join 
( select distinct from_unixtime(view_time,'%Y%m%d') as pdate , uid from member_record )b on a.uid = b.uid where b.pdate > a.pdate and datediff(b.pdate,a.pdate) <= 7 )d on c.uid = d.uid and c.pdate = d.pdate 
group by c.pdate