基础技术:
1.多分支条件检索:case when ... between ... and ....
2. 日期模糊检索:
-- 选择日期的日份和相应问题数量
SELECT
DAY(date) AS day, -- 提取日期中的日份,将其命名为 'day'
COUNT(question_id) AS question_cnt -- 统计每个日期的问题数量,将其命名为 'question_cnt'
FROM
question_practice_detail -- 选择名为 'question_practice_detail' 的表
WHERE
DATE_FORMAT(date, "%Y%m") = "202108" -- 对日期进行格式化,提取年月部分,然后筛选出年月为 '202108' 的记录
GROUP BY
DAY(date); -- 根据日期的日份进行分组
--如果是DATE_FORMAT(date, "%Y-%m") = "2021-08" 日期模糊检索
3. 题目:求第二天继续来刷题的用户平均次日留存率。表里的数据可以看作是全部第一天来刷题了的,那么我们需要构造出第二天来了的字段,因此可以考虑用left join把第二天来了的拼起来,限定第二天来了的可以用:
date_add(date1, interval 1 day)=date2 --筛选,并用device_id限定是同一个用户。
筛选,并用device_id限定是同一个用户(用户追踪数据)。
想法:(1)从表中去重取出刷题的用户,date。(2)去重取出第二天刷题的用户,date。
(3)将两个表基于用户进行匹配,并假设 q2.date = DATE_ADD(q1.date, interval
1
day)
从基于q1.date加一天之后得到q2.date并因为用户已经匹配,那么相邻两日该用户都刷题的话,那么就可以得到该平台的用户留存率。
4.
统计每个性别的用户分别有多少参赛者
select
substring_index(profile, ',', -1) as gender,
count(device_id) as number
from user_submit
group by gender
注:substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔:
(1).当n大于0时取第n个分隔符(n从1开始)左边的全部内容;
(2).当n小于0时取倒数第n个分隔符(n从-1开始)右边的全部内容;