/* 思路： 要求输出：视频类型、转发量、转发率。保留三位小数 时间窗口：最近一个月内。 现有数据：用户互动表、短视频信息表。 步骤： ①清洗出时间窗口内的数据。 -先用DATE_SUB(MAX(start_time), INTERVAL 29 DAY) 计算出基准日期范围（我们最早要看哪一天的记录）。 -用CROSS JOIN 把基准日期连接到每一条记录后，再用WHERE过滤掉基准日期范围外的记录。 ②计算指标：转发量和转发量率。 -关联短视频信息表，因为需要用短视频信息表中的tag进行分组。 -使用聚合函数计算转发量和转发率。 */ WITH sj AS ( -- 提取基准日期 SELEC...

SELECT answer_date, ROUND(COUNT(*) / COUNT(DISTINCT author_id),2) AS per_num FROM answer_tb WHERE answer_date >= '2021-11-1' AND answer_date < '2021-12-1' GROUP BY answer_date ORDER BY answer_date;

SELECT c.course_id, c.course_name, COALESCE( ROUND(SUM(CASE WHEN b.if_sign = '1' THEN 1 ELSE 0 END) / SUM(CASE WHEN b.if_vw = '1' THEN 1 ELSE 0 END) * 100,2) ) AS "sign_rate(%)" FROM behavior_tb AS b INNER JOIN course_tb AS c ON (b.course_id = c.course_id) GROUP BY c.course_id,c.course_nam...

/* 思路： 输出：北京司机平均接单次数，平均兼职收入（显示3位小数），只显示接单>=3的司机的数据 需要获取条件： ①清洗得出每个司机的接单数和每个司机的总收入，关联客户表，筛选北京订单。 ②计算核心指标，不用处理接单但未履约的订单，因为题目只要求输出接单数，且未履约订单的收入为NULL，在使用聚合函数时会被自动忽略。 */ WITH sj AS ( SELECT d.driver_id,k.city, COUNT(*) AS dds, SUM(fare) AS sr FROM tb_get_car_order AS d INNER JOIN tb_get_car_record AS ...

WITH -- 使用CTE wb AS ( -- 先关联两张表，然后计算出每个视频的播放进度 SELECT u.uid,u.video_id,v.tag,v.duration ,CASE WHEN TIMESTAMPDIFF(SECOND,u.start_time,u.end_time) >= v.duration THEN 1 -- 把播放时间转换为秒，并且播放时间超过视频长度的播放率记作1 WHEN TIMESTAMPDIFF(SECOND,u.start_time,u.end_time) < v.duration THEN TIMESTAMPDIFF(SECOND,u.sta...

WITH -- 使用CTE分模块计算输出结果。（这道题的本质是连接表后，进行列转行的操作） zongbiao AS ( -- 将所有要用到的表连接，并输出所有要用的字段 SELECT dan.record_id -- 借阅单id ,dan.book_id -- 书籍id ,dan.branch_id -- 图书馆id ,shu.book_title -- 书名（其实可以不取，后续会说原因） ,dan.borrow_date -- 借阅日期 ,guan.region -- 图书馆所属区域 ,DATE_FORMAT(borrow_date,'%Y-%m') AS yue -- 转换借阅日期格式（年...

SELECT -- 最外层查询，获取清洗完成后的数据 cid,round(pv,3),rk FROM( -- 第二层查询，使用窗口函数对每个视频按照重复播放次数进行排序 SELECT cid,pv, ROW_NUMBER() OVER(ORDER BY pv DESC,release_date DESC) AS rk FROM ( SELECT -- 第三层查询，计算每个视频的总共重复播放次数 p1.cid,c1.release_date, SUM(p1.pv) AS pv FROM ( SELECT cid,uid, -- 第四层查询，计算每个人重复播放每个视频的次数 CASE WHEN C...

SELECT age_cut,COUNT(*) -- 展示字段 FROM ( SELECT CASE WHEN age >= 25 THEN '25岁及以上' WHEN age IS NULL THEN '25岁以下' WHEN age < 25 THEN '25岁以下' END AS age_cut /*这里因为是二元条件，直接使用IF或者 CASE WHEN...THEN...ELSE会更加简洁，但是用3个WHEN可读性更强，更易于维护*/ FROM user_profile) AS 整理后的表 -- 因为GROUP BY在SELECT之前运行，所以需要先对age_cut进行定...