查询每个用户最新的一条订单
表结构:id,user_id,order_status,create_time,update_time
解决思路:子查询加表连接查询,首先将表格按user_id
group,使用max()
函数匹配每个id下的create_time
,接着利用user_id
和create_time
自连接,获得最终的订单记录
select b.* from(
select max(create_time) as create_time,user_id
from order_record
group by user_id) a
left join order_record b
on a.user_id=b.user_id
and a.create_time=b.create_time
)
新老用户金额查询
有一张全量的交易表和一张新用户交易表,使用sql统计新用户和老用户昨日成交的总金额。
select
case when t2.use_id is not null then 'new' else 'old' end as user_type,
sum(t1.money) as total_amount
from all_user as t1
left join new_user as t2
on t1.user_id=t2.user_id
where Date(t1.datetime)=CURDATE()-INTERVAL 1 DAY
group by user_type
牛客SQL11.每日的日活数及新用户的占比
用户行为日志表tb_user_log | id | uid | artical_id | in_time | out_time | sign_cin |
---|---|---|---|---|---|---|
1 | 101 | 9001 | 2021-10-31 10:00:00 | 2021-10-31 10:00:09 | 0 | |
2 | 102 | 9001 | 2021-10-31 10:00:00 | 2021-10-31 10:00:09 | 0 | |
3 | 101 | 0 | 2021-11-01 10:00:00 | 2021-11-01 10:00:42 | 1 | |
4 | 102 | 9001 | 2021-11-01 10:00:00 | 2021-11-01 10:00:09 | 0 | |
5 | 108 | 9001 | 2021-11-01 10:00:01 | 2021-11-01 10:00:50 | 0 | |
6 | 108 | 9001 | 2021-11-02 10:00:01 | 2021-11-02 10:00:50 | 0 | |
7 | 104 | 9001 | 2021-11-02 10:00:28 | 2021-11-02 10:00:50 | 0 | |
8 | 106 | 9001 | 2021-11-02 10:00:28 | 2021-11-02 10:00:50 | 0 | |
9 | 108 | 9001 | 2021-11-03 10:00:01 | 2021-11-03 10:00:50 | 0 | |
10 | 109 | 9002 | 2021-11-03 11:00:55 | 2021-11-03 11:00:59 | 0 | |
11 | 104 | 9003 | 2021-11-03 11:00:45 | 2021-11-03 11:00:55 | 0 | |
12 | 105 | 9003 | 2021-11-03 11:00:53 | 2021-11-03 11:00:59 | 0 | |
13 | 106 | 9003 | 2021-11-03 11:00:45 | 2021-11-03 11:00:55 | 0 |
(uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到)
问题:统计每天的日活数及新用户占比
- 新用户占比=当天的新用户数÷当天活跃用户数(日活数)。
- 如果in_time进入时间和out_time离开时间跨天了,在两天里都记为该用户活跃过。
- 新用户占比保留2位小数,结果按日期升序排序。
分析
- 建立一张登录表,记录每天登录的用户,并按天对用户进行去重
- 使用窗口函数对每个用户的登录日期进行排序,序号为1则为新用户
解答
with t1 as(
select uid,date(in_time) dt
from tb_user_log
union //union实现去重
select uid,date(out_time) dt
from tb_user_log
),
with t2 as(
uid,dt
row_number()over(partition by uid order by dt) rn //对用户登录日期进行排序
from t1
)
select dt,
count(uid) dau,
round(sum(if(rn=1,1,0))/count(uid),2) as uv_new_ration
from t2
group by dt
order by dt
次日留存率计算
实际业务中(特别是电商业务分析),需要计算今天用户登录,第二天还会登录的用户所占的比率,也就是次日留存率的概念。
计算思路就是将表进行自连接(条件是左表日期+1=右表日期),满足条件的记录数就是次日登录的用户总数,再除以左表记录总数(用户数)就是次日留存率。
select count(date2)/count(date1) as avg_ret
from(
select
distinct a.uid,
a.date as date1,
b.date as date2
from tableName a
left join(
select distinct uid,date
from tableName
)b
on a.uid=b.uid
and date_add(a.date,interval 1 day)=b.date
)t
Comments NOTHING