-- 3、统计每个用户的date_2出现的次数 select user_id ,count(1) as days_count from ( -- 2、计算排序后的user_id、销售日期、以及减去排序之后的日期date_2 select user_id ,sales_date ,date_sub(sales_date,interval rnk day) as date_2 from ( -- 1、先对user_id对应的销售日期使用窗口函数进行升序排序 select user_id ,sales_date ,rank() over(partition by user_id order by s...

-- 动销率=有销售的sku数量/在售sku数量 -- 售罄率=GMV/备货值，备货值=吊牌价*库存数 -- 注意事项：销售表中货号可能是重复的，需要进行去重处理 -- 计算销售表中每个款号对应的指标所需的计算信息 select style_id ,round(100*sum(num)/(sum(inventory)-sum(num)),2) as 'pin_rate(%)' ,round(100*sum(gmv)/sum(tag_price*inventory),2) as 'sell_through_rate(%)' from product_tb join ( -- 先对销售表中每个货号...

select sum(sales_price) as sales_total ,round(sum(sales_price)/count(distinct user_id),2) as per_trans from sales_tb 注意审题，表中给出就已经是结算金额，不需要再进行计算

-- 国庆头3天下单日期记录为dt，近7天商品销售数量记录为cnt，总上架商品数量记录为total_cnt select dt ,round(cnt/total_cnt,3) as sale_rate ,round(1-cnt/total_cnt,3) as unsale_rate from ( select -- 取国庆前三天的日期 distinct date(event_time) as dt -- 计算901店铺前三天对应的7天销售商品数量 ,(select count(distinct if(shop_id = 901,product_id,null)) from tb_order_o...

-- 计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数） -- 1、获取2021年10月新用户的首单信息：使用rank窗口函数，对用户uid分组，对下单日期进行升序排序，获取2021年10月排序为1的信息 select round(avg(total_amount),1) as avg_amount ,round(avg(amount-total_amount),1) as avg_cost from ( select a.order_id, uid, total_amount, amount from ( select *, rank() over...

-- 商品毛利率=（1-进价/平均单价售价）*100%，其中平均单件售价= sum(price*cnt)/sum(cnt) -- 店铺毛利率=（1-总进价成本/总销售收入）*100% -- 先获取901店铺2021年10月店铺内所有商品对应进价、销售单价、销售数量 with a as ( select t3.product_id ,in_price ,price ,cnt from tb_order_detail t3 left join tb_product_info t1 on t3.product_id = t1.product_id left join tb_order_overall...

select date_format(event_time,'%Y-%m') as 'month',sum(total_amount) as GMV from tb_order_overall where year(event_time) = 2021 and status != 2 group by month having GMV > 100000 order by GMV

select dt ,count(distinct uid) as dau , round(sum(if(rank_dt = 1,1,0)) / count(distinct uid),2) as uv_new_ratio from ( select uid ,dt ,rank() over(partition by uid order by dt) as rank_dt from ( -- 获取用户信息总表 select uid , date(in_time) as dt from tb_user_log union select uid , date(out_time) as dt fro...

-- 求用户等级以及对应的比例 select user_grade ,round(count(uid) / max(user_cnt),2) as ratio from ( -- 使用case when对用户进行分层 select uid ,user_cnt , case when last_dt_diff >= 30 then "流失用户" when last_dt_diff >= 7 then "沉睡用户" when first_dt_diff < 7 then "新晋用户" else "忠实用户&qu...

-- 先根据每个用户最初活跃时间，来统计每天的新用户信息 -- 再进行日期+1，获取对应的用户信息，与新用户信息进行连接 select t1.dt ,round(count(t2.uid)/count(t1.uid),2) as uv_rate from -- 新用户信息表 (select uid,min(date(in_time)) dt from tb_user_log group by uid ) t1 left join ( -- 用户信息全量表 select uid,date(in_time) as dt from tb_user_log union select uid,date(...

-- “同一时刻有进入有离开时，先记录增加后记录减少” ，进入+1，退出-1，可以使用sum窗口函数，对文章进行分组，然后先日期升序，再对人数进行降序排列 select artical_id,max(sum_cnt) as max_uv from ( select artical_id,sum(diff) over(partition by artical_id order by dt ,diff desc) as sum_cnt from ( select uid ,artical_id,in_time dt ,1 diff from tb_user_log where artical_id...

-- 1、日期为2021年11月；2、人均浏览时长=总浏览时长/人数；3、保留一位小数；4、时长由短到长排序 select date(in_time) as dt ,round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec from tb_user_log where artical_id <> 0 and substr(in_time,1,7) = '2021-11' group by 1 order by 2 知识点：1、timestampdiff ...

select dt ,count(uid) as dau ,round(sum(if(rnk = "1",1,0))/count(uid),2) as uv_new_ratio from ( -- 使用rank窗口函数对每个用户对应的日期进行排序，第一次出现的为新用户 select uid, dt ,rank() over(partition by uid order by dt) as rnk from ( -- 跨天均计入活跃，需要将用户的进入时间和离开时间进行合并处理 select uid ,date(in_time) as dt from tb_user_log u...

-- 用max函数取最大日期作为当天日期来衡量7天的标准,再获取每个用户的最小活跃日期和最大活跃日期。 -- 忠实用户：用户的最小活跃日期与当天日期的差值大于6，且最大活跃日期与当天日期差值小于等于6 -- 新晋用户：用户的最小活跃日期与当天日期的差值小于等于6； -- 沉睡用户：用户的最大活跃日期和当天日期的差值大于6； -- 流失用户：用户的最大活跃日期和当天日期的差值大于29； -- 使用case when函数对以上用户进行统计 select user_grade,round(count(uid)/(select count(distinct uid) from tb_user_log)...