/*
121. SCORE
SCORE()
Returns the relevance of a record that has been found.
*/
SELECT SCORE(),"Content" FROM SEARCH_TEXT WHERE CONTAINS("Content", 'cap', LINGUISTIC); SELECT SCORE(), "Content" FROM SEARCH_TEXT WHERE CONTAINS("Content", 'ca',Fuzzy(0.8));
/*
122. SECOND
SECOND(<time>)
返回时间的second
SECONDS_BETWEEN(<date_1>, <date_2>)
返回两个日期之间秒数
*/
SELECT SECOND ('12:34:56') FROM DUMMY; SELECT SECOND ('2014-03-25 12:34:56.789') FROM DUMMY; SELECT SECONDS_BETWEEN ('2009-12-05', '2010-01-05') FROM DUMMY;
/*
123. SERIES_DISAGGREGATE
SERIES_DISAGGREGATE(
{SERIES TABLE <source_series_table> | <source_increment_by>},
{SERIES TABLE <generate_series_table> | <target_increment_by>}
[, <min_value> [, <max_value>]] )
| { SERIES_DISAGGREGATE_TINYINT
| SERIES_DISAGGREGATE_SMALLINT
| SERIES_DISAGGREGATE_INTEGER
| SERIES_DISAGGREGATE_BIGINT
| SERIES_DISAGGREGATE_SMALLDECIMAL
| SERIES_DISAGGREGATE_DECIMAL
| SERIES_DISAGGREGATE_TIME
| SERIES_DISAGGREGATE_DATE
| SERIES_DISAGGREGATE_SECONDDATE
| SERIES_DISAGGREGATE_TIMESTAMP }
(source_increment_by, target_increment_by, min_value, max_value)
<source_series_table>等距序列table
<source_series_table> ::= <identifier>
<source_increment_by> ::= <real_const> | <datetime_const>
<generate_series_table> ::= <identifier>
<target_increment_by> ::= <real_const> | <datetime_const>
<min_value> ::= <real_const> | <datetime_const>
<max_value> ::= <real_const> | <datetime_const>
*/
CREATE COLUMN TABLE TEST_SOURCE_SERIES(id INT, ts TIMESTAMP, val DECIMAL(8,2)) SERIES(SERIES KEY(id ) EQUIDISTANT INCREMENT BY INTERVAL 1 YEAR MINVALUE '1999-01-01' MAXVALUE '2003-01-01' PERIOD FOR SERIES (ts)); CREATE COLUMN TABLE TEST_TARGET_SERIES(id INT, ts TIMESTAMP, val DECIMAL(8,2)) SERIES(SERIES KEY(id ) EQUIDISTANT INCREMENT BY INTERVAL 3 MONTH MINVALUE '1999-01-01' MAXVALUE '2001-01-01' PERIOD FOR SERIES (ts)); INSERT INTO TEST_TARGET_SERIES(id , ts, val) SELECT id, GENERATED_PERIOD_START AS ts, val * FRACTION_OF_SOURCE_PERIOD AS val FROM SERIES_DISAGGREGATE(SERIES TABLE TEST_SOURCE_SERIES, SERIES TABLE TEST_TARGET_SERIES) SD JOIN TEST_SOURCE_SERIES S ON source_period_start = ts ORDER BY id, ts; SELECT * FROM SERIES_DISAGGREGATE_DATE('INTERVAL 1 year', 'INTERVAL 3 MONTH', '1999-01-01', '2001-01-04' ); SELECT * from SERIES_DISAGGREGATE( SERIES TABLE TEST_SOURCE_SERIES, SERIES TABLE TEST_TARGET_SERIES);
/*
124. SERIES_ELEMENT_TO_PERIOD
SERIES_ELEMENT_TO_PERIOD( <element_number>, { <increment_by>, <min_value>, <max_value> | SERIES TABLE <series_table> } )
<element_number> ::= INTEGER
<increment_by> ::= <real_const> | <interval_const>
<min_value> ::= <real_const> | <datetime_const>
<max_value> ::= <real_const> | <datetime_const>
<element_number>生成序列中数字个数；
<increment_by>步长；
*/
--对应序列[0，2，4，6，8]，返回8 SELECT SERIES_ELEMENT_TO_PERIOD(5, 2, 0, 10) FROM DUMMY; --[1，2.25，3.50，4.75，6.00，7.25]，返回7.25 SELECT SERIES_ELEMENT_TO_PERIOD(6, 1.25, 1, 10) FROM DUMMY; --返回2014/01/07 SELECT SERIES_ELEMENT_TO_PERIOD(7, 'INTERVAL 1 DAY', '2014-01-01', '2014-12-31') FROM DUMMY; --返回1，1999-01-07和1999-01-01,间来年1 SELECT SERIES_PERIOD_TO_ELEMENT('1999-01-07',SERIES TABLE TEST_SOURCE_SERIES) FROM DUMMY; --SECOND间隔 SELECT SERIES_ELEMENT_TO_PERIOD(500000, 'INTERVAL 1.5 SECOND', '2014-01-01 00:00:00.000', '2014-12-31') FROM DUMMY;
/*
125. SERIES_FILTER
SERIES_FILTER(<filter_parameter> => <expression> [, <filter_parameter> => <expression> ... ])
OVER (
[ <series_definition> | <series_reference> ]
[ <window_partition_by_clause> ]
[ ORDER BY <window_order_by_expression> ]
[ <window_frame_clause> ]
)
<filter_parameter> ::= VALUE | METHOD_NAME | ALPHA | BETA
VALUE:是必须参数，指定应用筛选器列，列必须是数字类型，不能为null值
METHOD_NAME:必须参数，指定filter method,SINGLESMOOTH or DOUBLESMOOTH
ALPHA:可选，默认0.1，0-1,the level of the series ;
BETA:可选，默认0.1,0-1,the trend of the series;
<window_partition_by_clause> ::= PARTITION BY <expression> [ { , <expression> } ... ]
<window_frame_clause> ::= <window_frame_unit> <window_frame_extent>
<window_frame_unit> ::= ROWS
<window_frame_extent> ::= <window_frame_start> | <window_frame_between>
<window_frame_start> ::= UNBOUNDED PRECEDING | <window_frame_preceding> | CURRENT ROW
<window_frame_preceding> ::= <unsigned_integer> PRECEDING
<window_frame_between> ::= BETWEEN <lower_window_frame_bound> AND <upper_window_frame_bound>
<lower_window_frame_bound> ::= <window_frame_bound>
<upper_window_frame_bound> ::= <window_frame_bound>
<window_frame_bound> := <window_frame_start> | UNBOUNDED FOLLOWING | <window_frame_following>
<window_frame_following> ::= <unsigned_integer> FOLLOWING
*/
CREATE COLUMN TABLE TEST_FILTER_SERIES (ts DATE, temperature FLOAT); INSERT INTO TEST_FILTER_SERIES VALUES('2014-01-01', 0); INSERT INTO TEST_FILTER_SERIES VALUES('2014-01-02', 3); INSERT INTO TEST_FILTER_SERIES VALUES('2014-01-03', 4.5); INSERT INTO TEST_FILTER_SERIES VALUES('2014-01-04', 6); INSERT INTO TEST_FILTER_SERIES VALUES('2014-01-05', 6.3); INSERT INTO TEST_FILTER_SERIES VALUES('2014-01-06', 6.9); INSERT INTO TEST_FILTER_SERIES VALUES('2014-01-07', NULL); INSERT INTO TEST_FILTER_SERIES VALUES('2014-01-08', NULL); SELECT ts, temperature, SERIES_FILTER( VALUE => temperature, METHOD_NAME => 'SINGLESMOOTH', ALPHA => 0.2) OVER (ORDER BY ts) AS SES, SERIES_FILTER( VALUE => temperature, METHOD_NAME => 'DOUBLESMOOTH', ALPHA => 0.2, BETA => 0.3) OVER (ORDER BY ts) AS DES FROM TEST_FILTER_SERIES;
/*
126. SERIES_GENERATE
SERIES_GENERATE( SERIES TABLE <table_name> [, <min_value> [, <max_value> ]] )
| SERIES_GENERATE_<generation_spec>
Generates a complete series table based on the specified series definition.
<generation_spec> ::= <data_type> <generate_parameters>
<data_type> ::= TINYINT | SMALLINT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL | TIME | DATE | SECONDDATE | TIMESTAMP
<generate_parameters> ::=
( <increment_by>, <min_value>, <max_value> )
<increment_by> ::= { <numeric_literal> | INTERVAL <date_literal> }
<table_name> ::= <identifier>
<min_value> ::= <numeric_literal> | <date_literal>
<max_value> ::= <numeric_literal> | <date_literal>
*/
--生成range改变表[0->2.5->5->7.5->10]，0~10，间隔2.5 SELECT * FROM SERIES_GENERATE_DECIMAL(2.5, 0, 10); --生成range改变表[1->3->5]，1~5,间隔2 SELECT * FROM SERIES_GENERATE_INTEGER(2, 1, 5); --生成range改变表，1999-01-01~1999-01-02,间隔30秒 SELECT * FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 30 SECOND', '1999-01-01', '1999-01-02'); --创建等距SERIES表 CREATE COLUMN TABLE TEST_SERIES_GENERATE ( profile_id INT, ts TIMESTAMP, consumption DECIMAL(4,3)) SERIES( SERIES KEY(profile_id) PERIOD FOR SERIES(ts) EQUIDISTANT INCREMENT BY INTERVAL 1 HOUR MISSING ELEMENTS ALLOWED MINVALUE '2010-01-01' MAXVALUE '2015-01-01'); --2010-01-01~2015-01-01，间隔1hour SELECT * FROM SERIES_GENERATE_TIMESTAMP(SERIES TABLE TEST_SERIES_GENERATE); --创建等距SERIES表 CREATE COLUMN TABLE TEST_SERIES_GENERATE1(id INTEGER, pos INTEGER) SERIES( SERIES KEY(id) PERIOD FOR SERIES(pos) EQUIDISTANT INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 ); --[1->2->3->4->5], 1~5,间隔1 SELECT * FROM SERIES_GENERATE(SERIES TABLE TEST_SERIES_GENERATE1);
/*
127. SERIES_PERIOD_TO_ELEMENT
SERIES_PERIOD_TO_ELEMENT(
<value>, {<increment_by>, <min_value>, <max_value> [, <rounding_mode>]
| SERIES TABLE <series_table> [, <rounding_mode>]}
)
Returns the one-based series element number with which the given period value is associated,
where period = min_value + ( element - 1 ) * interval.
<value> ::= INTEGER | DOUBLE | TIMESTAMP
<increment_by> ::= <identifier>
<min_value> ::= <integer>
<max_value> ::= <integer>
<rounding_mode> ::= ROUND_HALF_UP| ROUND_HALF_DOWN | ROUND_HALF_EVEN | ROUND_UP | ROUND_DOWN | ROUND_CEILING| ROUND_FLOOR
*/
--0->2->4->6->8,返回中间值4 SELECT SERIES_PERIOD_TO_ELEMENT(5, 2, 0, 10, ROUND_HALF_UP) FROM DUMMY; --0->2->4->6->8,返回3？ SELECT SERIES_PERIOD_TO_ELEMENT(5, 2, 0, 10, ROUND_HALF_DOWN) FROM DUMMY; --返回3？ SELECT SERIES_PERIOD_TO_ELEMENT(5, 2, 0, 10, ROUND_HALF_EVEN) FROM DUMMY; --返回4 SELECT SERIES_PERIOD_TO_ELEMENT(5, 2, 0, 10, ROUND_UP) FROM DUMMY; --返回3 SELECT SERIES_PERIOD_TO_ELEMENT(5, 2, 0, 10, ROUND_DOWN) FROM DUMMY; --返回4，2014-01到2014-04，间隔月份， SELECT SERIES_PERIOD_TO_ELEMENT( '2014-04-01 12:00:00','INTERVAL 1 MONTH','2014-01-01','2014-12-31', ROUND_HALF_DOWN) FROM DUMMY;
/*
128. SERIES_ROUND
SERIES_ROUND( <value>, { <increment_by> | SERIES TABLE <series_table> } [, <rounding_mode> [, <alignment_expression> ] ] )
Rounds a specified value to the series value using the specified rounding settings.
<value> ::= { <real_const> | <datetime_const> }
<increment_by>::= <interval_const>
<series_table> ::= <identifier>
<rounding_mode> ::= ROUND_HALF_UP| ROUND_HALF_DOWN | ROUND_HALF_EVEN | ROUND_UP | ROUND_DOWN | ROUND_CEILING | ROUND_FLOOR
<alignment_expression> ::= { <real_const> | <datetime_const> }
<increment_by>和SERIES TABLE <series_table>选一
*/
SELECT SERIES_ROUND(4.5, 3, ROUND_HALF_UP) FROM DUMMY; SELECT SERIES_ROUND(4.5, 3, ROUND_HALF_DOWN) FROM DUMMY; SELECT SERIES_ROUND('2013-05-24', 'INTERVAL 1 YEAR', ROUND_DOWN) FROM DUMMY; CREATE COLUMN TABLE TEST_SERIES_ROUND(id INTEGER, pos INTEGER) SERIES( SERIES KEY(id) EQUIDISTANT INCREMENT BY 3 PERIOD FOR SERIES(pos)); SELECT SERIES_ROUND(4.5, SERIES TABLE TEST_SERIES_ROUND, ROUND_HALF_DOWN) FROM DUMMY;
/*
129. SESSION_CONTEXT
SESSION_CONTEXT(<session_variable>)
Returns the value of the specified session variable assigned to the current user.
<session_variable> 可以预定义，用户自定义
SET [SESSION] <variable_name> = <value>
UNSET [SESSION] <variable_name>
SESSION_USER
Returns the user name of the current session.
*/
SELECT SESSION_CONTEXT('APPLICATION') FROM DUMMY; SELECT SESSION_USER FROM DUMMY;
/*
130. SIGN
SIGN(<number>)
Returns the sign (positive or negative) of the specified numeric argument.
正数返回1，负数返回-1，0返回0，null返回null
*/
SELECT SIGN (-15) FROM DUMMY; SELECT SIGN (1) FROM DUMMY; SELECT SIGN (0) FROM DUMMY; SELECT SIGN (null) FROM DUMMY;
/*
131.SIN
SIN(<number>)
返回sin x三角函数值
SINH(<number>)
返回以弧度表示的角度的双曲正弦值
*/
--sin (pi/2) = 1 SELECT SIN ( 3.141592653589793/2) FROM DUMMY; --sin1,弧度,pi约等于3.14, SELECT SIN (1) FROM DUMMY; --近似于弧度1，sin1,sin(pi/3) SELECT SIN (3.1415926/3) FROM DUMMY; --双曲正弦值 SELECT SINH (0.0) FROM DUMMY;
/*
132.SOUNDEX
SOUNDEX(<string>)
Converts alphabet characters into a sound code that represents their sound.
*/
--不存在？ --SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') FROM DUMMY;
/*
133.SQRT
SQRT(<number>)
返回平方根
*/
SELECT SQRT(2) FROM DUMMY;
/*
134. STDDEV
Aggregate function:
STDDEV( [ ALL | DISTINCT ] <expression> )
Window function:
STDDEV( <expression> ) <window_specification>
Returns the standard deviation of the given expression as the square root of the VAR function.
STDDEV_POP(<expression>)
Returns the standard deviation of the given expression as the square root of the VAR_POP function.
STDDEV_SAMP(<expression>)
Returns the standard deviation of the given expression as the square root of VAR_SAMP function.
SELECT STDDEV("Price") FROM "MyProducts"; SELECT STDDEV_POP("Price") FROM "MyProducts"; SELECT STDDEV_SAMP("Price") FROM "MyProducts";
*/
/*
135. STRING_AGG
STRING_AGG( <expression>[, <delimiter> ] [ <order_by_clause> ] )
字符串连接
<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>
*/
CREATE ROW TABLE TEST_STRING(ID INT, STR VARCHAR(20), GRP INT); INSERT INTO TEST_STRING VALUES (3,'str2',0); INSERT INTO TEST_STRING VALUES (0,'str1',0); INSERT INTO TEST_STRING VALUES (NULL,'NULL',0); INSERT INTO TEST_STRING VALUES (5,'str3',0); INSERT INTO TEST_STRING VALUES (3,'val3',1); INSERT INTO TEST_STRING VALUES (6,'val6',1); INSERT INTO TEST_STRING VALUES (NULL,'NULL',1); INSERT INTO TEST_STRING VALUES (1,'val1',1); SELECT GRP,STRING_AGG(STR,'&') AS STR_AGG FROM TEST_STRING GROUP BY "GRP";
/*
136. STRTOBIN
STRTOBIN(<string>, <codepage>)
字符串转换为二进制
*/
SELECT STRTOBIN ('Ant', 'UTF-16BE') FROM DUMMY; SELECT STRTOBIN ('Ant', 'UTF-8') FROM DUMMY; SELECT STRTOBIN ('Ant', 'GBK') FROM DUMMY;
/*
137. SUBARRAY
SUBARRAY(<array_value_expression>, <start_position> , <length>)
获取指定长度array
*/
SELECT SUBARRAY(VAL, 1, 2) FROM ARRAY_TEST;
/*
138. SUBSTR_AFTER
SUBSTR_AFTER(<string>, <pattern>)
返回字符串<string>匹配<pattern>之后字符串
SUBSTR_BEFORE(<string>, <pattern>)
返回字符串<string>匹配<pattern>之前字符串
SUBSTR[ING]_REGEXPR( <pattern> [ FLAG <flag> ] IN <regex_subject_string>
[ FROM <start_position> ]
[ OCCURRENCE <regex_occurrence> ]
[ GROUP <regex_capture_group> ] )
正则表达式匹配
<pattern>正则表达式
<flag>：模式，i:大小写不敏感；m:多行模式；s:<.>可以匹配任意字符；x：regex表达式支持空格
<flag> ::= 'i' | 'm' | 's' | 'x'
<regex_subject_string> ::= <string>
被匹配的字符串
<start_position> ::= <numeric_literal>
开始位置
<regex_occurrence> ::= <numeric_literal >
匹配次数
<regex_capture_group> ::= <integer>
返回第几分组数
SUBSTRING(<string>, <start_position> [, <string_length>])
字符串切割
*/
--返回Friend SELECT SUBSTR_AFTER ('Hello My Friend','My') FROM DUMMY; --返回null SELECT SUBSTR_AFTER ('Hello My Friend',null) FROM DUMMY; --返回Hello SELECT SUBSTR_BEFORE ('Hello My Friend','My') FROM DUMMY; --返回01 SELECT SUBSTR_REGEXPR('([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})' IN '20140401' GROUP 3) FROM DUMMY; --切割字符串 SELECT SUBSTRING(x'ABCDEF',1,2) FROM DUMMY; SELECT SUBSTRING ('1234567890',4,2) "substring" FROM DUMMY;
/*
139.SUM
Aggregate function:
SUM( [ ALL | DISTINCT ] <expression> )
Window function:
SUM( <expression> ) <window_specification>
求和
*/
SELECT SUM("Quantity") FROM "MyProducts" WHERE "Product_Name" IN ('Jackets', 'Coats');
/*
140.SYSUUID
Returns a new universally unique identifier that is generated by the connected SAP HANA instance.
*/
SELECT SYSUUID FROM DUMMY;