【SQL查询】必会的常用函数：条件函数、日期函数、文本函数、窗口函数
2024-10-20 20:40:43
〇、概述
1、内容
选择（双分支、多分支）
一、条件函数
1、计算25岁以上和以下的用户数量
CASE THEN END多分支选择
SELECT
(CASE
WHEN age>=25 THEN '25岁及以上'
ELSE '25岁以下'
END) AS age_cut,
COUNT(*) AS number
FROM user_profile
GROUP BY age_cut;
IF ELSE双分支选择
SELECT
IF(age>=25,'25岁及以上','25岁以下') AS age_cut,
COUNT(*) AS number
FROM user_profile
GROUP BY age_cut;
集合运算联合UNION
SELECT
'25岁以下' AS age_cut,
COUNT(*) AS number
FROM user_profile
WHERE
age<25
or
age IS NULL
UNION
SELECT
'25岁及以上' AS age_cut,
COUNT(*) AS number
FROM user_profile
WHERE age>=25;
2、查看不同年龄段的用户明细
CASE WHEN THEN END，中间不加逗号
SELECT
device_id,
gender,
(CASE
WHEN age IS NULL THEN '其他'
WHEN age<20 THEN '20岁以下'
WHEN age BETWEEN 20 and 24 THEN '20-24岁'
ELSE '25岁及以上'
END) AS age_cut
FROM user_profile;
二、日期函数
1、计算用户8月每天的练题数量【多个字段用逗号分隔】
方法1：日期函数（日、月、天）
SELECT
DAY(date) AS day,
COUNT(*) AS question_cnt
FROM question_practice_detail
WHERE
YEAR(date)=2021
and
MONTH(date)=08
GROUP BY day;
方法2：like通配符
SELECT
DAY(date) AS day,
COUNT(*) AS question_cnt
FROM question_practice_detail
WHERE date like '2021-08%'
GROUP BY day;
方法3：substring
SELECT
DAY(date) AS day,
COUNT(*) AS question_cnt
FROM question_practice_detail
WHERE substring(date,1,7)='2021-08'
GROUP BY day;
2、计算用户的平均次日留存率
SELECT
COUNT(b.device_id)/COUNT(a.device_id) AS avg_ret
FROM
(SELECT
DISTINCT device_id,
date
FROM question_practice_detail) AS a
LEFT JOIN
(SELECT
DISTINCT device_id,
date
FROM question_practice_detail) AS b
ON
a.device_id=b.device_id
AND
DATE_ADD(a.date,interval 1 day)=b.date;
三、文本函数
1、统计每种性别的人数
SELECT
SUBSTRING(profile,15) as gender,
COUNT(*)
FROM user_submit
GROUP BY gender;
2、截取出年龄【SUBSTRING的第三个参数表示往后取几个字符】
SELECT
SUBSTRING(profile,12,2) AS age,
COUNT(*) AS number
FROM user_submit
GROUP BY age;
3、提取博客URL中的用户名
SELECT
device_id,
SUBSTRING(blog_url,11) AS user_name
FROM user_submit;
四、窗口函数
包括聚合函数、排名分析
1、找出每个学校GPA最低的同学
SELECT
a.device_id,
a.university,
a.gpa
FROM user_profile a
JOIN
(SELECT
university,
MIN(gpa) AS gpa
FROM user_profile
GROUP BY university
) b
ON
a.university=b.university
AND
a.gpa=b.gpa
ORDER BY a.university ASC;
最新文章
- linux学习日记之鸟哥
- BZOJ1004: [HNOI2008]Cards
- 移动前端头部标签(HTML5 head meta)
- 第四章：javascript： 栈
- SVN 首次用TortoiseSVN Checkout 提示Unexpected HTTP status 405
- Objective-C MRC多个对象相互引用的内存管理
- webservice未能加载文件或程序集“**.DLL”或它的某一个依赖项。
- C# 取小数点
- UITabBarController 标签栏控制器
- iconv any encoding to UTF-8
- boost的并发库
- python标准库-日志logging
- Win下JDK的安装和简单使用教程
- MonggoDB(二)
- SonarLint 代码质量管理
- 初识Vulkan【转】
- FFT/NTT总结+洛谷P3803 【模板】多项式乘法（FFT）（FFT/NTT）
- [PHP] 02 - Namespace & Class
- P1005 矩阵取数游戏
- Natural Language Processing, 2017, Mar.29, Weekly Report