sql 时间
SELECT NOW() 显示当前时间
SELECT paidTime,date(paidTime),date_format(paidTime,'%Y-%m')from data.orderinfo 改变日期的显示格式
SELECT date_add(paidTime,INTERVAL 1 day)from data.orderinfo 在原来的日期上加一天，
datediff()求差值
统计不同月份下单人数
SELECT month(paidTime),count(DISTINCT `userId`) from data.orderinfo
where `isPaid` = '已支付'
group by month(paidTime)
统计用户三月份的回购率和复购率
复购率
select count(ct) ,count(if(ct>1,1,NULL))from (
SELECT userId,count(userId) as ct from data.orderinfo
where `isPaid` = '已支付'and month(paidTime) = 3
group by userId ) t
回购率
select t1.ct,count(t1.ct),count(t2.ct),count(t2.ct)/count(t1.ct) from (
SELECT userId,date_format(paidTime,'%Y-%m-01') as ct from data.orderinfo
where `isPaid` = '已支付'
group by userId,date_format(paidTime,'%Y-%m-01'))as t1
left join (
SELECT userId,date_format(paidTime,'%Y-%m-01') as ct from data.orderinfo
where `isPaid` = '已支付'
group by userId,date_format(paidTime,'%Y-%m-01')) as t2
on t1.userId=t2.userId and t1.ct =date_sub(t2.ct,interval 1 month)
group by t1.ct
统计男女用户的消费频率是否由差异
select sex,avg(ct) from (
SELECT orderinfo.userId ,sex,COUNT(1) as ct FROM data.orderinfo
inner join (
select * from data.`userinfo`
where sex <> '') t
on orderinfo.userId=t.userId
group by userId,sex) t2
group by sex
多次消费的用户，最后一次和第一次的消费间隔是多少
select userId,max(paidTime),min(paidTime),max(paidTime)-min(paidTime),datediff(max(paidTime),min(paidTime)) from data.orderinfo
where isPaid = '已支付'
group by userId having count(1)>1
统计不同年龄段，用户的消费金额是否由差异
select age,avg(ct) from(
select o.userId,age,count(o.userId) as ct from data.orderinfo o
inner join (
select userId,ceil((year(now())-year(birth))/10) as age from data.userinfo
where birth>'1901-00-00') t
where isPaid ='已支付'
on o.userId=t.userId
where isPaid ='已支付'
GROUP BY o.userId,age) t2
group by age
统计消费的二八法则，消费top20%的用户，贡献了多少额度
