SQL数据分析常用函数

大家好，欢迎来到IT知识分享网。

SQL 中有许多常用的函数，可以用于处理和操作数据。以下是一些常见的SQL 函数：

1. 字符串函数：

• CONCAT(str1, str2, …): 用于把多个文本字符串合并成一个长字符串(参数中有null时返回null)。

select concat('一起','学', 'SQL'); -- 输出结果:'一起学SQL' select concat('一起','学',null,'SQL'); -- 输出结果:'' 

• UPPER(str) 将字符串转换为大写。

select upper('sql'); -- 输出结果:'SQL' select upper('sQl'); -- 输出结果:'SQL' 

• LOWER(str): 将字符串转换为小写。

select lower('SQL'); -- 输出结果:'sql' select lower('sQl'); -- 输出结果:'sql' 

• TRIM(str)： 去除字符串两端空格。

select trim(' 一起学SQL '); -- 输出结果:'一起学SQL' 

• LTRIM(str): 去除字符串左端的空格。

select ltrim(' 一起学SQL '); -- 输出结果:'一起学SQL ' 

• RTRIM(str):: 去除字符串右端的空格。

select rtrim(' 一起学SQL '); -- 输出结果:' 一起学SQL' 

• LEFT(str,length)： 返回字符串的左侧指定长度的部分。

select left('一起学SQL',3); -- 输出结果:'一起学' 

• RIGHT(str,length): 返回字符串的右侧指定长度的部分。

select right('一起学SQL',3); -- 输出结果:'SQL' 

• MID(str, start, length): 返回字符串的指定起始位置和长度的部分。

select mid('一起学SQL',3,3); -- 输出结果:'学SQ' 

• INSTR(str,substr)： 返回子字符串substr在文本字符串str中第一次出现的位置(文本字符串中不包含该子字符串时返回0)

select instr('EABCDDBCA','A'); -- 输出结果:'2' 

• LENGTH(str): 返回字符串的长度。

select length('sql'); -- 输出结果:'3' 

• SUBSTRING(str, start, length): 返回字符串的子字符串。
  str 是要操作的字符串。
  start 是子字符串的起始位置，索引从 1 开始。
  length 是要提取的子字符串的长度。
  注： SUBSTRING 函数在提取子字符串时，索引是基于字符位置的，索引从 1 开始而不是从 0 开始。如果提供了负数的起始位置，则表示从字符串末尾开始计数。
  
  
  
  

SELECT SUBSTRING('一起学SQL', 4, 3); -- 输出结果:'SQL' SELECT SUBSTRING('一起学SQL', -3); -- 输出结果:'SQL' 

• REPEAT(str, num): 返回重复多次的字符串。

SELECT REPEAT('SQL', 3); -- 返回结果： 'SQLSQLSQL' 

• REVERSE(str): 反转字符串。

SELECT reverse('SQL'); -- 返回结果： 'LQS' 

• REPLACE(str, old_str, new_str): 替换字符串中的子字符串。

SELECT replace('一起学SQL','SQL','sql'); -- 返回结果： '一起学sql' 

2. 数学函数：

• ABS(num): 返回一个数的绝对值。

select abs(-15); -- 返回结果：'15' 

• ROUND(num, d): 返回保留d位小数(d的默认值为0)的四舍五入值

select round(3.,3); -- 返回结果：'3.142' 

• CEILING(num) ： 向上取整，返回大于或等于给定数字 x 的最小整数。

SELECT ceiling(5.3); -- 返回结果：'6' 

• FLOOR(num)😗*向下取整，返回小于或等于给定数字 x 的最大整数。

SELECT floor(5.3); -- 返回结果：'5' 

• RAND(): 返回在范围0到1.0内的随机浮点值

select rand(); -- 返回结果：'0.27904' 

3. 日期时间函数：

3.1提取

• NOW(): 返回当前日期和时间。
  返回值所处上下文是字符串：以 ‘yyyy-mm-dd hh:mm:ss’ 格式返回当前日期时间
  返回值所处上下文是数字：以 ‘yyyymmddhhmmss’ 格式返回当前日期时间
  
  

select now(); -- 返回结果：'2024-05-20 15:21:08' select now()+0; -- 返回结果：'119' 

• CURDATE(): 返回当前日期。

select curdate(); -- 返回结果：'2024-05-20' 

• CURTIME(): 返回当前时间。

select curtime(); -- 返回结果：'16:10:49' 

• DATE(): 提取日期部分。
  SELECT DATE(‘2024-05-20 12:34:56’);
  

select curtime(); -- 返回结果：'16:10:49' 

• TIME(): 提取时间部分。

select time('2024-05-20 12:34:56'); -- 返回结果：'12:34:56' 

• WEEK(date,n): 提取指定日期是一年中的第几周。
  ①默认(n为0或空值)：
  周从周日开始，周六结束。
  1月1日所在的周被认为是第1周。
  如果1月1日是周四或更晚，该周被认为是一年中的第1周；否则，它被认为是一年中的最后一周。
  ②n=1：
  周从周一开始，周日结束。
  1月1日所在的周被认为是第1周。
  如果1月4日是周一或更早，该周被认为是一年中的第1周；否则，它被认为是一年中的最后一周。
  ③n=2：
  周从周日开始，周六结束。
  1月1日所在的周被认为是第1周。
  如果1月4日是周一或更早，该周被认为是一年中的第1周；否则，它被认为是一年中的最后一周。
  ④n=3：
  周从周一开始，周日结束。
  1月1日所在的周被认为是第1周。
  如果1月1日是周四或更晚，该周被认为是一年中的第1周；否则，它被认为是一年中的最后一周。
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

select week('2024-05-20 12:34:56'); -- 返回结果：'20' select week('2024-05-20 12:34:56',1); -- 返回结果：'21' 

• YEAR(): 提取年份。

select year('2024-05-20 12:34:56'); -- 返回结果：'2024' 

• QUARTER(): 提取季度。

select quarter('2024-05-20 12:34:56'); -- 返回结果：'2' 

• MONTH(): 提取月份。

select month('2024-05-20 12:34:56'); -- 返回结果：'5' 

• DAY(): 提取日期中的天数。

select day('2024-05-20 12:34:56'); -- 返回结果：'20' 

• HOUR(): 提取小时部分。

select hour('2024-05-20 12:34:56'); -- 返回结果：'12' 

• MINUTE(): 提取分钟部分。

select minute('2024-05-20 12:34:56'); -- 返回结果：'34' 

• SECOND(): 提取秒数部分。

select second('2024-05-20 12:34:56'); -- 返回结果：'56' 

3.2加减运算

DATE_ADD(date,interval expr type)
ADDDATE(date,interval expr type)
DATE_SUB(date,interval expr type)
SUBDATE(date,interval expr type)

• date是一个datetime或date值
• expr对date进行加减法的一个表达式字符串
• type指明表达式expr应该如何展示
  
  
• DATE_ADD(date, INTERVAL expr type) 、 ADDDATE(date, INTERVAL expr type)： 都用于在日期上执行加法操作。可以根据需要选择使用其中之一。

select date_add('2024-05-20 12:34:56',INTERVAL 1 day); -- 返回结果：'2024-05-21 12:34:56' select adddate('2024-05-20 12:34:56',INTERVAL 1 day); -- 返回结果：'2024-05-21 12:34:56' 

• DATE_SUB(date, INTERVAL expr type) 和 SUBDATE(date, INTERVAL expr type)： 都用于在日期上执行减法操作。可以根据需要选择使用其中之一。

select date_sub('2024-05-20 12:34:56',INTERVAL 1 day); -- 返回结果：'2024-05-19 12:34:56' select subdate('2024-05-20 12:34:56',INTERVAL 1 day); -- 返回结果：'2024-05-19 12:34:56' 

• DATEDIFF(date1, date2): 返回两个日期之间的天数差。

select datediff('2024-05-20', '2024-05-10'); -- 返回结果：'10' 

3.3格式化日期

• DATE_FORMAT(date, format): 根据format字符串格式化date值。
  在format字符串中可用标志符
  年份:
  %Y: 4 位的年份 (例如: 2024)
  %y: 2 位的年份 (例如: 24)
  月份:
  %m: 2 位的月份 (01-12)
  %c: 月份 (1-12)
  %M: 月份名称 (January – December)
  %b: 缩写的月份名称 (Jan – Dec)
  日期:
  %d: 2 位的日期 (01-31)
  %e: 日期 (1-31)
  时间:
  %H: 24 小时制的小时 (00-23)
  %h: 12 小时制的小时 (01-12)
  %I: 12 小时制的小时 (01-12)
  %k: 24 小时制的小时 (0-23)
  %l: 12 小时制的小时 (1-12)
  %i: 分钟 (00-59)
  %s: 秒 (00-59)
  %f: 微秒 (000000-)
  %p: AM 或 PM
  其他:
  %w: 一周中的第几天 (0=周日, 1=周一, …, 6=周六)
  %a: 缩写的星期名称 (Sun – Sat)
  %W: 完整的星期名称 (Sunday – Saturday)
  %j: 一年中的第几天 (001-366)
  %T: 24 小时制的时间 (hh:mm:ss)
  %r: 12 小时制的时间 (hh:mm:ss AM/PM)
  %D: 带有英文后缀的日期 (1st, 2nd, 3rd, …)
  这些格式化符号可以组合使用, 以满足不同的日期和时间显示需求。
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

select date_format('2024-05-20 12:34:56','%Y%M'); -- 返回结果：'2024May' select date_format('2024-05-20 12:34:56','%Y%m%d'); -- 返回结果：'' 

3.4时间戳

• UNIX_TIMESTAMP(): 返回一个unix时间戳(从’1970-01-01 00:00:00’开始的秒数,date默认值为当前时间)

select unix_timestamp(); -- 返回结果：'' 

• FROM_UNIXTIME(unix_timestamp): 以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回时间戳的值。

select from_unixtime(); -- 返回结果：'2024-05-20 17:09:21' 

4.转换函数

CAST(expression AS data_type): 用于将某种数据类型的表达式显式转换为另一种数据类型。

• expression：任何有效的SQServer表达式。
• AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
• data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。
  可以转换的类型：
  
• 二进制，同带binary前缀的效果 : BINARY
• 字符型，可带参数 : CHAR()
• 日期 : DATE
• 时间: TIME
• 日期时间型 : DATETIME
• 浮点数 : DECIMAL
• 整数 : SIGNED
• 无符号整数 : UNSIGNED

select cast('10.0' AS decimal); -- 返回结果：'10' 

5.逻辑函数：

• IF(condition, value_if_true, value_if_false): 根据条件返回不同的值。

select ename,sal,if(sal>=3000,'高',if(sal>=1500,'中','低')) 工资级别 from emp; +--------+------+--------------+ | ename | sal | 工资级别 | +--------+------+--------------+ | smith | 800 | 低 | | allen | 1600 | 中 | | ward | 1250 | 低 | | jones | 2975 | 中 | | martin | 1250 | 低 | | blake | 2850 | 中 | | clark | 2450 | 中 | +--------+------+--------------+ 

• IFNULL(val, default_val): 如果值为 NULL，则返回默认值。如果不为 NULL 则返回第一个参数的值。

 select ifnull(销量,0) from emp; +----------------+ | ifnull(销量,0) | +----------------+ | 0 | | 123| | 1000 | | 0 | | 500 | +----------------+ 

• CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4…ELSE expr] END: 如果expr1的值为true，则返回expr2的值，如果expr3的值为false，则返回expr4的值…

 select ename,sal,case when sal>=3000 then '高' when sal>=1500 then '中' else '低' end 工资级别 from emp; +--------+------+--------------+ | ename | sal | 工资级别 | +--------+------+--------------+ | smith | 800 | 低 | | allen | 1600 | 中 | | ward | 1250 | 低 | | jones | 2975 | 中 | | martin | 1250 | 低 | | blake | 2850 | 中 | | clark | 2450 | 中 | | scott | 3000 | 高 | | king | 5000 | 高 | | turner | 1500 | 中 | | adams | 1100 | 低 | | james | 950 | 低 | | ford | 3000 | 高 | | miller | 1300 | 低 | +--------+------+--------------+ 

• COALESCE(val1, val2, …): 返回参数列表中第一个非 NULL 值。

select coalesce(NULL,'a',NULL,'c'); -- 返回结果：'a' 

6.聚合函数：

• COUNT(): 统计行数或非 NULL 值的数量。
• SUM() : 求和。
• AVG(): 求平均值。
• MAX() : 求最大值。
• MIN() : 求最小值。

SELECT COUNT(*) FROM table_name; SELECT COUNT(column_name) FROM table_name WHERE condition; SELECT SUM(column_name),AVG(column_name),MAX(column_name),MIN(column_name) FROM table_name; 

• GROUP_CANCAT([distinct] str [order by str asc/desc] [separator]): 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

-- 查询每个部门的员工姓名 select deptno,group_concat(name) from emp group by deptno; +--------+--------------------------------------+ | deptno | name | +--------+--------------------------------------+ | 10 | clark,king,miller | | 20 | smith,jones,scott,adams,ford | | 30 | allen,ward,martin,blake,turner,james | +--------+--------------------------------------+