3.11SQL Functions
/*11.SQL Functions*/
/*
1.ABAP_ALPHANUM
ABAP_ALPHANUM( <string>, <chars> )
<string>,如果是数字字符串,返回左边补0到<chars>位数的字符串,如果是混合数字字母字符串,返回unchanged
<chars>,返回字符串长度,如果<chars>大于原字符串长度,添加0;
*/
--ABAP_ALPHANUM,找不到?
--SELECT ABAP_ALPHANUM('12', 3) FROM DUMMY;
--SELECT ABAP_ALPHANUM('123', 2) FROM DUMMY;
--SELECT ABAP_ALPHANUM('12x', 13) FROM DUMMY;
/*
2.ABAP_NUMC
ABAP_NUMC( <string>, <chars> )
处理数字字符串,如果<chars>大于原字符串长度,左边补0,如果小于,从左边截取掉超出字符;
数字负号会被省略掉
*/
--ABAP_NUMC,找不到?
--SELECT ABAP_NUMC(12, 3) FROM DUMMY;
--SELECT ABAP_NUMC(1.2e13, 13) FROM DUMMY;
--SELECT ABAP_NUMC(-1234.5, 6) FROM DUMMY;
/*
3.ABAP_LOWER
ABAP_LOWER(<string>)
将字符串转换小写
4.ABAP_UPPER
ABAP_UPPER(<string>)
将字符串转换大写
*/
--ABAP_LOWER
SELECT ABAP_LOWER('ABC') FROM DUMMY;
--ABAP_UPPER
SELECT ABAP_UPPER('abc') FROM DUMMY;
/*
5.ABS Function
ABS(<num>)
绝对值
*/
--ABS
SELECT ABS(-1) FROM DUMMY;
/*
6.ACOS Function
ACOS(<num>)
获取反余弦值,y=arccos x x取值[-1,1],y=cosx的反函数
<num>取值-1~1
7.ASIN Function
ASIN(<number>)
反正弦函数y=arcsinx x=[-1,1]
8.ATAN Function
ATAN(<number>)
反正切函数y=arctanx x属于负无穷到正无穷
9.ATAN2 Function
ATAN2(<number1>, <number2>)
两个数之比的反正切值
*/
--ACOS
SELECT ACOS(-1) FROM DUMMY;
--ASIN
SELECT ASIN (0.5) FROM DUMMY;
--ATAN
SELECT ATAN (1000) FROM DUMMY;
--ATAN2
SELECT ATAN2 (100, 200) FROM DUMMY;
/*
10.ADD_DAYS Function
ADD_DAYS(<date>, <num_days>)
日期加上<num_days>
11.ADD_MONTHS Function
ADD_MONTHS(<date>, <num_months>)
日期加上<num_months>
<date>参数数据类型 DATE,TIMESTAMP,SECONDDATE
返回的日期类型和<date>参数类型保持一致
12.ADD_MONTHS_LAST Function
ADD_MONTHS_LAST(<date>, <num_months>)
日期加上<num_months>,然后返回该月最后一天日期
<date>参数数据类型 DATE,TIMESTAMP,SECONDDATE
返回的日期类型和<date>参数类型保持一致
13.ADD_NANO100 Function
ADD_NANO100( <time>, <num> )
<time>:TimeStamp类型数据
<num>:microsecond,微秒
14.ADD_SECONDS Function
ADD_SECONDS(<time>, <num_seconds>)
<num_seconds>:增加秒数,可以是毫秒,微秒级别小数
15.ADD_WORKDAYS Function
ADD_WORKDAYS(<factory_calendar_id>, <start_date>, <workdays> [, <source_schema>])
<start_date>上加上<workdays>
factory table: TFACS.
<factory_calendar_id> ::= <string_literal>
<start_date> ::= <string_literal> | <date>,可以是日期类型,也可以是日期格式字符串'20200101' or '2020-01-01'
<source_schema> ::= <string_literal> ,schema factory table is located
16.ADD_YEARS Function
ADD_YEARS(<date>, <num_years>)
*/
--ADD_DAYS
SELECT ADD_DAYS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 30) FROM DUMMY;
--ADD_MONTHS
SELECT ADD_MONTHS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) FROM DUMMY;
SELECT ADD_MONTHS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), -1) FROM DUMMY;
--ADD_MONTHS_LAST,??function不存在
--SELECT ADD_MONTHS_LAST (TO_DATE ('2009-02-28', 'YYYY-MM-DD'), 1) FROM DUMMY;
--ADD_NANO100,??function不存在
--SELECT ADD_NANO100(TO_TIMESTAMP('1990-01-01 10:00:00.0000000'), 864000000000) FROM DUMMY;
--ADD_SECONDS
SELECT ADD_SECONDS (TO_TIMESTAMP ('2012-01-01 23:30:45'), 30) FROM DUMMY;
SELECT ADD_SECONDS (TO_TIMESTAMP ('2012-01-01 23:30:45'), 30.0000001) FROM DUMMY;
--ADD_WORKDAYS,需要schema有相应表
--SELECT ADD_WORKDAYS('01', '2014-01-20', 1, 'FCTEST') "result date" FROM DUMMY;
--ADD_YEARS
SELECT ADD_YEARS(TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) FROM DUMMY;
/*
17.ALLOW_PRECISION_LOSS Function
ALLOW_PRECISION_LOSS( <aggregate_expression> )
<aggregate_expression> ::= SUM ( <expression> )
使用聚合表达式聚合十进制值时允许精度损失。
当精度损失可接受时,使用此函数可提高聚合表达式对十进制值的性能,并提高聚合的性能。
*/
--ALLOW_PRECISION_LOSS
CREATE TABLE TEST_DECIMAL (COL1 decimal(10,5), COL2 decimal);
INSERT INTO TEST_DECIMAL VALUES(1.139999, 1.138888888);
INSERT INTO TEST_DECIMAL VALUES(2.119999, 2.118888888);
INSERT INTO TEST_DECIMAL VALUES(2.119999, 2.118888888);
INSERT INTO TEST_DECIMAL VALUES(2.669999, 2.668888888);
-- The following query, which does not allow precision loss, returns 8.01, 8.01
SELECT SUM(TO_DECIMAL(COL1,10,2)), SUM(TO_DECIMAL(COL2,10,2)) FROM TEST_DECIMAL;
-- The following query, which uses the ALLOW_PRECISION_LOSS function to allow precision loss, returns 8.04, 8.04
--SELECT ALLOW_PRECISION_LOSS(SUM(TO_DECIMAL(COL1,10,2))), ALLOW_PRECISION_LOSS(SUM(TO_DECIMAL(COL2,10,2))) FROM TEST_DECIMAL;
--没有这个function?
/*
18.ASCII Function
ASCII(<string>)
返回字符对应ASCII整型数据
*/
--ASCII
--A 对应ASCII 65
SELECT ASCII('Ant') FROM DUMMY;
--a 对应ASCII 97
SELECT ASCII('ant') FROM DUMMY;
/*
19.AUTO_CORR Function
AUTO_CORR( <expression>, <maxTimeLag> { <series_order_by_clause> | <order_by_clause> })
计算给定输入表达式的所有自相关系数并返回值数组。
maxTimeLag:必须正整数;
<expression>值可以是任何数字类型;
<series_orderby> ::= SERIES( <series_period> <series_equidistant_definition> )
<series_orderby>只能与等距序列使用
<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> :指定结果值排序排序规则;
<collate_clause>只能用于定义为NVARCHAR或VARCHAR的列;
<collation_name>是COLLATIONS系统视图中列出的受支持的排序规则名称之一。
*/
实例1:
--密集序列数据返回自相关系数数组
CREATE COLUMN TABLE correlationTable (TS_ID VARCHAR(10), "DATE" DAYDATE, "VALUE" DOUBLE);
INSERT INTO correlationTable VALUES ('A', '2014-10-01', 1);
INSERT INTO correlationTable VALUES ('A', '2014-10-02', 2);
INSERT INTO correlationTable VALUES ('A', '2014-10-03', 3);
INSERT INTO correlationTable VALUES ('A', '2014-10-04', 4);
INSERT INTO correlationTable VALUES ('A', '2014-10-05', 5);
INSERT INTO correlationTable VALUES ('A', '2014-10-06', 1);
INSERT INTO correlationTable VALUES ('A', '2014-10-07', 2);
INSERT INTO correlationTable VALUES ('A', '2014-10-08', 3);
INSERT INTO correlationTable VALUES ('A', '2014-10-09', 4);
INSERT INTO correlationTable VALUES ('A', '2014-10-10', 5);
SELECT TS_ID, AUTO_CORR("VALUE", 8 SERIES (PERIOD FOR SERIES("DATE") EQUIDISTANT INCREMENT BY INTERVAL 1 DAY MISSING ELEMENTS NOT ALLOWED))
FROM correlationTable
GROUP BY TS_ID
ORDER BY TS_ID;
实例2:
--稀疏序列数据自相关系数,
--不考虑缺失条目,MISSING ELEMENT NOT ALLOWED
CREATE COLUMN TABLE correlationTable1 (TS_ID VARCHAR(20), "DATE" DAYDATE, VAL DOUBLE);
INSERT INTO correlationTable1 VALUES ('A', '2014-10-01', 1);
INSERT INTO correlationTable1 VALUES ('A', '2014-10-02', 2);
INSERT INTO correlationTable1 VALUES ('A', '2014-10-04', 3);
INSERT INTO correlationTable1 VALUES ('A', '2014-10-07', 4);
INSERT INTO correlationTable1 VALUES ('A', '2014-10-11', 5);
INSERT INTO correlationTable1 VALUES ('A', '2014-10-21', 6);
INSERT INTO correlationTable1 VALUES ('A', '2014-10-22', 7);
SELECT ts_id, AUTO_CORR(VAL, 999 SERIES (PERIOD FOR SERIES("DATE") EQUIDISTANT INCREMENT BY INTERVAL 1 DAY MISSING ELEMENTS NOT ALLOWED))
FROM correlationTable1
GROUP BY TS_ID
ORDER BY TS_ID;
实例3:
--稀疏序列数据自相关系数,
--考虑缺失条目,MISSING ELEMENT ALLOWED
CREATE COLUMN TABLE correlationTable2 (TS_ID VARCHAR(20), "DATE" DAYDATE, VAL DOUBLE);
INSERT INTO correlationTable2 VALUES ('A', '2014-10-01', 1);
INSERT INTO correlationTable2 VALUES ('A', '2014-10-02', 2);
INSERT INTO correlationTable2 VALUES ('A', '2014-10-04', 3);
INSERT INTO correlationTable2 VALUES ('A', '2014-10-07', 4);
INSERT INTO correlationTable2 VALUES ('A', '2014-10-11', 5);
INSERT INTO correlationTable2 VALUES ('A', '2014-10-21', 6);
INSERT INTO correlationTable2 VALUES ('A', '2014-10-22', 7);
SELECT ts_id, AUTO_CORR(VAL, 999 SERIES (PERIOD FOR SERIES("DATE") EQUIDISTANT INCREMENT BY INTERVAL 1 DAY MISSING ELEMENTS ALLOWED))
FROM correlationTable2
GROUP BY TS_ID
ORDER BY TS_ID;
/*
20.AVG Function
Aggregate function:
AVG( [ ALL | DISTINCT ] <expression> )
Window function:
AVG( <expression> ) <window_specification>
<window_specification>
Defines a window on the data over which the function operates.
For <window_specification>, see Window Functions and the Window Specification
AVG输出数据类型取决于输入值类型
输入类型:输出类型
TINYINT:DECIMAL(9,6)
SMALLINT:DECIMAL(11,6)
INTEGER:DECIMAL(16,6)
BIGINT:DECIMAL(25,6)
DECIMAL(p ,s):DECIMAL(p,s)
DECIMAL:DECIMAL
REAL:REAL
DOUBLE:DOUBLE
*/
--删除Table
DROP TABLE "MyProducts";
--创建Table
CREATE COLUMN TABLE "MyProducts"(
"Product_ID" VARCHAR(10),
"Product_Name" VARCHAR(100),
"Category" VARCHAR(100),
"Quantity" INTEGER,
"Price" DECIMAL(10,2),
PRIMARY KEY ("Product_ID") );
INSERT INTO "MyProducts" VALUES('P1','Shirts', 'Clothes', 32, 20.99);
INSERT INTO "MyProducts" VALUES('P2','Jackets', 'Clothes', 16, 99.49);
INSERT INTO "MyProducts" VALUES('P3','Trousers', 'Clothes', 30, 32.99);
INSERT INTO "MyProducts" VALUES('P4','Coats', 'Clothes', 5, 129.99);
INSERT INTO "MyProducts" VALUES('P5','Purse', 'Accessories', 3, 89.49);
--AVG
SELECT AVG("Price") FROM "MyProducts";