琳波微步

分类

/ 注册

全部文章（共12篇）

最简单易懂的代码

最难的就两部分1、结束等待的时间判定。有上车就是start_time结束等待；没上车，司机接单后取消finish_time；没有司机接单，取消打车end_timeifnull(start_time,ifnull(finish_time,end_time) 2、单日同时等车人数，先记增加后减少：sum...

2022-12-21

0 271

最简单的代码方式

select round(avg(total_amount),1) as avg_amount, round(avg(sum_cost-total_amount),1) as avg_cost from tb_order_overall join(select order_...

2022-12-20

0 259

最简单的复购率求法

#复购率top3高 #复购率保留3位小数，近90天内购买它至少两次的人数 ÷ 购买它的总人数 #复购率倒序、商品ID升序排序 select product_id, round(avg(cnt>1),3) as repurchase_rate from ( select...

2022-12-20

0 323

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

select ifnull(t1.product_id,'店铺汇总') as product_id, concat(round(100*(1-sum(in_price*cnt)/sum(price*cnt)),1),'%') as profit_rate from tb_pro...

2022-12-20

0 226

题解 | #连续签到领金币#

select uid, date_format(dt,'%Y%m') as month, sum(case when rk2=3 then 3 when rk2=0 then 7 else 1 end) as coin # 用户每天签到可以领1金币，连续签到的第3、7天分别领...

2022-12-20

0 295

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

select dt, count(uid) as dau, round(avg(newd=dt),2) as uv_new_ratio from ( select uid,date(in_time) as dt from tb_user_log union ...

2022-12-20

0 264

不用子查询，最简洁易懂的代码

select case when datediff(today,newd)<7 then '新晋用户' when datediff(today,dt)<7 then '忠实用户' when datediff(today,dt)<30 then '沉睡用...

2022-12-20

0 202

子查询找出每门课程总报名人数简单易懂

子查询计算每门课程的总人数，(select count(distinct user_id) from behavior_tb as t2 where t2.course_id=t3.course_id and if_sign=1 group by t2.course_id )出勤人数：count(d...

2022-12-07

0 343

最容易理解的，不用子查询，直接join

视频完播率：avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0))点赞数：sum(if_like)评论数：count(comment_id)转发数：sum(if_retweet)因为最近播放日期以end_time-结束观...

2022-12-04

0 238

极简题解 | 自连接，逻辑清晰，代码简单

with a as (select #统计次数，窗口函数完成排名 exam_id ,year(submit_time) as start_yea...

Mysql

2022-07-14

0 250