/*
60.DAYNAME
DAYNAME(<date>)
Returns the weekday in English for the specified date.
DAYOFMONTH(<date>)
Returns an integer for the day of the month for the specified date.
DAYOFYEAR(<date>)
Returns an integer representation of the day of the year for the specified date.
DAYS_BETWEEN(<date_1>, <date_2>)
Computes the number of entire days between <date_1> and <date_2>.
EXTRACT( {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM <date> )
返回日期部分
*/
SELECT DAYNAME ('2011-05-30') FROM DUMMY; SELECT DAYOFMONTH ('2011-05-30') FROM DUMMY; SELECT DAYOFYEAR ('2011-05-30') FROM DUMMY; SELECT DAYS_BETWEEN (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), TO_DATE('2010-01-05', 'YYYY-MM-DD')) FROM DUMMY; SELECT DAYS_BETWEEN('2018-02-07 23:00:00', '2018-02-08 01:00:00') FROM dummy; SELECT EXTRACT (YEAR FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) FROM DUMMY;
/*
61.RANK
RANK() <window_specification>
Returns rank of a row within a partition, starting from 1.
DENSE_RANK() <window_specification>
Performs the same ranking operation as the RANK function, except that rank numbering does not skip when ties are found.
*/
CREATE ROW TABLE TEST_RANK (class CHAR(10), val INT, offset INT); INSERT INTO TEST_RANK VALUES('A', 1, 1); INSERT INTO TEST_RANK VALUES('A', 3, 3); INSERT INTO TEST_RANK VALUES('A', 5, null); INSERT INTO TEST_RANK VALUES('A', 5, 2); INSERT INTO TEST_RANK VALUES('A', 10, 0); INSERT INTO TEST_RANK VALUES('B', 1, 3); INSERT INTO TEST_RANK VALUES('B', 1, 1); INSERT INTO TEST_RANK VALUES('B', 7, 1); --生成RANK排序值 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 TEST_RANK;
/*
62.DFT
DFT( <expression>, <N> { <series_orderby> | <order_by_clause> } ).{ REAL | IMAGINARY | AMPLITUDE | PHASE }
计算前<N>个值的离散傅里叶变换
参数说明:
<expression>: cannot contain any NULL values.
<N>:This parameter must be a power of 2.
当输入少于<N>个元素,以0填充
series_orderby :
The SERIES definition can only be used with an equidistant series.
<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>
*/
SELECT DFT("VALUE",4 ORDER BY "DATE") .REAL FROM CORRELATIONTABLE; SELECT DFT("VALUE", 8 ORDER BY "DATE").IMAGINARY FROM CORRELATIONTABLE; SELECT DFT("VALUE", 8 ORDER BY "DATE").AMPLITUDE FROM CORRELATIONTABLE; SELECT DFT("VALUE", 8 ORDER BY "DATE").PHASE FROM CORRELATIONTABLE;