• SAPHANA学习(5):SQL Function(D)


    /*

    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;
  • 相关阅读:
    JavaScript深入之参数按值传递
    计算机网络:这是一份全面 & 详细 的TCP协议学习指南
    前端点击下载excel表格数据
    为什么选择器:last-child有时没有起作用?
    深入理解防抖和节流函数
    收集常用正则表达式
    深入研究-webkit-overflow-scrolling:touch及ios滚动
    一文搞懂网络知识,IP、子网掩码、网关、DNS、端口号
    正则替换replace中$1的用法
    数据库连接池性能对比
  • 原文地址:https://www.cnblogs.com/tangToms/p/13886558.html
Copyright © 2020-2023  润新知