26、从商品信息表(sku_info)求出各分类商品价格的中位数,如果一个分类下的商品个数为偶数则输出中间两个值的平均值,如果是奇数,则输出中间数即可
结果如下:
category_id | medprice |
1 | 3500.00 |
2 | 550.00 |
3 | 75.00 |
需要用到的表:
商品信息表:sku_info
sku_id(商品id) | name(商品名称) | category_id(分类id) | from_date(上架日期) | price(商品价格) |
1 | xiaomi 10 | 1 | 2020-01-01 | 2000 |
6 | 洗碗机 | 2 | 2020-02-01 | 2000 |
9 | 自行车 | 3 | 2020-01-01 | 1000 |
代码
with t1 as ( -- 类别 商品 排名 各类别商品总数 select category_id ,sku_id ,price ,row_number() over (partition by category_id order by price ) as rn ,count(1) over (partition by category_id rows between unbounded preceding and unbounded following ) as cnt from sku_info ) select category_id ,cast(avg(price) as decimal(10,2) ) as medprice from t1 -- 偶数取中间, 奇数取中间值 where if(cnt %2 = 0,rn in(cnt/2,cnt/2+1), rn = (cnt+1)/2) group by category_id
27、从订单详情表(order_detail)中找出销售额连续3天超过100的商品
结果如下:
sku_id |
1 |
10 |
11 |
12 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
需要用到的表:
订单明细表:order_detail
order_detail_id(订单明细id) | order_id(订单id) | sku_id(商品id) | create_date(下单日期) | price(商品单价) | sku_num(商品件数) |
1 | 1 | 1 | 2021-09-30 | 2000.00 | 2 |
2 | 1 | 3 | 2021-09-30 | 5000.00 | 5 |
22 | 10 | 4 | 2020-10-02 | 6000.00 | 1 |
23 | 10 | 5 | 2020-10-02 | 500.00 | 24 |
24 | 10 | 6 | 2020-10-02 | 2000.00 | 5 |
with t as ( -- 商品 日期 销售额 下一行日期 下下一行日期 select sku_id ,create_date ,sum(price*sku_num) as sale_all ,lead(create_date,1,'null') over(partition by sku_id order by create_date) as next_date ,lead(create_date,2,'null') over(partition by sku_id order by create_date) as next_next_date from order_detail group by sku_id ,create_date ) select distinct sku_id from (select * from t where sale_all >=100)a where datediff(next_date,create_date)=1 and datediff(next_next_date,next_date)=1
28、从用户登录明细表(user_login_detail)中首次登录算作当天新增,第二天也登录了算作一日留存
结果如下:
first_login | register | retention |
2021-09-21 | 1 | 0.00 |
2021-09-22 | 1 | 0.00 |
2021-09-23 | 1 | 0.00 |
2021-09-24 | 1 | 0.00 |
2021-09-25 | 1 | 0.00 |
2021-09-26 | 1 | 0.00 |
2021-09-27 | 1 | 0.00 |
2021-10-04 | 2 | 0.50 |
2021-10-06 | 1 | 0.00 |
需要用到的表:
用户登录明细表:user_login_detail
user_id(用户id) | ip_address(ip地址) | login_ts(登录时间) | logout_ts(登出时间) |
101 | 180.149.130.161 | 2021-09-21 08:00:00 | 2021-09-27 08:30:00 |
102 | 120.245.11.2 | 2021-09-22 09:00:00 | 2021-09-27 09:30:00 |
103 | 27.184.97.3 | 2021-09-23 10:00:00 | 2021-09-27 10:30:00 |
代码
-- 用户 登录时间 注册日期 注册时间第二天 with t1 as ( select user_id ,substr(login_ts,1,10) as login_ts ,min(login_ts) over(partition by user_id) as date1 ,lead(login_ts,1,'null') over(partition by user_id order by login_ts) as date2 from (select distinct user_id,substr(login_ts,1,10) as login_ts from user_login_detail)a ) ,t2 as ( -- 用户 登录时间 注册日期 注册时间第二天 是否新增 是否留存 select * ,if(login_ts=date1,1,0) as is_new ,if(datediff(date2,date1)=1,1,0) as is_retention from t1 ) -- 注册日期 注册期内新增数 注册期内留存数的用户/注册期内注册的所有用户 select date1 as first_login ,sum(is_new) as register ,cast (sum(is_retention)/count(distinct user_id) as decimal(10,2))as retention from t2 group by date1