/*
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;