暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / GBase 8a 学习笔记 012 —— GBase 8a MPP Cluster 常用系统函数

GBase 8a 学习笔记 012 —— GBase 8a MPP Cluster 常用系统函数

心有阳光 2023-03-07
2394

GBase 8a 学习笔记 012 —— GBase 8a MPP Cluster 常用系统函数

系统函数综述

  • 系统函数：GBase 8a 内置有几百个实用的系统函数，使得数据处理更加方便。
  • 格式
    • 函数名(参数)
    • 参数可以是字段，也可以是数值或表达式
  • 位置
    • select 中使用函数，可用于投影列，也可用在查询条件中。
    • 举例：select 函数名(参数) from 表名 where …;

系统函数分类

函数分类 举例
数学函数（数值函数） ABS、CEIL、FLOOR、MOD、POWER、ROUND、TRUNCATE……
日期时间函数 NOW、SYSDATE、DAY、ADD_MONTHS、LAST_DAY、NEXT_DAY……
字符串处理函数 UPPER、REPLACE、CONCAT、SUBSTRING……
数据转换函数 CAST、CONVERT、TO_NUMBER、TO_CHAR、TO_DATE
控制流函数 CASE、IF(expr1,expr2,expr3)
聚合函数 AVG、COUNT、MAX、MIN、SUM……
OLAP函数分析函数） COUNT OVER，SUM OVER、GROUP BY CUBE、RANK…
其他函数 VERSION、DATABASE、USER……

数学函数

ROUND(按位数四舍五入)

gbase> select round(456.789), round(456.789,0), round(456.789,-2), round(456.789,2);
+----------------+------------------+-------------------+------------------+
| round(456.789) | round(456.789,0) | round(456.789,-2) | round(456.789,2) |
+----------------+------------------+-------------------+------------------+
|            457 |              457 |               500 |           456.79 |
+----------------+------------------+-------------------+------------------+
1 row in set (Elapsed: 00:00:00.00)

TRUNCATE（按位数截取）

gbase> select truncate(456.789,0), truncate(456.789,-2), truncate(456.789,2);
+---------------------+----------------------+---------------------+
| truncate(456.789,0) | truncate(456.789,-2) | truncate(456.789,2) |
+---------------------+----------------------+---------------------+
|                 456 |                  400 |              456.78 |
+---------------------+----------------------+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a105.png

时间函数

函数 功能
NOW, SYSDATE, CURRENT 当前日期与当前时间
YEAR，MONTH，DAY，WEEKDAY 取指定表达式中的年份，月份，日期，星期
LAST_DAY 取月份日期的最后一天。
TO_DAYS 返回日期 date 对应的天数（从年份 0 开始天数）
DATE_FORMAT 依照 FORMAT 字符串格式化日期值
ADD_MONTHS 、PERIOD_DIFF(P1,P2) 取表达式几个月后的值、月份之差
DATE_ADD、DATE_SUB、DATEDIFF 日期加法、减法计算、日期相差天数

NOW, SYSDATE, CURRENT_DATE, CURRENT_TIME

取gcluster节点当前日期与时间

gbase> select NOW(), SYSDATE(),CURRENT_DATE, CURDATE(),CURRENT_TIME(),CURTIME() ;
+---------------------+---------------------+--------------+------------+----------------+-----------+
| NOW()               | SYSDATE()           | CURRENT_DATE | CURDATE()  | CURRENT_TIME() | CURTIME() |
+---------------------+---------------------+--------------+------------+----------------+-----------+
| 2023-03-02 22:15:36 | 2023-03-02 22:15:36 | 2023-03-02   | 2023-03-02 | 22:15:36       | 22:15:36  |
+---------------------+---------------------+--------------+------------+----------------+-----------+
1 row in set (Elapsed: 00:00:00.01)

gbase8a106.png

建议使用now()函数

  • SYSDATE()返回的是函数执行时的时间
  • now()返回的是语句执行时的时间

YEAR, MONTH, DAY，WEEKDAY

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒

gbase> select now(),YEAR(NOW()), month(now()),day(now()),WEEKDAY(now());
+---------------------+-------------+--------------+------------+----------------+
| now()               | YEAR(NOW()) | month(now()) | day(now()) | WEEKDAY(now()) |
+---------------------+-------------+--------------+------------+----------------+
| 2023-03-02 22:16:22 |        2023 |            3 |          2 |              3 |
+---------------------+-------------+--------------+------------+----------------+
1 row in set (Elapsed: 00:00:00.00)

-- 季度
gbase> SELECT now(),quarter(now());
+---------------------+----------------+
| now()               | quarter(now()) |
+---------------------+----------------+
| 2023-03-02 22:16:35 |              1 |
+---------------------+----------------+
1 row in set (Elapsed: 00:00:00.00)

-- 小时
gbase> SELECT now(),hour(now());
+---------------------+-------------+
| now()               | hour(now()) |
+---------------------+-------------+
| 2023-03-02 22:16:46 |          22 |
+---------------------+-------------+
1 row in set (Elapsed: 00:00:00.00)

-- 分钟
gbase> SELECT now(),minute(now());
+---------------------+---------------+
| now()               | minute(now()) |
+---------------------+---------------+
| 2023-03-02 22:16:57 |            16 |
+---------------------+---------------+
1 row in set (Elapsed: 00:00:00.00)

-- 秒
gbase> SELECT now(),second(now());
+---------------------+---------------+
| now()               | second(now()) |
+---------------------+---------------+
| 2023-03-02 22:17:15 |            15 |
+---------------------+---------------+
1 row in set (Elapsed: 00:00:00.00)

-- 微秒
gbase> SELECT now(),microsecond(now());
+---------------------+--------------------+
| now()               | microsecond(now()) |
+---------------------+--------------------+
| 2023-03-02 22:17:26 |                  0 |
+---------------------+--------------------+
1 row in set (Elapsed: 00:00:00.01)

gbase8a107.png

LAST_DAY

返回月份日期的最后一天

-- 查询日期月份最后一天，日期支持两位数年份值转换为四位数形式，错误日期返回NULL

gbase> select last_day('2019-2-10') lastday1, last_day('2020-2-10 12:10:30 ') lastday2, last_day('19-5-10') lastday3, last_day('190510') lastday4;
+------------+------------+------------+------------+
| lastday1   | lastday2   | lastday3   | lastday4   |
+------------+------------+------------+------------+
| 2019-02-28 | 2020-02-29 | 2019-05-31 | 2019-05-31 |
+------------+------------+------------+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select last_day(now()) lastday1, last_day('2019-2-30') lastday2, last_day('0000-00-00');
+------------+----------+------------------------+
| lastday1   | lastday2 | last_day('0000-00-00') |
+------------+----------+------------------------+
| 2023-03-31 | NULL     | NULL                   |
+------------+----------+------------------------+
1 row in set, 2 warnings (Elapsed: 00:00:00.00)

gbase8a108.png

TO_DAYS

返回日期对应的天数,从年份 0 开始的天数,日期转换为数字

-- 查询一个日期对应的天数，日期支持两位数年份值转换为四位数形式
gbase> select TO_DAYS('2018-10-09'), TO_DAYS('18-10-09'),TO_DAYS('181009');
+-----------------------+---------------------+-------------------+
| TO_DAYS('2018-10-09') | TO_DAYS('18-10-09') | TO_DAYS('181009') |
+-----------------------+---------------------+-------------------+
|                737341 |              737341 |            737341 |
+-----------------------+---------------------+-------------------+
1 row in set (Elapsed: 00:00:00.00)

-- 查询当天日期和对应的天数，下月第一天的日期和对应天数
gbase> select NOW() today, TO_DAYS(NOW()) todaydate, LAST_DAY(NOW())+1 nextmonthfirst ,TO_DAYS(LAST_DAY(NOW())+1) nextmonthfirstdate;
+---------------------+-----------+----------------+--------------------+
| today               | todaydate | nextmonthfirst | nextmonthfirstdate |
+---------------------+-----------+----------------+--------------------+
| 2023-03-02 22:19:46 |    738946 | 2023-04-01     |             738976 |
+---------------------+-----------+----------------+--------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a109.png

DATE_FORMAT

日期的格式

SELECT sysdate(),DATE_FORMAT(sysdate(), '%W %M %Y');

gbase> SELECT DATE_FORMAT('2010-10-04 22:23:00', '%H:%i:%s') ;
+------------------------------------------------+
| DATE_FORMAT('2010-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

ADD_MONTHS (date, number)

在一个日期上加上指定的月份数；要减去月份数，则 number 为负数；

gbase> SELECT NOW(), ADD_MONTHS(NOW(),3) AS next3month, ADD_MONTHS(NOW(),-1) as lastmonth;
+---------------------+---------------------+---------------------+
| NOW()               | next3month          | lastmonth           |
+---------------------+---------------------+---------------------+
| 2023-03-02 22:21:05 | 2023-06-02 22:21:05 | 2023-02-02 22:21:05 |
+---------------------+---------------------+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a110.png

DATE_ADD(date,INTERVAL expr type)

日期加法操作 负值表示减法

DATE_SUB(date,INTERVAL expr type)

日期减法操作

DATEDIFF(expr1, expr2)

开始日期 expr1 和结束日期 expr2 之间的天数

-- 取当前日期，后一天，前7天，前30天的日期；
gbase> SELECT NOW(),DATE_ADD(now(), INTERVAL 1 DAY) as 后一天, DATE_SUB(date(now()), INTERVAL 7 DAY) as 前七天, DATE_SUB(date(now()), INTERVAL 30 DAY)前30天;
+---------------------+---------------------+------------+------------+
| NOW()               | 后一天              | 前七天     | 前30天     |
+---------------------+---------------------+------------+------------+
| 2023-03-02 22:22:06 | 2023-03-03 22:22:06 | 2023-02-23 | 2023-01-31 |
+---------------------+---------------------+------------+------------+
1 row in set (Elapsed: 00:00:00.00)

-- 求距离月底还有多少天；
gbase> SELECT NOW(), DATEDIFF(LAST_DAY(NOW()),NOW());
+---------------------+---------------------------------+
| NOW()               | DATEDIFF(LAST_DAY(NOW()),NOW()) |
+---------------------+---------------------------------+
| 2023-03-02 22:22:36 |                              29 |
+---------------------+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a111.png

字符串处理函数

函数 功能
LOWER，LCASE，UPPER 转换字母大小写
REPLACE 替换字符串中指定的字符串
ASCII 取ASCII码
CONCAT，|| 字符串连接
SUBSTRING，LEFT，RIGHT 字符串截取
INSTR 取字符串位置
NVL 替换NULL值
TRIM，LTRIM，RTRIM 去除字符串中前后特定字符
LENGTH ，CHAR_LENGTH 取字符串长度

LOWER, LCASE(全部字母转换为小写)，UPPER（转换为大写）

gbase> SELECT upper('gb') || LOWER('ASE') || LCASE(' 8A') ;
+---------------------------------------------+
| upper('gb') || LOWER('ASE') || LCASE(' 8A') |
+---------------------------------------------+
| GBase 8a                                    |
+---------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

REPLACE

替换字符串中指定的字符串

gbase> select replace('1234567','23','11');
+------------------------------+
| replace('1234567','23','11') |
+------------------------------+
| 1114567                      |
+------------------------------+
1 row in set (Elapsed: 00:00:00.00)

ASCII

返回ASCII编码，多个字符时返回首字符的ASCII码

gbase> select ascii('A'), ascii('hello') from dual;
+------------+----------------+
| ascii('A') | ascii('hello') |
+------------+----------------+
|         65 |            104 |
+------------+----------------+
1 row in set (Elapsed: 00:00:00.09)

CONCAT，||

字符串连接，将数字转换为字符串

gbase> select concat('我已使用', 20,'天GBase 8a') as concat_fun;
+---------------------------+
| concat_fun                |
+---------------------------+
| 我已使用20天GBase 8a      |
+---------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a112.png

NVL

替换NULL值

select cno,cname,nvl(cpno,'unknow') from school.course;
+------+--------------+--------------------+
| cno | cname | nvl(cpno,'unknow') |
+------+--------------+--------------------+
| 1 | 数据库 | 5 |
| 2 | 数学 | unknow |
| 3 | 信息系统 | 1 |
| 4 | 操作系统 | 6 |
| 5 | 数据结构 | 7 |
| 6 | 数据处理 | unknow |
| 7 | PASCAL语言 | 6 |
+------+--------------+--------------------+

LEFT，RIGHT

从左，右截取指定位数字符串

gbase> select left('1234567',3) , right('1234567',3) ;
+-------------------+--------------------+
| left('1234567',3) | right('1234567',3) |
+-------------------+--------------------+
| 123               | 567                |
+-------------------+--------------------+
1 row in set (Elapsed: 00:00:00.00)

LENGTH ，CHAR_LENGTH

取字符串长度

gbase> select length('南大通用'), char_length('南大通用');
+------------------------+-----------------------------+
| length('南大通用')     | char_length('南大通用')     |
+------------------------+-----------------------------+
|                     12 |                           4 |
+------------------------+-----------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a113.png

数据转换函数

函数 功能
CAST、CONVERT 数据类型转换
CONV 不同数字进制间的转换
TO_NUMBER 字符串 string转化成数值
TO_CHAR 日期转化为字符串
TO_DATE 字符串 string 格式化成 format 类型的日期

CAST、CONVERT

数据类型转换

CAST(expr AS type)，CONVERT(expr,type)，CONVERT(expr USING transcoding_name)

type 可以是下列值之一：
CHAR、DATE、DATETIME、DECIMAL、TIME、NUMERIC、INT、SIGNED INT、SIGNED

gbase> SELECT NOW(),CAST(NOW() AS DATE);
+---------------------+---------------------+
| NOW()               | CAST(NOW() AS DATE) |
+---------------------+---------------------+
| 2023-03-02 22:27:02 | 2023-03-02          |
+---------------------+---------------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> SELECT 1+'3'+10 ;
+----------+
| 1+'3'+10 |
+----------+
|       14 |
+----------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a114.png

字符串和数字类型的转换是隐式操作，条件中右边向左边转换

CONV

不同数字进制间的转换

不同数字进制间的转换。将 N 由 from_base 进制转化为 to_base 进制，返回值为to_base 进制形式的字符串，如有任意一个参数为 NULL，则返回值为 NULL。

gbase> SELECT CONV(5,16,2);
+--------------+
| CONV(5,16,2) |
+--------------+
| 101          |
+--------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT CONV('a',16,2);
+----------------+
| CONV('a',16,2) |
+----------------+
| 1010           |
+----------------+
1 row in set (Elapsed: 00:00:00.00)

TO_NUMBER

返回数值

gbase> select to_number('3.14')+3.14;
+------------------------+
| to_number('3.14')+3.14 |
+------------------------+
|                   6.28 |
+------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a115.png

TO_CHAR(datetime,[FORMAT])

将日期时间转换为字符串，并进行格式化输出

gbase> select now(),to_char(now(),'YYYY/MM/DD');
+---------------------+-----------------------------+
| now()               | to_char(now(),'YYYY/MM/DD') |
+---------------------+-----------------------------+
| 2023-03-02 22:29:23 | 2023/03/02                  |
+---------------------+-----------------------------+
1 row in set (Elapsed: 00:00:00.00)

TO_CHAR(number,[FORMAT])

将数字转换为字符串，并进行格式化输出。

gbase> SELECT TO_CHAR(987654321,'999,999,999');
+----------------------------------+
| TO_CHAR(987654321,'999,999,999') |
+----------------------------------+
|  987,654,321                     |
+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

TO_DATE(string,format)

将字符串 string 格式化成 format 类型的日期。

gbase> SELECT TO_DATE('2011/11/15','YYYY/MM/DD') ;
+------------------------------------+
| TO_DATE('2011/11/15','YYYY/MM/DD') |
+------------------------------------+
| 2011-11-15                         |
+------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase8a116.png

CASE 用法

CASE 子句的两种格式

-- 格式11
CASE value 
WHEN [compare-value] THEN result 
[WHEN [compare-value] THEN result ...]
[ELSE result] END

-- 格式12
CASE WHEN 
[condition] THEN result 
[WHEN [condition] THEN result ...] 
[ELSE result] END

gbase> SELECT Sname,Sage, CASE Ssex WHEN ' 男 '
    -> THEN '帅哥' WHEN '女' THEN '美女'
    -> ELSE '不明' END sex
    -> FROM student;
+--------+------+--------+
| Sname  | Sage | sex    |
+--------+------+--------+
| 张立   |   19 | 不明   |
| 王敏   |   18 | 美女   |
| 刘晨   |   19 | 美女   |
| 李勇   |   20 | 不明   |
+--------+------+--------+
4 rows in set (Elapsed: 00:00:00.02)

gbase8a117.png

控制流函数

IF(expr1,expr2,expr3)

expr1 为 TRUE ，则 IF()的返回值为 expr2；否则返回值为 expr3；

SELECT IF(1<2,'yes','no') FROM t;

SELECT (CASE WHEN expr1 THEN expr2 ELSE expr3 END) FROM t;

函数使用优化建议

-- 下面哪条语句快？

select ENAME, DEPTNO from emp
where to_char(HIREDATE,'YYYY') >= 1981;

-- 这一条快
select ENAME, DEPTNO from emp
where HIREDATE >= to_date('1981-01-01','YYYY-MM-DD');

原理：

  • 函数使用在字段上，每一行字段数据都会执行函数；
  • 而函数使用在值上，只执行一次，还能利用智能索引。
gbase墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

GBase 8a 学习笔记（共21篇）
GBase 8a 学习笔记

评论