select '店铺汇总' as product_id, concat(round((1-sum(cnt*in_price)/sum(cnt*price))*100,1),'%') profit_rate FROM tb_order_detail t3 LEFT JOIN tb_product_info t1 ON t3.product_id=t1.product_id LEFT JOIN tb_order_overall t2 ON t3.order_id=t2.order_id WHERE DATE_FORMAT(event_time,'%Y-%m')>='2021-10' AND ...

一开始完全找不到算同一时刻最大在看人数的思路，看到别的大佬的答案才得到了启发。 思路：先算出每组artical_id的所有时刻的在看人数，再用group by语句找出每组artical_id最大的在看人数。那么怎么求每个时刻的在看人数呢？可以这么理解：in_time的时刻在看人数+1，out_time时刻在看人数-1，每一组artical_id内进行在看人数变化量按时间顺序的累加就可以算出每个时刻在看的人数，最后找出每组artical_id内最大的在看人数就是题目要求的同一时刻最大在看人数了。 理清思路后再来想SQL语句： 第一个难点在于UNION ALL的使用，这里是我没有想到的。in_ti...

又是离成功只差一点，这次一开始只写出了FROM后子查询的内容，发现输出与答案不符合，想了好久发现WHERE子句要写在子查询外才能输出正确结果。 除此之外还要注意滑动窗口函数rows between的用法和日期范围筛选是between and而不是between()。 这个题很绕还需要多加思考复习。 SELECT tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d FROM(SELECT tag,dt, sum(like_cnt)over(partition by tag rows between 6 preceding and current row) sum_...

呜呜犯了三个错误，一个是DATE_FORMAT()之前用过但是这次又忘了，第一次还百分号写反了位置。第二个是计算总粉丝数的时候，想到了用窗口函数和CASE WHEN结合，但只写了一个sum()，但其实最里层的sum()计算的是创作者每个月的粉丝涨跌变化总量，要把涨跌值累加起来才是截止本月的总粉丝数，所以有两个sum()，这个逻辑要理清楚。第三个是GROUP BY后只接了author字段，别忘了要算每个创作者每月的情况，所以还要加上month字段。 SELECT author,DATE_FORMAT(end_time,'%Y-%m') month, round(SUM(CASE WHEN if_...

这道题一开始我只写出了FROM后子查询的内容，因为忽略了几个细节： 保留两位小数的百分数要注意round(A/B*100,2），用这个数筛选出大于60的，最后再查询，用CONCAT(…,'%')连接这个数和百分号 SELECT tag,concat(avg_play_progress,'%') avg_play_progress FROM (SELECT tag, round(AVG((CASE WHEN TIMESTAMPDIFF(second,start_time,end_time)>=duration THEN 1 ELSE TIMESTAMPDIFF(second,start_t...

题解： 查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序。 第一个难点在于最高的用户可能有多个，则这里不能用LIMIT 1来查找最值，想到用WHERE或者HAVING来筛选出grade_sum等于最大值的所有用户。一钟是在WHERE后用子查询查找出最大值令grade_sum等于它；另一种是利用窗口函数给grade_sum一个排名，直接利用WHERE rk=1，这里我们用窗口函数。 第二个难点在于根据type分类加减积分，用sum()和if()嵌套使用解决。 第三个难点在于语句的结构。这里使用子查询嵌套，最里层子查询先查询出user_id和grade_...

SELECT a.user_id, MAX(CASE WHEN r_d=1 THEN a.date ELSE 0 END) first_buy_date, MAX(CASE WHEN r_d=2 THEN a.date ELSE 0 END) second_buy_date, a.cnt FROM (SELECT user_id,date, dense_rank()over(partition by user_id order by date) r_d, COUNT(*)over(partition by user_id) cnt FROM order_info WHERE date>'...

深入理解窗口函数 思路分析：要求每个用户截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的那一天的数据不需要输出。有登录但是没刷题的不需要输出所以我们只用看passing_number表和user表。 求截止到某天累计通过题，想到用sum()+窗口函数，order by date（根据日期进行累计），那么partition by 后面应该跟什么字段呢？这里是按每个用户来看，所以按照用户分窗，然后按题目要求排序，完整代码如下： SELECT u.name u_n,p.date,sum(number)over(partition by u.na...

思路分析 新用户次日留存率p=（第一次登陆本日和次日都登陆的用户数）/（本日是第一次登陆的用户数） 想到分别对分子分母用SUM+CASE WHEN，如果本日没有新登录的用户，则分母为NULL，利用IFNULL函数将其p值转化为0。 难点在于CASE WHEN后面的条件如何表示呢？ 在login表按日期分组后，分母本日期第一次登陆即l表里的用户和日期满足是该用户是第一天登录的条件即 (l.user_id,l.date) IN (SELECT user_id,MIN(date) date FROM login GROUP BY user_id)  注意这里一开始我是创建了个表(SELECT use...

SELECT l.date, COUNT(CASE WHEN l.user_id=a.user_id AND l.date=a.date THEN l.user_id ELSE null END) new FROM login l, ( SELECT user_id,MIN(date) date FROM login GROUP BY user_id ) a GROUP BY l.date ORDER BY l.date;  我的思路： 首先找出新用户及其注册日期作为表a，然后在login表中按日期分组去重，如果user_id、date和a表匹配则计数（即每组日期中满足条件是新用户则计数） 对...

SELECT ROUND(COUNT(DISTINCT user_id)/(SELECT COUNT(DISTINCT user_id) FROM login),3) FROM login WHERE (user_id,date) IN ( SELECT user_id,DATE_ADD(MIN(date),INTERVAL 1 DAY) FROM login GROUP BY user_id);  一开始的思路： 公式：连续两天登陆的用户/总用户=留存率 首先子查询找出连续两天登陆的用户数，利用两表联查，条件是两表user_id相等，登陆天数连续。 (SELECT COUNT(DISTINC...

SELECT emp_no, salary, sum(salary)over(rows between unbounded preceding and current row) running_total FROM salaries WHERE to_date='9999-01-01';  利用窗口函数的解题思路