常用函数：普通类型函数

1、函数的基本概念

• 函数的定义：用于对用户给定的输入数据进行处理，实现指定的功能，并输出对应结果的代码叫做函数

• 功能：实现对数据进行特定功能的加工处理转换

• 场景：数据清洗、数据分析

• 分类

  • 按照输入输出划分

    • 一对一 : 输入一行，输出一行，大多数函数

    • 多对一 : 输入多行，输出一行，聚合函数：count、sum、max、min、avg

    • 一对多 : 输入一个，输出多个，功能比较强大

  • 按照功能划分

    • 字符串函数：专门用于实现对字符串数据的处理，裁剪、拼接、分割、替换、去空格

    • 数值函数：专门用于实现对数值的处理，取整、随机、四舍五入、次方、取余

    • 日期函数：专门用于实现对时间的处理，转换、求值、取值、求差、加减日期

    • 特殊函数：专门用于实现一些复杂数据处理的函数，分析函数、位置偏移函数、窗口聚合函数

2、常用字符串函数

• length：长度

  • 功能：用于计算字符串的字节个数

  • 语法：length(字符串)

  • 示例：

  • -- 字节，一个中文用3个字节存储， 所有数字、汉字、英文都可以共用字符表示，统一单位
    
    select '123456' as str, length('123456'); -- 数值或者字母都是一个字符用一个字节存储
    
    select '我爱中国' as str, length('我爱中国');-- 汉字是一个字符用三个字节存储

• char_length：字符长度

  • 功能：用于计算字符串的字符个数

  • 语法：char_length(字符串)

  • 示例：

  • -- length：用于计算字符串的字节个数，char_length：用于计算字符串的字符个数
    select length('我爱你中国'), char_length('我爱你中国'); # 结果为 15 5

• lower：小写

  • 功能：用于将大写字母转换成小写字母

  • 语法：lower(字符串)

  • 示例：

  • select 'Hello world', lower('Hello world'), lower('HELLO WORLD');

• upper：大写

  • 功能：用于将小写字母转换成大写字母

  • 语法：upper(字符串)

  • 示例:

  • select 'Hello world', upper('Hello World'), upper('hello world');

• concat：连接

  • 功能：用于实现字符串的拼接

  • 语法：concat（字符串1，字符串2，……，字符串N）

  • 注意：如果拼接中有任何一个元素为null，整个结果就为null

  • 示例：

  •   -- 建测试数据表
      create table if not exists tb_fun_concat
          as
      select '2020' as year, '08' as month, '01' as day
      union all
      select '2020' as year, '08' as month, '02' as day;
      
      -- 查询数据
      select * from tb_fun_concat;
      
      -- 合并成一列
      select
             concat(year,'-', month,'-', day) as daystr
      from tb_fun_concat;
      
      -- 如果有 null
      select
             concat(year,'-', month,'-', day, null) as daystr
      from tb_fun_concat;

• concat_ws

  • 功能：用于实现字符串的拼接，可以指定字符串之间的分隔符

  • 语法：concat_ws（分隔符，字符串1，字符串2，……，字符串N）

  • 注意：只要有一个不为null，结果就不为null

  • 示例：

  •   select
             concat_ws('-', year, month, day) as daystr
      from tb_fun_concat;

• substr

  • 功能：用于实现字符串的裁剪

  • 语法：substr(字符串， 开始位置， 截取长度)

  • 示例：

  • -- 获取每个学生姓名的姓氏（第一个字：指定长度）
    select substr(s_name,1,1) as surname
    from student
    ;
    
    -- 获取每个学生姓名的名字（出去第一个字：不指定长度）
    select substr(s_name,2) as n_name
    from student
    ;

• replace

  • 功能：用于实现字符串的替换

  • 语法：replace(字符串，替换谁，替换成什么)

  • 示例：

  •   -- 将yyyy-MM-dd 转换成 yyyy.MM.dd
      select '2024-07-05', replace('2024-07-05', '-', '.') as new_date

• reverse

  • 功能：实现字符串的反转

  • 语法：reverse(字符串)

  • 示例：

  •   select 'abcdefg', reverse('abcdefg');

• locate：定位

  • 功能：查找子串的位置或者是否包含子串

  • 语法：locate(子串，字符串) ，不存在就返回0，存在就返回子串的开始位置

  • 示例：

  •   -- 查询所有老师名字中包含 三 的教师
      select
          *,
          locate('三', name)
      from tb_agg_func
      where locate('三', name) > 0;

• trim

  • 功能：用于实现对字符串去首尾空格

  • 语法：trim(字符串)

  • 示例：

  •   select ' 我两边各有一个空格 ', trim(' 我两边各有一个空格 ', trim(' 我两边和中 间各有一个空格 ');

  • 注意：trim无法去除字符串中间的空格

• strcmp：

  • 功能：string compare：字符串比较，用于比较两个字符串的大小

  • 语法：strcmp(str1, str2)，0表示相等，1表示str1大于str2，-1表示str1小于str2

  • 示例：

  •   select strcmp('abc', 'abc');
      select strcmp('abcd', 'abc');
      select strcmp('abc', 'bbc');
      select strcmp('abc', 'b');

3、常用数值函数

• abs

  • 功能：用于返回给定数值的绝对值

  • 语法：abs(数值)

  • 示例：

  • select abs(-1), abs(1), abs(-15.99), abs(0);

• ceil

  • 功能：用于实现对数值进行向上取整

  • 语法：ceil(数值)

  • 示例：

  • select ceil(9.3), ceil(9.9), ceil(10), ceil(10.1);

• floor

  • 功能：用于实现对数值进行向下取整

  • 语法：floor(数值)

  • 示例：

  • select floor(9.3), floor(9.9), floor(10), floor(10.1);

• mod

  • 功能：用于实现两个数值 A除以B运算 的余数

  • 语法：mod(A, B)

  • 示例：

  • select mod(1, 3), mod(4, 5), mod(5, 3), mod(9, 3);

• and: random:随机

  • 功能：用于随机生成一个0 - 1 之间的小数

  • 语法：rand() 、rand(N)，如果给定了N，相当于随机数只生成一次，以后每次结果都相同

  • 示例：

  • select rand(), rand(100);

• round

  • 功能：用于实现四舍五入，指定小数点后保留几位

  • 语法：round(数值，小数点后位数)，不给定位数则取整

  • 示例：

  • select round(19.12345), round(19.56789), round(19.12345, 2), round(19.56789, 3);

• runcate

  • 功能：用于直接截取小数点后的位数，指定小数点后保留几位，不四舍五入

  • 语法：truncate(数值，小数点后位数)

  • 示例：

  • select truncate(19.12345, 2), truncate(19.56789, 3);

• pi

  • 功能：用于生成圆周率

  • 语法：pi()

  • 示例：

  • select pi();

• power

  • 功能：用于计算数值指定的次方

  • 语法：power(数值，次方)

  • 示例：

  • select power(2, 2), power(2, 3), power(2, 10), power(2, 20), power(2,100);

• sqrt

  • 功能：用于返回指定数值的平方根

  • 语法：sqrt(数值)

  • 示例：

  • select sqrt(4), sqrt(9), sqrt(8), sqrt(10);

• log

  • 功能：返回指定以M为底的N的对数值

  • 语法：log(底数，数值)

  • 示例：

  • select log(2, 16), log(3, 1), log(4, 4);

• 数值运算符

  • 功能：用于实现数值的运算

  • 语法：加+、减-、乘*、除/ 、取余%

  • 示例：

  • select s_score,
           s_score + 1,
           s_score + s_score,
           s_score - 1,
           s_score * 2,
           s_score / 2,
           s_score % 2
    from score;

4、常用时间函数

• now：现在

  • 功能：获取当前的日期和时间

  • 语法：now()

  • 示例：

  • select now();

• curdate：现在日期

  • 功能：获取当前的日期，不包含时间

  • 语法：curdate()

  • 示例：

  • select curdate();

• curtime

  • 功能：获取当前的时间，不包含日期

  • 语法：curtime

  • 示例：

  • select curtime();

• date

  • 功能：从日期时间中获取日期

  • 语法：date(日期时间)

  • 示例：

  • select date('2023-01-01 12:30:29');

• time

  • 功能：从日期时间中获取时间

  • 语法：time(日期时间)

  • 示例：

  • select time('2023-01-01 12:30:29');

• year/quarter/month/day/hour/minute/second

  • 功能：从日期时间中获取指定的元素

  • 语法：year/quarter/month/day/hour/minute/second(日期时间)

  • 示例：

  • select
           year('2023-04-01 12:30:29') as year,
           quarter('2023-04-01 12:30:29') as quater,
           month('2023-04-01 12:30:29') as month,
           day('2023-04-01 12:30:29') as day,
           hour('2023-04-01 12:30:29') as hour,
           minute('2023-04-01 12:30:29') as minute,
           second('2023-04-01 12:30:29') as seconds
           ;

• unix_timestamp

  • 功能：将指定日期时间转换我时间戳

  • 语法：unix_timestamp(日期时间)

  • 示例：

  • select unix_timestamp('2022-01-01 21:00:00');

• from_unixtime

  • 功能：将时间戳，转换成日期时间

  • 语法：from_unixtime(时间戳)

  • 示例：

  • select from_unixtime(1641042000), from_unixtime(1641042000, '%Y-%m-%d %H:%i:%S');

• date_format

  • 功能：用于实现两个日期格式之间的转换

  • 语法：date_format(日期时间， 目标格式)

  • 示例：

  • SELECT DATE_FORMAT('2022-02-01 12:30:45', '%d/%M/%Y %H:%i:%s');

• date_add/date_sub

  • 功能：用于实现日期的加减

  • 语法：date_add/date_sub(日期，天数)

  • 示例：

  • SELECT
           date_add('2022-02-01 12:30:00', interval 1 second ),
           date_add('2022-02-01 12:30:00', interval 1 minute ),
           date_add('2022-02-01 12:30:00', interval 1 hour ),
           date_add('2022-02-01 12:30:00', interval 1 day),
           date_add('2022-02-01 12:30:00', interval 1 week ),
           date_add('2022-02-01 12:30:00', interval 1 month ),
           date_add('2022-02-01 12:30:00', interval 1 year ),
           date_sub('2022-02-01 12:30:00', interval 1 year ),
           date_add('2022-02-01 12:30:00', interval -1 year )
           ;

• datediff

  • 功能：用于获取两个日期之间的差值

  • 语法：date_diff(日期1， 日期2)

  • 示例：

  • select datediff('2023-11-11', '2023-09-10');

• timediff

  • 功能：用于获取两个时间之间的差值

  • 语法：timediff(时间1， 时间2)

  • 示例：

  • select timediff('12:30:45', '10:00:00');

• last_day

  • 功能：用于获取每个月最后一天

  • 语法：last_day(日期)

  • 示例：

  • select last_day(curdate());

• day_of_week / day_of_month / day_of_year

  • 功能：获取某个日期是那一周/那一个月/那一年的第几天

  • 语法：day_of_week(日期)/day_of_month(日期)/day_of_year(日期)

  • 示例：

  • select dayofweek(curdate()), dayofmonth(curdate()), dayofyear(curdate());

• week_of_year

  • 功能：获取日期在今年的第几周

  • 语法：week_of_year(日期)

  • 示例：

  • select weekofyear(curdate());