内置函数

更新时间：2025-08-21

数学函数

abs(double a)

功能：返回参数的绝对值

返回类型：double类型

使用说明：使用该函数需要确保函数的返回值是整数。

acos(double a)

功能：返回参数的反余弦值

返回类型：double类型

asin(double a)

功能：返回参数的反正弦值

返回类型：double类型

atan(double a)

功能：返回参数的反正切值

返回类型：double类型

bin(bigint a)

功能：返回整型的二进制表示形式（即0 和1 序列）

返回类型：string类型

Plain Text

1 mysql> select bin(10);
2 +---------+
3 | bin(10) |
4 +---------+
5 | 1010    |
6 +---------+
7 1 row in set (0.01 sec)

ceil(double a)

ceiling(double a)

dceil(double a)

功能：返回大于等于该参数的最小整数

返回类型：int类型

conv(bigint num, int from_base, int to_base)

conv(string num,int from_base, int to_base)

功能：进制转换函数，返回某个整数在特定进制下的的字符串形式。输入参数可以是整型的字符串形式。如果想要将函数的返回值转换成整数，可以使用CAST函数。

返回类型：string类型

举例:

Plain Text

1 mysql> select conv(64,10,8);
2 +-----------------+
3 | conv(64, 10, 8) |
4 +-----------------+
5 | 100             |
6 +-----------------+
7 1 row in set (0.01 sec)
8
9 mysql> select cast(conv('fe', 16, 10) as int) as "transform_string_to_int";
10 +-------------------------+
11 | transform_string_to_int |
12 +-------------------------+
13 |                     254 |
14 +-------------------------+
15 1 row in set (0.00 sec)

cos(double a)

功能：返回参数的余弦值

返回类型：double类型

degrees(double a)

功能：将弧度转成角度

返回类型：double类型

e()

功能：返回数学上的常量e

返回类型：double类型

exp(double a)

dexp(double a)

功能：返回e 的a 次幂（即ea）

返回类型： double 类型

floor(double a)

dfloor(double a)

功能：返回小于等于该参数的最大整数

返回类型：int类型

fmod(double a, double b)

fmod(float a, float b)

功能：返回a除以b的余数。等价于%算术符

返回类型：float或者double类型

举例：

Plain Text

1 mysql> select fmod(10,3);
2 +-----------------+
3 | fmod(10.0, 3.0) |
4 +-----------------+
5 |               1 |
6 +-----------------+
7 1 row in set (0.01 sec)
8
9 mysql> select fmod(5.5,2);
10 +----------------+
11 | fmod(5.5, 2.0) |
12 +----------------+
13 |            1.5 |
14 +----------------+
15 1 row in set (0.01 sec)

greatest(bigint a[, bigint b ...])

greatest(double a[, double b ...])

greatest(decimal(p,s) a[, decimal(p,s) b ...])

greatest(string a[, string b ...])

greatest(timestamp a[, timestamp b ...])

功能：返回列表里的最大值

返回类型：和参数类型相同

hex(bigint a)

hex(string a)

功能：返回整型或字符串中各个字符的16进制表示形式。

返回类型：string类型

举例：

Plain Text

1 mysql> select hex('abc');
2 +------------+
3 | hex('abc') |
4 +------------+
5 | 616263     |
6 +------------+
7 1 row in set (0.01 sec)
8
9 mysql> select unhex(616263);
10 +---------------+
11 | unhex(616263) |
12 +---------------+
13 | abc           |
14 +---------------+
15 1 row in set (0.01 sec)

least(bigint a[, bigint b ...])

least(double a[, double b ...])

least(decimal(p,s) a[, decimal(p,s) b ...])

least(string a[, string b ...])

least(timestamp a[, timestamp b ...])

功能：返回列表里的最小值

返回类型：和参数类型相同

ln(double a)

dlog1(double a)

功能：返回参数的自然对数形式

返回类型：double类型

log(double base, double a)

功能：返回log以base为底数，以a为指数的对数值。

返回类型：double类型

log10(double a)

dlog10(double a)

功能：返回log以10为底数，以a为指数的对数值。

返回类型：double类型

log2(double a)

功能：返回log以2为底数，以a为指数的对数值。

返回类型：double类型

mod(numeric_type a, same_type b)

功能：返回a除以b的余数。等价于%算术符。

返回类型：和输入类型相同

举例：

Plain Text

1 mysql> select mod(10,3);
2 +------------+
3 | mod(10, 3) |
4 +------------+
5 |          1 |
6 +------------+
7 1 row in set (0.01 sec)
8
9 mysql> select mod(5.5,2);
10 +-------------+
11 | mod(5.5, 2) |
12 +-------------+
13 |         1.5 |
14 +-------------+
15 1 row in set (0.01 sec)

negative(int a)

negative(double a)

功能：将参数a的符号位取反，如果参数是负值，则返回正值

返回类型：根据输入参数类型返回int类型或double类型

使用说明：如果你需要确保所有返回值都是负值，可以使用-abs(a)函数。

pi()

功能：返回常量Pi

返回类型： double类型

pmod(int a, int b)

pmod(double a, double b)

功能：正取余函数

返回类型：int类型或者double类型（由输入参数决定）

positive(int a)

功能：返回参数的原值，即使参数是负的，仍然返回原值。

返回类型：int类型

使用说明：如果你需要确保所有返回值都是正值，可以使用abs()函数。

pow(double a, double p)

power(double a, double p)

功能：返回a的p次幂

返回类型：double类型

radians(double a)

功能：将弧度转换成角度

返回类型：double类型

rand()

rand(int seed)

random()

random(int seed)

功能：返回0～1之间的随机值。参数为随机种子。

返回类型：double

使用说明：每次查询的随机序列都会重置，多次调用rand 函数会产生相同的结果。如果每次查询想产生不同的结果，可以在每次查询时使用不同的随机种子。例如select rand(unix_timestamp()) from ...

round(double a)

round(double a, int d)

功能：取整函数。如果只带一个参数，该函数会返回距离该值最近的整数。如果带2个参数，第二个参数为小数点后面保留的位数。

返回类型：如果参数是浮点类型则返回bigint。如果第二个参数大于1，则返回double类型。

举例:

Plain Text

1 mysql> select round(100.456, 2);
2 +-------------------+
3 | round(100.456, 2) |
4 +-------------------+
5 |            100.46 |
6 +-------------------+
7 1 row in set (0.02 sec)

sign(double a)

功能：如果a是整数或者0，返回1；如果a是负数，则返回-1

返回类型：int类型

sin(double a)

功能：返回a的正弦值

返回类型：double类型

sqrt(double a)

功能：返回a的平方根

返回类型：double类型

tan(double a)

功能：返回a的正切值

返回类型：double类型

unhex(string a)

功能：把十六进制格式的字符串转化为原来的格式

返回类型：string类型

举例：

Plain Text

1 mysql> select hex('abc');
2 +------------+
3 | hex('abc') |
4 +------------+
5 | 616263     |
6 +------------+
7 1 row in set (0.01 sec)
8
9 mysql> select unhex(616263);
10 +---------------+
11 | unhex(616263) |
12 +---------------+
13 | abc           |
14 +---------------+
15 1 row in set (0.01 sec)

位操作函数

bitand(integer_type a, same_type b)

功能：按位与运算

返回类型：和输入类型相同

举例：

Plain Text

1 mysql> select bitand(255, 32767); /* 0000000011111111 & 0111111111111111 */
2 +--------------------+
3 | bitand(255, 32767) |
4 +--------------------+
5 |                255 |
6 +--------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select bitand(32767, 1); /* 0111111111111111 & 0000000000000001 */
10 +------------------+
11 | bitand(32767, 1) |
12 +------------------+
13 |                1 |
14 +------------------+
15 1 row in set (0.01 sec)
16
17 mysql> select bitand(32, 16); /* 00010000 & 00001000 */
18 +----------------+
19 | bitand(32, 16) |
20 +----------------+
21 |              0 |
22 +----------------+
23 1 row in set (0.01 sec)
24
25 mysql> select bitand(12,5); /* 00001100 & 00000101 */
26 +---------------+
27 | bitand(12, 5) |
28 +---------------+
29 |             4 |
30 +---------------+
31 1 row in set (0.01 sec)
32
33 mysql> select bitand(-1,15); /* 11111111 & 00001111 */
34 +----------------+
35 | bitand(-1, 15) |
36 +----------------+
37 |             15 |
38 +----------------+
39 1 row in set (0.01 sec)

bitnot(integer_type a)

功能：按位非运算

返回类型：和输入类型相同

举例：

Plain Text

1 mysql> select bitnot(127); /* 01111111 -> 10000000 */
2 +-------------+
3 | bitnot(127) |
4 +-------------+
5 |        -128 |
6 +-------------+
7 1 row in set (0.01 sec)
8
9 mysql> select bitnot(16); /* 00010000 -> 11101111 */
10 +------------+
11 | bitnot(16) |
12 +------------+
13 |        -17 |
14 +------------+
15 1 row in set (0.01 sec)
16
17 mysql> select bitnot(0); /* 00000000 -> 11111111 */
18 +-----------+
19 | bitnot(0) |
20 +-----------+
21 |        -1 |
22 +-----------+
23 1 row in set (0.01 sec)
24
25 mysql> select bitnot(-128); /* 10000000 -> 01111111 */
26 +--------------+
27 | bitnot(-128) |
28 +--------------+
29 |          127 |
30 +--------------+
31 1 row in set (0.01 sec)

bitor(integer_type a, same_type b)

功能：按位或运算

返回类型：和输入类型相同

举例：

Plain Text

1 mysql> select bitor(1,4); /* 00000001 | 00000100 */
2 +-------------+
3 | bitor(1, 4) |
4 +-------------+
5 |           5 |
6 +-------------+
7 1 row in set (0.01 sec)
8
9 mysql> select bitor(16,48); /* 00001000 | 00011000 */
10 +---------------+
11 | bitor(16, 48) |
12 +---------------+
13 |            48 |
14 +---------------+
15 1 row in set (0.01 sec)
16
17 mysql> select bitor(0,7); /* 00000000 | 00000111 */
18 +-------------+
19 | bitor(0, 7) |
20 +-------------+
21 |           7 |
22 +-------------+
23 1 row in set (0.01 sec)

bitxor(integer_type a, same_type b)

功能：按位异或运算

返回类型：和输入类型相同

举例：

Plain Text

1 mysql> select bitxor(0,15); /* 00000000 ^ 00001111 */
2 +---------------+
3 | bitxor(0, 15) |
4 +---------------+
5 |            15 |
6 +---------------+
7 1 row in set (0.01 sec)
8
9 mysql> select bitxor(7,7); /* 00000111 ^ 00000111 */
10 +--------------+
11 | bitxor(7, 7) |
12 +--------------+
13 |            0 |
14 +--------------+
15 1 row in set (0.01 sec)
16
17 mysql> select bitxor(8,4); /* 00001000 ^ 00000100 */
18 +--------------+
19 | bitxor(8, 4) |
20 +--------------+
21 |           12 |
22 +--------------+
23 1 row in set (0.01 sec)
24
25 mysql> select bitxor(3,7); /* 00000011 ^ 00000111 */
26 +--------------+
27 | bitxor(3, 7) |
28 +--------------+
29 |            4 |
30 +--------------+
31 1 row in set (0.01 sec)

类型转换函数

cast(expr as type)

转换函数通常会和其他函数一同使用，显示的将expression转换成指定的参数类型。PALO对于函数的参数类型有严格的数据类型定义。例如PALO不会自动将bigtint转换成int类型，或者其余可能会损失精度或者产生溢出的转换。用户使用cast函数可以把列值或者字面常量转换成函数参数需要的其他类型。

举例：

Plain Text

1 mysql> select concat('Here are the first ', cast(10 as string), ' results.');
2 +-------------------------------------------------------------------+
3 | concat('Here are the first ', CAST(10 AS CHARACTER), ' results.') |
4 +-------------------------------------------------------------------+
5 | Here are the first 10 results.                                    |
6 +-------------------------------------------------------------------+
7 1 row in set (0.01 sec)

日期和时间函数

PALO支持的时间类型是TIMESTAMP，包括DATE和DATETIME两种类型。TIMESTAMP包含date和time两部分，日期和时间函数可以抽取出单个字段，如hour(), minute()。通常这些函数的返回值是整型。格式化日期的函数(如date_add())的返回值是字符串类型。用户可以通过加上或减去时间间隔来改变时间类型的值。时间间隔通常作为date_add()和date_sub()的第二个参数。PALO支持如下的日期和时间函数。

add_months(timestamp date, int months)

add_months(timestamp date, bigint months)

功能：返回指定date加上months个月的新date。和months_add()相同

返回类型：timestamp类型

举例：

如果这个月的这一日并不存在于目标月中，那么结果将是那个月的最后一天；如果参数中的months是负数，则是求先前的月。

Plain Text

1 mysql> select now(), add_months(now(), 2);
2 +---------------------+---------------------+
3 | now()               | add_months(now(), 2)|
4 +---------------------+---------------------+
5 | 2016-05-31 10:47:00 | 2016-07-31 10:47:00 |
6 +---------------------+---------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select now(), add_months(now(), 1);
10 +---------------------+---------------------+
11 | now()               | add_months(now(), 1)|
12 +---------------------+---------------------+
13 | 2016-05-31 10:47:14 | 2016-06-30 10:47:14 |
14 +---------------------+---------------------+
15 1 row in set (0.01 sec)
16
17 mysql> select now(), add_months(now(), -1);
18 +---------------------+----------------------+
19 | now()               | add_months(now(), -1)|
20 +---------------------+----------------------+
21 | 2016-05-31 10:47:31 | 2016-04-30 10:47:31  |
22 +---------------------+----------------------+
23 1 row in set (0.01 sec)

adddate(timestamp startdate, int days)

adddate(timestamp startdate, bigint days)

功能：给startdate加上指定的天数

返回类型：timestamp类型

举例:

Plain Text

1 mysql> select adddate(date_column, 10) from big_table limit 1;
2 +-------------------------------+
3 |   adddate(date_column, 10)    |
4 +-------------------------------+
5 |          2014-01-11 00:00:00  |
6 +-------------------------------+

current_timestamp()

功能：和now()函数功能相同，获取当前的时间

返回类型：timestamp类型

date_add(timestamp startdate, int days)

功能：给TIMESTAMP值加上指定的天数。第一个参数可以是字符串，如果字符串符合TIMESTAMP数据类型的格式，该字符串会自动转成TIMESTAMP类型。第二个参数是时间间隔。

返回类型：timestamp类型

date_format(timestamp day, string fmt)

功能：将日期类型按照format的类型转化为字符串，当前支持最大128字节的字符串，如果返回长度超过128，则返回NULL。

返回类型：string类型

format的含义如下：

Plain Text

1 %a Abbreviated weekday name (Sun..Sat)   
2 %b Abbreviated month name (Jan..Dec)    
3 %c Month, numeric (0..12)    
4 %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)   
5 %d Day of the month, numeric (00..31)   
6 %e Day of the month, numeric (0..31)    
7 %f Microseconds (000000..999999)   
8 %H Hour (00..23)   
9 %h Hour (01..12)    
10 %I Hour (01..12)    
11 %i Minutes, numeric (00..59)   
12 %j Day of year (001..366)   
13 %k Hour (0..23)   
14 %l Hour (1..12)    
15 %M Month name (January..December)   
16 %m Month, numeric (00..12)   
17 %p AM or PM    
18 %r Time, 12-hour (hh:mm:ss followed by AM or PM)    
19 %S Seconds (00..59)    
20 %s Seconds (00..59)    
21 %T Time, 24-hour (hh:mm:ss)    
22 %U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0    
23 %u Week (00..53), where Monday is the first day of the week; WEEK() mode 1    
24 %V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X    
25 %v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x   
26 %W Weekday name (Sunday..Saturday)    
27 %w Day of the week (0=Sunday..6=Saturday)    
28 %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V    
29 %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v    
30 %Y Year, numeric, four digits    
31 %y Year, numeric (two digits)   
32 %% A literal “%” character   
33 %x x, for any “x” not listed above

举例：

Plain Text

1 mysql> select date_format('2009-10-04 22:23:00', '%W %M %Y');
2 +------------------------------------------------+
3 | date_format('2009-10-04 22:23:00', '%W %M %Y') |
4 +------------------------------------------------+
5 | Sunday October 2009                            |
6 +------------------------------------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select date_format('2007-10-04 22:23:00', '%H:%i:%s');
10 +------------------------------------------------+
11 | date_format('2007-10-04 22:23:00', '%H:%i:%s') |
12 +------------------------------------------------+
13 | 22:23:00                                       |
14 +------------------------------------------------+
15 1 row in set (0.01 sec)
16
17 mysql> select date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
18 +------------------------------------------------------------+
19 | date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
20 +------------------------------------------------------------+
21 | 4th 00 Thu 04 10 Oct 277                                   |
22 +------------------------------------------------------------+
23 1 row in set (0.03 sec)
24
25 mysql> select date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
26 +------------------------------------------------------------+
27 | date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
28 +------------------------------------------------------------+
29 | 22 22 10 10:23:00 PM 22:23:00 00 6                         |
30 +------------------------------------------------------------+
31 1 row in set (0.00 sec)

date_sub(timestamp startdate, int days)

功能：给TIMESTAMP值减去指定的天数。第一个参数可以是字符串，如果字符串符合TIMESTAMP数据类型的格式，该字符串会自动转成成TIMESTAMP类型。第二个参数是时间间隔。

返回类型：timestamp类型

datediff(string enddate, string startdate)

功能：返回两个日期的天数差值

返回类型：int类型

day(string date)

dayofmonth(string date)

功能：返回日期中的天字段

返回类型：int类型

举例:

Plain Text

1 mysql> select dayofmonth('2013-01-21');
2 +-----------------------------------+
3 | dayofmonth('2013-01-21 00:00:00') |
4 +-----------------------------------+
5 |                                21 |
6 +-----------------------------------+
7 1 row in set (0.01 sec)

days_add(timestamp startdate, int days)

days_add(timestamp startdate, bigint days)

功能：给startdate加上指定的天数，和date_add函数相似，差别在于本函数的参数是TIMESTAMP类型而不是string类型。

返回类型：timestamp类型

days_sub(timestamp startdate, int days)

days_sub(timestamp startdate, bigint days)

功能：给startdate减去指定的天数，和date_dub函数相似，差别在于本函数的参数是TIMESTAMP类型而不是string类型。

返回类型：timestamp类型

extract(unit FROM timestamp)

功能：提取timestamp某个指定单位的值。单位可以为year, month, day, hour, minute或者second

返回类型：int类型

举例：

Plain Text

1 mysql> select now() as right_now,
2 ->   extract(year from now()) as this_year,
3 ->   extract(month from now()) as this_month;
4 +---------------------+-----------+------------+
5 | right_now           | this_year | this_month |
6 +---------------------+-----------+------------+
7 | 2017-10-16 20:47:28 |      2017 |         10 |
8 +---------------------+-----------+------------+
9 1 row in set (0.01 sec)
10
11 mysql> select now() as right_now,
12     ->   extract(day from now()) as this_day,
13     ->   extract(hour from now()) as this_hour;
14 +---------------------+----------+-----------+
15 | right_now           | this_day | this_hour |
16 +---------------------+----------+-----------+
17 | 2017-10-16 20:47:34 |       16 |        20 |
18 +---------------------+----------+-----------+
19 1 row in set (0.01 sec)

from_unixtime(bigint unixtime[, string format])

功能：将unix时间（自1970年1月1日起经过的秒数）转换成相应格式的日期类型

返回类型：字符串类型

使用说明：当前日期格式是大小写敏感的，用户尤其要区分小写m（表达分钟）和大写M（表达月份）。日期字符串的完整型式是"yyyy-MM-dd HH:mm:ss.SSSSSS"，也可以只包含其中部分字段。

举例:

Plain Text

1 mysql> select from_unixtime(100000);
2 +-----------------------+
3 | from_unixtime(100000) |
4 +-----------------------+
5 | 1970-01-02 11:46:40   |
6 +-----------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select from_unixtime(100000, 'yyyy-MM-dd');
10 +-------------------------------------+
11 | from_unixtime(100000, 'yyyy-MM-dd') |
12 +-------------------------------------+
13 | 1970-01-02                          |
14 +-------------------------------------+
15 1 row in set (0.00 sec)
16
17 mysql> select from_unixtime(1392394861, 'yyyy-MM-dd');      
18 +-----------------------------------------+
19 | from_unixtime(1392394861, 'yyyy-MM-dd') |
20 +-----------------------------------------+
21 | 2014-02-15                              |
22 +-----------------------------------------+
23 1 row in set (0.00 sec)

unix_timestamp()和from_unixtime()经常结合使用，将时间戳类型转换成指定格式的字符串。

Plain Text

1 mysql> select from_unixtime(unix_timestamp(now()), 'yyyy-MM-dd');
2 +----------------------------------------------------+
3 | from_unixtime(unix_timestamp(now()), 'yyyy-MM-dd') |
4 +----------------------------------------------------+
5 | 2014-01-01                                         |
6 +----------------------------------------------------+

hour(string date)

功能：返回字符串所表达日期的小时字段

返回类型：int类型

hours_add(timestamp date, int hours)

hours_add(timestamp date, bigint hours)

功能：返回指定的日期加上若干小时后的时间

返回类型：timestamp

hours_sub(timestamp date, int hours)

hours_sub(timestamp date, bigint hours)

功能：返回指定的日期减去若干小时后的时间

返回类型：timestamp

microseconds_add(timestamp date, int microseconds)

microseconds_add(timestamp date, bigint microseconds)

功能：返回指定的日期加上若干微秒后的时间

返回类型：timestamp

microseconds_sub(timestamp date, int microseconds)

microseconds_sub(timestamp date, bigint microseconds)

功能：返回指定的日期减去若干微秒后的时间

返回类型：timestamp

minute(string date)

功能：返回字符串所表达日期的分钟字段

返回类型：int类型

minutes_add(timestamp date, int minutes)

minutes_add(timestamp date, bigint minutes)

功能：返回指定的日期加上若干分钟后的时间

返回类型：timestamp

minutes_sub(timestamp date, int minutes)

minutes_sub(timestamp date, bigint minutes)

功能：返回指定的日期减去若干分钟后的时间

返回类型：timestamp

month(string date)

功能：返回字符串所表达的日期的月份字段

返回类型：int类型

months_add(timestamp date, int months)

months_add(timestamp date, bigint months)

功能：返回指定的日期加上若干月份后的时间

返回类型：timestamp

months_sub(timestamp date, int months)

months_sub(timestamp date, bigint months)

功能：返回指定的日期减去若干月份后的时间

返回类型：timestamp

now()

功能：返回当前的日期和时间（东八区的时区）

返回类型：timestamp

second(string date)

功能：返回字符串所表达的日期的秒字段

返回类型：int 类型

seconds_add(timestamp date, int seconds)

seconds_add(timestamp date, bigint seconds)

功能：返回指定的日期加上若干秒后的时间

返回类型：timestamp

seconds_sub(timestamp date, int seconds)

seconds_sub(timestamp date, bigint seconds)

功能：返回指定的日期减去若干秒后的时间

返回类型：timestamp

subdate(timestamp startdate, int days)

subdate(timestamp startdate, bigint days)

功能：从startdate的时间减去若干天后的时间。和date_sub()函数相似，但是本函数的第一个参数是确切的TIMESTAMP，而非可以转成TIMESTAMP类型的字符串。

返回类型：timestamp

str_to_date(string str, string format)

功能：通过format指定的方式将str转化为timestamp类型，如果转化结果不对返回NULL。支持的format格式与date_format一致。

返回类型：timestamp

to_date(timestamp)

功能：返回timestamp的date域

返回类型：string类型

举例：

Plain Text

1 mysql> select now() as right_now,
2 ->   concat('The date today is ',to_date(now()),'.') as date_announcement;
3 +---------------------+-------------------------------+
4 | right_now           | date_announcement             |
5 +---------------------+-------------------------------+
6 | 2017-10-16 21:10:24 | The date today is 2017-10-16. |
7 +---------------------+-------------------------------+
8 1 row in set (0.01 sec)

unix_timestamp()

unix_timestamp(string datetime)

unix_timestamp(string datetime, string format)

unix_timestamp(timestamp datetime)

功能：返回当前时间的时间戳（相对1970年1月1日的秒数）或者从一个指定的日期和时间转换成时间戳。返回的时间戳是相对于格林尼治时区的时间戳。

返回类型：bigint类型

weeks_add(timestamp date, int weeks)

weeks_add(timestamp date, bigint weeks)

功能：返回指定的日期加上若干周后的时间

返回类型：timestamp

weekofyear(timestamp date)

功能：获得一年中的第几周

返回类型：int

weeks_add(timestamp date, int weeks)

weeks_add(timestamp date, bigint weeks)

功能：返回指定的日期加上若干周后的时间

返回类型：timestamp

weeks_sub(timestamp date, int weeks)

weeks_sub(timestamp date, bigint weeks)

功能：返回指定的日期减去若干周后的时间

返回类型：timestamp

quarter(timestamp date)

功能：返回指定的日期所属季度返回类型：int

year(string date)

功能：返回字符串所表达的日期的年字段

返回类型：int类型

years_add(timestamp date, int years)

years_add(timestamp date, bigint years)

功能：返回指定的日期加上若干年后的时间

返回类型：timestamp

years_sub(timestamp date, int years)

years_sub(timestamp date, bigint years)

功能：返回指定的日期减去若干年的时间

返回类型：timestamp

条件函数

CASE a WHEN b THEN c [WHEN d THEN e]... [ELSE f] END

功能：将表达式和多个可能的值进行比较，当匹配时返回相应的结果

返回类型：匹配后返回结果的类型

举例:

Plain Text

1 mysql> select case tiny_column when 1 then "tiny_column=1" when 2 then "tiny_column=2" end from small_table limit 2;
2 +-------------------------------------------------------------------------------+
3 | CASE`tiny_column` WHEN 1 THEN 'tiny_column=1' WHEN 2 THEN 'tiny_column=2' END |
4 +-------------------------------------------------------------------------------+
5 | tiny_column=1                                                                 |
6 | tiny_column=2                                                                 |
7 +-------------------------------------------------------------------------------+
8 2 rows in set (0.02 sec)

if(boolean condition, type ifTrue, type ifFalseOrNull)

功能：测试一个表达式，根据结果是true还是false返回相应的结果

返回类型：ifTrue表达式结果的类型

举例

Plain Text

1 mysql> select if(tiny_column = 1, "true", "false") from small_table limit 1;
2 +----------------------------------------+
3 | if(`tiny_column` = 1, 'true', 'false') |
4 +----------------------------------------+
5 | true                                   |
6 +----------------------------------------+
7 1 row in set (0.03 sec)

ifnull(type a, type isNull)

功能：测试一个表达式，如果表达式是NULL，则返回第二个参数，否则返回第一个参数。

返回类型：第一个参数的类型

举例

Plain Text

1 mysql> select ifnull(1,0);
2 +--------------+
3 | ifnull(1, 0) |
4 +--------------+
5 |            1 |
6 +--------------+
7 1 row in set (0.01 sec)
8
9 mysql> select ifnull(null,10);
10 +------------------+
11 | ifnull(NULL, 10) |
12 +------------------+
13 |               10 |
14 +------------------+
15 1 row in set (0.01 sec)

nullif(expr1,expr2)

功能：如果两个参数相等，则返回NULL。否则返回第一个参数的值。它和以下的CASE WHEN效果一样。

Plain Text

1CASE
2    WHEN expr1 = expr2 THEN NULL
3    ELSE expr1
4END

返回类型：expr1的类型或者NULL

举例

Plain Text

1 mysql> select nullif(1,1);
2 +--------------+
3 | nullif(1, 1) |
4 +--------------+
5 |         NULL |
6 +--------------+
7 1 row in set (0.00 sec)
8
9 mysql> select nullif(1,0);
10 +--------------+
11 | nullif(1, 0) |
12 +--------------+
13 |            1 |
14 +--------------+
15 1 row in set (0.01 sec)

字符串函数

ascii(string str)

功能：返回字符串第一个字符串对应的ascii 码

返回类型：int类型

concat(string a, string b...)

功能：将多个字符串连接起来

返回类型：string类型

使用说明：concat()和concat_ws()都是将一行中的多个列合成1个新的列，group_concat()是聚合函数，将不同行的结果合成1个新的列

concat_ws(string sep, string a, string b...)

功能：将第二个参数以及后面的参数连接起来，连接符为第一个参数。

返回类型：string类型

举例:

Plain Text

1 mysql> select concat_ws('a', 'b', 'c', 'd');
2 +-------------------------------+
3 | concat_ws('a', 'b', 'c', 'd') |
4 +-------------------------------+
5 | bacad                         |
6 +-------------------------------+
7 1 row in set (0.01 sec)

find_in_set(string str, string strList)

功能：返回strlist中出现第一次str的位置（从1开始计数）。strList用逗号分隔多个字符串。如果在strList中没有找到str，则返回0。

返回类型：int类型

举例:

Plain Text

1 mysql> select find_in_set("beijing", "tianji,beijing,shanghai");
2 +---------------------------------------------------+
3 | find_in_set('beijing', 'tianji,beijing,shanghai') |
4 +---------------------------------------------------+
5 |                                                 2 |
6 +---------------------------------------------------+
7 1 row in set (0.00 sec)

group_concat(string s [, string sep])

功能：该函数是类似于sum()的聚合函数，group_concat将结果集中的多行结果连接成一个字符串。第二个参数为字符串之间的连接符号，该参数可以省略。该函数通常需要和group by 语句一起使用。

返回类型：string类型

instr(string str, string substr)

功能：返回substr在str中第一次出现的位置（从1开始计数）。如果substr不在str中出现，则返回0。

返回类型：int类型

举例：

Plain Text

1 mysql> select instr('foo bar bletch', 'b');
2 +------------------------------+
3 | instr('foo bar bletch', 'b') |
4 +------------------------------+
5 |                            5 |
6 +------------------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select instr('foo bar bletch', 'z');
10 +------------------------------+
11 | instr('foo bar bletch', 'z') |
12 +------------------------------+
13 |                            0 |
14 +------------------------------+
15 1 row in set (0.01 sec)

length(string a)

功能：返回字符串的长度

返回类型：int类型

locate(string substr, string str[, int pos])

功能：返回substr在str中出现的位置（从1开始计数）。如果指定第3个参数，则从str以pos下标开始的字符串处开始查找substr出现的位置。

返回类型：int类型

举例:

Plain Text

1 mysql> select locate('bj', 'where is bj', 10);
2 +---------------------------------+
3 | locate('bj', 'where is bj', 10) |
4 +---------------------------------+
5 |                              10 |
6 +---------------------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select locate('bj', 'where is bj', 11);
10 +---------------------------------+
11 | locate('bj', 'where is bj', 11) |
12 +---------------------------------+
13 |                               0 |
14 +---------------------------------+
15 1 row in set (0.01 sec)

lower(string a)

lcase(string a)

功能：将参数中所有的字符串都转换成小写

返回类型：string类型

lpad(string str, int len, string pad)

功能：返回str中长度为len（从首字母开始算起）的字符串。如果len大于str的长度，则在str的前面不断补充pad字符，直到该字符串的长度达到len为止。如果len小于str的长度，该函数相当于截断str字符串，只返回长度为len的字符串。

返回类型：string类型

举例:

Plain Text

1 mysql> select lpad("hello", 10, 'xy');
2 +-------------------------+
3 | lpad('hello', 10, 'xy') |
4 +-------------------------+
5 | xyxyxhello              |
6 +-------------------------+
7 1 row in set (0.01 sec)

ltrim(string a)

功能：将参数中从开始部分连续出现的空格去掉。

返回类型：string类型

regexp_extract(string subject, string pattern, int index)

功能：字符串正则匹配。index为0返回整个匹配的字符串，index为1，2，……，返回第一，第二，……部分。

返回类型：string类型

举例：

Plain Text

1 mysql> select regexp_extract('AbcdBCdefGHI','.*?([[:lower:]]+)',1);
2 +--------------------------------------------------------+
3 | regexp_extract('AbcdBCdefGHI', '.*?([[:lower:]]+)', 1) |
4 +--------------------------------------------------------+
5 | def                                                    |
6 +--------------------------------------------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select regexp_extract('AbcdBCdefGHI','.*?([[:lower:]]+).*?',1);
10 +-----------------------------------------------------------+
11 | regexp_extract('AbcdBCdefGHI', '.*?([[:lower:]]+).*?', 1) |
12 +-----------------------------------------------------------+
13 | bcd                                                       |
14 +-----------------------------------------------------------+
15 1 row in set (0.01 sec)

regexp_replace(string initial, string pattern, string replacement)

功能：用replacement替换initial字符串中匹配pattern的部分。

返回类型：string类型

举例：

Plain Text

1 mysql> select regexp_replace('aaabbbaaa','b+','xyz');
2 +------------------------------------------+
3 | regexp_replace('aaabbbaaa', 'b+', 'xyz') |
4 +------------------------------------------+
5 | aaaxyzaaa                                |
6 +------------------------------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select regexp_replace('aaabbbaaa','(b+)','<\\1>');
10 +---------------------------------------------+
11 | regexp_replace('aaabbbaaa', '(b+)', '<\1>') |
12 +---------------------------------------------+
13 | aaa<bbb>aaa                                 |
14 +---------------------------------------------+
15 1 row in set (0.01 sec)
16
17 mysql> select regexp_replace('123-456-789','[^[:digit:]]','');
18 +---------------------------------------------------+
19 | regexp_replace('123-456-789', '[^[:digit:]]', '') |
20 +---------------------------------------------------+
21 | 123456789                                         |
22 +---------------------------------------------------+
23 1 row in set (0.01 sec)

repeat(string str, int n)

功能：返回字符串str重复n次的结果

返回类型：string类型

举例：

Plain Text

1 mysql> select repeat("abc", 3);
2 +------------------+
3 | repeat('abc', 3) |
4 +------------------+
5 | abcabcabc        |
6 +------------------+
7 1 row in set (0.01 sec)

reverse(string a)

功能：将字符串反转

返回类型：string类型

rpad(string str, int len, string pad)

功能：返回str中长度为len（从首字母开始算起）的字符串。如果len大于str的长度，则在str 的后面不断补充pad字符，直到该字符串的长度达到len 为止。如果len小于str的长度，该函数相当于截断str字符串，只返回长度为len的字符串。

返回类型：string类型

举例:

Plain Text

1 mysql> select rpad("hello", 10, 'xy');  
2 +-------------------------+
3 | rpad('hello', 10, 'xy') |
4 +-------------------------+
5 | helloxyxyx              |
6 +-------------------------+
7 1 row in set (0.00 sec)

rtrim(string a)

功能：将参数中从右侧部分部分连续出现的空格去掉。

返回类型：string类型

space(int n)

功能：返回n个空格的字符串

返回类型：string类型

strleft(string a, int num_chars)

功能：返回字符串中最左边的num_chars个字符。

返回类型：string类型

strright(string a, int num_chars)

功能：返回字符串中最右边的num_chars个字符。

返回类型：string类型

substr(string a, int start [, int len])

substring(string a, int start[, int len])

功能：求子串函数，返回第一个参数描述的字符串中从start开始长度为len的部分字符串。首字母的下标为1。

返回类型：string类型

trim(string a)

功能：将参数中右侧部分连续出现的空格和左侧部分连续出现的空格都去掉。该函数的效果和同时使用ltrim()和rtrim()的效果是一样的。

返回类型：string类型

upper(string a)

ucase(string a)

功能：将字符串所有字母都转换成大写。

返回类型：string类型

聚合函数

AVG函数

功能：该聚合函数返回集合中的平均数。该函数只有1个参数，该参数可以是数字类型的列，返回值是数字的函数，或者计算结果是数字的表达式。包含NULL值的行将被忽略。如果该表是空的或者AVG 的参数都是NULL，则该函数返回NULL。当查询指定使用GROUP BY从句时，则每个group by的值都会返回1条结果。

返回类型： double类型

COUNT函数

功能：该聚合函数返回满足要求的行的数目，或者非NULL行的数目。COUNT(*) 会计算包含NULL 值的行。COUNT(column_name)仅会计算非NULL值的行。用户可以同时使用COUNT函数和DISTINCT操作符，count(distinct col_name...)会先对数据去重，然后再计算多个列的组合出现的次数。

返回类型：int类型

举例:

Plain Text

1 mysql> select count(distinct tiny_column, short_column) from small_table;
2 +-----------------------------------------------+
3 | count(DISTINCT `tiny_column`, `short_column`) |
4 +-----------------------------------------------+
5 |                                             2 |
6 +-----------------------------------------------+
7 1 row in set (0.08 sec)

MAX函数

功能：该聚合函数返回集合中的最大值。该函数和min函数的功能相反。该函数只有1个参数，该参数可以是数字类型的列，返回值是数字的函数，或者计算结果是数字的表达式。包含NULL值的行将被忽略。如果该表是空的或者MAX的参数都是NULL，则该函数返回NULL。当查询指定使用GROUP BY从句时，则每个group by的值都会返回1条结果。

返回类型：和输入参数相同的类型。

MIN函数

功能：该聚合函数返回集合中的最小值。该函数和max函数的功能相反。该函数只有1个参数，该参数可以是数字类型的列，返回值是数字的函数，或者计算结果是数字的表达式。包含NULL 值的行将被忽略。如果该表是空的或者MIN 的参数都是NULL，则该函数返回NULL。当查询指定使用GROUP BY从句时，则每个group by的值都会返回1条结果。

返回类型：和输入参数相同的类型。

SUM函数

功能：该聚合函数返回集合中所有值的和。该函数只有1个参数，该参数可以是数字类型的列，返回值是数字的函数，或者计算结果是数字的表达式。包含NULL值的行将被忽略。如果该表是空的或者MIN的参数都是NULL，则该函数返回NULL。当查询指定使用GROUP BY从句时，则每个group by的值都会返回1条结果。

返回类型：如果参数整型，则返回BIGINT，如果参数是浮点型则返回double类型

GROUP_CONCAT函数

功能：该聚合函数会返回1个字符串，该字符串是集合中所有字符串连接起来形成的新字符串。如果用户指定分隔符，则分隔符用来连接两个相邻行的字符串。

返回类型：string类型

使用说明：默认情况下，该函数返回1个覆盖所有结果集的字符串。当查询指定使用group by 从句时，则每个group by的值都会返回1条结果。concat()和concat_ws()都是将一行中的多个列合成1个新的列，group_concat()是聚合函数，将不同行的结果合成1个新的列。

方差函数

语法：

Plain Text

1 VARIANCE | VAR[IANCE]_SAMP | VAR[IANCE]_POP

功能：该类聚合函数返回一组数的方差。这是一个数学属性，它表示值与平均值之间的距离。它作用于数值类型。VARIANCE_SAMP()和VARIANCE_POP()分别计算样本方差和总体方差，VARIANCE()是VARIANCE_SAMP()的别名。VAR_SAMP()和VAR_POP()分别是VARIANCE_SAMP()和VARIANCE_POP()是别名。

返回类型：double类型

标准差函数

语法：

Plain Text

1 STDDEV | STDDEV_SAMP | STDDEV_POP

功能：该类聚合函数返回一组数的标准差。它作用于数值类型。STDDEV_POP()和STDDEV_SAMP()分别计算总体标准差和样本标准差。STDDEV()是STDDEV_SAMP()的别名。

返回类型：double类型

json解析函数

doris目前支持3个json解析函数

get_json_int（string，string）
get_json_string（string，string）
get_json_double（string，string）

其中第一个参数为json字符串，第二个参数为json内的路径

举例：

Plain Text

1 mysql> select get_json_int('{"col1":100, "col2":"string", "col3":1.5}', "$.col1");
2 +---------------------------------------------------------------------+
3 | get_json_int('{"col1":100, "col2":"string", "col3":1.5}', '$.col1') |
4 +---------------------------------------------------------------------+
5 |                                                                 100 |
6 +---------------------------------------------------------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select get_json_string('{"col1":100, "col2":"string", "col3":1.5}', "$.col2");   
10 +------------------------------------------------------------------------+
11 | get_json_string('{"col1":100, "col2":"string", "col3":1.5}', '$.col2') |
12 +------------------------------------------------------------------------+
13 | string                                                                 |
14 +------------------------------------------------------------------------+
15 1 row in set (0.01 sec)
16
17 mysql> select get_json_double('{"col1":100, "col2":"string", "col3":1.5}', "$.col3");      
18 +------------------------------------------------------------------------+
19 | get_json_double('{"col1":100, "col2":"string", "col3":1.5}', '$.col3') |
20 +------------------------------------------------------------------------+
21 |                                                                    1.5 |
22 +------------------------------------------------------------------------+
23 1 row in set (0.01 sec)

HLL函数

HLL是基于HyperLogLog算法的工程实现，用于保存HyperLogLog计算过程的中间结果，它只能作为表的value列类型、通过聚合来不断的减少数据量，以此来实现加快查询的目的，基于它得到的是一个估算结果，误差大概在1%左右，hll列是通过其它列或者导入数据里面的数据生成的，导入的时候通过hll_hash函数来指定数据中哪一列用于生成hll列，它常用于替代count distinct，通过结合rollup在业务上用于快速计算uv等

HLL_UNION_AGG(hll)

此函数为聚合函数，用于计算满足条件的所有数据的基数估算。

HLL_CARDINALITY(hll)

此函数用于计算单条hll列的基数估算

HLL_HASH(column_name)

生成HLL列类型，用于insert或导入的时候，导入的使用见相关说明

example:(仅为说明使用方式)

1、首先创建一张含有hll列的表：

Plain Text

1    create table test(      
2    time date,      
3    id int,         
4    name char(10),      
5    province char(10),      
6    os char(1),         
7    set1 hll hll_union,         
8    set2 hll hll_union)         
9    distributed by hash(id) buckets 32;
10

2、导入数据，导入的方式见相关help mini load

Plain Text

1（1）使用表中的列生成hll列  
2    curl --location-trusted -uname:password -T data http://host/api/test_db/test/_load?label=load_1\&hll=set1,id:set2,name      
3（2）使用数据中的某一列生成hll列        
4    curl --location-trusted -uname:password -T data http://host/api/test_db/test/_load?label=load_1\&hll=set1,cuid:set2,os\&columns=time,id,name,province,sex,cuid,os
5

3、聚合数据，常用方式3种：（如果不聚合直接对base表查询，速度可能跟直接使用ndv速度差不多）

Plain Text

1（1）创建一个rollup，让hll列产生聚合，  
2    alter table test add rollup test_rollup(date, set1);        
3（2）创建另外一张专门计算uv的表，然后insert数据）   
4    create table test_uv(       
5    time date,      
6    uv_set hll hll_union)       
7    distributed by hash(id) buckets 32;     
8    insert into test_uv select date, set1 from test;        
9（3）创建另外一张专门计算uv的表，然后insert并通过hll_hash根据test其它非hll列生成hll列                       
10    create table test_uv(       
11    time date,      
12    id_set hll hll_union)       
13    distributed by hash(id) buckets 32;     
14    insert into test_uv select date, hll_hash(id) from test;
15

4、查询，hll列不允许直接查询它的原始值，可以通过配套的函数进行查询

Plain Text

1（1）求总uv     
2    select HLL_UNION_AGG(uv_set) from test_uv;          
3（2）求每一天的uv       
4    select HLL_CARDINALITY(uv_set) from test_uv;

分析函数（窗口函数）

分析函数介绍

分析函数是一类特殊的内置函数。和聚合函数类似，分析函数也是对于多个输入行做计算得到一个数据值。不同的是，分析函数是在一个特定的窗口内对输入数据做处理，而不是按照group by来分组计算。每个窗口内的数据可以用over()从句进行排序和分组。分析函数会对结果集的每一行计算出一个单独的值，而不是每个group by分组计算一个值。这种灵活的方式允许用户在select从句中增加额外的列，给用户提供了更多的机会来对结果集进行重新组织和过滤。分析函数只能出现在select列表和最外层的order by从句中。在查询过程中，分析函数会在最后生效，就是说，在执行完join，where和group by等操作之后再执行。分析函数在金融和科学计算领域经常被使用到，用来分析趋势、计算离群值以及对大量数据进行分桶分析等。

分析函数的语法：

Plain Text

1function(args) OVER(partition_by_clause order_by_clause [window_clause])    
2partition_by_clause ::= PARTITION BY expr [, expr ...]    
3order_by_clause ::= ORDER BY expr [ASC | DESC] [, expr [ASC | DESC] ...]

window_clause: 见后面Window Clause

Function

目前支持的Function包括AVG(), COUNT(), DENSE_RANK(), FIRST_VALUE(), LAG(), LAST_VALUE(), LEAD(), MAX(), MIN(), RANK(), ROW_NUMBER()和SUM()。

PARTITION BY从句

Partition By从句和Group By类似。它把输入行按照指定的一列或多列分组，相同值的行会被分到一组。

ORDER BY从句

Order By从句和外层的Order By基本一致。它定义了输入行的排列顺序，如果指定了Partition By，则Order By定义了每个Partition分组内的顺序。与外层Order By的唯一不同点是，OVER从句中的Order By n（n是正整数）相当于不做任何操作，而外层的Order By n表示按照第n列排序。

举例:

这个例子展示了在select列表中增加一个id列，它的值是1，2，3等等，顺序按照events表中的date_and_time列排序。

Plain Text

1SELECT   
2row_number() OVER (ORDER BY date_and_time) AS id,   
3c1, c2, c3, c4   
4FROM events;

Window从句

Window从句用来为分析函数指定一个运算范围，以当前行为准，前后若干行作为分析函数运算的对象。Window从句支持的方法有：AVG(), COUNT(), FIRST_VALUE(), LAST_VALUE()和SUM()。对于 MAX()和MIN(), window从句可以指定开始范围UNBOUNDED PRECEDING

语法:

Plain Text

1ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

举例：

假设我们有如下的股票数据，股票代码是JDR，closing price是每天的收盘价。

Plain Text

1create table stock_ticker (stock_symbol string, closing_price decimal(8,2), closing_date timestamp);    
2...load some data...    
3select * from stock_ticker order by stock_symbol, closing_date
4 | stock_symbol | closing_price | closing_date |
5 |--------------|---------------|---------------------|
6 | JDR | 12.86 | 2014-10-02 00:00:00 |
7 | JDR | 12.89 | 2014-10-03 00:00:00 |
8 | JDR | 12.94 | 2014-10-04 00:00:00 |
9 | JDR | 12.55 | 2014-10-05 00:00:00 |
10 | JDR | 14.03 | 2014-10-06 00:00:00 |
11 | JDR | 14.75 | 2014-10-07 00:00:00 |
12 | JDR | 13.98 | 2014-10-08 00:00:00 |

这个查询使用分析函数产生moving_average这一列，它的值是3天的股票均价，即前一天、当前以及后一天三天的均价。第一天没有前一天的值，最后一天没有后一天的值，所以这两行只计算了两天的均值。这里Partition By没有起到作用，因为所有的数据都是JDR的数据，但如果还有其他股票信息，Partition By会保证分析函数值作用在本Partition之内。

Plain Text

1select stock_symbol, closing_date, closing_price,    
2avg(closing_price) over (partition by stock_symbol order by closing_date    
3rows between 1 preceding and 1 following) as moving_average    
4from stock_ticker;
5 | stock_symbol | closing_date | closing_price | moving_average |
6 |--------------|---------------------|---------------|----------------|
7 | JDR | 2014-10-02 00:00:00 | 12.86 | 12.87 |
8 | JDR | 2014-10-03 00:00:00 | 12.89 | 12.89 |
9 | JDR | 2014-10-04 00:00:00 | 12.94 | 12.79 |
10 | JDR | 2014-10-05 00:00:00 | 12.55 | 13.17 |
11 | JDR | 2014-10-06 00:00:00 | 14.03 | 13.77 |
12 | JDR | 2014-10-07 00:00:00 | 14.75 | 14.25 |
13 | JDR | 2014-10-08 00:00:00 | 13.98 | 14.36 |

Function使用举例

本节介绍PALO中可以用作分析函数的方法。

AVG()

语法：

AVG([DISTINCT | ALL] expression) [OVER (analytic_clause)]

举例：

计算当前行和它前后各一行数据的x平均值

Plain Text

1select x, property,    
2avg(x) over    
3(   
4partition by property    
5order by x    
6rows between 1 preceding and 1 following    
7) as 'moving average'    
8from int_t where property in ('odd','even');
9 | x | property | moving average |
10 |----|----------|----------------|
11 | 2 | even | 3 |
12 | 4 | even | 4 |
13 | 6 | even | 6 |
14 | 8 | even | 8 |
15 | 10 | even | 9 |
16 | 1 | odd | 2 |
17 | 3 | odd | 3 |
18 | 5 | odd | 5 |
19 | 7 | odd | 7 |
20 | 9 | odd | 8 |

COUNT()

语法：

Plain Text

1COUNT([DISTINCT | ALL] expression) [OVER (analytic_clause)]

举例：

计算从当前行到第一行x出现的次数。

Plain Text

1select x, property,   
2count(x) over   
3(   
4partition by property    
5order by x    
6rows between unbounded preceding and current row    
7) as 'cumulative total'    
8from int_t where property in ('odd','even');
9 | x | property | cumulative count |
10 |----|----------|------------------|
11 | 2 | even | 1 |
12 | 4 | even | 2 |
13 | 6 | even | 3 |
14 | 8 | even | 4 |
15 | 10 | even | 5 |
16 | 1 | odd | 1 |
17 | 3 | odd | 2 |
18 | 5 | odd | 3 |
19 | 7 | odd | 4 |
20 | 9 | odd | 5 |

DENSE_RANK()

DENSE_RANK()函数用来表示排名，与RANK()不同的是，DENSE_RANK()不会出现空缺数字。比如，如果出现了两个并列的1，DENSE_RANK()的第三个数仍然是2，而RANK()的第三个数是3。

语法：

Plain Text

1DENSE_RANK() OVER(partition_by_clause order_by_clause)

举例：

下例展示了按照property列分组对x列排名：

Plain Text

1 select x, y, dense_rank() over(partition by x order by y) as rank from int_t;
2 | x | y | rank |
3 |----|------|----------|
4 | 1 | 1 | 1 |
5 | 1 | 2 | 2 |
6 | 1 | 2 | 2 |
7 | 2 | 1 | 1 |
8 | 2 | 2 | 2 |
9 | 2 | 3 | 3 |
10 | 3 | 1 | 1 |
11 | 3 | 1 | 1 |
12 | 3 | 2 | 2 |

FIRST_VALUE()

FIRST_VALUE()返回窗口范围内的第一个值。

语法：

Plain Text

1FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])

举例：

我们有如下数据

Plain Text

1 select name, country, greeting from mail_merge;
2 | name | country | greeting |
3 |---------|---------|--------------|
4 | Pete | USA | Hello |
5 | John | USA | Hi |
6 | Boris | Germany | Guten tag |
7 | Michael | Germany | Guten morgen |
8 | Bjorn | Sweden | Hej |
9 | Mats | Sweden | Tja |

使用FIRST_VALUE()，根据country分组，返回每个分组中第一个greeting的值：

Plain Text

1select country, name,    
2first_value(greeting)    
3over (partition by country order by name, greeting) as greeting from mail_merge;
4| country | name | greeting |
5|---------|---------|-----------|
6| Germany | Boris | Guten tag |
7| Germany | Michael | Guten tag |
8| Sweden | Bjorn | Hej |
9| Sweden | Mats | Hej |
10| USA | John | Hi |
11| USA | Pete | Hi |

LAG()

LAG()方法用来计算当前行向前数若干行的值。

语法：

Plain Text

1LAG (expr, offset, default) OVER (partition_by_clause order_by_clause)

举例：

计算前一天的收盘价

Plain Text

1select stock_symbol, closing_date, closing_price,    
2lag(closing_price,1, 0) over (partition by stock_symbol order by closing_date) as "yesterday closing"   
3from stock_ticker   
4order by closing_date;
5| stock_symbol | closing_date | closing_price | yesterday closing |
6|--------------|---------------------|---------------|-------------------|
7| JDR | 2014-09-13 00:00:00 | 12.86 | 0 |
8| JDR | 2014-09-14 00:00:00 | 12.89 | 12.86 |
9| JDR | 2014-09-15 00:00:00 | 12.94 | 12.89 |
10| JDR | 2014-09-16 00:00:00 | 12.55 | 12.94 |
11| JDR | 2014-09-17 00:00:00 | 14.03 | 12.55 |
12| JDR | 2014-09-18 00:00:00 | 14.75 | 14.03 |
13| JDR | 2014-09-19 00:00:00 | 13.98 | 14.75

LAST_VALUE()

LAST_VALUE()返回窗口范围内的最后一个值。与FIRST_VALUE()相反。

语法：

Plain Text

1LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])

使用FIRST_VALUE()举例中的数据：

Plain Text

1select country, name,    
2last_value(greeting)   
3over (partition by country order by name, greeting) as greeting   
4from mail_merge;
5| country | name | greeting |
6|---------|---------|--------------|
7| Germany | Boris | Guten morgen |
8| Germany | Michael | Guten morgen |
9| Sweden | Bjorn | Tja |
10| Sweden | Mats | Tja |
11| USA | John | Hello |
12| USA | Pete | Hello

LEAD()

LEAD()方法用来计算当前行向后数若干行的值。

语法：

Plain Text

1LEAD (expr, offset, default]) OVER (partition_by_clause order_by_clause)

举例：

计算第二天的收盘价对比当天收盘价的走势，即第二天收盘价比当天高还是低。

Plain Text

1select stock_symbol, closing_date, closing_price,    
2case   
3(lead(closing_price,1, 0)   
4over (partition by stock_symbol order by closing_date)-closing_price) > 0   
5when true then "higher"   
6when false then "flat or lower"    
7end as "trending"   
8from stock_ticker    
9order by closing_date;
10| stock_symbol | closing_date | closing_price | trending |
11|--------------|---------------------|---------------|---------------|
12| JDR | 2014-09-13 00:00:00 | 12.86 | higher |
13| JDR | 2014-09-14 00:00:00 | 12.89 | higher |
14| JDR | 2014-09-15 00:00:00 | 12.94 | flat or lower |
15| JDR | 2014-09-16 00:00:00 | 12.55 | higher |
16| JDR | 2014-09-17 00:00:00 | 14.03 | higher |
17| JDR | 2014-09-18 00:00:00 | 14.75 | flat or lower |
18| JDR | 2014-09-19 00:00:00 | 13.98 | flat or lower |

MAX()

语法：

Plain Text

1MAX([DISTINCT | ALL] expression) [OVER (analytic_clause)]

举例：

计算从第一行到当前行之后一行的最大值

Plain Text

1select x, property,   
2max(x) over    
3(   
4order by property, x    
5rows between unbounded preceding and 1 following    
6) as 'local maximum'    
7from int_t where property in ('prime','square');
8| x | property | local maximum |
9|---|----------|---------------|
10| 2 | prime | 3 |
11| 3 | prime | 5 |
12| 5 | prime | 7 |
13| 7 | prime | 7 |
14| 1 | square | 7 |
15| 4 | square | 9 |
16| 9 | square | 9 |

MIN()

语法：

Plain Text

1MIN([DISTINCT | ALL] expression) [OVER (analytic_clause)]

举例：

计算从第一行到当前行之后一行的最小值

Plain Text

1select x, property,   
2min(x) over    
3(    
4order by property, x desc    
5rows between unbounded preceding and 1 following   
6) as 'local minimum'   
7from int_t where property in ('prime','square');
8| x | property | local minimum |
9|---|----------|---------------|
10| 7 | prime | 5 |
11| 5 | prime | 3 |
12| 3 | prime | 2 |
13| 2 | prime | 2 |
14| 9 | square | 2 |
15| 4 | square | 1 |
16| 1 | square | 1 |

RANK()

RANK()函数用来表示排名，与DENSE_RANK()不同的是，RANK()会出现空缺数字。比如，如果出现了两个并列的1， RANK()的第三个数就是3，而不是2。

语法：

Plain Text

1RANK() OVER(partition_by_clause order_by_clause)

举例：

根据x列进行排名

Plain Text

1select x, y, rank() over(partition by x order by y) as rank from int_t;
2| x | y | rank |
3|----|------|----------|
4| 1 | 1 | 1 |
5| 1 | 2 | 2 |
6| 1 | 2 | 2 |
7| 2 | 1 | 1 |
8| 2 | 2 | 2 |
9| 2 | 3 | 3 |
10| 3 | 1 | 1 |
11| 3 | 1 | 1 |
12| 3 | 2 | 3 |

ROW_NUMBER()

为每个Partition的每一行返回一个从1开始连续递增的整数。与RANK()和DENSE_RANK()不同的是，ROW_NUMBER()返回的值不会重复也不会出现空缺，是连续递增的。

语法：

Plain Text

1ROW_NUMBER() OVER(partition_by_clause order_by_clause)

举例：

Plain Text

1select x, y, row_number() over(partition by x order by y) as rank from int_t;
2| x | y | rank |
3|---|------|----------|
4| 1 | 1 | 1 |
5| 1 | 2 | 2 |
6| 1 | 2 | 3 |
7| 2 | 1 | 1 |
8| 2 | 2 | 2 |
9| 2 | 3 | 3 |
10| 3 | 1 | 1 |
11| 3 | 1 | 2 |
12| 3 | 2 | 3 |

SUM()

语法：

Plain Text

1SUM([DISTINCT | ALL] expression) [OVER (analytic_clause)]

举例：

按照property进行分组，在组内计算当前行以及前后各一行的x列的和。

Plain Text

1select x, property,   
2sum(x) over    
3(   
4partition by property   
5order by x   
6rows between 1 preceding and 1 following    
7) as 'moving total'    
8from int_t where property in ('odd','even');
9| x | property | moving total |
10|----|----------|--------------|
11| 2 | even | 6 |
12| 4 | even | 12 |
13| 6 | even | 18 |
14| 8 | even | 24 |
15| 10 | even | 18 |
16| 1 | odd | 4 |
17| 3 | odd | 9 |
18| 5 | odd | 15 |
19| 7 | odd | 21 |
20| 9 | odd | 16 |

注释

白名单管理

数据仓库 PALO

数据仓库 PALO

内置函数

数学函数

位操作函数

类型转换函数

日期和时间函数

条件函数

字符串函数

聚合函数

json解析函数

HLL函数

分析函数（窗口函数）

分析函数介绍

Function使用举例

AVG()

COUNT()

DENSE_RANK()

FIRST_VALUE()

LAG()

LAST_VALUE()

LEAD()

MAX()

MIN()

RANK()

ROW_NUMBER()

SUM()