上一篇博客我们介绍了SAP HANA 提供的4类SQL函数:1.数据类型转换函数 2.日期函数 3.全文函数 4.数值处理函数。这一篇博客里我们接着介绍剩下的3类函数:1.字符串函数 2.窗口函数 3.杂项函数。本文的测试案例所使用的SAP HANA版本为SAP HANA SPS7 Revision 70.00。
1.字符串函数
SAP HANA 提供了数量众多的字符串函数以方便用户，包括
ASCII BINTOSTR CHAR CONCAT LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM NCHAR REPLACE RIGHT RPAD RTRIM STRTOBIN SUBSTR_AFTER SUBSTR_BEFORE SUBSTRING TRIM UCASE UNICODE UPPER |
其中有些函数我们在MySQL里也见过，有些看名字就知道是做什么用的及如何使用，比如CONCAT用于字符串连接，LENGTH返回一个字符串的长度，UPPER将字符串转换为大写，TRIM去掉字符串两边的空白字符，LTRIM只去掉左边的空白字符，RTRIM只去掉右边的空白字符等，LCASE将字符串转换为小写，等等。我们只介绍值得注意的字符串函数。
ASCII函数返回一个ASCII字符串的首字母的ASCII码值。
BINTOSTR返回VARBINARY类型的字符串对应的字符串。注意这个函数在SAP HANA SP6不支持。
相反地有STRTOBIN函数，STRTOBIN (str, codepage)将字符串中所有字符转换为codepage对应的编码表示。
CHAR (n) 将ASCII值转变为对应的字符。和CHAR类似，NCHAR是一个更通用的函数，NCHAR(n)返回整数n对应的unicode字符。
LEFT (str, n)返回字符串str左边开始的n个字符的子串。RIGHT(str, n)和此类似。
LOCATE (haystack, needle) 返回字符串needle在haystack的出现位置，注意下标位置从1而非从0开始，0用于表示未找到的返回值。
LPAD (str, n [, pattern]) 有一个可选参数，如果未提供，则用空格填充字符串str的左边，否则循环使用pattern填充，直到整个字符串的长度为n。
RPAD和LAPD类似，只是方向相反。
LTRIM(str [, remove_set]) 有个可选参数remove_set，若未指定，则删除字符串左边的前导空格，直到遇到非空格位置；若指定remove_set，则从左边开始一直删除字符串中在remove_set的字符直到一个不在remove_set中的字符终止。
SUBSTR_AFTER (str, pattern) 返回pattern出现之后的str子串。如果没有找到，返回空串。如果pattern为空，返回str。如果pattern为NULL，返回NULL。
SUBSTR_BEFORE (str, pattern)功能与此类似。
其它函数请参考SAP HANA SQL and System View Reference文档。
2. 窗口函数
窗口函数使得用户能够将查询的结果集分成由不同行构成的组，这样的划分被称为窗口划分，窗口函数由此得名。窗口划分在OVER子句中通过一个或多个表达式指定。
其中包括
RANK DENSE_RANK ROW_NUMBER PEERCENT_RANK CUME_DIST LEAD LAG NTILE FIRST_VALUE LAST_VALUE NTH_VALUE |
为了示例其用法，我们先建一个表T，其中包括class、val和offset三个字段。
create table T (class char(10), val int, offset int); insert into T values('A', 1, 1); insert into T values('A', 3, 3); insert into T values('A', 5, null); insert into T values('A', 5, 2); insert into T values('A', 10, 0); insert into T values('B', 1, 3); insert into T values('B', 1, 1); insert into T values('B', 7, 1); |
然后我们使用ROW_NUMBER和RANK函数。其中RANK函数返回一个一行在其划分中的排序，从1开始，ROW_NUMBER返回某行在划分中的行号，也从1开始。
select class, val, ROW_NUMBER() over (partition by class order by val) as row_num, RANK() over (partition by class order by val) as rank, DENSE_RANK() over (partition by class order by val) as dense_rank from T; |
比如以上查询，按class值划分，不同的class在不同分组中，分组中的行按val排序，所以CLASS为A，VAL为1的行排在分组中第一，因此ROW_NUM和RANK都是1，VAL相同的RANK相同，但是ROW_NUMBER不同。
另一个例子，SQL如下，查询结果如下图所示。
select class, val, offset, COUNT(*) over (partition by class) as c1, COUNT(offset) over (partition by class) as c2, COUNT(*) over (partition by class order by val) as c3, COUNT(offset) over (partition by class order by val) as c4, MAX(val) over (partition by class) as m1, MAX(val) over (partition by class order by val) as m2 from T; |
关于其他函数的使用请参考SAP HANA SQL and System View Reference。
3.杂项函数
包含了一些没法分在其它类别的实用函数，包括
COALESCE CONVERT_CURRENCY CURRENT_CONNECTION CURRENT_SCHEMA CURRENT_TRANSACTION_ISOLATION_LEVEL CURRENT_USER GREATEST GROUPING GROUPING_ID HASH_SHA256 IFNULL LEAST MAP NULLIF SESSION_CONTEXT SESSION_USER SYSUUID |
其中有几个和判断参数是否和NULL有关，如
COALESCE (expression_list) 返回expression_list中首个非NULL的结果，如果都是NULL则返回NULL。
IFNULL (exp1, exp2)返回exp1和exp2中的非NULL值。如果exp1非NULL，返回exp1；否则如果exp2非NULL，返回exp2；否则返回NULL。
NULLIF (exp1, exp2)比较exp1是否和exp2相等，若相等则返回NULL，否则返回exp1。
另有几个和当前连接信息有关的函数。
CURRENT_CONNECTION 返回当前连接的ID。
CURRENT_SCHEMA 返回当前的SCHEMA名称，注意，调用的时候不需要加括号，以下CURRENT_TRANSACTION_ISOLATION_LEVEL和CURRENT_USER也是如此。CURRENT_TRANSACTION_ISOLATION_LEVEL 返回当前的事物隔离级别，如READ COMMITTED等。CURRENT_USER 返回登录的当前用户名。
GREATEST返回几个数或者字符串中的最大值，字符串按字典序比较，参数个数可变。
LEAST类似GREAEST，不过返回几个数或者字符串中的最小值。
GROUPING(column) 用于判断指定column是否用于grouping
HASH_SHA256 (<argument> [{, <argument>}...]) 可用于计算一个字符串的哈希码，或者计算几个字符串合起来的哈希码。
MAP (<exp>, <exp1>, <r1> [{, <exp2>, <r2>}...] [, default]) 类似SQL中的CASE WHEN结构，如果exp的值等于exp1，返回r1，如果和exp2相等，返回r2，…，否则返回default。如果没有指定default，则返回NULL。
SESSION_CONTEXT(session_variable) 返回由session_variable指定的会话变量对应的会话值。会话变量既可以是预定义的也可以是用户定义的。定义会话变量可通过SET SESSION <variable_name>=<value>。
SESSION_USER返回当前会话的用户名。
SYSUUID返回一个全局唯一标识符，可用于生成唯一键。
在这一篇日志里，我们介绍了SAP HANA的SQL函数里的字符串处理函数、窗口函数、杂项函数。至此，我们关于SAP HANA的SQL函数的介绍就全部结束了，如果对于细节还有不清楚的，可以参考SAP HANA SQL and System View Reference文档。
想获取更多SAP HANA学习资料或有任何疑问，请关注新浪微博@HANAGeek！我们欢迎你的加入！
转载本文章请注明作者和出处http://scn.sap.com/community/chinese/hana/blog/2014/04/02/sap-hana-sql-%E5%87%BD%E6%95%B0%E7%AE%80%E...，请勿用于任何商业用途。
- SAP Managed Tags:
