MySQL, Oracle Abc Wiki

-- 総合文法
analytic_function::=
    analytic_function([ arguments ])
       --analytic_clause
       OVER (
        --query_partition_clause
        [PARTITION BY
          { value_expr[, value_expr ]...
          | ( value_expr[, value_expr ]... )
          } ]
        --order_by_clause
        [ ORDER [ SIBLINGS ] BY
            { expr | position | c_alias }
            [ ASC | DESC ]
            [ NULLS FIRST | NULLS LAST ]
              [, { expr | position | c_alias }
                 [ ASC | DESC ]
                 [ NULLS FIRST | NULLS LAST ]
              ]...
            --windowing_clause
            [     { ROWS | RANGE }
            { BETWEEN
              { UNBOUNDED PRECEDING
              | CURRENT ROW
              | value_expr { PRECEDING | FOLLOWING }
              }
              AND
              { UNBOUNDED FOLLOWING
              | CURRENT ROW
              | value_expr { PRECEDING | FOLLOWING }
              }
            | { UNBOUNDED PRECEDING
              | CURRENT ROW
              | value_expr PRECEDING
              }
            } ] 
        ])
 
--それぞれのクローズの文法
analytic_function::=
    analytic_function([ arguments ])
       OVER (analytic_clause)
 
analytic_clause::=
    [ query_partition_clause ]
    [ order_by_clause [ windowing_clause ] ]
 
query_partition_clause::=
    PARTITION BY
      { value_expr[, value_expr ]...
      | ( value_expr[, value_expr ]... )
      }
 
order_by_clause::=
    ORDER [ SIBLINGS ] BY
    { expr | position | c_alias }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
      [, { expr | position | c_alias }
         [ ASC | DESC ]
         [ NULLS FIRST | NULLS LAST ]
      ]...
 
windowing_clause::=
    { ROWS | RANGE }
    { BETWEEN
      { UNBOUNDED PRECEDING
      | CURRENT ROW
      | value_expr { PRECEDING | FOLLOWING }
      }
      AND
      { UNBOUNDED FOLLOWING
      | CURRENT ROW
      | value_expr { PRECEDING | FOLLOWING }
      }
    | { UNBOUNDED PRECEDING
      | CURRENT ROW
      | value_expr PRECEDING
      }
    }

    ABS(n)

    ACOS(n)

    ADD_MONTHS(date, integer)

    analytic_function([ arguments ])
       OVER (analytic_clause)

    APPENDCHILDXML
      ( XMLType_instance, XPath_string, value_expr [, namespace_string ]
      )

    ASCII(char)

    ASCIISTR(char)

    ASIN(n)

    ATAN(n)

    ATAN2(n1 { , | / } n2)

    AVG([ DISTINCT | ALL ] expr)
      [ OVER(analytic_clause) ]

    BFILENAME('directory', 'filename')

    BIN_TO_NUM(expr [, expr ]... )

    BITAND(expr1, expr2)

    CARDINALITY(nested_table)

    CAST({ expr | MULTISET (subquery) } AS type_name)

    CEIL(n)

    CHARTOROWID(char)

    CHR(n [ USING NCHAR_CS ])

    CLUSTER_ID ( [ schema . ] model mining_attribute_clause )

    CLUSTER_PROBABILITY ( [ schema . ] model
       [ , cluster_id ] mining_attribute_clause )

    CLUSTER_SET ( [ schema . ] model
       [ , topN [ , cutoff ]
       ]
       mining_attribute_clause )

    COALESCE(expr [, expr ]...)

    COLLECT (column)

    COMPOSE(char)

    CONCAT(char1, char2)

    CONVERT(char, dest_char_set[, source_char_set ])

    CORR(expr1, expr2)
       [ OVER (analytic_clause) ]

    { CORR_K | CORR_S }
       (expr1, expr2
        [, { COEFFICIENT
           | ONE_SIDED_SIG
           | ONE_SIDED_SIG_POS
           | ONE_SIDED_SIG_NEG
           | TWO_SIDED_SIG
           }
        ]
       )

    COS(n)

    COSH(n)

    COUNT({ * | [ DISTINCT | ALL ] expr })
       [ OVER (analytic_clause) ]
    COVAR_POP
    COVAR_POP(expr1, expr2)
       [ OVER (analytic_clause) ]
    COVAR_SAMP
    COVAR_SAMP(expr1, expr2)
       [ OVER (analytic_clause) ]
    CUME_DIST（集計）
    CUME_DIST(expr[,expr ]...)
       WITHIN GROUP
       (ORDER BY expr [ DESC | ASC ]
              [ NULLS { FIRST | LAST } ]
             [, expr [ DESC | ASC ]
                 [ NULLS { FIRST | LAST } ]
             ]...
       )

    CUME_DIST( )
       OVER ([ query_partition_clause ] order_by_clause)

    CURRENT_DATE
    CURRENT_TIMESTAMP
    CURRENT_TIMESTAMP [ (precision) ]
    CV
    CV([ dimension_column ])

    DBTIMEZONE
    DECODE
    DECODE(expr, search, result
             [, search, result ]...
           [, default ]
          )

    DECOMPOSE( string [ CANONICAL | COMPATIBILITY ] )

    DELETEXML
      ( XMLType_instance, XPath_string
        [, namespace_string ]
      )

    DENSE_RANK(expr [, expr ]...) WITHIN GROUP
      (ORDER BY expr [ DESC | ASC ]
             [ NULLS { FIRST | LAST } ]
            [,expr [ DESC | ASC ]
               [ NULLS { FIRST | LAST } ]
            ]...
      )

    DENSE_RANK( )
       OVER([ query_partition_clause ] order_by_clause)

    DEPTH(correlation_integer)

    DEREF(expr)

    DUMP(expr[, return_fmt
            [, start_position [, length ] ]
         ]
        )

    { EMPTY_BLOB | EMPTY_CLOB }( )

    EXISTSNODE
       (XMLType_instance, XPath_string
          [, namespace_string ]
       )

    EXP(n)

    EXTRACT( { { YEAR
           | MONTH
           | DAY
           | HOUR
           | MINUTE
           | SECOND
           }
         | { TIMEZONE_HOUR
           | TIMEZONE_MINUTE
           }
         | { TIMEZONE_REGION
           | TIMEZONE_ABBR
           }
         }
         FROM { datetime_value_expression
              | interval_value_expression
              }
           )

    EXTRACT(XMLType_instance, XPath_string
        [, namespace_string ]
           )

    EXTRACTVALUE
       (XMLType_instance, XPath_string
         [, namespace_string ]
       )

    FEATURE_ID ( [ schema . ] model mining_attribute_clause )

    FEATURE_SET ( [ schema . ] model
       [ , topN [ , cutoff ]
       ]
       mining_attribute_clause )

    FEATURE_VALUE ( [ schema . ] model
       [ , feature_id ] mining_attribute_clause )

    aggregate_function
       KEEP
       (DENSE_RANK FIRST ORDER BY
        expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
        [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
        ]...
       )
       [ OVER query_partition_clause ]

    FIRST_VALUE (expr [ IGNORE NULLS ])
       OVER (analytic_clause)

    FLOOR(n)

    FROM_TZ (timestamp_value, time_zone_value)

    GREATEST(expr [, expr ]...)

    GROUP_ID( )

    GROUPING(expr)

    GROUPING_ID(expr [, expr ]...)

    HEXTORAW(char)

    INITCAP(char)

    INSERTCHILDXML
      ( XMLType_instance, XPath_string, child_expr,
          value_expr [, namespace_string ]
      )

    INSERTXMLBEFORE
       ( XMLType_instance, XPath_string,
          value_expr [, namespace_string ]
       )

    { INSTR
    | INSTRB
    | INSTRC
    | INSTR2
    | INSTR4
    }
    (string , substring [, position [, occurrence ] ])

    ITERATION_NUMBER

    LAG(value_expr [, offset ] [, default ])
       OVER ([ query_partition_clause ] order_by_clause)

    aggregate_function KEEP
       (DENSE_RANK LAST ORDER BY
        expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
        [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
        ]...
       )
       [ OVER query_partition_clause ]

    LAST_DAY(date)

    LAST_VALUE(expr [ IGNORE NULLS ])
       OVER (analytic_clause)

    LEAD(value_expr [, offset ] [, default ])
       OVER ([ query_partition_clause ] order_by_clause)

    LEAST(expr [, expr ]...)

    { LENGTH
    | LENGTHB
    | LENGTHC
    | LENGTH2
    | LENGTH4
    }
    (char)

    LN(n)

    LNNVL(condition)

    LOCALTIMESTAMP [ (timestamp_precision) ]

    LOG(n2, n1)

    LOWER(char)

    LPAD(expr1, n [, expr2 ])

    LTRIM(char [, set ])

    MAKE_REF({ table | view } , key [, key ]...)

    MAX([ DISTINCT | ALL ] expr)
       [ OVER (analytic_clause) ]

    MEDIAN(expr) [ OVER (query_partition_clause) ]

    MIN([ DISTINCT | ALL ] expr)
       [ OVER (analytic_clause) ]

    MOD(n2, n1)

    MONTHS_BETWEEN(date1, date2)

    NANVL(n2, n1)

    NCHR(number)

    NEW_TIME(date, timezone1, timezone2)

    NEXT_DAY(date, char)

    NLS_CHARSET_DECL_LEN(byte_count, 'char_set_id')

    NLS_CHARSET_ID ( string )

    NLS_CHARSET_NAME(number)

    NLS_INITCAP(char [, 'nlsparam' ])

    NLS_LOWER(char [, 'nlsparam' ])

    NLS_UPPER(char [, 'nlsparam' ])

    NLSSORT(char [, 'nlsparam' ])

    NTILE(expr)
       OVER ([ query_partition_clause ] order_by_clause)

    NULLIF(expr1, expr2)

    NUMTODSINTERVAL(n, 'interval_unit')

    NUMTOYMINTERVAL(n, 'interval_unit')

    NVL(expr1, expr2)

    NVL2(expr1, expr2, expr3)

    ORA_HASH (expr [, max_bucket [, seed_value ] ])

    PATH (correlation_integer)

    PERCENT_RANK(expr [, expr ]...) WITHIN GROUP
       (ORDER BY
        expr [ DESC | ASC ]
         [NULLS { FIRST | LAST } ]
        [, expr [ DESC | ASC ]
            [NULLS { FIRST | LAST } ]
        ]...
       )

    PERCENT_RANK( )
       OVER ([ query_partition_clause ] order_by_clause)

    PERCENTILE_CONT(expr) WITHIN GROUP
       (ORDER BY expr [ DESC | ASC ])
       [ OVER (query_partition_clause) ]

    PERCENTILE_DISC(expr) WITHIN GROUP
       (ORDER BY expr [ DESC | ASC ])
       [ OVER (query_partition_clause) ]

    POWER(n2, n1)

    POWERMULTISET(expr)

    POWERMULTISET_BY_CARDINALITY(expr, cardinality)

    PREDICTION ( [ schema . ] model [ cost_matrix_clause ] mining_attribute_clause )

    PREDICTION_COST ( [ schema . ] model [ , class ] cost_matrix_clause
     mining_attribute_clause )

    PREDICTION_DETAILS ( [ schema . ] model mining_attribute_clause )

    PREDICTION_PROBABILITY ( [ schema . ] model [ , class ]
       mining_attribute_clause )

    PREDICTION_SET ( [ schema . ] model [ , bestN [ , cutoff ] ]
      [ cost_matrix_clause ] mining_attribute_clause )

    PRESENTNNV(cell_reference, expr1, expr2)

    PRESENTV(cell_reference, expr1, expr2)

    PREVIOUS(cell_reference)

    RANK(expr [, expr ]...) WITHIN GROUP
       (ORDER BY
        expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
        [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
        ]...
       )

    RANK( )
       OVER ([ query_partition_clause ] order_by_clause)

    RATIO_TO_REPORT(expr)
       OVER ([ query_partition_clause ])

    RAWTOHEX(raw)

    RAWTONHEX(raw)

    REF (correlation_variable)

    REFTOHEX (expr)

    REGEXP_INSTR (source_char, pattern
              [, position
             [, occurrence
                [, return_option
                   [, match_parameter ]
                ]
             ]
              ]
             )

    REGEXP_REPLACE(source_char, pattern
               [, replace_string
              [, position
                 [, occurrence
                [, match_parameter ]
                 ]
              ]
               ]
              )

    REGEXP_SUBSTR(source_char, pattern
              [, position
             [, occurrence
                [, match_parameter ]
             ]
              ]
             )

    { REGR_SLOPE
    | REGR_INTERCEPT
    | REGR_COUNT
    | REGR_R2
    | REGR_AVGX
    | REGR_AVGY
    | REGR_SXX
    | REGR_SYY
    | REGR_SXY
    }
    (expr1 , expr2)
    [ OVER (analytic_clause) ]

    REMAINDER(n2, n1)

    REPLACE(char, search_string
        [, replacement_string ]
           )

    ROUND(date [, fmt ])

    ROUND(n [, integer ])

    ROW_NUMBER( )
       OVER ([ query_partition_clause ] order_by_clause)

    ROWIDTOCHAR(rowid)

    ROWIDTONCHAR(rowid)

    RPAD(expr1 , n [, expr2 ])

    RTRIM(char [, set ])

    SCN_TO_TIMESTAMP(number)

    SESSIONTIMEZONE

    SET (nested_table)

    SIGN(n)

    SIN(n)

    SINH(n)

    SOUNDEX(char)

    SQRT(n)

    STATS_BINOMIAL_TEST(expr1, expr2, p
                [, { TWO_SIDED_PROB
                   | EXACT_PROB
                   | ONE_SIDED_PROB_OR_MORE
                   | ONE_SIDED_PROB_OR_LESS
                   }
                ]
               )

    STATS_CROSSTAB(expr1, expr2
               [, { CHISQ_OBS
              | CHISQ_SIG
              | CHISQ_DF
              | PHI_COEFFICIENT
              | CRAMERS_V
              | CONT_COEFFICIENT
              | COHENS_K
              }
               ]
              )

    STATS_F_TEST(expr1, expr2
             [, { { STATISTIC
              | DF_NUM
              | DF_DEN
              | ONE_SIDED_SIG
              } expr3
            | TWO_SIDED_SIG
            }
             ]
            )

    STATS_KS_TEST(expr1, expr2
              [, { STATISTIC | SIG } ]
             )

    STATS_MODE(expr)

    STATS_MW_TEST(expr1, expr2
              [, { STATISTIC
             | U_STATISTIC
             | ONE_SIDED_SIG "expr3"
             | TWO_SIDED_SIG
             }
              ]
             )

    STATS_ONE_WAY_ANOVA(expr1, expr2
                [, { SUM_SQUARES_BETWEEN
                   | SUM_SQUARES_WITHIN
                   | DF_BETWEEN
                   | DF_WITHIN
                   | MEAN_SQUARES_BETWEEN
                   | MEAN_SQUARES_WITHIN
                   | F_RATIO
                   | SIG
                   }
                ]
               )

    { STATS_T_TEST_INDEP
    | STATS_T_TEST_INDEPU
    | STATS_T_TEST_ONE
    | STATS_T_TEST_PAIRED
    }
    (expr1, expr2
      [, { { STATISTIC
           | ONE_SIDED_SIG
           } expr3
         | TWO_SIDED_SIG
         | DF
         }
      ]
    )

    STATS_WSR_TEST(expr1, expr2
               [, { STATISTIC
              | ONE_SIDED_SIG
              | TWO_SIDED_SIG
              }
               ]
              )

    STDDEV([ DISTINCT | ALL ] expr)
       [ OVER (analytic_clause) ]

    STDDEV_POP(expr)
       [ OVER (analytic_clause) ]

    STDDEV_SAMP(expr)
       [ OVER (analytic_clause) ]

    { SUBSTR
    | SUBSTRB
    | SUBSTRC
    | SUBSTR2
    | SUBSTR4
    }
    (char, position [, substring_length ])

    SUM([ DISTINCT | ALL ] expr)
       [ OVER (analytic_clause) ]

    SYS_CONNECT_BY_PATH(column, char)

    SYS_CONTEXT('namespace', 'parameter' [, length ])

    SYS_DBURIGEN({ column | attribute }
             [ rowid ]
               [, { column | attribute }
              [ rowid ]
               ]...
             [, 'text ( )' ]
            )

    SYS_EXTRACT_UTC(datetime_with_timezone)

    SYS_GUID( )

    SYS_TYPEID(object_type_value)

    SYS_XMLAGG(expr [, fmt ])

    SYS_XMLGEN(expr [, fmt ])

    SYSDATE

    SYSTIMESTAMP

    TAN(n)

    TANH(n)

    TIMESTAMP_TO_SCN(timestamp)

    TO_BINARY_DOUBLE(expr [, fmt [, 'nlsparam' ] ])

    TO_BINARY_FLOAT(expr [, fmt [, 'nlsparam' ] ])

    TO_CHAR(nchar | clob | nclob)

    TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

    TO_CHAR(n [, fmt [, 'nlsparam' ] ])

    TO_CLOB(lob_column | char)

    TO_DATE(char [, fmt [, 'nlsparam' ] ])

    TO_DSINTERVAL(char [, 'nlsparam' ])

    TO_LOB(long_column)

    TO_MULTI_BYTE(char)

    TO_NCHAR({char | clob | nclob})

    TO_NCHAR({ datetime | interval }
         [, fmt [, 'nlsparam' ] ]
        )

    TO_NCHAR(n [, fmt [, 'nlsparam' ] ])

    TO_NCLOB(lob_column | char)

    TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])

    TO_SINGLE_BYTE(char)

    TO_TIMESTAMP(char [, fmt [, 'nlsparam' ] ])

    TO_TIMESTAMP_TZ(char [, fmt [, 'nlsparam' ] ])

    TO_YMINTERVAL(char)

    TRANSLATE(expr, from_string, to_string)

    TRANSLATE ( char USING
          { CHAR_CS | NCHAR_CS }
          )

    TREAT(expr AS [ REF ] [ schema. ]type)

    TRIM([ { { LEADING | TRAILING | BOTH }
         [ trim_character ]
           | trim_character
           }
           FROM
         ]
         trim_source
        )

    TRUNC(date [, fmt ])

    TRUNC(n1 [, n2 ])

    TZ_OFFSET({ 'time_zone_name'
          | '{ + | - } hh : mi'
          | SESSIONTIMEZONE
          | DBTMEZONE
          }
         )

    UID
    UNISTR
    UNISTR( string )

    UPDATEXML
          (XMLType_instance,
        XPath_string, value_expr
          [, XPath_string, value_expr ]...
        [, namespace_string ]
          )

    UPPER(char)

    USER
    ユーザー定義ファンクション
    [ schema. ]
    { [ package. ]function | user_defined_operator }
    [ @ dblink. ]
    [ ([ DISTINCT | ALL ] expr [, expr ]...) ]

    USERENV('parameter')

    VALUE(correlation_variable)

    VAR_POP(expr) [ OVER (analytic_clause) ]
    VAR_SAMP
    VAR_SAMP(expr) [ OVER (analytic_clause) ]
    VARIANCE
    VARIANCE([ DISTINCT | ALL ] expr)
        [ OVER (analytic_clause) ]
    VSIZE
    VSIZE(expr)

    WIDTH_BUCKET
       (expr, min_value, max_value, num_buckets)

    XMLAGG(XMLType_instance [ order_by_clause ])

    XMLCOLATTVAL
      (value_expr [ AS c_alias ]
        [, value_expr [ AS c_alias ]
          ]...
      )

    XMLCOMMENT ( value_expr )

     XMLCDATA ( value_expr )

    XMLCONCAT(XMLType_instance [, XMLType_instance ]...)

    XMLELEMENT
     ( [ NAME ] identifier
        [, XML_attributes_clause ]
        [, value_expr [ AS c_alias ]
          [, value_expr [ AS c_alias ]
        ]...
     )

    XMLFOREST
      ( value_expr [ AS c_alias ]
        [, value_expr [ AS c_alias ]
          ]...
      )

    XMLPARSE
      ({ DOCUMENT | CONTENT } value_expr [ WELLFORMED ]
      )

    XMLPI
     (
      [ NAME ] identifier
      [, value_expr ]
     )

    XMLQUERY
     ( XQuery_string
       [ XML_passing_clause ]
       RETURNING CONTENT
     )

    XMLROOT
      ( value_expr, VERSION
      { value_expr | NO VALUE }
      [, STANDALONE { YES | NO | NO VALUE } ]
      )

    XMLSEQUENCE( XMLType_instance
           | sys_refcursor_instance [, fmt ]
           )

    XMLSERIALIZE
      ( { DOCUMENT | CONTENT } value_expr
          [ AS datatype ]
      )

    XMLTABLE
     (
      [ XML_namespaces_clause , ] XQuery_string XMLTABLE_options
     )

    [ XML_passing_clause ] [ COLUMNS XML_table_column
                     [, XML_table_column
                     ]...
                   ]

    XMLTRANSFORM(XMLType_instance, XMLType_instance)