更新时间:2024-12-20 GMT+08:00
分享

DQL语法定义

DQL语句

  • 语法定义
    query:
      values
      | {
          select
          | selectWithoutFrom
          | query UNION [ ALL ] query
          | query EXCEPT query
          | query INTERSECT query
        }
        [ ORDER BY orderItem [, orderItem ]* ]
        [ LIMIT { count | ALL } ]
        [ OFFSET start { ROW | ROWS } ]
        [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY]
    
    orderItem:
      expression [ ASC | DESC ]
    
    select:
      SELECT [ ALL | DISTINCT ]
      { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]
      [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
    
    selectWithoutFrom:
      SELECT [ ALL | DISTINCT ]
      { * | projectItem [, projectItem ]* }
    
    projectItem:
      expression [ [ AS ] columnAlias ]
      | tableAlias . *
    
    tableExpression:
      tableReference [, tableReference ]*
      | tableExpression [ NATURAL ] [ LEFT | RIGHT | FULL ] JOIN tableExpression [ joinCondition ]
    
    joinCondition:
      ON booleanExpression
      | USING '(' column [, column ]* ')'
    
    tableReference:
      tablePrimary
      [ matchRecognize ]
      [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
    
    tablePrimary:
      [ TABLE ] [ [ catalogName . ] schemaName . ] tableName
      | LATERAL TABLE '(' functionName '(' expression [, expression ]* ')' ')'
      | UNNEST '(' expression ')'
    
    values:
      VALUES expression [, expression ]*
    
    groupItem:
      expression
      | '(' ')'
      | '(' expression [, expression ]* ')'
      | CUBE '(' expression [, expression ]* ')'
      | ROLLUP '(' expression [, expression ]* ')'
      | GROUPING SETS '(' groupItem [, groupItem ]* ')'
    
    windowRef:
        windowName
      | windowSpec
    
    windowSpec:
        [ windowName ]
        '('
        [ ORDER BY orderItem [, orderItem ]* ]
        [ PARTITION BY expression [, expression ]* ]
        [
            RANGE numericOrIntervalExpression {PRECEDING}
          | ROWS numericExpression {PRECEDING}
        ]
        ')'
    
    matchRecognize:
          MATCH_RECOGNIZE '('
          [ PARTITION BY expression [, expression ]* ]
          [ ORDER BY orderItem [, orderItem ]* ]
          [ MEASURES measureColumn [, measureColumn ]* ]
          [ ONE ROW PER MATCH ]
          [ AFTER MATCH
                ( SKIP TO NEXT ROW
                | SKIP PAST LAST ROW
                | SKIP TO FIRST variable
                | SKIP TO LAST variable
                | SKIP TO variable )
          ]
          PATTERN '(' pattern ')'
          [ WITHIN intervalLiteral ]
          DEFINE variable AS condition [, variable AS condition ]*
          ')'
    
    measureColumn:
          expression AS alias
    
    pattern:
          patternTerm [ '|' patternTerm ]*
    
    patternTerm:
          patternFactor [ patternFactor ]*
    
    patternFactor:
          variable [ patternQuantifier ]
    
    patternQuantifier:
          '*'
      |   '*?'
      |   '+'
      |   '+?'
      |   '?'
      |   '??'
      |   '{' { [ minRepeat ], [ maxRepeat ] } '}' ['?']
      |   '{' repeat '}'
  • 注意事项
    1. SecMaster SQL兼容Flink 1.7.2 版本SQL语法。
    2. Flink SQL 对于标识符(表、属性、函数名)有类似于 Java 的词法约定:
      • 不管是否引用标识符,都保留标识符的大小写。
      • 且标识符需区分大小写。
      • 与 Java 不一样的地方在于,通过反引号,可以允许标识符带有非字母的字符(如:"SELECT a AS `my field` FROM t")。

      字符串文本常量需要被单引号包起来(如 SELECT 'Hello World' )。两个单引号表示转义(如 SELECT 'It''s me.')。字符串文本常量支持 Unicode 字符,如需明确使用 Unicode 编码,请使用以下语法:

      • 使用反斜杠(\)作为转义字符(默认):SELECT U&'\263A'
      • 使用自定义的转义字符: SELECT U&'#263A' UESCAPE '#'
    3. 慎用正则函数(REGEXP)

      正则表达式是非常耗时的操作,对比加减乘除通常有百倍的性能开销,而且正则表达式在某些极端情况下可能会进入无限循环,导致作业阻塞,因此建议使用LIKE。正则函数包括:

      REGEXP

      REGEXP_REPLACE

相关文档