风飞飞飞

题解

/ 注册

全部文章 / 题解（共57篇）

题解 | #统计2021年10月每个退货率不大于0.5的商品各项指标#

select product_id,round(sum(if_click)/count(1),3) as ctr, round(if(sum(if_click)!=0,sum(if_cart)/sum(if_click),0),3) as cart_rate, round(if(sum(if_car...

Mysql

2022-04-01

0 322

题解 | #计算商城中2021年每月的GMV#

select date_format(event_time,'%Y-%m') as month,round(sum(total_amount)) as GMV from tb_order_overall where (status=1 or status=0) and year(event_time...

Mysql

2022-04-01

0 283

题解 | #每天的日活数及新用户占比#

select t1.dt,count(distinct t1.uid) as dau, round(count(t2.uid)/count(distinct t1.uid),2) as uv_new_ratio from (select uid,date(in_time) as dt ...

Mysql

2022-03-30

0 280

题解 | #统计活跃间隔对用户分级结果#

select user_grade, round(count(user_grade)/(select count(distinct uid) from tb_user_log),2) as ratio from (select uid, case when min(day_...

Mysql

2022-03-29

0 378

题解 | #2021年11月每天新用户的次日留存率#

select min_time,round(count(t2.uid)/count(t1.uid),2) as uv_left_rate from (select uid,min(date(in_time)) as min_time from tb_user_log grou...

Mysql

2022-03-29

0 279

题解 | #每篇文章同一时刻最大在看人数#

select artical_id,max(num_cnt) as max_uv from (select artical_id, sum(num) over(partition by artical_id order by read_time asc,num desc) as nu...

Mysql

2022-03-28

0 331

题解 | #近一个月发布的视频中热度最高的top3视频#

select video_id, round((100*watch_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(no_watch_cnt+1)) as hot_index from (select log.video_id, avg(i...

Mysql

2022-03-27

0 376

题解 | #国庆期间每类视频点赞量和转发量#

select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d from (select tag,dt, sum(like_cnt) over(partition by tag order by dt rows 6 preceding) as sum_like_cn...

Mysql

2022-03-26

0 325

题解 | SQL50#纠错4#

分析使用union组合查询时，只能使用一条order by字句，他必须位于最后一条select语句之后，因为对于结果集不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。代码 SELECT cust_name, cust_contact, cust_email FROM Custo...

Mysql

2022-03-05

73 1662

题解 | SQL49#组合 Products 表中的产品名称和 Customers 表中的顾客名称#

分析关键词：union 用法： union--连接表，对行操作。 union--将两个表做行拼接，同时自动删除重复的行。 union all---将两个表做行拼接，保留重复的行代码 select prod_name from Products union select cust_name ...

Mysql

2022-03-05

37 1551