HANA DB 쿼리 참조
https://help.sap.com/viewer/7c78579ce9b14a669c1f3295b0d8ca16/Cloud/en-US/6fff7f0ae9184d1db47a25791545a1b6.html
Index
Aggregate Functions
AUTO_CORR Function (Aggregate)
AVG Function (Aggregate)
CORR Function (Aggregate)
CORR_SPEARMAN Function (Aggregate)
COUNT Function (Aggregate)
CROSS_CORR Function (Aggregate)
DFT Function (Aggregate)
FIRST_VALUE Function (Aggregate)
LAST_VALUE Function (Aggregate)
MAX Function (Aggregate)
MEDIAN Function (Aggregate)
MIN Function (Aggregate)
NTH_VALUE Function (Aggregate)
STDDEV Function (Aggregate)
STDDEV_POP Function (Aggregate)
STDDEV_SAMP Function (Aggregate)
STRING_AGG Function (Aggregate)
SUM Function (Aggregate)
VAR Function (Aggregate)
VAR_POP Function (Aggregate)
VAR_SAMP Function (Aggregate)
Array Functions
CARDINALITY Function (Array)
MEMBER_AT Function (Array)
SUBARRAY Function (Array)
TRIM_ARRAY Function (Array)
Data Type Conversion Functions
CAST Function (Data Type Conversion)
TO_ALPHANUM Function (Data Type Conversion)
TO_BIGINT Function (Data Type Conversion)
TO_BINARY Function (Data Type Conversion)
TO_BLOB Function (Data Type Conversion)
TO_BOOLEAN Function (Data Type Conversion)
TO_CLOB Function (Data Type Conversion)
TO_DATE Function (Data Type Conversion)
TO_DATS Function (Data Type Conversion)
TO_DECIMAL Function (Data Type Conversion)
TO_DOUBLE Function (Data Type Conversion)
TO_FIXEDCHAR Function (Data Type Conversion)
TO_INT Function (Data Type Conversion)
TO_INTEGER Function (Data Type Conversion)
TO_JSON_BOOLEAN (Data Type Conversion)
TO_NCLOB Function (Data Type Conversion)
TO_NVARCHAR Function (Data Type Conversion)
TO_REAL Function (Data Type Conversion)
TO_SECONDDATE Function (Data Type Conversion)
TO_SMALLDECIMAL Function (Data Type Conversion)
TO_SMALLINT Function (Data Type Conversion)
TO_TIME Function (Data Type Conversion)
TO_TIMESTAMP Function (Data Type Conversion)
TO_TINYINT Function (Data Type Conversion)
TO_VARCHAR Function (Data Type Conversion)
Datetime Functions
ADD_DAYS Function (Datetime)
ADD_MONTHS Function (Datetime)
ADD_MONTHS_LAST Function (Datetime)
ADD_NANO100 Function (Datetime)
ADD_SECONDS Function (Datetime)
ADD_WORKDAYS Function (Datetime)
ADD_YEARS Function (Datetime)
CURRENT_DATE Function (Datetime)
CURRENT_MVCC_SNAPSHOT_TIMESTAMP Function (Datetime)
CURRENT_TIME Function (Datetime)
CURRENT_TIMESTAMP Function (Datetime)
CURRENT_UTCDATE Function (Datetime)
CURRENT_UTCTIME Function (Datetime)
CURRENT_UTCTIMESTAMP Function (Datetime)
DAYNAME Function (Datetime)
DAYOFMONTH Function (Datetime)
DAYOFYEAR Function (Datetime)
DAYS_BETWEEN Function (Datetime)
EXTRACT Function (Datetime)
HOUR Function (Datetime)
ISOWEEK Function (Datetime)
LAST_DAY Function (Datetime)
LOCALTOUTC Function (Datetime)
MINUTE Function (Datetime)
MONTH Function (Datetime)
MONTHNAME Function (Datetime)
MONTHS_BETWEEN Function (Datetime)
NANO100_BETWEEN Function (Datetime)
NEXT_DAY Function (Datetime)
NOW Function (Datetime)
QUARTER Function (Datetime)
SECOND Function (Datetime)
SECONDS_BETWEEN Function (Datetime)
UTCTOLOCAL Function (Datetime)
WEEK Function (Datetime)
WEEKDAY Function (Datetime)
WORKDAYS_BETWEEN Function (Datetime)
YEAR Function (Datetime)
YEARS_BETWEEN Function (Datetime)
Fulltext Functions
INDEXING_ERROR_CODE Function (Fulltext)
INDEXING_ERROR_MESSAGE Function (Fulltext)
INDEXING_STATUS Function (Fulltext)
LANGUAGE Function (Fulltext)
MIMETYPE Function (Fulltext)
PLAINTEXT Function (Fulltext)
SCORE Function (Fulltext)
Hierarchy Functions
HIERARCHY Generator Function
HIERARCHY_ANCESTORS Navigation Function
HIERARCHY_ANCESTORS_AGGREGATE Navigation Function
HIERARCHY_COMPOSITE_ID Scalar Function
HIERARCHY_DESCENDANTS Navigation Function
HIERARCHY_DESCENDANTS_AGGREGATE Navigation Function
HIERARCHY_LEVELED Generator Function
HIERARCHY_SIBLINGS Navigation Function
HIERARCHY_SPANTREE Generator Function
HIERARCHY_TEMPORAL Generator Function
JSON Functions
JSON_QUERY Function (JSON)
JSON_TABLE Function (JSON)
JSON_VALUE Function (JSON)
Miscellaneous Functions
ALLOW_PRECISION_LOSS Function (Miscellaneous)
COALESCE Function (Miscellaneous)
CONVERT_CURRENCY Function (Miscellaneous)
CONVERT_UNIT Function (Miscellaneous)
CURRENT_CONNECTION Function (Miscellaneous)
CURRENT_IDENTITY_VALUE Function (Miscellaneous)
CURRENT_OBJECT_SCHEMA Function (Miscellaneous)
CURRENT_SCHEMA Function (Miscellaneous)
CURRENT_SITE_ID Function (Miscellaneous)
CURRENT_TRANSACTION_ISOLATION_LEVEL Function (Miscellaneous)
CURRENT_UPDATE_STATEMENT_SEQUENCE Function (Miscellaneous)
CURRENT_UPDATE_TRANSACTION Function (Miscellaneous)
CURRENT_USER Function (Miscellaneous)
CURRENT_USER_ID Function (Miscellaneous)
EXPRESSION_MACRO Function (Miscellaneous)
GREATEST Function (Miscellaneous)
GROUPING Function (Miscellaneous)
GROUPING_ID Function (Miscellaneous)
HASH_MD5 Function (Miscellaneous)
HASH_SHA256 Function (Miscellaneous)
IFNULL Function (Miscellaneous)
LEAST Function (Miscellaneous)
MAP Function (Miscellaneous)
NEWUID Function (Miscellaneous)
NULLIF Function (Miscellaneous)
RECORD_COMMIT_TIMESTAMP Function (Miscellaneous)
RECORD_ID Function (Miscellaneous)
RESULT_CACHE_ID Function (Miscellaneous)
RESULT_CACHE_REFRESH_TIME Function (Miscellaneous)
SESSION_CONTEXT Function (Miscellaneous)
SESSION_USER Function (Miscellaneous)
SYSUUID Function (Miscellaneous)
WIDTH_BUCKET Function (Miscellaneous)
XMLEXTRACT Function (Miscellaneous)
XMLEXTRACTVALUE Function (Miscellaneous)
Numeric Functions
ABS Function (Numeric)
ACOS Function (Numeric)
ASIN Function (Numeric)
ATAN Function (Numeric)
ATAN2 Function (Numeric)
BITAND Function (Numeric)
BITCOUNT Function (Numeric)
BITNOT Function (Numeric)
BITOR Function (Numeric)
BITSET Function (Numeric)
BITUNSET Function (Numeric)
BITXOR Function (Numeric)
CEIL Function (Numeric)
COS Function (Numeric)
COSH Function (Numeric)
COT Function (Numeric)
EXP Function (Numeric)
FLOOR Function (Numeric)
LN Function (Numeric)
LOG Function (Numeric)
MOD Function (Numeric)
NDIV0 Function (Numeric)
POWER Function (Numeric)
RAND Function (Numeric)
RAND_SECURE Function (Numeric)
ROUND Function (Numeric)
SIGN Function (Numeric)
SIN Function (Numeric)
SINH Function (Numeric)
SQRT Function (Numeric)
TAN Function (Numeric)
TANH Function (Numeric)
UMINUS Function (Numeric)
Security Functions
ENCRYPTION_ROOT_KEYS_EXTRACT_KEYS Function (Security)
ENCRYPTION_ROOT_KEYS_HAS_BACKUP_PASSWORD Function (Security)
ESCAPE_DOUBLE_QUOTES Function (Security)
ESCAPE_SINGLE_QUOTES Function (Security)
GENERATE_PASSWORD Function (Security)
IS_SQL_INJECTION_SAFE Function (Security)
Series Data Functions
SERIES_DISAGGREGATE Function (Series Data)
SERIES_ELEMENT_TO_PERIOD Function (Series Data)
SERIES_GENERATE Function (Series Data)
SERIES_PERIOD_TO_ELEMENT Function (Series Data)
SERIES_ROUND Function (Series Data)
String Functions
ABAP_ALPHANUM Function (String)
ABAP_LOWER Function (String)
ABAP_NUMC Function (String)
ABAP_UPPER Function (String)
ASCII Function (String)
BINTOHEX Function (String)
BINTONHEX Function (String)
BINTOSTR Function (String)
CHAR Function (String)
CONCAT Function (String)
CONCAT_NAZ Function (String)
HAMMING_DISTANCE Function (String)
HEXTOBIN Function (String)
HEXTONUM Function (String)
INITCAP Function (String)
LCASE Function (String)
LEFT Function (String)
LENGTH Function (String)
LOCATE Function (String)
LOCATE_REGEXPR Function (String)
LOWER Function (String)
LPAD Function (String)
LTRIM Function (String)
NCHAR Function (String)
NORMALIZE Function (String)
NUMTOHEX Function (String)
OCCURRENCES_REGEXPR Function (String)
REPLACE Function (String)
REPLACE_REGEXPR Function (String)
RIGHT Function (String)
RPAD Function (String)
RTRIM Function (String)
SOUNDEX Function (String)
STRTOBIN Function (String)
SUBSTR_AFTER Function (String)
SUBSTR_BEFORE Function (String)
SUBSTRING Function (String)
SUBSTRING_REGEXPR Function (String)
TRIM Function (String)
UCASE Function (String)
UNICODE Function (String)
UPPER Function (String)
XMLTABLE Function (String)
Aggregate
AUTO_CORR Function (Aggregate)
Computes all autocorrelation coefficients for a given input expression and returns an array of values.
Pairs that contain at least one null are removed. Even though AUTO_CORR can handle null input values, it is highly recommended to replace null values first (e.g. by using LINEAR_APPROX), which allows for much faster processing.
The output is empty if there are fewer than two rows.
지정된 입력 표현식에 대한 모든 자기 상관 계수를 계산하고 값의 배열을 반환합니다. 하나 이상의 null이 포함된 쌍이 제거됩니다. AUTO_CORR이 null 입력 값을 처리할 수 있더라도 먼저 Null 값을 바꾸는 것이 좋습니다(예: LINEAR_APROx 사용). 이렇게 하면 훨씬 빠른 처리가 가능합니다. 행이 두 개 미만이면 출력이 비어 있습니다.
The example below shows autocorrelation of dense series data and returns [0.285714,-0.351351,-0.5625,-0.25,1,1,1,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;
The example below shows autocorrelation of sparse series data without considering missing entries, and returns [1,1,1,1,1].
CREATE COLUMN TABLE correlationTable (ts_id VARCHAR(20), date DAYDATE, val 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-04', 3);
INSERT INTO correlationTable VALUES ('A', '2014-10-07', 4);
INSERT INTO correlationTable VALUES ('A', '2014-10-11', 5);
INSERT INTO correlationTable VALUES ('A', '2014-10-21', 6);
INSERT INTO correlationTable 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 correlationTable
GROUP BY ts_id ORDER BY ts_id;
The example below shows autocorrelation of sparse series data considering the missing entries, and returns [1.0, null, 1.0, null, null, null, null, null, null, * 1.0, null, null, null, null, null, null, null, null, null, 1.0].
CREATE COLUMN TABLE correlationTable (ts_id VARCHAR(20), date DAYDATE, val 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-04', 3);
INSERT INTO correlationTable VALUES ('A', '2014-10-07', 4);
INSERT INTO correlationTable VALUES ('A', '2014-10-11', 5);
INSERT INTO correlationTable VALUES ('A', '2014-10-21', 6);
INSERT INTO correlationTable 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 correlationTable
GROUP BY ts_id ORDER BY ts_id;
AVG Function (Aggregate)
Returns the arithmetical mean of the expression. This function can also be used as a window function.
식의 산술 평균을 반환합니다. 이 기능은 Window Function 으로도 사용할 수 있습니다.
DROP TABLE "MyProducts";
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);
The following example averages the prices of all products in the MyProducts table and returns the value 74.50.:
SELECT AVG("Price") FROM "MyProducts";
CORR Function (Aggregate)
Computes the Pearson product momentum correlation coefficient between two columns. This function can also be used as a window function.
두열 사이의 피어슨 상관계수를 계산한다.
※ 피어슨 상관 계수(Pearson Correlation Coefficient ,PCC)란 두 변수 X 와 Y 간의 선형 상관 관계를 계량화한 수치
CREATE COLUMN TABLE correlationTable (
ts_id VARCHAR(20),
DATE DAYDATE,
value1 DOUBLE,
value2 DOUBLE);
INSERT INTO correlationTable VALUES ('A', '2014-10-01', 1, 1);
INSERT INTO correlationTable VALUES ('A', '2014-10-02', 2, 2);
INSERT INTO correlationTable VALUES ('A', '2014-10-04', 3, 3);
INSERT INTO correlationTable VALUES ('B', '2014-10-07', 1, 3);
INSERT INTO correlationTable VALUES ('B', '2014-10-11', 2, 2);
INSERT INTO correlationTable VALUES ('B', '2014-10-21', 3, 1);
The following aggregate function example returns the correlation between the ts_id column and the columns value1 and value2.
SELECT ts_id, CORR(value1, value2) FROM correlationTable GROUP BY ts_id;
TS_ID CORR(VALUE1, VALUE2)
A 1
B -1
The following WHERE clause example returns the correlation between the ts_id column and the columns value1 and value2 only for rows where ts_id equals A.
SELECT ts_id, CORR(value1, value2) FROM correlationTable
WHERE ts_id='A' GROUP BY ts_id;
TS_ID CORR(VALUE1, VALUE2)
A 1
The example below uses a window function.
SELECT ts_id, CORR(value1, value2) OVER (PARTITION BY ts_id) FROM correlationTable;
TS_ID CORR(VALUE1, VALUE2)OVER(PARTITIONBYTS_ID)
A 1
The example below uses a sliding window function.
SELECT ts_id, CORR(value1, value2) OVER (PARTITION BY ts_id ORDER BY date)
FROM correlationTable ORDER BY ts_id;
TS_ID CORR(VALUE1, VALUE2)OVER(PARTITIONBYTS_IDORDERBYDATE)
A ?
A 0.9999999999999998
A 0.9999999999999998
B ?
B -0.9999999999999998
B -0.9999999999999998
The example below 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 correlationTable;
TS_ID CORR(VALUE1, VALUE2)OVER(PARTITIONBYTS_IDORDERBYDATEROWS)
A ?
A 0.9999999999999998
A 0.9999999999999998
B ?
B -0.9999999999999998
B -0.9999999999999998
CORR_SPEARMAN Function
Returns the Spearman's rank correlation coefficient of the values found in the corresponding rows of <column1> and <column2>.
Returns the Spearman's rank correlation coefficient of the values found in the corresponding rows of <column1> and <column2>.
The result ranges from -1 to 1, depending on the correlation, or NULL if a correlation could not 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
Whenever a NULL value is found then both the NULL value and the corresponding value of the other input column are ignored.
COUNT Function
....
Window Functions and the Window Specification
Example
CREATE TABLE myTable (Class CHAR(10), Value INT, Offset INT);
INSERT INTO myTable VALUES('A', 1, 1);
INSERT INTO myTable VALUES('A', 3, 3);
INSERT INTO myTable VALUES('A', 5, NULL);
INSERT INTO myTable VALUES('A', 5, 2);
INSERT INTO myTable VALUES('A', 10, 0);
INSERT INTO myTable VALUES('B', 1, 3);
INSERT INTO myTable VALUES('B', 1, 1);
INSERT INTO myTable VALUES('B', 7, 1);
SELECT Class, VAL,
ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Value) AS ROW_NUM,
RANK() OVER (PARTITION BY Class ORDER BY Value) AS RANK,
DENSE_RANK() OVER (PARTITION BY Class ORDER BY Value) AS DENSE_RANK
FROM myTable;
class val row_num rank dense_rank
A 1 1 1 1
A 3 2 2 2
A 5 3 3 3
A 5 4 3 3
A 10 5 5 4
B 1 1 1 1
B 1 2 1 1
B 7 3 3 2
Window Aggregate Functions
CREATE ROW TABLE T (class CHAR(10), val INT, offset INT);
INSERT INTO T VALUES('A', 1, 1);
INSERT INTO T VALUES('A', 3, 3);
INSERT INTO T VALUES('A', 5, null);
INSERT INTO T VALUES('A', 5, 2);
INSERT INTO T VALUES('A', 10, 0);
INSERT INTO T VALUES('B', 1, 3);
INSERT INTO T VALUES('B', 1, 1);
INSERT INTO T VALUES('B', 7, 1);
SELECT class,
val,
offset,
COUNT(*) OVER (PARTITION BY class) AS c1,
COUNT(offset) OVER (PARTITION BY class) AS c2,
COUNT(*) OVER (PARTITION BY class ORDER BY val) AS c3,
COUNT(offset) OVER (PARTITION BY class ORDER BY val) AS c4,
MAX(val) OVER (PARTITION BY class) AS m1,
MAX(val) OVER (PARTITION BY class ORDER BY val) AS m2
FROM T;
CLASS VAL OFFSET C1 C2 C3 C4 M1 M2
A 1 1 5 4 1 1 10 1
A 3 3 5 4 2 2 10 3
A 5 null 5 4 4 3 10 5
A 5 2 5 4 4 3 10 5
A 10 0 5 4 5 4 10 10
B 1 3 3 3 2 2 7 1
B 1 1 3 3 2 2 7 1
B 7 1 3 3 3 3 7 7
BINNING Functions
DROP TABLE weather;
CREATE ROW TABLE weather (station INT, ts DATE, temperature FLOAT);
INSERT INTO weather VALUES(1, '2014-01-01', 0);
INSERT INTO weather VALUES(1, '2014-01-02', 3);
INSERT INTO weather VALUES(1, '2014-01-03', 4.5);
INSERT INTO weather VALUES(1, '2014-01-04', 6);
INSERT INTO weather VALUES(1, '2014-01-05', 6.3);
INSERT INTO weather VALUES(1, '2014-01-06', 5.9);
INSERT INTO weather VALUES(1, '2015-01-01', 1);
INSERT INTO weather VALUES(1, '2015-01-02', 3.4);
INSERT INTO weather VALUES(1, '2015-01-03', 5);
INSERT INTO weather VALUES(1, '2015-01-04', 6.7);
INSERT INTO weather VALUES(1, '2015-01-05', 4.6);
INSERT INTO weather VALUES(1, '2015-01-06', 6.9);
SELECT *, BINNING(VALUE => temperature, BIN_COUNT => 4) OVER () AS bin_num FROM weather;
STATION TS TEMPERATURE BIN_NUM
1 01.01.2014 0.0 1
1 02.01.2014 3.0 2
1 03.01.2014 4.5 3
1 04.01.2014 6.0 4
1 05.01.2014 6.3 4
1 06.01.2014 5.9 4
1 01.01.2014 1.0 1
1 02.01.2014 3.4 2
1 03.01.2014 5.0 3
1 04.01.2014 6.7 4
1 05.01.2014 4.6 3
1 06.01.2014 6.9 4
CUBIC_SPLINE_APPROX Function
Natural cubic spline interpolation
Perform natural cubic spline interpolation:
SELECT CUBIC_SPLINE_APPROX(temperature, 'SPLINE_TYPE_NATURAL') OVER (PARTITION BY station ORDER BY ts) FROM WEATHER;
For natural boundary conditions, the BoundaryConditionArgument can be omitted. The following query returns identical results to the above one:
SELECT CUBIC_SPLINE_APPROX(temperature) OVER(PARTITION BY station ORDER BY ts) FROM WEATHER;
Perform cubic spline interpolation by using not-a-knot boundary conditions:
SELECT CUBIC_SPLINE_APPROX(temperature, 'SPLINE_TYPE_NOT_A_KNOT') OVER(PARTITION BY station ORDER BY ts) FROM WEATHER;
Cubic spline interpolation using series table
Perform cubic spline interpolation on a series by using a series table. In this example, the not-a-knot boundary condition is used, and it returns [?, 1, 2, 7, 10, 5, ?].
DROP TABLE InterpolationTable;
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);
SELECT CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT')
OVER(SERIES TABLE InterpolationTable) AS MyResult
FROM InterpolationTable;
MYRESULT
?
1
2
7
10
5
?
Cubic spline interpolation together with linear extrapolation
This example extrapolates leading and trailing nulls, besides cubic spline interpolation. The slope of the line extrapolating the leading nulls is equal to the slope of the cubic spline at the first non-null value (which is '2013-09-29'). The slope of the line extrapolating the trailing nulls is equal to the slope of the cubic spline at the last non-null value (which is '2013-10-04'). This example refers to the InterpolationTable, as defined and populated in the previous example. This query returns [4, 1, 2, 7, 10, 5, -6].
SELECT date, val, CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT') OVER(
SERIES TABLE InterpolationTable) FROM InterpolationTable;
DATE VAL MYRESULT
Sep 29, 2013 ? ?
Sep 30, 2013 1 1
Oct 1, 2013 2 2
Oct 2, 2013 ? 7
Oct 3, 2013 10 10
Oct 4, 2013 5 5
Oct 5, 2013 ? ?
Linear extrapolation
Extrapolate leading and trailing nulls, besides cubic spline interpolation. The slope of the line extrapolating the leading nulls is equal to the slope of the cubic spline at the first non-null value ('2013-09-29'). The slope of the line extrapolating the trailing nulls is equal to the slope of the cubic spline at the last non-null value ( '2013-10-04'). This example refers to the InterpolationTable as defined and populated in the previous example. This query returns [?, 1, 2, 7, 10, 5, ?].
SELECT date, val, CUBIC_SPLINE_APPROX(val, 'SPLINE_TYPE_NOT_A_KNOT') OVER(
SERIES TABLE InterpolationTable) FROM InterpolationTable;
DATE VAL LINEAR_APPROX(VAL,'EXTRAPOLATION_LINEAR')OVER(SERIES())
Sep 29, 2013 ? 0
Sep 30, 2013 1 1
Oct 1, 2013 2 2
Oct 2, 2013 ? 6
Oct 3, 2013 10 10
Oct 4, 2013 5 5
Oct 5, 2013 ? 0
This example performs linear extrapolation on a series.
SELECT date, val, LINEAR_APPROX(val, 'EXTRAPOLATION_LINEAR') OVER(
SERIES(SERIES KEY(ts_id) PERIOD FOR SERIES(date)
EQUIDISTANT INCREMENT BY INTERVAL 1 DAY MISSING ELEMENTS ALLOWED))
FROM InterpolationTable;
This example performs linear extrapolation on a series using a series table.
DROP TABLE InterpolationTable;
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);
SELECT date, val, LINEAR_APPROX(val, 'EXTRAPOLATION_LINEAR') OVER(
SERIES TABLE InterpolationTable) FROM InterpolationTable;
This example performs linear extrapolation on a series using a series table and returns [1, 2, 6, 10].
DROP TABLE InterpolationTable;
CREATE COLUMN TABLE InterpolationTable
(TS_ID VARCHAR(20), date DAYDATE, val DOUBLE);
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);
SELECT date, val, LINEAR_APPROX (val, 'EXTRAPOLATION_LINEAR')
OVER (PARTITION BY TS_ID ORDER BY date) FROM InterpolationTable;
DATE VAL LINEAR_APPROX(VAL,'EXTRAPOLATION_LINEAR')OVER(SERIES())
Sep 30, 2013 1 1
Oct 1, 2013 2 2
Oct 2, 2013 ? 6
Oct 3, 2013 10 10
CUME_DIST Function
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;
PRODNAME DESCRIPTION SALES PERCENT_RANK CUME_DIST
Hoodie Team logo 80 1 1
Hoodie Lettered 65 0.875 0.8888888888888888
Hoodie Plain 60 0.75 0.7777777777777778
Ballcap Lettered 40 0.625 0.6666666666666666
Tee Shirt Team logo 30 0.5 0.5555555555555556
Ballcap Team logo 27 0.375 0.4444444444444444
Tee Shirt Lettered 22 0.25 0.3333333333333333
Tee Shirt Plain 21 0.125 0.2222222222222222
Ballcap Plain 8 0 0.1111111111111111
DENSE_RANK Function
In this example, the RANK function returns the rank 5 for the row ('A', 10, 0) because there were two rows that returned the rank of 3. However, DENSE_RANK returns the rank 4 for the row ('A', 10, 0).
CREATE ROW TABLE T (class CHAR(10), val INT, offset INT);
INSERT INTO T VALUES('A', 1, 1);
INSERT INTO T VALUES('A', 3, 3);
INSERT INTO T VALUES('A', 5, null);
INSERT INTO T VALUES('A', 5, 2);
INSERT INTO T VALUES('A', 10, 0);
INSERT INTO T VALUES('B', 1, 3);
INSERT INTO T VALUES('B', 1, 1);
INSERT INTO T VALUES('B', 7, 1);
SELECT class,
val,
ROW_NUMBER() OVER (PARTITION BY class ORDER BY val) AS row_num,
RANK() OVER (PARTITION BY class ORDER BY val) AS rank,
DENSE_RANK() OVER (PARTITION BY class ORDER BY val) AS dense_rank
FROM T;
CLASS VAL ROW_NUM RANK DENSE_RANK
A 1 1 1 1
A 3 2 2 2
A 5 3 3 3
A 5 4 3 3
A 10 5 5 4
B 1 1 1 1
B 1 2 1 1
B 7 3 3 2
LAG Function
The output of the LAG function can be non-deterministic among tie values.
CREATE ROW TABLE T (class CHAR(10), val INT, offset INT);
INSERT INTO T VALUES('A', 1, 1);
INSERT INTO T VALUES('A', 3, 3);
INSERT INTO T VALUES('A', 5, null);
INSERT INTO T VALUES('A', 5, 2);
INSERT INTO T VALUES('A', 10, 0);
INSERT INTO T VALUES('B', 1, 3);
INSERT INTO T VALUES('B', 1, 1);
INSERT INTO T VALUES('B', 7, 1);
SELECT class,
val,
offset,
LEAD(val) OVER (PARTITION BY class ORDER BY val) AS lead,
LEAD(val,offset,-val) OVER (PARTITION BY class ORDER BY val) AS lead2,
LAG(val) OVER (PARTITION BY class ORDER BY val) AS lag,
LAG(val,offset,-val) OVER (PARTITION BY class ORDER BY val) AS lag2
FROM T;
CLASS VAL OFFSET LEAD LEAD2 LAG LAG2
A 1 1 3 3 null -1
A 3 3 5 10 1 -3
A 5 null 5 -5 3 -5
A 5 2 10 -5 5 3
A 10 0 null 10 5 10
B 1 3 1 -1 null -1
B 1 1 7 7 1 1
B 7 1 null -7 1 1
LEAD Function
CREATE ROW TABLE T (class CHAR(10), val INT, offset INT);
INSERT INTO T VALUES('A', 1, 1);
INSERT INTO T VALUES('A', 3, 3);
INSERT INTO T VALUES('A', 5, null);
INSERT INTO T VALUES('A', 5, 2);
INSERT INTO T VALUES('A', 10, 0);
INSERT INTO T VALUES('B', 1, 3);
INSERT INTO T VALUES('B', 1, 1);
INSERT INTO T VALUES('B', 7, 1);
SELECT class,
val,
offset,
LEAD(val) OVER (PARTITION BY class ORDER BY val) AS lead,
LEAD(val, offset, -val) OVER (PARTITION BY class ORDER BY val) AS lead2,
LAG(val) OVER (PARTITION BY class ORDER BY val) AS lag,
LAG(val, offset, -val) OVER (PARTITION BY class ORDER BY val) AS lag2
FROM T;
CLASS VAL OFFSET LEAD LEAD2 LAG LAG2
A 1 1 3 3 null -1
A 3 3 5 10 1 -3
A 5 null 5 -5 3 -5
A 5 2 10 -5 5 3
A 10 0 null 10 5 10
B 1 3 1 -1 null -1
B 1 1 7 7 1 1
B 7 1 null -7 1 1
LINEAR_APPROX Function
Linear approximation without a series definition
If the SERIES definition were not specified in the example above, then the result would have been different. For the same SparseApproxTable, as populated above, the example below does not specify a series. The example below returns [-1, 0, 1, 2, 3, 4, 5, 6].
SELECT LINEAR_APPROX(val, 'EXTRAPOLATION_LINEAR') OVER (PARTITION BY ts_id ORDER BY DATE) AS approximated_value
FROM "SparseApproxTable";
In the example query above, the series is assumed to be dense and equidistant, so the date column is not taken into consideration when calculating the approximations.
Linear approximation of sparse series data
In the example below, the INCREMENT BY INTERVAL is set to 1 MONTH, meaning that at most one row is expected for each month. Apart from the null values, values are missing for several months in the table. The LINEAR_APPROX function always replaces the null values and does not insert new lines for missing values. However, if the SERIES definition is specified in the SELECT statement, then LINEAR_APPROX considers the missing months when calculating the slope between two non-null values.
CREATE COLUMN TABLE "SparseApproxTable" (ts_id VARCHAR(20), date DAYDATE, val DOUBLE);
INSERT INTO "SparseApproxTable" VALUES('A','2013-11-01', null);
INSERT INTO "SparseApproxTable" VALUES('A','2014-01-01', null);
INSERT INTO "SparseApproxTable" VALUES('A','2014-02-05', 2);
INSERT INTO "SparseApproxTable" VALUES('A','2014-03-07', null);
INSERT INTO "SparseApproxTable" VALUES('A','2014-05-01', 5);
INSERT INTO "SparseApproxTable" VALUES('A','2014-07-27', 7);
INSERT INTO "SparseApproxTable" VALUES('A','2014-12-07', null);
INSERT INTO "SparseApproxTable" VALUES('A','2015-02-07', null);
SELECT LINEAR_APPROX(val, 'EXTRAPOLATION_LINEAR') OVER (SERIES (
SERIES KEY(ts_id) EQUIDISTANT INCREMENT BY INTERVAL 1 MONTH
MISSING ELEMENTS ALLOWED PERIOD FOR SERIES(date))
PARTITION BY ts_id) AS approximated_value
FROM "SparseApproxTable";
APPROXIMATED_VALUE
-1
1
2
3
5
7
9.666666666666666
11
Linear approximation of series data
CREATE COLUMN TABLE "InterpolationTable" (TS_ID VARCHAR(20), date DAYDATE, val DOUBLE);
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);
SELECT date,
val,
LINEAR_APPROX (val, 'EXTRAPOLATION_LINEAR') OVER (PARTITION BY TS_ID ORDER BY date) AS LINEAR_APPROX
FROM "InterpolationTable";
DATE VAL MYRESULT
Sep 30, 2013 1 1
Oct 1, 2013 2 2
Oct 2, 2013 ? 6
Oct 3, 2013 10 10
NTILE Function
CREATE ROW TABLE T (class CHAR(10), val INT, offset INT);
INSERT INTO T VALUES('A', 1, 1);
INSERT INTO T VALUES('A', 3, 3);
INSERT INTO T VALUES('A', 5, null);
INSERT INTO T VALUES('A', 5, 2);
INSERT INTO T VALUES('A', 10, 0);
INSERT INTO T VALUES('B', 1, 3);
INSERT INTO T VALUES('B', 1, 1);
INSERT INTO T VALUES('B', 7, 1);
SELECT class, val,
NTILE(3) OVER (PARTITION BY class ORDER BY val) AS NTILE,
FIRST_VALUE(val) OVER (PARTITION BY class ORDER BY val) AS first,
LAST_VALUE(val) OVER (PARTITION BY class ORDER BY val) AS last,
NTH_VALUE(val, 4) OVER (PARTITION BY class ORDER BY val) AS nth
FROM T;
CLASS VAL NTILE FIRST LAST NTH
A 1 1 1 1 null
A 3 1 1 3 null
A 5 2 1 5 5
A 5 2 1 5 5
A 10 3 1 10 5
B 1 1 1 1 null
B 1 2 1 1 null
B 7 3 1 7 null
PERCENT_RANK Function
CREATE ROW TABLE ProductSales(ProdName VARCHAR(50), Type 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('Hoodie','Vintage',67);
INSERT INTO ProductSales VALUES('Ballcap','Plain',8);
INSERT INTO ProductSales VALUES('Ballcap','Lettered',40);
INSERT INTO ProductSales VALUES('Ballcap','Team logo',27);
SELECT ProdName, Type, Sales,
PERCENT_RANK() OVER (PARTITION BY ProdName ORDER BY Sales ASC) AS Percent_Rank
FROM ProductSales
ORDER BY Sales DESC;
PRODNAME Type SALES PERCENT_RANK
Hoodie Team logo 80 1
Hoodie Vintage 67 0.6666666666666666
Hoodie Lettered 65 0.3333333333333333
Hoodie Plain 60 0
Ballcap Lettered 40 1
Tee Shirt Team logo 30 1
Ballcap Team logo 27 0.5
Tee Shirt Lettered 22 0.5
Tee Shirt Plain 21 0
Ballcap Plain 8 0
PERCENTILE_CONT Function
DROP TABLE p_cont;
CREATE ROW TABLE p_cont (class CHAR(10), val INT, offset INT);
INSERT INTO p_cont VALUES('A', 1, 1);
INSERT INTO p_cont VALUES('A', 3, 3);
INSERT INTO p_cont VALUES('A', 5, null);
INSERT INTO p_cont VALUES('A', 5, 2);
INSERT INTO p_cont VALUES('A', 10, 0);
INSERT INTO p_cont VALUES('B', 1, 3);
INSERT INTO p_cont VALUES('B', 1, 1);
INSERT INTO p_cont VALUES('B', 7, 1);
SELECT class, val,
PERCENTILE_CONT(0.125) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc1,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc2,
PERCENTILE_CONT(0.875) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc3
FROM p_cont;
CLASS VAL PC1 PC2 PC3
A 1 2.0 5.0 7.5
A 3 2.0 5.0 7.5
A 5 2.0 5.0 7.5
A 5 2.0 5.0 7.5
A 10 2.0 5.0 7.5
B 1 1.0 1.0 5.5
B 1 1.0 1.0 5.5
B 7 1.0 1.0 5.5
PERCENTILE_DISC Function
CREATE ROW TABLE p_disc (class CHAR(10), val INT, offset INT);
INSERT INTO p_disc VALUES('A', 1, 1);
INSERT INTO p_disc VALUES('A', 3, 3);
INSERT INTO p_disc VALUES('A', 5, null);
INSERT INTO p_disc VALUES('A', 5, 2);
INSERT INTO p_disc VALUES('A', 10, 0);
INSERT INTO p_disc VALUES('B', 1, 3);
INSERT INTO p_disc VALUES('B', 1, 1);
INSERT INTO p_disc VALUES('B', 7, 1);
SELECT class,
val,
PERCENTILE_DISC(0.125) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd1,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd2,
PERCENTILE_DISC(0.875) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd3
FROM p_disc;
CLASS VAL PD1 PD2 PD3
A 1 1 5 10
A 3 1 5 10
A 5 1 5 10
A 5 1 5 10
A 10 1 5 10
B 1 1 1 7
B 1 1 1 7
B 7 1 1 7
RANDOM_PARTITION Function
Stratified partitions
The following example creates stratified partitions (random partitions per station with fractional partition sizes). Since the partition numbers are randomly assigned to the rows, the result might differ from call to call.
CREATE TABLE weather (station INT, ts DATE, temperature FLOAT);
INSERT INTO weather VALUES (1, '2014-01-01', 0);
INSERT INTO weather VALUES (1, '2014-01-02', 3);
INSERT INTO weather VALUES (1, '2014-01-03', 4.5);
INSERT INTO weather VALUES (1, '2014-01-04', 6);
INSERT INTO weather VALUES (1, '2014-01-05', 6.3);
INSERT INTO weather VALUES (1, '2014-01-06', 5.9);
INSERT INTO weather VALUES (2, '2014-01-01', 1);
INSERT INTO weather VALUES (2, '2014-01-02', 3.4);
INSERT INTO weather VALUES (2, '2014-01-03', 5);
INSERT INTO weather VALUES (2, '2014-01-04', 6.7);
INSERT INTO weather VALUES (2, '2014-01-05', 4.6);
INSERT INTO weather VALUES (2, '2014-01-06', 6.9);
SELECT *, RANDOM_PARTITION(0.5, 0.2, 0.3, 0) OVER (PARTITION BY station ORDER BY ts) AS part_num FROM weather;
STATION TS TEMPERATURE PART_NUM
1 01.01.2014 0.0 1
1 02.01.2014 3.0 1
1 03.01.2014 4.5 2
1 04.01.2014 6.0 3
1 05.01.2014 6.3 1
1 06.01.2014 5.9 3
2 01.01.2014 1.0 1
2 02.01.2014 3.4 1
2 03.01.2014 5.0 2
2 04.01.2014 6.7 3
2 05.01.2014 4.9 1
2 06.01.2014 6.9 3
Random partitions with explicit partition sizes
The following example creates random partitions with explicit partition sizes:
DROP TABLE weather;
CREATE ROW TABLE weather (station INT, ts DATE, temperature FLOAT);
INSERT INTO weather VALUES(1, '2014-01-01', 0.0);
INSERT INTO weather VALUES(1, '2014-01-02', 3.0);
INSERT INTO weather VALUES(1, '2014-01-03', 4.5);
INSERT INTO weather VALUES(1, '2014-01-04', 6.0);
INSERT INTO weather VALUES(1, '2014-01-05', 6.3);
INSERT INTO weather VALUES(1, '2014-01-06', 5.9);
INSERT INTO weather VALUES(2, '2014-01-01', 1.0);
INSERT INTO weather VALUES(2, '2014-01-02', 3.4);
INSERT INTO weather VALUES(2, '2014-01-03', 5.0);
INSERT INTO weather VALUES(2, '2014-01-04', 6.7);
INSERT INTO weather VALUES(2, '2014-01-05', 4.6);
INSERT INTO weather VALUES(2, '2014-01-06', 6.9);
SELECT *, RANDOM_PARTITION(5, 2, 3, 0) OVER (ORDER BY temperature) AS part_num FROM weather;
STATION TS TEMPERATURE PART_NUM
1 01.01.2014 0 0
2 01.01.2014 1 1
1 02.01.2014 3 1
2 02.01.2014 3.4 1
1 03.01.2014 4.5 1
2 05.01.2014 4.6 1
2 03.01.2014 5 2
1 06.01.2014 5.9 0
1 04.01.2014 6 2
1 05.01.2014 6.3 3
2 04.01.2014 6.7 3
2 06.01.2014 6.9 3
RANK Function
CREATE ROW TABLE ProductSales(ProdName VARCHAR(50), Type 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','Vintage',60);
INSERT INTO ProductSales VALUES('Ballcap','Plain',8);
INSERT INTO ProductSales VALUES('Ballcap','Lettered',40);
INSERT INTO ProductSales VALUES('Ballcap','Team logo',40);
SELECT ProdName, Type, Sales,
RANK() OVER ( PARTITION BY ProdName ORDER BY Sales DESC ) AS Rank
FROM ProductSales
ORDER BY ProdName, Type;
PRODNAME Type SALES RANK
Ballcap Lettered 40 2
Ballcap Plain 8 4
Ballcap Team logo 40 2
Ballcap Vintage 60 1
Hoodie Lettered 65 2
Hoodie Plain 60 3
Hoodie Team logo 80 1
Tee Shirt Lettered 22 2
Tee Shirt Plain 21 3
Tee Shirt Team logo 30 1
ROW_NUMBER Function
CREATE ROW TABLE ProductSales(ProdName VARCHAR(50), Type 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, Type, Sales,
ROW_NUMBER() OVER (PARTITION BY ProdName ORDER BY Sales DESC) AS row_num
FROM ProductSales
ORDER BY ProdName, Sales DESC;
PRODNAME DESCRIPTION SALES ROW_NUM
Ballcap Lettered 40 1
Ballcap Team logo 27 2
Ballcap Plain 8 3
Hoodie Team logo 80 1
Hoodie Lettered 65 2
Hoodie Plain 60 3
Ballcap Team logo 30 1
Ballcap Lettered 22 2
Ballcap Plain 21 3
In the following example, the ROW_NUM values for the items are different because there is no <windows_order_by_clause> specification:
SELECT ProdName, Type, Sales,
ROW_NUMBER() OVER (PARTITION BY ProdName) AS row_num
FROM ProductSales
ORDER BY ProdName, Sales DESC;
PRODNAME DESCRIPTION SALES ROW_NUM
Ballcap Lettered 40 2
Ballcap Team logo 27 3
Ballcap Plain 8 1
Hoodie Team logo 80 3
Hoodie Lettered 65 2
Hoodie Plain 60 1
Ballcap Team logo 30 3
Ballcap Lettered 22 2
Ballcap Plain 21 1
SERIES_FILTER Function
The following example illustrates single and double exponential smoothing with forecasting.
DROP TABLE weather;
CREATE COLUMN TABLE weather (ts DATE, temperature FLOAT);
INSERT INTO weather VALUES('2014-01-01', 0);
INSERT INTO weather VALUES('2014-01-02', 3);
INSERT INTO weather VALUES('2014-01-03', 4.5);
INSERT INTO weather VALUES('2014-01-04', 6);
INSERT INTO weather VALUES('2014-01-05', 6.3);
INSERT INTO weather VALUES('2014-01-06', 6.9);
INSERT INTO weather VALUES('2014-01-07', NULL);
INSERT INTO weather 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 weather;
TS TEMPERATURE SES DES
01.01.2014 0.0 NULL NULL
02.01.2014 3.0 0 3
03.01.2014 4.5 0.6000000000000001 6
04.01.2014 6.0 1.3800000000000001 8.61
05.01.2014 6.3 2.3040000000000003 10.8414
06.01.2014 6.9 3.1032 12.414036
07.01.2014 NULL 3.86256 13.46130264
08.01.2014 NULL 3.86256 15.61137648
The following example uses a window frame ROWS specification:
CREATE ROW TABLE T (class CHAR(10), val INT, offset INT);
INSERT INTO T VALUES('A', 1, 1);
INSERT INTO T VALUES('A', 3, 3);
INSERT INTO T VALUES('A', 5, null);
INSERT INTO T VALUES('A', 5, 2);
INSERT INTO T VALUES('A', 10, 0);
INSERT INTO T VALUES('B', 1, 3);
INSERT INTO T VALUES('B', 1, 1);
INSERT INTO T VALUES('B', 7, 1);
SELECT class,
val,
SUM(val) OVER(PARTITION BY class ORDER BY val) AS s0,
SUM(val) OVER(PARTITION BY class ORDER BY val ROWS BETWEENn UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s1,
SUM(val) OVER (PARTITION BY class ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s2,
SUM(val) OVER (PARTITION BY class ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS s3,
SUM(val) OVER (PARTITION BY class ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS s4
SUM(val) OVER (PARTITION BY class ORDER BY val ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s5,
SUM(val) OVER (PARTITION BY class ORDER BY val ROWS BETWEEN CURRENT ROW AND CURRENT ROW) AS s6,
SUM(val) OVER (PARTITION BY class ORDER BY val ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s7
FROM T;
CLASS VAL S0 S1 S2 S3 S4 S5 S6 S7 S8 S9 S10 S11 S12 S13 S14 S15 S16
A 1 1 24 1 null 4 24 1 4 24 1 null 4 23 8 1 1 1
A 3 4 24 4 1 9 23 3 8 24 4 1 9 20 10 4 3 4
A 5 14 24 9 4 14 20 5 10 23 8 4 13 15 15 9 5 8
A 5 14 24 14 9 24 15 5 15 20 10 8 20 10 10 14 5 10
A 10 24 24 24 14 24 10 10 10 15 15 10 15 null null 24 10 15
B 1 2 9 1 null 2 9 1 2 9 1 null 2 8 8 1 1 1
B 1 9 9 2 1 9 8 1 8 9 2 1 9 7 7 2 1 2
B 7 9 9 9 2 9 7 7 7 8 8 2 8 null null 9 7 8
WEIGHTED_AVG Function
Computes a weighted moving average by using arithmetically decreasing weights.
CREATE ROW TABLE weather (station INT, ts DATE, temperature FLOAT);
INSERT INTO weather VALUES(1, '2014-01-01', 0.0);
INSERT INTO weather VALUES(1, '2014-01-02', 3.0);
INSERT INTO weather VALUES(1, '2014-01-03', 4.5);
INSERT INTO weather VALUES(1, '2014-01-04', 6.0);
INSERT INTO weather VALUES(1, '2014-01-05', 6.3);
INSERT INTO weather VALUES(1, '2014-01-06', 5.9);
INSERT INTO weather VALUES(2, '2014-01-01', 1.0);
INSERT INTO weather VALUES(2, '2014-01-02', 3.4);
INSERT INTO weather VALUES(2, '2014-01-03', 5.0);
INSERT INTO weather VALUES(2, '2014-01-04', 6.7);
INSERT INTO weather VALUES(2, '2014-01-05', 4.6);
INSERT INTO weather VALUES(2, '2014-01-06', 6.9);
SELECT ts, temperature, WEIGHTED_AVG(temperature) OVER (ORDER BY ts ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM weather ORDER BY ts;
ts TEMPERATURE WEIGHTED_AVG
01.01.2014 0 0
01.01.2014 1 0.6666666666666666
02.01.2014 3 2.3333333333333335
02.01.2014 3.4 3.2666666666666666
03.01.2014 4.5 4.133333333333333
03.01.2014 5 4.833333333333333
04.01.2014 6 5.666666666666666
04.01.2014 6.7 6.466666666666667
05.01.2014 6.3 6.433333333333333
05.01.2014 4.6 5.166666666666666
06.01.2014 5.9 5.466666666666667
06.01.2014 6.9 6.566666666666666
Aggregate
AUTO_CORR Function (Aggregate)
The example below shows autocorrelation of dense series data and returns [0.285714,-0.351351,-0.5625,-0.25,1,1,1,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;
The example below shows autocorrelation of sparse series data without considering missing entries, and returns [1,1,1,1,1].
CREATE COLUMN TABLE correlationTable (ts_id VARCHAR(20), date DAYDATE, val 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-04', 3);
INSERT INTO correlationTable VALUES ('A', '2014-10-07', 4);
INSERT INTO correlationTable VALUES ('A', '2014-10-11', 5);
INSERT INTO correlationTable VALUES ('A', '2014-10-21', 6);
INSERT INTO correlationTable 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 correlationTable
GROUP BY ts_id ORDER BY ts_id;
The example below shows autocorrelation of sparse series data considering the missing entries, and returns [1.0, null, 1.0, null, null, null, null, null, null, * 1.0, null, null, null, null, null, null, null, null, null, 1.0].
CREATE COLUMN TABLE correlationTable (ts_id VARCHAR(20), date DAYDATE, val 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-04', 3);
INSERT INTO correlationTable VALUES ('A', '2014-10-07', 4);
INSERT INTO correlationTable VALUES ('A', '2014-10-11', 5);
INSERT INTO correlationTable VALUES ('A', '2014-10-21', 6);
INSERT INTO correlationTable 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 correlationTable
GROUP BY ts_id ORDER BY ts_id;
AVG Function (Aggregate)
DROP TABLE "MyProducts";
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);
The following example averages the prices of all products in the MyProducts table and returns the value 74.50.:
SELECT AVG("Price") FROM "MyProducts";
CORR Function (Aggregate)
CREATE COLUMN TABLE correlationTable (
ts_id VARCHAR(20),
DATE DAYDATE,
value1 DOUBLE,
value2 DOUBLE);
INSERT INTO correlationTable VALUES ('A', '2014-10-01', 1, 1);
INSERT INTO correlationTable VALUES ('A', '2014-10-02', 2, 2);
INSERT INTO correlationTable VALUES ('A', '2014-10-04', 3, 3);
INSERT INTO correlationTable VALUES ('B', '2014-10-07', 1, 3);
INSERT INTO correlationTable VALUES ('B', '2014-10-11', 2, 2);
INSERT INTO correlationTable VALUES ('B', '2014-10-21', 3, 1);
The following aggregate function example returns the correlation between the ts_id column and the columns value1 and value2.
SELECT ts_id, CORR(value1, value2) FROM correlationTable GROUP BY ts_id;
TS_ID CORR(VALUE1, VALUE2)
A 1
B -1
The following WHERE clause example returns the correlation between the ts_id column and the columns value1 and value2 only for rows where ts_id equals A.
SELECT ts_id, CORR(value1, value2) FROM correlationTable
WHERE ts_id='A' GROUP BY ts_id;
TS_ID CORR(VALUE1, VALUE2)
A 1
The example below uses a window function.
SELECT ts_id, CORR(value1, value2) OVER (PARTITION BY ts_id) FROM correlationTable;
TS_ID CORR(VALUE1, VALUE2)OVER(PARTITIONBYTS_ID)
A 1
The example below uses a sliding window function.
SELECT ts_id, CORR(value1, value2) OVER (PARTITION BY ts_id ORDER BY date)
FROM correlationTable ORDER BY ts_id;
TS_ID CORR(VALUE1, VALUE2)OVER(PARTITIONBYTS_IDORDERBYDATE)
A ?
A 0.9999999999999998
A 0.9999999999999998
B ?
B -0.9999999999999998
B -0.9999999999999998
The example below 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 correlationTable;
TS_ID CORR(VALUE1, VALUE2)OVER(PARTITIONBYTS_IDORDERBYDATEROWS)
A ?
A 0.9999999999999998
A 0.9999999999999998
B ?
B -0.9999999999999998
B -0.9999999999999998