LittleLily

分类

题解(25)

/ 注册

全部文章（共8篇）

题解 | #某店铺的各商品毛利率及店铺整体毛利率#

SELECT '店铺汇总' AS product_id , CONCAT(ROUND(100*(1-SUM(in_price*cnt)/SUM(price*cnt)),1),'%') AS profit_rate FROM tb_order_detail detail JOIN t...

Mysql

2022-05-07

0 306

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

SELECT product_id, ROUND(SUM(if_click)/COUNT(product_id),3) AS ctr, ROUND(IF(SUM(if_click)=0,0,SUM(if_cart)/SUM(if_click)),3) AS cart_rat...

Mysql

2022-05-07

0 326

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

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 G...

Mysql

2022-05-07

0 297

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

SELECT * FROM( SELECT tag, DATE_FORMAT(end_time,'%Y-%m-%d') AS dt, SUM(SUM(if_like)) OVER(partition by tag order by DATE_FORMAT(end_time,...

Mysql

2022-05-06

0 319

题解 | #平均播放进度大于60%的视频类别#

SELECT author, SUBSTR(start_time,1,7) AS month, ROUND(SUM(CASE WHEN if_follow=2 THEN -1 WHEN if_follow=0 THEN 0 WHEN if_follow...

Mysql

2022-05-06

0 323

题解 | #平均播放进度大于60%的视频类别#

SELECT tag, SUM(if_retweet) AS retweet_cut, ROUND(SUM(if_retweet)/COUNT(video_id),3) AS retweet_rate FROM tb_user_video_log log JOIN tb_vi...

Mysql 数据库

2022-05-06

0 291

题解 | #平均播放进度大于60%的视频类别#

SELECT tag, CONCAT(ROUND(AVG(IF( TIME_TO_SEC(end_time)-TIME_TO_SEC(start_time)>=info.duration, 100, (TIME_TO_SEC(end_time)-TIME...

Mysql 数据库

2022-05-06

0 353

题解 | #各个视频的平均完播率#

SELECT tb.video_id, ROUND(SUM(IF( TIME_TO_SEC(end_time)-TIME_TO_SEC(start_time) >= info.duration, 1, 0 ))/COUNT(tb.video_i...

Mysql 数据库

2022-05-06

0 355