SAPHANA学习(17):SQL Function(R)

 /*

112. RAND

RAND()

返回double类型伪随机数,不可以用于安全和加密

 

RANDOM_PARTITION(<training_set_size>, <validation_set_size>, <test_set_size>, <seed>) <window_specification>

随机划分集合的三个子集称为训练、验证和测试。这三个子集的并集可能不是完整的初始数据集。

<training_set_size>, <validation_set_size>,<test_set_size>可以是数值,指定对应集合大小;

<seed>指定随机数生成的种子。设置为零时,<seed>将使用当前时间初始化。

 

RAND_SECURE()

返回double类型伪随机数,可以用于安全和加密

*/

SELECT RAND() FROM DUMMY;
SELECT *,RANDOM_PARTITION(0.5, 0.2, 0.3, 0) OVER (PARTITION BY CLASS ORDER BY VAL) AS part_num FROM TEST_RANK;
--随机划分子集
SELECT *,RANDOM_PARTITION(3, 2, 3, 0) OVER (ORDER BY VAL) AS part_num FROM TEST_RANK;
SELECT RAND_SECURE() from dummy;

/*

113. RECORD_COMMIT_TIMESTAMP

RECORD_COMMIT_TIMESTAMP(<table_name_or_alias>)

返回给定表的指定行的提交时间戳。

*/

--不存在?
--CREATE COLUMN TABLE TEST_RECORD_COMMIT(A int) RECORD COMMIT TIMESTAMP;
--SELECT RECORD_COMMIT_TIMESTAMP(TEST_RECORD_COMMIT) FROM TEST_RECORD_COMMIT;

/*

114. RECORD_ID

RECORD_ID( <table> )

为结果的每一行返回一个具有唯一BIGINT值的结果集。这些值对于当前事务仍然有效。

*/

--不存在
--SELECT RECORD_ID("TEST") AS RECORD_ID FROM "TEST";

/*

115. REPLACE

REPLACE(<original_string>, <search_string>, <replace_string>)

字符串替换

 

REPLACE_REGEXPR(<pattern> [ FLAG <flag> ]

  IN <regex_subject_string>

  [ WITH <replacement_string> ]

  [ FROM <start_position> ]

  [ OCCURRENCE <regex_replace_occurrence> ])

替换正则表达式匹配的字符串

<pattern> ::= !!Perl Compatible Regular Expression

正则表达式

<flag> ::= ‘i‘ | ‘m‘ | ‘s‘ | ‘x‘

i:大小写不敏感匹配;

m: Enables multiline mode

s:.匹配任意字符;

x:Permits whitespace and comments in the pattern

<regex_subject_string> ::= <string_literal>

查询的字符串

<replacement_string> ::= <string_literal>

替换的字符串

<start_position> ::= <numeric_literal>

开始位置

<regex_replace_occurrence> ::= <numeric_literal> | ALL

替换次数,非负整数 or ALL

*/

SELECT REPLACE (DOWNGRADE DOWNWARD,‘‘, UP) FROM DUMMY;
SELECT REPLACE (DOWNGRADE DOWNWARD,‘‘, ‘‘) FROM DUMMY;
SELECT REPLACE (‘‘,‘‘, UP) FROM DUMMY;
SELECT REPLACE (DOWNGRADE DOWNWARD,DOWN, UP) FROM DUMMY;

SELECT REPLACE_REGEXPR(([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2}) IN 2014-04-01
 WITH \3/\2/\1 OCCURRENCE ALL) FROM DUMMY;

/*

116. RESULT_CACHE_ID

RESULT_CACHE_ID()

Returns the cache ID of a result cache entry.

 

RESULT_CACHE_REFRESH_TIME()

Returns the last cache refresh time of a result cache entry.

*/

SELECT DISTINCT RESULT_CACHE_ID() FROM DUMMY;
SELECT DISTINCT RESULT_CACHE_REFRESH_TIME() FROM DUMMY;

/*

117. RIGHT

RIGHT(<string>, <number>)

获取字符串<string>右边<number>字符

*/

SELECT RIGHT(0123456789, 3) FROM DUMMY;

/*

118. ROUND

ROUND(<number> [, <position> [, <rounding_mode>]])

四舍五入

<position>指定四舍五入小数点位数

<rounding_mode> ::=

 ROUND_HALF_UP | ROUND_HALF_DOWN | ROUND_HALF_EVEN | ROUND_UP | ROUND_DOWN | ROUND_CEILING | ROUND_FLOOR

ROUND_HALF_UP :该值向上舍入到下一整数,如果恰好在中间向上舍入,默认值;

ROUND_HALF_DOWN :该值向下舍入到下一整数,如果恰好在中间向下舍入;

ROUND_HALF_EVEN:该值四舍五入到下一个整数。如果该值正好落在两个舍入值之间的中间,则将舍入到最后一个小数位为偶数的值。

ROUND_UP :向上舍入;

ROUND_DOWN :向下舍入;

ROUND_CEILING :向值大方向舍入;

ROUND_FLOOR :向值小方向舍入;

*/

--16.2
SELECT ROUND (16.16, 1) FROM DUMMY;

--20
SELECT ROUND (16.16, -1) FROM DUMMY;

--默认,向上舍入438.8
SELECT ROUND( 438.75, 1, ROUND_HALF_UP) FROM DUMMY;

--向下舍入,438.7
SELECT ROUND( 438.75, 1, ROUND_HALF_DOWN) FROM DUMMY;

--保证最后保留小数字为偶数,438.8
SELECT ROUND( 438.75, 1, ROUND_HALF_EVEN) FROM DUMMY;

--向上舍入438.8
SELECT ROUND( 438.75, 1, ROUND_UP) FROM DUMMY;

--向下舍入438.7
SELECT ROUND( 438.75, 1, ROUND_DOWN) FROM DUMMY;

/*

119. RPAD

RPAD(<string>, <number> [, <pattern>])

<string>右边填充空格或者指定<pattern>

 

RTRIM(<string> [,<remove_set> ])

右边切掉指定<remove_set>字符串中字符

*/

SELECT RPAD (end, 15, 12345) FROM DUMMY;
SELECT RPAD (end, 2) FROM DUMMY;
SELECT RTRIM (endabAabbabab,ab) FROM DUMMY;

/*

120. ROW_NUMBER

ROW_NUMBER() <window_specification>

对结果集分区进行编号,从1开始

*/

--按照SALES排序后编号
SELECT *, ROW_NUMBER() OVER (PARTITION BY PRODNAME ORDER BY SALES DESC) AS row_num
       FROM PRODUCTSALES ORDER BY PRODNAME,SALES DESC;
--按照原始数据顺序编号
SELECT *, ROW_NUMBER() OVER (PARTITION BY PRODNAME) AS row_num
       FROM PRODUCTSALES ORDER BY PRODNAME,SALES DESC;

 

SAPHANA学习(17):SQL Function(R)

上一篇:tidb4.0.4使用tiup扩容TiKV 节点


下一篇:IMapControl3 Interface(1) Properties属性