查询每个用户最新的一条订单
表结构：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