SAPHANA学习（4）：SQL Function(C)
32.CARDINALITY Function
CARDINALITY(<array_value_expression>)
返回Array中包含数据个数
*/
CREATE COLUMN TABLE ARRAY_TEST (IDX INT, VAL INT ARRAY); INSERT INTO ARRAY_TEST VALUES (1, ARRAY(1, 2, 3)); INSERT INTO ARRAY_TEST VALUES (2, ARRAY(10, 20, 30, 40)); SELECT CARDINALITY(VAL) FROM ARRAY_TEST;
/*
类型转换
33.CAST Function
CAST( <expression> AS <data_type>[ ( <length> ) ] )
<data_type ::= TINYINT
| SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE
| ALPHANUM | VARCHAR | NVARCHAR | DAYDATE | DATE | TIME | SECONDDATE | TIMESTAMP>
将表达式转换为指定数据类型值
34.CEIL Function
CEIL(<number>)
返回大于等于<number>的最小整数
35.CHAR Function
CHAR(<number>)
Returns the character that has the ASCII value of the specified number.
将ASCII数字转换为字符
*/
SELECT CAST (7 AS VARCHAR) FROM DUMMY; SELECT CAST (10.5 AS INTEGER) FROM DUMMY; SELECT CEIL (14.5) FROM DUMMY; SELECT CHAR (65) || CHAR (110) || CHAR (116) FROM DUMMY;
/*
36.COALESCE Function
COALESCE(<expression_list>)
返回<expression_list>中第一个非空元素
*/
CREATE ROW TABLE coalesce_example (ID INT PRIMARY KEY, A REAL, B REAL); INSERT INTO coalesce_example VALUES(1, 100, 80); INSERT INTO coalesce_example VALUES(2, NULL, 63); INSERT INTO coalesce_example VALUES(3, NULL, NULL); SELECT id, a, b, COALESCE (a, b*1.1, 50.0) FROM coalesce_example;
/*
37.CONTAT Function
CONCAT(<string1>, <string2>)
连接两个字符串,和||操作功能一样
The maximum length of the concatenated string is 8,388,607；
38.CONCAT_NAZ
CONCAT_NAZ(<string1>, <string2>)
连接两个字符串，如果一个string1为null,返回另一个string2;
如果两个string都为null,返回null;
*/
SELECT CONCAT ('C', 'at') FROM DUMMY; SELECT CONCAT_NAZ ('A', 'B') FROM DUMMY; SELECT CONCAT_NAZ ('C', null) FROM DUMMY; SELECT CONCAT_NAZ (null, null) FROM DUMMY;
/*
39.CONVERT_CURRENCY Function
CONVERT_CURRENCY( <named_parameter_value>[{, <named_parameter_value>}...])
币别转换
<named_parameter_value> ::=
"<field_reference_parameter>" => <expression>
| "<const_string_parameter>" => <const_string>
<field_reference_parameter> ::=
AMOUNT | SOURCE_UNIT | TARGET_UNIT | REFERENCE_DATE | CLIENT
参数说明：
AMOUNT（强制）:The column identifier containing the values to be converted.
CLIENT（强制）：客户端；
SOURCE_UNIT（强制） ：The column identifier describing the input unit.
A constant string is also accepted using single quotations.
TARGET_UNIT（强制） ：The column identifier describing the target unit.
A constant string is also accepted using single quotations.
REFERENCE_DATE（强制） ：The column identifier describing the currency reference date.
A constant string is also accepted using single quotations.
<const_string_parameter> ::=
SCHEMA | DATABASE | CONVERSION_TYPE | LOOKUP | ERROR_HANDLING | ACCURACY | DATE_FORMAT
| STEPS | CONFIGURATION_TABLE | PRECISIONS_TABLE | NOTATION_TABLE | RATES_TABLE | PREFACTORS_TABLE
参数说明：
SCHEMA （强制）：Defines the schema that contains the conversion tables used for the conversion.
DATABASE： Defines the tenant where the conversion tables are located (for example the TCUR* tables of the ERP currency conversion).
If not specified, the conversion uses the current database
METHOD ： Possible values: ERP, Banking
BID_ASK_TYPE ：Possible values: bid, ask, mid
MARKET_DATA_AREA： Defines the market data area as stored in the tables.
This is mandatory for banking currency conversion.
SYSTEM_TIME： Defines the system timestamp for time travel functionality
CONVERSION_TYPE：Defines the conversion type as stored in the conversion tables.
The conversion types available in your system vary according to the setup of your ERP system.
In general, these are either M or EURX.
Contact your system administrator for the details of your specific table configuration.
LOOKUP： The type of lookup to be performed.
Possible values:
Regular: A regular conversion is performed.
Reverse: Performs a reverse conversion with the input units swapped.
ERROR_HANDLING： Defines how the system handles a situation where a row could not be converted.
Possible values:
fail on error:The conversion fails with an error.
set to null:The output from the row that caused the error is set to NULL.
keep unconverted: The input value is returned.
ACCURACY : Defines the rounding behavior of the system.
Possible values:
compatibility:Mimics ERP behavior by rounding the result.
highest: Keeps as many digits as possible in the result.
DATE_FORMAT : Defines the format in which the reference date is presented.
Possible values :
auto detect :Attempt automatic detection of the date format.
normal:Date is provided in a regular format
inverted : Date is provided in inverted SAP legacy format.
STEPS : Defines the steps that should be included in the conversion process.
You provide a comma delimited list of the steps to be included, and the order of the steps is irrelevant.
shift:
Enables a decimal shift according to the source currency selected.
For example, if the source currency has 0 valid digits according to PRECISIONS_TABLE,
each value needs to be multiplied by 100 because in SAP ERP systems, values are stored using two digits.
This has to be done to convert ERP values to their correct numerical representation.
convert:
Triggers the actual conversion from the source to the target currency.
round:
Rounds the converted value to the number of digits of the target currency.
Use this step carefully if subsequent aggregations take place on the number, as rounding errors could accumulate.
shift_back:
While shift changes the decimals from two to the configured precision of the source currency, shift_back No changes them back to two, but from the target currency.
If error handling is set to keep unconverted the output currency might be the source instead of the target currency.
In case of an error, the rounding and the shift_back are done with respect to the source currency and the conversion is dropped.
This renders all steps redundant, and yields the input value again.
CONFIGURATION_TABLE ：The table identifier of the conversion type configuration.
If the table resides in a different tenant, provide a fully qualified name for the table or use the DATABASE parameter.
PRECISIONS_TABLE： The table identifier of the precision table.
If the table resides in a different tenant, provide a fully qualified name for the table or use the DATABASE parameter
NOTATION_TABLE： The table identifier of the table that stores notations.
If the table resides in a different tenant, provide a fully qualified name for the table or use the DATABASE parameter.
RATES_TABLE：The table identifier of the conversion rates table.
If the table resides in a different tenant, provide a fully qualified name for the table or use the DATABASE parameter.
PREFACTORS_TABLE：The table identifier of the pre-factors table.
If the table resides in a different tenant, provide a fully qualified name for the table or use the DATABASE parameter
CONVERT_CURRENCY函数，SAP HANA数据库中必须有货币转换表TCURV、TCURX、TCURN、TCURR和TCURF。
CONVERT_CURRENCY函数不支持某些转换函数。它只支持自定义表TCURV中的XINVR、BWAER、XBWRL和XEURO列。
当表TCURV中维护不支持的字段BKUZU、GKUZU或XBWRL时，会出现错误消息。
请参阅SAP注释2792149-货币/单位转换错误：转换类型“<conversion type>”具有不受支持的“BKUZU”或“GKUZU”类型集。
*/
/* CREATE ROW TABLE sample_input (price DECIMAL(15,2), source_unit VARCHAR(4), target_unit VARCHAR(4), ref_date VARCHAR(10) ); INSERT INTO sample_input VALUES (1.0, 'SRC', 'TRG', '2011-01-01'); INSERT INTO sample_input VALUES (1.0, 'SRC', 'TRG', '2011-02-01'); SELECT CONVERT_CURRENCY( amount=>price, "SOURCE_UNIT" =>source_unit, "SCHEMA" => 'SYSTEM', "DATABASE" => 'NDB', "TARGET_UNIT" => target_unit, "REFERENCE_DATE" =>'2013-09-23', "ERROR_HANDLING"=>'set to null', "CLIENT" => '000') AS converted FROM sample_input; SELECT CONVERT_CURRENCY( method=>'Banking', -- new market_data_area=>'S000’, -- new (and mandatory for Banking) amount=>ext_limit, source_unit =>ext_limit_curr, schema => 'SAPCOB', target_unit=> 'EUR', reference_date => business_day, error_handling=> 'set to null', client => '150') as converted from v_fx_input; SELECT CONVERT_CURRENCY(method=>'Banking', -- new market_data_area=>'S000', -- mandatory (for Banking) bid_ask_type=>'MID', -- optional system_time=>to_timestamp('2011-05-11 12:59.999','YYYY-MM-DD HH:SS.FF3') -- optional
amount=>ext_limit,
source_unit =>ext_limit_curr,
schema => 'SAPCOB',
target_unit=> 'EUR',
reference_date => business_day,
error_handling=> 'set to null', client => '150') as converted from v_fx_input; SELECT SESSION_CONTEXT('APPLICATION') AS "session context" , SESSION_CONTEXT('CLIENT') AS "client" FROM DUMMY; SELECT * FROM SAPB01.TCURV; SELECT * FROM SAPB01.TCURX; SELECT * FROM SAPB01.TCURN; SELECT * FROM SAPB01.TCURR; SELECT * FROM SAPB01.TCURF; */
/*
40.CONVERT_UNIT Function
CONVERT_UNIT( <named_parameter_value>, ... )
<named_parameter_value> := "<field_reference_parameter>" =>
<expression> | "<const_string_parameter>" => <const_string>
<field_reference_parameter> ::=
QUANTITY | SOURCE_UNIT | TARGET_UNIT | CLIENT
<const_string_parameter> ::=
SCHEMA | DATABASE | ERROR_HANDLING | RATES_TABLE | DIMENSION_TABLE
参数说明：
QUANTITY(强制):The column to be converted.
SOURCE_UNIT(强制)： The column identifier describing the input unit.
A constant string is also accepted using single quotations.
TARGET_UNIT(强制): The column identifier describing the target unit.
A constant string is also accepted using single quotations.
CLIENT(强制)：Defines a three character string that is used to separate tenants within ERP system tables.
This is used in the conversion tables to select the correct rows for each user.
A column identifier is also accepted using double quotations.
This parameter is mandatory, as the CLIENT session context variable is not used by this command.
SCHEMA(强制)： Defines the schema that contains the conversion tables used for the conversion.
DATABASE ：Defines the tenant where the conversion tables are located.
If not specified, the conversion uses the current database.
ERROR_HANDLING： Defines how the system handles a situation where a row cannot be converted.
fail on error：The conversion fails with an error.
set to null ：The output from the row that caused the error is set to NULL.
keep unconverte： The input value is returned.
RATES_TABLE： Defines a table that stores the conversion rates.
If the table resides in a different tenant, provide a fully qualified name for the table or use the DATABASE parameter.
DIMENSION_TABLE： Defines a table that stores the conversion dimensions.
If the table resides in a different tenant, provide a fully qualified name for the table or use the DATABASE parameter.
要使用CONVERT_UNIT函数，SAP HANA数据库中必须有单位转换表T006和T006D。
*/
/* CREATE ROW TABLE sample_input ( quant DECIMAL(15,2), source_unit VARCHAR(4), target_unit VARCHAR(4) ); INSERT INTO sample_input VALUES (1.0, 'SRC', 'TRG'); INSERT INTO sample_input VALUES (1.0, 'SRC', 'TRG'); SELECT CONVERT_UNIT( "QUANTITY"=>quant , "SOURCE_UNIT" =>source_unit , "SCHEMA" => 'SYSTEM' , "DATABASE" => 'NDB' , "TARGET_UNIT" => target_unit , "ERROR_HANDLING"=>'set to null' , "CLIENT" => '000') AS converted FROM sample_input; */
/*
41.CORR Function
Aggregate function: CORR(<column1>, <column2>)
Window function: CORR(<column1>, <column2>) <window_specification>
Computes the Pearson product momentum correlation coefficient between two columns.
The result ranges from -1 to 1, depending on the correlation, or NULL if a correlation cannot be computed.
The result can return NULL for one of the following reasons:
● Less than two value pairs are correlated after NULLs have been removed
● There is zero variance in at least one of the two columns
42.CORR_SPEARMAN Function
Aggregate function: CORR_SPEARMAN(<column1>, <column2>)
Window function: CORR_SPEARMAN(<column1>, <column2>) <window_specification>
Returns the Spearman's rank correlation coefficient of the values found in the corresponding rows of <column1> and <column2>.
*/
CREATE COLUMN TABLE correlationTable3 (ts_id VARCHAR(20), DATE DAYDATE, value1 DOUBLE, value2 DOUBLE); INSERT INTO correlationTable3 VALUES ('A', '2014-10-01', 1, 1); INSERT INTO correlationTable3 VALUES ('A', '2014-10-02', 2, 2); INSERT INTO correlationTable3 VALUES ('A', '2014-10-04', 3, 3); INSERT INTO correlationTable3 VALUES ('B', '2014-10-07', 1, 3); INSERT INTO correlationTable3 VALUES ('B', '2014-10-11', 2, 2); INSERT INTO correlationTable3 VALUES ('B', '2014-10-21', 3, 1); SELECT ts_id, CORR(value1, value2) FROM correlationTable3 GROUP BY ts_id; --筛选‘A’ SELECT ts_id, CORR(value1, value2) FROM correlationTable3 WHERE ts_id='A' GROUP BY ts_id; --use a window function SELECT ts_id, CORR(value1, value2) OVER (PARTITION BY ts_id) FROM correlationTable3; --uses a sliding window function SELECT ts_id, CORR(value1, value2) OVER (PARTITION BY ts_id ORDER BY date) FROM correlationTable3 ORDER BY ts_id; -- uses a ROWS BETWEEN clause SELECT ts_id, CORR(value1, value2) OVER (PARTITION BY ts_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from correlationTable3; --不同列相关系数 SELECT CORR_SPEARMAN(correlationTable1.val, correlationTable2.val) FROM correlationTable1,correlationTable2 WHERE correlationTable1.date = correlationTable2.date; --创建测试表 CREATE COLUMN TABLE correlationSpearmanTable ( ts_id VARCHAR(20), date DAYDATE, value1 DOUBLE, value2 DOUBLE); INSERT INTO correlationSpearmanTable VALUES ('A', '2014-10-01', 34.345, 45.345); INSERT INTO correlationSpearmanTable VALUES ('A', '2014-10-02', 27.145, 28.893); INSERT INTO correlationSpearmanTable VALUES ('A', '2014-10-02', 48.312, 28.865); INSERT INTO correlationSpearmanTable VALUES ('A', '2014-10-03', 94.213, 58.854); INSERT INTO correlationSpearmanTable VALUES ('A', '2014-10-03', 16.567, 28.231); INSERT INTO correlationSpearmanTable VALUES ('A', '2014-10-03', 38.894, 94.378); INSERT INTO correlationSpearmanTable VALUES ('B', '2014-10-04', 45.643, 76.987); INSERT INTO correlationSpearmanTable VALUES ('B', '2014-10-04', 53.345, 50.893); INSERT INTO correlationSpearmanTable VALUES ('B', '2014-10-04', 66.342, 48.342); INSERT INTO correlationSpearmanTable VALUES ('B', '2014-10-04', 76.432, 37.234); INSERT INTO correlationSpearmanTable VALUES ('B', '2014-10-05', 88.432, 23.242); INSERT INTO correlationSpearmanTable VALUES ('B', '2014-10-05', 93.234, 13.132); --分组计算spearman系数 SELECT ts_id, CORR_SPEARMAN(value1, value2) FROM correlationSpearmanTable GROUP BY ts_id; -- window function SELECT ts_id, CORR_SPEARMAN(value1, value2) OVER (PARTITION BY ts_id) FROM correlationSpearmanTable; --sliding window example SELECT ts_id, CORR_spearman(value1, value2) OVER (PARTITION BY ts_id ORDER BY date) FROM correlationSpearmanTable ORDER BY ts_id; -- ROWS BETWEEN example SELECT ts_id, CORR_spearman(value1, value2) OVER (PARTITION BY ts_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from correlationSpearmanTable; -- the group example SELECT ts_id, CORR_spearman(value1, value2) OVER (PARTITION BY ts_id ORDER BY date GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM correlationSpearmanTable;
/*
43.COS Function
COS(<number>)
返回角度<number>的余弦值，以弧度为单位。
44.COSH Function
COSH(<number>)
计算数值参数<number>的双曲余弦。
45.COT Function
COT(<number>)
Computes the cotangent of a number <number>, where <number> is an angle expressed in radians.
*/
SELECT COS (0.0) FROM DUMMY; SELECT COSH (0.5) FROM DUMMY; SELECT COT (40) FROM DUMMY;
/*
46.COUNT Function
Aggregate function: COUNT (*)
| COUNT ( [ ALL ] <expression> )
| COUNT ( DISTINCT <expression_list>
Window function: COUNT (*) [ <window_specification> ]
| COUNT ( [ ALL ] <expression> ) [ <window_specification> ]
| COUNT ( DISTINCT <expression> ) [ <window_specification> ]
返回查询语句返回数据条数
*/
SELECT COUNT(*) FROM TEST_COUNT; --去重，不统计空值 SELECT COUNT(DISTINCT "A") FROM TEST_COUNT;
/*
47.CROSS_CORR Function
CROSS_CORR(<expression1>, <expression2>, <maxLag>
{ <series_orderby> | <order_by_clause> } ).
{ POSITIVE_LAGS | NEGATIVE_LAGS | ZERO_LAG }
expression1 and expression2:
用于计算cross-correlation coefficients的数值型值
maxLag :
非负整数，返回的cross-correlation coefficients个数
The <maxLag> parameter must be a positive integer that defines the number of cross-correlation coefficients to be returned.
<maxLag> ::= INTEGER series_orderby
The SERIES clause can only be used with an equidistant series.
For more information about the SERIES clause, see the CREATE TABLE statement and the SERIES_GENERATE function.
<series_orderby> ::= SERIES (<series_period> <series_equidistant_definition>)
order_by_clause Specifies the sort order of the input rows.
<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> <collate_clause>
specifies the collation to use for ordering values in the results.
<collate_clause> can only be used on columns defined as NVARCHAR or VARCHAR.
<collation_name> is one of the supported collation names listed in the COLLATIONS system view.
*/
CREATE COLUMN TABLE table1 ( ts_id INTEGER, number1 DOUBLE, number2 DOUBLE ); INSERT INTO table1 VALUES ('1', 1, 2); INSERT INTO table1 VALUES ('2', 2 ,1); INSERT INTO table1 VALUES ('3', 1 ,2); SELECT CROSS_CORR(number1, number2, 10 ORDER BY ts_id) FROM table1; --使用Series CREATE COLUMN TABLE TSeries( "KEY" INTEGER, ts TIMESTAMP, val1 DOUBLE, val2 DOUBLE, PRIMARY KEY(key, ts) ) SERIES( SERIES KEY ("KEY") EQUIDISTANT INCREMENT BY INTERVAL 1 DAY PERIOD FOR SERIES(ts) ); INSERT INTO TSeries VALUES (1, '2014-1-1', 1, 3); INSERT INTO TSeries VALUES (2, '2014-1-3', 2, 4); INSERT INTO TSeries VALUES (3, '2014-1-4', 4, 2); INSERT INTO TSeries VALUES (4, '2014-1-5', 3, 1); SELECT CROSS_CORR(val1, val2, 10 ORDER BY ts) FROM TSeries;
/*
48.CUBIC_SPLINE_APPROX Function
CUBIC_SPLINE_APPROX ( <expression> [, <BoundaryConditionArgument> [, <ExtrapolationModeArgument> [, <Value1Argument> [, <Value2Argument> ] ] ] ] )
[ OVER ( {
SERIES TABLE <series_table> [ PARTITION BY <col1> [,...] [ <window_order_by_clause> ]
| SERIES(...) [ PARTITION BY <col1> [,...] [ <window_order_by_clause> ]
| [ PARTITION BY <col1>[,...] <window_order_by_clause>
} ) ]
<expression>:输入数据表达式
<series_table> ::= [<schema_name>.]<table_name>
<schema_name> ::= <unicode_name>
<table_name> ::= <identifier>
<BoundaryConditionArgument> ::= SPLINE_TYPE_NATURAL | SPLINE_TYPE_NOT_A_KNOT
默认值SPLINE_TYPE_NATURAL
<ExtrapolationModeArgument> ::= EXTRAPOLATION_NONE
| EXTRAPOLATION_LINEAR
| EXTRAPOLATION_CONSTANT
设置Extrapolation模式，默认EXTRAPOLATION_NONE
在Null插入合适估值
*/
--Natural cubic spline interpolation --SPLINE_TYPE_NATURAL可以省略 INSERT INTO weather VALUES(1, '2015-02-01', null); INSERT INTO weather VALUES(1, '2015-02-02', 3.4); INSERT INTO weather VALUES(1, '2015-02-03', 5); INSERT INTO weather VALUES(1, '2015-02-04', null); INSERT INTO weather VALUES(1, '2015-02-05', 4.6); INSERT INTO weather VALUES(1, '2015-02-06', 6.9); --Natual生成double类型数据，NOT_A_KNOT,原本数据类型或整型 SELECT CUBIC_SPLINE_APPROX(temperature, 'SPLINE_TYPE_NATURAL') OVER (PARTITION BY station ORDER BY ts) FROM WEATHER; SELECT CUBIC_SPLINE_APPROX(temperature, 'SPLINE_TYPE_NOT_A_KNOT') OVER(PARTITION BY station ORDER BY ts) FROM WEATHER; --SERIES Table CREATE COLUMN TABLE InterpolationTable (ts_id VARCHAR(20), date DAYDATE, val DOUBLE) SERIES(SERIES KEY(ts_id) PERIOD FOR SERIES(date) EQUIDISTANT INCREMENT BY INTERVAL 1 DAY MISSING ELEMENTS ALLOWED); INSERT INTO InterpolationTable VALUES('A','2013-09-29', null); INSERT INTO InterpolationTable VALUES('A','2013-09-30', 1); INSERT INTO InterpolationTable VALUES('A','2013-10-01', 2); INSERT INTO InterpolationTable VALUES('A','2013-10-02', null); INSERT INTO InterpolationTable VALUES('A','2013-10-03', 10); INSERT INTO InterpolationTable VALUES('A','2013-10-04', 5); INSERT INTO InterpolationTable VALUES('A','2013-10-05', null); --EXTRAPOLATION_NONE可以省略 SELECT CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT','EXTRAPOLATION_NONE') OVER(SERIES TABLE InterpolationTable) FROM InterpolationTable; --EXTRAPOLATION_LINEAR SELECT CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT','EXTRAPOLATION_LINEAR') OVER(SERIES TABLE InterpolationTable) FROM InterpolationTable; --EXTRAPOLATION_CONSTANT SELECT CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT','EXTRAPOLATION_CONSTANT') OVER(SERIES TABLE InterpolationTable) FROM InterpolationTable; --Value1Argument,Value2Argument设置 CREATE COLUMN TABLE InterpolationTable1 (ts_id VARCHAR(20), date DAYDATE, val DOUBLE) SERIES(SERIES KEY(ts_id) PERIOD FOR SERIES(date) EQUIDISTANT INCREMENT BY INTERVAL 1 DAY MISSING ELEMENTS ALLOWED); INSERT INTO InterpolationTable1 VALUES('A','2013-09-29', null); INSERT INTO InterpolationTable1 VALUES('A','2013-09-30', null); INSERT INTO InterpolationTable1 VALUES('A','2013-10-01', 1); INSERT INTO InterpolationTable1 VALUES('A','2013-10-02', null); INSERT INTO InterpolationTable1 VALUES('A','2013-10-03', 3); INSERT INTO InterpolationTable1 VALUES('A','2013-10-04', null); INSERT INTO InterpolationTable1 VALUES('A','2013-10-05', null); --不设置value1Argument和value2Argument,返回值[null,null,1,2,3,null,null] SELECT CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT','EXTRAPOLATION_LINEAR') OVER(SERIES TABLE InterpolationTable1) FROM InterpolationTable1; CREATE COLUMN TABLE InterpolationTable2 (ts_id VARCHAR(20), date DAYDATE, val DOUBLE) SERIES(SERIES KEY(ts_id) PERIOD FOR SERIES(date) EQUIDISTANT INCREMENT BY INTERVAL 1 DAY MISSING ELEMENTS ALLOWED); INSERT INTO InterpolationTable2 VALUES('A','2013-10-01', null); INSERT INTO InterpolationTable2 VALUES('A','2013-10-02', null); INSERT INTO InterpolationTable2 VALUES('A','2013-10-03', 3); INSERT INTO InterpolationTable2 VALUES('A','2013-10-04', null); --不设置value1Argument和value2Argument,返回值[3,3,3,3] SELECT CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT','EXTRAPOLATION_LINEAR') OVER(SERIES TABLE InterpolationTable2) FROM InterpolationTable2; --设置Value1Argument和Value2Argument CREATE COLUMN TABLE InterpolationTable3 (ts_id VARCHAR(20), date DAYDATE, val DOUBLE) SERIES(SERIES KEY(ts_id) PERIOD FOR SERIES(date) EQUIDISTANT INCREMENT BY INTERVAL 1 DAY MISSING ELEMENTS ALLOWED); INSERT INTO InterpolationTable3 VALUES('A','2013-10-01', null); INSERT INTO InterpolationTable3 VALUES('A','2013-10-02', 5); INSERT INTO InterpolationTable3 VALUES('A','2013-10-03', 3); INSERT INTO InterpolationTable3 VALUES('A','2013-10-04', null); SELECT CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT','EXTRAPOLATION_LINEAR',2,null) OVER(SERIES TABLE InterpolationTable3) FROM InterpolationTable3;
/*
49.CUME_DIST Function
CUME_DIST() <window_specification>
*/
CREATE ROW TABLE ProductSales(ProdName VARCHAR(50), Description VARCHAR(20), Sales INT); INSERT INTO ProductSales VALUES('Tee Shirt','Plain',21); INSERT INTO ProductSales VALUES ('Tee Shirt','Lettered',22); INSERT INTO ProductSales VALUES ('Tee Shirt','Team logo',30); INSERT INTO ProductSales VALUES('Hoodie','Plain',60); INSERT INTO ProductSales VALUES ('Hoodie','Lettered',65); INSERT INTO ProductSales VALUES ('Hoodie','Team logo',80); INSERT INTO ProductSales VALUES('Ballcap','Plain',8); INSERT INTO ProductSales VALUES ('Ballcap','Lettered',40); INSERT INTO ProductSales VALUES ('Ballcap','Team logo',27); SELECT ProdName, Description, Sales, PERCENT_RANK() OVER (ORDER BY Sales ASC) AS Percent_Rank, CUME_DIST() OVER (ORDER BY Sales ASC) AS Cume_Dist FROM ProductSales ORDER BY Sales DESC;
/*
50.CURRENT_CONNECTION
整数形式返回当前连接，调用不需要大括号
*/
SELECT CURRENT_CONNECTION FROM DUMMY;
/*
51.CURRENT_DATE
返回当前系统日期
CURRENT_TIME
Returns the current local system time.
CURRENT_TIMESTAMP
Returns the current local system timestamp information.
CURRENT_UTCDATE
Returns the current UTC date.
CURRENT_UTCTIME
Returns the current UTC time.
CURRENT_UTCTIMESTAMP
Returns the current UTC timestamp.
*/
SELECT CURRENT_DATE FROM DUMMY; SELECT CURRENT_TIME FROM DUMMY; SELECT CURRENT_TIMESTAMP FROM DUMMY; SELECT CURRENT_UTCDATE FROM DUMMY; --时间不太准确 SELECT CURRENT_UTCTIME FROM DUMMY; SELECT CURRENT_UTCTIMESTAMP FROM DUMMY;
/*
52.CURRENT_IDENTITY_VALUE()
返回BIGINT值，最新插入的标识值
*/
CREATE COLUMN TABLE TEST_IDENTITY (objectid BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101), number INT); INSERT INTO TEST_IDENTITY(number) VALUES ( 10 ); INSERT INTO TEST_IDENTITY(number) VALUES ( 10 ); SELECT CURRENT_IDENTITY_VALUE() FROM DUMMY;
/*
53.CURRENT_MVCC_SNAPSHOT_TIMESTAMP()
returns the timestamp of the current MVCC snapshot in SSSS format.
*/
--不存在？ --SELECT CURRENT_MVCC_SNAPSHOT_TIMESTAMP() FROM DUMMY;
/*
54.CURRENT_OBJECT_SCHEMA()
创建视图时，可以使用CURRENT_OBJECT_SCHEMA
WORKDAYS_BETWEEN， CONVERT_CURRENCY functions 支持 CURRENT_OBJECT_SCHEMA 替换实际schema字符串
*/
--Function name不存在 --CREATE VIEW LUJIE_TANG.VIEW1 AS SELECT WORKDAYS_BETWEEN('01', '2014-01-09', '2014-01-10', CURRENT_OBJECT_SCHEMA()) FROM DUMMY;
/*
55.CURRENT_SCHEMA
当前Schema
*/
SELECT CURRENT_SCHEMA FROM DUMMY;
/*
56.CURRENT_SITE_ID()
Every site in an SAP HANA System Replication (HSR) environment has a site ID.
When this function is called in a non-HSR system, 0 is returned.
*/
--SELECT TOP 1 CURRENT_SITE_ID() FROM "TEST";
/*
57.CURRENT_TRANSACTION_ISOLATION_LEVEL
Returns a string containing the current transaction isolation level.
*/
SELECT CURRENT_TRANSACTION_ISOLATION_LEVEL FROM DUMMY;
/*
58.CURRENT_UPDATE_STATEMENT_SEQUENCE()
返回事务中写语句次数，当write操作为0，function 返回1，当read操作，function返回1；
CURRENT_UPDATE_TRANSACTION()
返回唯一ID(BIGINT),当前write mode事务
当前为read mode事务,返回0
*/
INSERT INTO "TEST" VALUES (FALSE); INSERT INTO "TEST" VALUES (TRUE); SELECT CURRENT_UPDATE_STATEMENT_SEQUENCE() FROM DUMMY; SELECT CURRENT_UPDATE_TRANSACTION() FROM DUMMY;
/*
59.CURRENT_USER
当前user
CURRENT_USER_ID
当前user id
*/
SELECT CURRENT_USER FROM DUMMY; --不存在 --SELECT CURRENT_USER_ID() from DUMMY;
本文来自博客园，作者：渔歌晚唱，转载请注明原文链接：https://www.cnblogs.com/tangToms/p/13869339.html
