SAPHANA学习（14）：SQL Function(N)

2023-12-14 19:34:45

100.NCHAR

NCHAR(<number>)

将数字转换为Union编码字符

SELECT NCHAR(65) FROM DUMMY;

101.NEWUID

在SAP HANA数据库创建唯一标识符

SELECT NEWUID() FROM DUMMY;

102.

NORMALIZE( <character_value_expression> [ , <normalized_format> ] )

<character_value_expression>:VARCHAR,NVARCHAR

<normalized_format> ::= NFC | NFD | NFKC | NFKD

--不存在
--SELECT NORMALIZE(‘?‘) FROM DUMMY;

103.NTH_VALUE

Aggregate function:

NTH_VALUE( <expression>, <position> <order_by_clause> )

Window function:

NTH_VALUE( <expression>, <position> <order_by_clause> ) <window_specification>

<order_by_clause> ::= ORDER BY <order_by_expression> [, <order_by_expression> [,...] ]

<order_by_expression> ::=

<column_name> [ <collate_clause> ] [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

| <column_position> [ <collate_clause> ] [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

<collate_clause> ::= COLLATE <collation_name>

NTILE( <number_of_buckets> ) <window_specification>

--返回根据COL2排序后，COL1的第二个值
SELECT NTH_VALUE(COL1,2 ORDER BY COL2) FROM TEST_DECIMAL;

SELECT "CLASS","VAL","OFFSET",
       NTILE(3) OVER (PARTITION BY "CLASS" ORDER BY "VAL") AS "NTILE", 
       FIRST_VALUE("VAL") OVER (PARTITION BY "CLASS" ORDER BY "VAL") AS "FIRST", 
       LAST_VALUE("VAL") OVER (PARTITION BY "CLASS" ORDER BY "VAL") AS "LAST", 
       NTH_VALUE("VAL",4) OVER (PARTITION BY "CLASS" ORDER BY "VAL") AS "NTH"
  FROM TEST_RANK;

104.NULLIF

NULLIF(<expression1>, <expression2>)

比较<exp1>,<exp2>是否相等，相等返回null;

如果不相等，返回<exp1>;

SELECT NULLIF(‘diff‘, ‘same‘) FROM DUMMY;

105.NUMTOHEX

NUMTOHEX(<integer> [, <integer>])

返回hex类型数据

--不存在
--SELECT NUMTOHEX(-1) FROM DUMMY;
--SELECT NUMTOHEX(1323) FROM DUMMY;