« SQL Language Reserved keywords »
Elastic Docs ›Elasticsearch Guide ›SQL

函数和操作符

edit

函数和操作符

edit

Elasticsearch SQL 提供了一套全面的内置运算符和函数：

比较运算符

edit

用于比较一个或多个表达式的布尔运算符。

相等 (=)

edit
SELECT last_name l FROM "test_emp" WHERE emp_no = 10000 LIMIT 5;

空安全相等 (<=>)

edit
SELECT 'elastic' <=> null AS "equals";

    equals
---------------
false
SELECT null <=> null AS "equals";

    equals
---------------
true

不等于 (<> 或 !=)

edit
SELECT last_name l FROM "test_emp" WHERE emp_no <> 10000 ORDER BY emp_no LIMIT 5;

比较运算符 (<, <=, >, >=)

edit
SELECT last_name l FROM "test_emp" WHERE emp_no < 10003 ORDER BY emp_no LIMIT 5;

BETWEEN

edit
SELECT last_name l FROM "test_emp" WHERE emp_no BETWEEN 9990 AND 10003 ORDER BY emp_no;

IS NULL/IS NOT NULL

edit
SELECT last_name l FROM "test_emp" WHERE emp_no IS NOT NULL AND gender IS NULL;

IN (, , ...)

edit
SELECT last_name l FROM "test_emp" WHERE emp_no IN (10000, 10001, 10002, 999) ORDER BY emp_no LIMIT 5;

逻辑运算符

edit

用于评估一个或两个表达式的布尔运算符。

AND

edit
SELECT last_name l FROM "test_emp" WHERE emp_no > 10000 AND emp_no < 10005 ORDER BY emp_no LIMIT 5;

或

edit
SELECT last_name l FROM "test_emp" WHERE emp_no < 10003 OR emp_no = 10005 ORDER BY emp_no LIMIT 5;

NOT

edit
SELECT last_name l FROM "test_emp" WHERE NOT emp_no = 10000 LIMIT 5;

数学运算符

edit

执行影响一个或两个值的数学运算。 结果是一个数值类型的值。

加 (+)

edit
SELECT 1 + 1 AS x;

减法 (中缀 -)

edit
SELECT 1 - 1 AS x;

取反 (一元 -)

edit
SELECT - 1 AS x;

乘法 (*)

edit
SELECT 2 * 3 AS x;

除法 (/)

edit
SELECT 6 / 3 AS x;

模数或余数(%)

edit
SELECT 5 % 2 AS x;

转换操作符

edit

类型转换 (::)

edit

:: 提供了与 CAST 函数不同的语法。

SELECT '123'::long AS long;

      long
---------------
123

LIKE 和 RLIKE 运算符

edit

LIKE 和 RLIKE 运算符通常用于根据字符串模式过滤数据。它们通常作用于运算符左侧的字段，但也可以作用于常量（字面量）表达式。运算符的右侧表示模式。 两者都可以在 SELECT 语句的 WHERE 子句中使用，但 LIKE 也可以在其他地方使用，例如定义一个 索引模式 或跨各种 SHOW命令。 本节仅涵盖 SELECT ... WHERE ... 用法。

与全文搜索谓词相比，LIKE/RLIKE的一个显著区别在于前者作用于精确字段，而后者还可以作用于分析过的字段。如果与LIKE/RLIKE一起使用的字段没有精确的非规范化子字段（属于关键字类型），Elasticsearch SQL将无法运行该查询。如果该字段是精确的或者具有精确的子字段，它将直接使用该字段，或者即使未在语句中显式指定，它也会自动使用精确的子字段。

LIKE

edit

概要：

expression        
LIKE constant_exp

通常是一个字段，或一个常量表达式

模式

描述: SQL 中的 LIKE 运算符用于使用通配符运算符将一个值与相似值进行比较。与 LIKE 运算符一起使用的通配符有两个:

  • 百分号 (%)
  • 下划线 (_)

百分号表示零个、一个或多个字符。下划线表示单个数字或字符。这些符号可以组合使用。

没有其他字符具有特殊含义或作为通配符。在其他语言中常用作通配符的字符（* 或 ?）被视为普通字符。

SELECT author, name FROM library WHERE name LIKE 'Dune%';

    author     |     name
---------------+---------------
Frank Herbert  |Dune
Frank Herbert  |Dune Messiah

如果需要匹配通配符本身，也可以使用转义字符。这可以通过在 LIKE ... 操作符后使用 ESCAPE [escape_character] 语句来实现：

SELECT name, author FROM library WHERE name LIKE 'Dune/%' ESCAPE '/';

在上面的示例中，/ 被定义为一个转义字符，如果需要在模式中特别匹配 % 或 _ 字符，则需要将其放在这些字符之前。默认情况下，没有定义转义字符。

尽管在Elasticsearch SQL中搜索或过滤时，LIKE是一个有效的选项，但全文搜索谓词MATCH和QUERY更快、更强大，是首选的替代方案。

RLIKE

edit

概要：

expression         
RLIKE constant_exp

通常是一个字段，或一个常量表达式

模式

描述: 这个操作符类似于 LIKE，但用户不仅限于基于带有百分号 (%) 和下划线 (_) 的固定模式进行字符串搜索；在这种情况下，模式是一个正则表达式，允许构建更灵活的模式。

有关支持的语法，请参阅正则表达式语法。

SELECT author, name FROM library WHERE name RLIKE 'Child.* Dune';

    author     |      name
---------------+----------------
Frank Herbert  |Children of Dune

尽管在Elasticsearch SQL中搜索或过滤时，RLIKE是一个有效的选项，但全文搜索谓词MATCH和QUERY更快、更强大，并且是首选的替代方案。

优先使用全文搜索谓词

edit

在使用LIKE/RLIKE时，请考虑使用全文搜索谓词，它们更快、更强大，并且提供了按相关性排序的选项（结果可以根据匹配程度返回）。

例如：

LIKE/RLIKE

查询/匹配

foo LIKE 'bar'

MATCH(foo, 'bar')

foo LIKE 'bar' AND tar LIKE 'goo'

MATCH('foo^2, tar^5', 'bar goo', 'operator=and')

foo LIKE 'barr'

QUERY('foo: bar~')

foo LIKE 'bar' AND tar LIKE 'goo'

QUERY('foo: bar AND tar: goo')

foo RLIKE 'ba.*'

MATCH(foo, 'ba', 'fuzziness=AUTO:1,5')

foo RLIKE 'b.{1}r'

MATCH(foo, 'br', 'fuzziness=1')

聚合函数

edit

用于从一组输入值中计算单个结果的函数。 Elasticsearch SQL 仅在 分组（隐式或显式）时支持聚合函数。

通用目的

edit

AVG

edit

概要：

AVG(numeric_field)

输入:

数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数会忽略此字段中的 null 值。

输出: double 数值

描述: 返回输入值的平均值（算术平均值）。

SELECT AVG(salary) AS avg FROM emp;

      avg
---------------
48248.55
SELECT AVG(salary / 12.0) AS avg FROM emp;

      avg
---------------
4020.7125

COUNT

edit

概要：

COUNT(expression)

输入:

一个字段名、通配符（*）或任何数值。对于COUNT(*)或COUNT()，所有值都被考虑，包括null或缺失的值。对于COUNT()，null值不被考虑。

输出: 数值

描述: 返回输入值的总数（计数）。

SELECT COUNT(*) AS count FROM emp;

     count
---------------
100

COUNT(ALL)

edit

概要：

COUNT(ALL field_name)

输入:

一个字段名称。如果该字段仅包含 null 值，则函数返回 null。否则，函数会忽略该字段中的 null 值。

输出: 数值

描述: 返回所有非空输入值的总数（计数）。COUNT() 和 COUNT(ALL ) 是等价的。

SELECT COUNT(ALL last_name) AS count_all, COUNT(DISTINCT last_name) count_distinct FROM emp;

   count_all   |  count_distinct
---------------+------------------
100            |96
SELECT COUNT(ALL CASE WHEN languages IS NULL THEN -1 ELSE languages END) AS count_all, COUNT(DISTINCT CASE WHEN languages IS NULL THEN -1 ELSE languages END) count_distinct FROM emp;

   count_all   |  count_distinct
---------------+---------------
100            |6

COUNT(DISTINCT)

edit

概要：

COUNT(DISTINCT field_name)

输入:

一个字段名称

输出: 数值。如果此字段仅包含null值，则函数返回null。否则，函数将忽略此字段中的null值。

描述: 返回输入值中不同非空值的总数。

SELECT COUNT(DISTINCT hire_date) unique_hires, COUNT(hire_date) AS hires FROM emp;

  unique_hires  |     hires
----------------+---------------
99              |100
SELECT COUNT(DISTINCT DATE_TRUNC('YEAR', hire_date)) unique_hires, COUNT(DATE_TRUNC('YEAR', hire_date)) AS hires FROM emp;

 unique_hires  |     hires
---------------+---------------
14             |100

FIRST/FIRST_VALUE

edit

概要：

FIRST(
    field_name               
    [, ordering_field_name])

输入:

用于聚合的目标字段

用于排序的可选字段

输出: 与输入类型相同

描述: 返回按 ordering_field_name 列排序的 field_name 输入列中的第一个非 null 值（如果存在）。如果未提供 ordering_field_name，则仅使用 field_name 列进行排序。例如：

a b

100

1

200

1

1

2

2

2

10

空

20

空

空

空

 
SELECT FIRST(a) FROM t

将导致：

FIRST(a)

1

和

SELECT FIRST(a, b) FROM t

结果将是：

FIRST(a, b)

100

 
SELECT FIRST(first_name) FROM emp;

   FIRST(first_name)
--------------------
Alejandro
SELECT gender, FIRST(first_name) FROM emp GROUP BY gender ORDER BY gender;

   gender   |   FIRST(first_name)
------------+--------------------
null        |   Berni
F           |   Alejandro
M           |   Amabile
SELECT FIRST(first_name, birth_date) FROM emp;

   FIRST(first_name, birth_date)
--------------------------------
Remzi
SELECT gender, FIRST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

    gender    |   FIRST(first_name, birth_date)
--------------+--------------------------------
null          |   Lillian
F             |   Sumant
M             |   Remzi

FIRST_VALUE 是一个名称别名，可以用来代替 FIRST，例如：

SELECT gender, FIRST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

    gender    |   FIRST_VALUE(first_name, birth_date)
--------------+--------------------------------------
null          |   Lillian
F             |   Sumant
M             |   Remzi
SELECT gender, FIRST_VALUE(SUBSTRING(first_name, 2, 6), birth_date) AS "first" FROM emp GROUP BY gender ORDER BY gender;

    gender     |     first
---------------+---------------
null           |illian
F              |umant
M              |emzi

FIRST 不能在 HAVING 子句中使用。

FIRST 不能与类型为 text 的列一起使用，除非该字段也 保存为关键词。

LAST/LAST_VALUE

edit

概要：

LAST(
    field_name               
    [, ordering_field_name])

输入:

用于聚合的目标字段

用于排序的可选字段

输出: 与输入类型相同

描述: 它是 FIRST/FIRST_VALUE 的逆函数。返回按 ordering_field_name 列降序排序的 field_name 输入列的最后一个非 null 值（如果存在）。如果未提供 ordering_field_name，则仅使用 field_name 列进行排序。例如：

a b

10

1

20

1

1

2

2

2

100

空

200

空

空

空

 
SELECT LAST(a) FROM t

将导致：

LAST(a)

200

和

SELECT LAST(a, b) FROM t

将导致：

LAST(a, b)

2

 
SELECT LAST(first_name) FROM emp;

   LAST(first_name)
-------------------
Zvonko
SELECT gender, LAST(first_name) FROM emp GROUP BY gender ORDER BY gender;

   gender   |   LAST(first_name)
------------+-------------------
null        |   Patricio
F           |   Xinglin
M           |   Zvonko
SELECT LAST(first_name, birth_date) FROM emp;

   LAST(first_name, birth_date)
-------------------------------
Hilari
SELECT gender, LAST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

   gender  |   LAST(first_name, birth_date)
-----------+-------------------------------
null       |   Eberhardt
F          |   Valdiodio
M          |   Hilari

LAST_VALUE 是一个名称别名，可以用来代替 LAST，例如：

SELECT gender, LAST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

   gender  |   LAST_VALUE(first_name, birth_date)
-----------+-------------------------------------
null       |   Eberhardt
F          |   Valdiodio
M          |   Hilari
SELECT gender, LAST_VALUE(SUBSTRING(first_name, 3, 8), birth_date) AS "last" FROM emp GROUP BY gender ORDER BY gender;

    gender     |     last
---------------+---------------
null           |erhardt
F              |ldiodio
M              |lari

LAST 不能用于 HAVING 子句中。

LAST 不能与类型为 text 的列一起使用，除非该字段也 保存为关键词。

MAX

edit

概要：

MAX(field_name)

输入:

一个数值字段。如果该字段仅包含null值，则函数返回null。否则，函数会忽略该字段中的null值。

输出: 与输入类型相同

描述: 返回字段 field_name 中输入值的最大值。

SELECT MAX(salary) AS max FROM emp;

      max
---------------
74999
SELECT MAX(ABS(salary / -12.0)) AS max FROM emp;

       max
-----------------
6249.916666666667

MAX 在一个类型为 text 或 keyword 的字段上被转换为 LAST/LAST_VALUE，因此，它不能在 HAVING 子句中使用。

MIN

edit

概要：

MIN(field_name)

输入:

一个数值字段。如果该字段仅包含null值，则函数返回null。否则，函数会忽略该字段中的null值。

输出: 与输入类型相同

描述: 返回字段 field_name 中输入值的最小值。

SELECT MIN(salary) AS min FROM emp;

      min
---------------
25324

MIN 在一个类型为 text 或 keyword 的字段上会被转换为 FIRST/FIRST_VALUE，因此，它不能在 HAVING 子句中使用。

SUM

edit

概要：

SUM(field_name)

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

输出: bigint 表示整数输入, double 表示浮点数输入

描述: 返回字段 field_name 中输入值的总和。

SELECT SUM(salary) AS sum FROM emp;

      sum
---------------
4824855
SELECT ROUND(SUM(salary / 12.0), 1) AS sum FROM emp;

      sum
---------------
402071.3

统计

edit

KURTOSIS

edit

概要：

KURTOSIS(field_name)

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

输出: double 数值

描述:

量化字段 field_name 中输入值分布的形状。

SELECT MIN(salary) AS min, MAX(salary) AS max, KURTOSIS(salary) AS k FROM emp;

      min      |      max      |        k
---------------+---------------+------------------
25324          |74999          |2.0444718929142986

KURTOSIS 不能用于标量函数或运算符之上，而只能直接用于字段上。因此，例如，以下操作是不允许的，并且会返回错误：

 SELECT KURTOSIS(salary / 12.0), gender FROM emp GROUP BY gender

MAD

edit

概要：

MAD(field_name)

输入:

一个数值字段。如果该字段仅包含null值，则函数返回null。否则，函数会忽略该字段中的null值。

输出: double 数值

描述:

测量字段 field_name 中输入值的变异性。

SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, MAD(salary) AS mad FROM emp;

      min      |      max      |      avg      |      mad
---------------+---------------+---------------+---------------
25324          |74999          |48248.55       |10096.5
SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, AVG(salary/ 12.0) AS avg, MAD(salary / 12.0) AS mad FROM emp;

       min        |       max       |      avg      |       mad
------------------+-----------------+---------------+-----------------
2110.3333333333335|6249.916666666667|4020.7125      |841.3750000000002

PERCENTILE

edit

概要：

PERCENTILE(
    field_name,         
    percentile[,        
    method[,            
    method_parameter]])

输入:

一个数值字段。如果该字段仅包含null值，则函数返回null。否则，函数会忽略该字段中的null值。

一个数值表达式（必须是一个常量，而不是基于字段的）。如果 null，函数返回 null。

用于百分位数算法的可选字符串字面量。可能的值：tdigest 或 hdr。默认值为 tdigest。

可选的数值字面量，用于配置百分位数算法。为tdigest配置compression，或为hdr配置number_of_significant_value_digits。默认值与底层算法的默认值相同。

输出: double 数值

描述:

返回字段 field_name 中输入值的第 n 个 百分位数（由 numeric_exp 参数表示）。

SELECT languages, PERCENTILE(salary, 95) AS "95th" FROM emp
       GROUP BY languages;

   languages   |      95th
---------------+-----------------
null           |74482.4
1              |71122.8
2              |70271.4
3              |71926.0
4              |69352.15
5              |56371.0
SELECT languages, PERCENTILE(salary / 12.0, 95) AS "95th" FROM emp
       GROUP BY languages;

   languages   |       95th
---------------+------------------
null           |6206.866666666667
1              |5926.9
2              |5855.949999999999
3              |5993.833333333333
4              |5779.345833333333
5              |4697.583333333333
SELECT
    languages,
    PERCENTILE(salary, 97.3, 'tdigest', 100.0) AS "97.3_TDigest",
    PERCENTILE(salary, 97.3, 'hdr', 3) AS "97.3_HDR"
FROM emp
GROUP BY languages;

   languages   | 97.3_TDigest    |   97.3_HDR
---------------+-----------------+---------------
null           |74720.036        |74992.0
1              |72316.132        |73712.0
2              |71792.436        |69936.0
3              |73326.23999999999|74992.0
4              |71753.281        |74608.0
5              |61176.16000000001|56368.0

PERCENTILE_RANK

edit

概要：

PERCENTILE_RANK(
    field_name,         
    value[,             
    method[,            
    method_parameter]])

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

一个数值表达式（必须是一个常量，而不是基于字段的）。如果 null，函数返回 null。

用于百分位数算法的可选字符串字面量。可能的值：tdigest 或 hdr。默认值为 tdigest。

可选的数值字面量，用于配置百分位数算法。为tdigest配置compression，或为hdr配置number_of_significant_value_digits。默认值与底层算法的默认值相同。

输出: double 数值

描述:

返回字段 field_name 中输入值的第 n 个 百分位排名（由 numeric_exp 参数表示）。

SELECT languages, PERCENTILE_RANK(salary, 65000) AS rank FROM emp GROUP BY languages;

   languages   |      rank
---------------+-----------------
null           |73.65766569962062
1              |73.7291625157734
2              |88.88005607010643
3              |79.43662623295829
4              |85.70446389643493
5              |96.79075152940749
SELECT languages, PERCENTILE_RANK(salary/12, 5000) AS rank FROM emp GROUP BY languages;

   languages   |       rank
---------------+------------------
null           |66.91240875912409
1              |66.70766707667076
2              |84.13266895048271
3              |61.052992625621684
4              |76.55646443990001
5              |94.00696864111498
SELECT
    languages,
    ROUND(PERCENTILE_RANK(salary, 65000, 'tdigest', 100.0), 2) AS "rank_TDigest",
    ROUND(PERCENTILE_RANK(salary, 65000, 'hdr', 3), 2) AS "rank_HDR"
FROM emp
GROUP BY languages;

   languages   | rank_TDigest  |   rank_HDR
---------------+---------------+---------------
null           |73.66          |80.0
1              |73.73          |73.33
2              |88.88          |89.47
3              |79.44          |76.47
4              |85.7           |83.33
5              |96.79          |95.24

偏度

edit

概要：

SKEWNESS(field_name)

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

输出: double 数值

描述:

量化字段 field_name 中输入值的非对称分布。

SELECT MIN(salary) AS min, MAX(salary) AS max, SKEWNESS(salary) AS s FROM emp;

      min      |      max      |        s
---------------+---------------+------------------
25324          |74999          |0.2707722118423227

SKEWNESS 不能在标量函数之上使用，而只能直接在字段上使用。因此，例如，以下操作是不允许的，并且会返回错误：

 SELECT SKEWNESS(ROUND(salary / 12.0, 2), gender FROM emp GROUP BY gender

STDDEV_POP

edit

概要：

STDDEV_POP(field_name)

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

输出: double 数值

描述:

返回字段 field_name 中输入值的总体标准差。

SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_POP(salary) AS stddev FROM emp;

      min      |      max      |      stddev
---------------+---------------+------------------
25324          |74999          |13765.125502787832
SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, STDDEV_POP(salary / 12.0) AS stddev FROM emp;

       min        |       max       |     stddev
------------------+-----------------+-----------------
2110.3333333333335|6249.916666666667|1147.093791898986

STDDEV_SAMP

edit

概要：

STDDEV_SAMP(field_name)

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

输出: double 数值

描述:

返回字段 field_name 中输入值的样本标准差。

SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_SAMP(salary) AS stddev FROM emp;

      min      |      max      |      stddev
---------------+---------------+------------------
25324          |74999          |13834.471662090747
SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, STDDEV_SAMP(salary / 12.0) AS stddev FROM emp;

       min        |       max       |     stddev
------------------+-----------------+-----------------
2110.3333333333335|6249.916666666667|1152.872638507562

平方和

edit

概要：

SUM_OF_SQUARES(field_name)

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

输出: double 数值

描述:

返回字段 field_name 中输入值的平方和。

SELECT MIN(salary) AS min, MAX(salary) AS max, SUM_OF_SQUARES(salary) AS sumsq
       FROM emp;

      min      |      max      |     sumsq
---------------+---------------+----------------
25324          |74999          |2.51740125721E11
SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, SUM_OF_SQUARES(salary / 24.0) AS sumsq FROM emp;

       min        |       max        |       sumsq
------------------+------------------+-------------------
1055.1666666666667|3124.9583333333335|4.370488293767361E8

VAR_POP

edit

概要：

VAR_POP(field_name)

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

输出: double 数值

描述:

返回字段 field_name 中输入值的总体方差。

SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_POP(salary) AS varpop FROM emp;

      min      |      max      |     varpop
---------------+---------------+----------------
25324          |74999          |1.894786801075E8
SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, VAR_POP(salary / 24.0) AS varpop FROM emp;

       min        |       max        |      varpop
------------------+------------------+------------------
1055.1666666666667|3124.9583333333335|328956.04185329855

VAR_SAMP

edit

概要：

VAR_SAMP(field_name)

输入:

一个数值字段。如果此字段仅包含 null 值，则函数返回 null。否则，函数将忽略此字段中的 null 值。

输出: double 数值

描述:

返回字段 field_name 中输入值的样本方差。

SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_SAMP(salary) AS varsamp FROM emp;

      min      |      max      |     varsamp
---------------+---------------+----------------
25324          |74999          |1.913926061691E8
SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, VAR_SAMP(salary / 24.0) AS varsamp FROM emp;

       min        |       max        |     varsamp
------------------+------------------+----------------
1055.1666666666667|3124.9583333333335|332278.830154847

分组函数

edit

用于创建特殊分组（也称为分桶）的函数；因此，这些函数需要作为分组的一部分使用。

直方图

edit

概要：

HISTOGRAM(
    numeric_exp,        
    numeric_interval)   

HISTOGRAM(
    date_exp,           
    date_time_interval)

输入:

数值表达式（通常是一个字段）。如果此字段仅包含null值，则函数返回null。否则，函数将忽略此字段中的null值。

数值区间。如果为null，函数返回null。

日期/时间表达式（通常是一个字段）。如果此字段仅包含null值，则函数返回null。否则，函数会忽略此字段中的null值。

日期/时间 时间间隔。如果为null，函数返回null。

输出: 根据给定间隔划分的非空桶或组

描述: 直方图函数将所有匹配的值按照给定的间隔分成固定大小的桶，使用（大致）以下公式：

bucket_key = Math.floor(value / interval) * interval

SQL中的直方图不会像传统的直方图和日期直方图那样为缺失的区间返回空桶。这种行为在SQL中并不适用，因为SQL将所有缺失值视为null；因此，直方图将所有缺失值放置在null组中。

直方图可以应用于数值字段：

SELECT HISTOGRAM(salary, 5000) AS h FROM emp GROUP BY h;

       h
---------------
25000
30000
35000
40000
45000
50000
55000
60000
65000
70000

或日期/时间字段：

SELECT HISTOGRAM(birth_date, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM emp GROUP BY h;


           h            |       c
------------------------+---------------
null                    |10
1952-01-01T00:00:00.000Z|8
1953-01-01T00:00:00.000Z|11
1954-01-01T00:00:00.000Z|8
1955-01-01T00:00:00.000Z|4
1956-01-01T00:00:00.000Z|5
1957-01-01T00:00:00.000Z|4
1958-01-01T00:00:00.000Z|7
1959-01-01T00:00:00.000Z|9
1960-01-01T00:00:00.000Z|8
1961-01-01T00:00:00.000Z|8
1962-01-01T00:00:00.000Z|6
1963-01-01T00:00:00.000Z|7
1964-01-01T00:00:00.000Z|4
1965-01-01T00:00:00.000Z|1

直方图内的表达式也同样支持，只要返回类型是数值型：

SELECT HISTOGRAM(salary % 100, 10) AS h, COUNT(*) AS c FROM emp GROUP BY h;

       h       |       c
---------------+---------------
0              |10
10             |15
20             |10
30             |14
40             |9
50             |9
60             |8
70             |13
80             |3
90             |9

请注意，直方图（以及一般的分组函数）允许自定义表达式，但不能在GROUP BY中应用任何函数。换句话说，以下语句是不允许的：

SELECT MONTH(HISTOGRAM(birth_date), 2)) AS h, COUNT(*) as c FROM emp GROUP BY h ORDER BY h DESC;

因为它需要两个分组（一个用于直方图，然后第二个用于在直方图组上应用函数）。

相反，可以将查询重写为将直方图上的表达式移入其中：

SELECT HISTOGRAM(MONTH(birth_date), 2) AS h, COUNT(*) as c FROM emp GROUP BY h ORDER BY h DESC;

       h       |       c
---------------+---------------
12             |7
10             |17
8              |16
6              |16
4              |18
2              |10
0              |6
null           |10

当直方图在 SQL 中应用于 DATE 类型而不是 DATETIME 类型时，指定的间隔会被截断为一天的倍数。例如：对于 HISTOGRAM(CAST(birth_date AS DATE), INTERVAL '2 3:04' DAY TO MINUTE)，实际使用的间隔将是 INTERVAL '2' DAY。如果指定的间隔小于 1 天，例如：HISTOGRAM(CAST(birth_date AS DATE), INTERVAL '20' HOUR)，那么使用的间隔将是 INTERVAL '1' DAY。

为日期/时间直方图指定的所有间隔将在其date_histogram聚合定义中使用固定间隔，但INTERVAL '1' YEAR、INTERVAL '1' MONTH和INTERVAL '1' DAY除外，这些情况下使用日历间隔。选择日历间隔是为了使年、月和日的分组结果更加直观。例如，对于年份，日历间隔将一年视为从该年1月1日开始的一年，而固定间隔的一年桶则将一年视为一定数量的毫秒（例如，31536000000ms对应于365天，每天24小时，每小时60分钟等）。使用固定间隔时，例如2019年2月5日的日期属于从2018年12月20日开始的桶，Elasticsearch（以及隐含的Elasticsearch SQL）将返回2018年，而实际上该日期在2019年。使用日历间隔时，这种行为更加直观，2019年2月5日的日期实际上属于2019年的年份桶。

SQL中的直方图不能应用于时间类型。 例如：HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES)目前不受支持。

日期/时间及间隔函数和操作符

edit

Elasticsearch SQL 提供了广泛的日期/时间操作功能。

时间间隔

edit

在处理日期/时间时，一个常见的需求围绕着区间的概念，这个话题在Elasticsearch和Elasticsearch SQL的背景下值得探讨。

Elasticsearch 对 日期数学 在 索引名称 和 查询 中都有全面的支持。 在 Elasticsearch SQL 中，前者通过在表名中传递表达式来支持，而后者则通过标准的 SQL INTERVAL 来支持。

下表显示了Elasticsearch和Elasticsearch SQL之间的映射关系：

Elasticsearch

Elasticsearch SQL

索引/表日期时间计算

查询日期/时间计算

1年

INTERVAL 1 YEAR

2M

INTERVAL 2 MONTH

3w

INTERVAL 21 DAY

4d

INTERVAL 4 DAY

5小时

INTERVAL 5 HOUR

6米

INTERVAL 6 分钟

7秒

INTERVAL 7 SECOND

INTERVAL 允许 YEAR 和 MONTH 混合在一起 或 DAY、HOUR、MINUTE 和 SECOND。

Elasticsearch SQL 也接受每个时间单位的复数形式（例如，YEAR 和 YEARS 都是有效的）。

可能的组合示例如下：

区间

描述

INTERVAL '1-2' YEAR TO MONTH

1年和2个月

INTERVAL '3 4' DAYS TO HOURS

3天4小时

INTERVAL '5 6:12' DAYS TO MINUTES

5天，6小时和12分钟

INTERVAL '3 4:56:01' DAY TO SECOND

3天，4小时，56分钟和1秒

INTERVAL '2 3:45:01.23456789' DAY TO SECOND

2天，3小时，45分钟，1秒和234567890纳秒

INTERVAL '123:45' HOUR TO MINUTES

123 小时和 45 分钟

INTERVAL '65:43:21.0123' HOUR TO SECONDS

65小时，43分钟，21秒和12300000纳秒

INTERVAL '45:01.23' 分钟到秒

45分钟，1秒和230000000纳秒

比较

edit

日期/时间字段可以与日期数学表达式使用等式（=）和IN运算符进行比较：

SELECT hire_date FROM emp WHERE hire_date = '1987-03-01||+4y/y';

       hire_date
------------------------
1991-01-26T00:00:00.000Z
1991-10-22T00:00:00.000Z
1991-09-01T00:00:00.000Z
1991-06-26T00:00:00.000Z
1991-08-30T00:00:00.000Z
1991-12-01T00:00:00.000Z
SELECT hire_date FROM emp WHERE hire_date IN ('1987-03-01||+2y/M', '1987-03-01||+3y/M');

       hire_date
------------------------
1989-03-31T00:00:00.000Z
1990-03-02T00:00:00.000Z

运算符

edit

基本算术运算符（+、-、*）支持日期/时间参数，如下所示：

SELECT INTERVAL 1 DAY + INTERVAL 53 MINUTES AS result;

    result
---------------
+1 00:53:00
SELECT CAST('1969-05-13T12:34:56' AS DATETIME) + INTERVAL 49 YEARS AS result;

       result
--------------------
2018-05-13T12:34:56Z
SELECT - INTERVAL '49-1' YEAR TO MONTH result;

    result
---------------
-49-1
SELECT INTERVAL '1' DAY - INTERVAL '2' HOURS AS result;

    result
---------------
+0 22:00:00
SELECT CAST('2018-05-13T12:34:56' AS DATETIME) - INTERVAL '2-8' YEAR TO MONTH AS result;

       result
--------------------
2015-09-13T12:34:56Z
SELECT -2 * INTERVAL '3' YEARS AS result;

    result
---------------
-6-0

函数

edit

针对日期/时间的函数。

CURRENT_DATE/CURDATE

edit

概要：

CURRENT_DATE
CURRENT_DATE()
CURDATE()

输入: 无

输出: 日期

描述: 返回当前查询到达服务器时的日期（无时间部分）。 它可以作为关键字使用：CURRENT_DATE 或作为无参数的函数使用：CURRENT_DATE()。

与CURRENT_DATE不同，CURDATE()只能作为不带参数的函数使用，而不能作为关键字使用。

此方法在同一查询中每次出现时总是返回相同的值。

SELECT CURRENT_DATE AS result;

         result
------------------------
2018-12-12
SELECT CURRENT_DATE() AS result;

         result
------------------------
2018-12-12
SELECT CURDATE() AS result;

         result
------------------------
2018-12-12

通常，此函数（以及其孪生函数 TODAY()）用于相对日期过滤：

SELECT first_name FROM emp WHERE hire_date > TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5;

 first_name
------------
Alejandro
Amabile
Anoosh
Basil
Bojan

当前时间/当前时间

edit

概要：

CURRENT_TIME
CURRENT_TIME([precision]) 
CURTIME

输入:

小数位数；可选

输出: 时间

描述: 返回当前查询到达服务器的时间。 作为一个函数，CURRENT_TIME() 接受 精度 作为可选参数，用于舍入秒的小数部分（纳秒）。默认的 精度 是 3， 这意味着将返回毫秒精度的当前时间。

此方法在同一查询中的每次出现时总是返回相同的值。

SELECT CURRENT_TIME AS result;

         result
------------------------
12:31:27.237Z
SELECT CURRENT_TIME() AS result;

         result
------------------------
12:31:27.237Z
SELECT CURTIME() AS result;

         result
------------------------
12:31:27.237Z
SELECT CURRENT_TIME(1) AS result;

         result
------------------------
12:31:27.2Z

通常，此函数用于相对日期/时间过滤：

SELECT first_name FROM emp WHERE CAST(hire_date AS TIME) > CURRENT_TIME() - INTERVAL 20 MINUTES ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

目前，使用大于6的精度对函数的输出没有任何影响，因为返回的最大秒小数位数为6。

CURRENT_TIMESTAMP

edit

概要：

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])

输入:

小数位数；可选

输出: 日期/时间

描述: 返回当前查询到达服务器的时间/日期。 作为一个函数，CURRENT_TIMESTAMP() 接受 精度 作为可选参数，用于舍入秒的小数部分（纳秒）。默认的 精度 是 3， 这意味着将返回一个毫秒精度的当前日期/时间。

此方法在同一查询中的每次出现时总是返回相同的值。

SELECT CURRENT_TIMESTAMP AS result;

         result
------------------------
2018-12-12T14:48:52.448Z
SELECT CURRENT_TIMESTAMP() AS result;

         result
------------------------
2018-12-12T14:48:52.448Z
SELECT CURRENT_TIMESTAMP(1) AS result;

         result
------------------------
2018-12-12T14:48:52.4Z

通常，此函数（以及其孪生函数 NOW()) 用于相对日期/时间过滤：

SELECT first_name FROM emp WHERE hire_date > NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

目前，使用大于6的精度对函数的输出没有任何影响，因为返回的最大秒小数位数为6。

DATE_ADD/DATEADD/TIMESTAMP_ADD/TIMESTAMPADD

edit

概要：

DATE_ADD(
    string_exp, 
    integer_exp, 
    datetime_exp)

输入:

表示要添加到日期/日期时间的日期/时间单位的字符串表达式。 如果为null，函数返回null。

整数表达式，表示上述单位应添加到日期/时间中的次数，如果使用负值，则结果是从日期/时间中减去。如果为null，函数返回null。

日期/日期时间表达式。如果为null，函数返回null。

输出: 日期时间

描述: 将给定的日期/时间单位数添加到日期/日期时间。如果单位数为负数，则从日期/日期时间中减去。

如果第二个参数是长整型，由于会从中提取并使用一个整数值，因此可能会发生截断。

Datetime units to add/subtract

单位

缩写

年

年份, yy, yyyy

季度

季度, qq, q

月份

月份, mm, m

一年中的第几天

dy, y

天

天数, dd, d

周

周, 周, 周

工作日

工作日, dw

小时

小时, hh

分钟

分钟, mi, n

第二

秒, ss, s

毫秒

毫秒, ms

微秒

微秒, mcs

纳秒

纳秒, ns

 
SELECT DATE_ADD('years', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 years";

      +10 years
------------------------
2029-09-04T11:22:33.000Z
SELECT DATE_ADD('week', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 weeks";

      +10 weeks
------------------------
2019-11-13T11:22:33.000Z
SELECT DATE_ADD('seconds', -1234, '2019-09-04T11:22:33.000Z'::datetime) AS "-1234 seconds";

      -1234 seconds
------------------------
2019-09-04T11:01:59.000Z
SELECT DATE_ADD('qq', -417, '2019-09-04'::date) AS "-417 quarters";

      -417 quarters
------------------------
1915-06-04T00:00:00.000Z
SELECT DATE_ADD('minutes', 9235, '2019-09-04'::date) AS "+9235 minutes";

      +9235 minutes
------------------------
2019-09-10T09:55:00.000Z

DATE_DIFF/DATEDIFF/TIMESTAMP_DIFF/TIMESTAMPDIFF

edit

概要：

DATE_DIFF(
    string_exp, 
    datetime_exp, 
    datetime_exp)

输入:

表示日期/时间单位差异的字符串表达式，用于以下两个日期/时间表达式之间。如果为null，函数返回null。

开始日期/日期时间表达式。如果null，函数返回null。

结束日期/日期时间表达式。如果null，函数返回null。

输出: 整数

描述: 从第三个参数中减去第二个参数，并以第一个参数指定的单位返回它们的差值。如果第二个参数（开始）大于第三个参数（结束），则返回负值。

Datetime difference units

单位

缩写

年

年份, yy, yyyy

季度

季度, qq, q

月份

月份, mm, m

一年中的第几天

dy, y

天

天数, dd, d

周

周, 周, 周

工作日

工作日, dw

小时

小时, hh

分钟

分钟, mi, n

第二

秒, ss, s

毫秒

毫秒, ms

微秒

微秒, mcs

纳秒

纳秒, ns

 
SELECT DATE_DIFF('years', '2019-09-04T11:22:33.000Z'::datetime, '2032-09-04T22:33:11.000Z'::datetime) AS "diffInYears";

      diffInYears
------------------------
13
SELECT DATE_DIFF('week', '2019-09-04T11:22:33.000Z'::datetime, '2016-12-08T22:33:11.000Z'::datetime) AS "diffInWeeks";

      diffInWeeks
------------------------
-143
SELECT DATE_DIFF('seconds', '2019-09-04T11:22:33.123Z'::datetime, '2019-07-12T22:33:11.321Z'::datetime) AS "diffInSeconds";

      diffInSeconds
------------------------
-4625362
SELECT DATE_DIFF('qq', '2019-09-04'::date, '2025-04-25'::date) AS "diffInQuarters";

      diffInQuarters
------------------------
23

对于小时和分钟，DATEDIFF不会进行任何四舍五入，而是首先将两个日期中更详细的时间字段截断为零，然后进行减法计算。

SELECT DATEDIFF('hours', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T23:59:59.999Z'::datetime) AS "diffInHours";

      diffInHours
------------------------
11
SELECT DATEDIFF('minute', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T12:15:59.999Z'::datetime) AS "diffInMinutes";

      diffInMinutes
------------------------
5
SELECT DATE_DIFF('minutes', '2019-09-04'::date, '2015-08-17T22:33:11.567Z'::datetime) AS "diffInMinutes";

      diffInMinutes
------------------------
-2128407

DATE_FORMAT

edit

概要：

DATE_FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

输入:

日期/日期时间/时间表达式。如果null，函数返回null。

格式模式。如果为null或空字符串，函数返回null。

输出: 字符串

描述: 使用第二个参数中指定的格式将日期/日期时间/时间作为字符串返回。格式化模式是 MySQL DATE_FORMAT() 函数中使用的说明符之一。

如果第一个参数是类型 time，则第二个参数指定的模式不能包含与日期相关的单位（例如 dd、MM、yyyy 等）。如果包含此类单位，则会返回错误。月份和日期说明符的范围（%c、%D、%d、%e、%m）从一开始，这与 MySQL 不同，MySQL 从零开始，因为 MySQL 允许存储不完整日期，例如 2014-00-00。在这种情况下，Elasticsearch 会返回错误。

SELECT DATE_FORMAT(CAST('2020-04-05' AS DATE), '%d/%m/%Y') AS "date";

      date
------------------
05/04/2020
SELECT DATE_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), '%d/%m/%Y %H:%i:%s.%f') AS "datetime";

      datetime
------------------
05/04/2020 11:22:33.987654
SELECT DATE_FORMAT(CAST('23:22:33.987' AS TIME), '%H %i %s.%f') AS "time";

      time
------------------
23 22 33.987000

DATE_PARSE

edit

概要：

DATE_PARSE(
    string_exp, 
    string_exp)

输入:

日期表达式作为字符串。如果为null或空字符串，函数返回null。

解析模式。如果为null或空字符串，函数返回null。

输出: 日期

描述: 通过使用第二个参数中指定的格式解析第一个参数来返回一个日期。解析格式模式使用的是来自 java.time.format.DateTimeFormatter的模式。

如果解析模式不包含所有有效的日期单位（例如 HH:mm:ss、dd-MM HH:mm:ss 等），则会返回一个错误，因为函数需要返回一个包含日期部分的 date 类型的值。

SELECT DATE_PARSE('07/04/2020', 'dd/MM/yyyy') AS "date";

   date
-----------
2020-04-07

生成的 date 将具有用户通过 time_zone/timezone REST/驱动程序参数指定的时区，且不会进行转换。

{
    "query" : "SELECT DATE_PARSE('07/04/2020', 'dd/MM/yyyy') AS \"date\"",
    "time_zone" : "Europe/Athens"
}

   date
------------
2020-04-07T00:00:00.000+03:00

DATETIME_FORMAT

edit

概要：

DATETIME_FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

输入:

日期/日期时间/时间表达式。如果null，函数返回null。

格式模式。如果为null或空字符串，函数返回null。

输出: 字符串

描述: 使用第二个参数中指定的格式将日期/日期时间/时间作为字符串返回。使用的格式化模式是来自 java.time.format.DateTimeFormatter。

如果第一个参数是类型 time，那么第二个参数指定的模式不能包含与日期相关的单位（例如 dd、MM、yyyy 等）。如果包含此类单位，则会返回错误。

SELECT DATETIME_FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/yyyy') AS "date";

      date
------------------
05/04/2020
SELECT DATETIME_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/yyyy HH:mm:ss.SS') AS "datetime";

      datetime
------------------
05/04/2020 11:22:33.98
SELECT DATETIME_FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.S') AS "time";

      time
------------------
11 22 33.9

DATETIME_PARSE

edit

概要：

DATETIME_PARSE(
    string_exp, 
    string_exp)

输入:

日期时间表达式作为字符串。如果为null或空字符串，函数返回null。

解析模式。如果为null或空字符串，函数返回null。

输出: 日期时间

描述: 通过使用第二个参数中指定的格式解析第一个参数，返回一个日期时间。解析格式模式使用的是来自 java.time.format.DateTimeFormatter的格式。

如果解析模式仅包含日期或仅包含时间单位（例如 dd/MM/yyyy、HH:mm:ss 等），则会返回错误，因为该函数需要返回一个包含日期和时间的 datetime 类型的值。

SELECT DATETIME_PARSE('07/04/2020 10:20:30.123', 'dd/MM/yyyy HH:mm:ss.SSS') AS "datetime";

      datetime
------------------------
2020-04-07T10:20:30.123Z
SELECT DATETIME_PARSE('10:20:30 07/04/2020 Europe/Berlin', 'HH:mm:ss dd/MM/yyyy VV') AS "datetime";

      datetime
------------------------
2020-04-07T08:20:30.000Z

如果在日期时间字符串表达式和解析模式中未指定时区，则生成的 datetime 将具有用户通过 time_zone/timezone REST/驱动程序参数指定的时区，且不进行转换。

{
    "query" : "SELECT DATETIME_PARSE('10:20:30 07/04/2020', 'HH:mm:ss dd/MM/yyyy') AS \"datetime\"",
    "time_zone" : "Europe/Athens"
}

      datetime
-----------------------------
2020-04-07T10:20:30.000+03:00

TIME_PARSE

edit

概要：

TIME_PARSE(
    string_exp, 
    string_exp)

输入:

时间表达式作为字符串。如果为null或空字符串，函数返回null。

解析模式。如果为null或空字符串，函数返回null。

输出: 时间

描述: 使用第二个参数指定的格式解析第一个参数，返回一个时间。解析格式模式使用的是来自 java.time.format.DateTimeFormatter的格式。

如果解析模式仅包含日期单位（例如 dd/MM/yyyy），则会返回错误，因为该函数需要返回一个仅包含时间的 time 类型的值。

SELECT TIME_PARSE('10:20:30.123', 'HH:mm:ss.SSS') AS "time";

     time
---------------
10:20:30.123Z
SELECT TIME_PARSE('10:20:30-01:00', 'HH:mm:ssXXX') AS "time";

     time
---------------
11:20:30.000Z

如果在时间字符串表达式和解析模式中未指定时区，则生成的 time 将具有用户通过 time_zone/timezone REST/驱动程序参数指定的时区的偏移量，该偏移量在 Unix 纪元日期（1970-01-01）上应用，且不进行转换。

{
    "query" : "SELECT DATETIME_PARSE('10:20:30', 'HH:mm:ss') AS \"time\"",
    "time_zone" : "Europe/Athens"
}

      time
------------------------------------
10:20:30.000+02:00

DATE_PART/DATEPART

edit

概要：

DATE_PART(
    string_exp, 
    datetime_exp)

输入:

表示要从日期/时间中提取的单位的字符串表达式。如果为null，函数返回null。

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取指定的单位。它类似于 EXTRACT，但具有不同的名称和单位别名，并提供更多选项（例如：TZOFFSET）。

Datetime units to extract

单位

缩写

年

年份, yy, yyyy

季度

季度, qq, q

月份

月份, mm, m

一年中的第几天

dy, y

天

天数, dd, d

周

周, 周, 周

工作日

工作日, dw

小时

小时, hh

分钟

分钟, mi, n

第二

秒, ss, s

毫秒

毫秒, ms

微秒

微秒, mcs

纳秒

纳秒, ns

时区偏移

时区

 
SELECT DATE_PART('year', '2019-09-22T11:22:33.123Z'::datetime) AS "years";

   years
----------
2019
SELECT DATE_PART('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;

   mins
-----------
22
SELECT DATE_PART('quarters', CAST('2019-09-24' AS DATE)) AS quarter;

   quarter
-------------
3
SELECT DATE_PART('month', CAST('2019-09-24' AS DATE)) AS month;

   month
-------------
9

对于week和weekday，单位是使用非ISO计算方法提取的，这意味着给定的周被认为是周日开始的，而不是周一。

SELECT DATE_PART('week', '2019-09-22T11:22:33.123Z'::datetime) AS week;

   week
----------
39

The tzoffset 返回表示时区偏移量的总分钟数（带符号）。

SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123+05:15'::datetime) AS tz_mins;

   tz_mins
--------------
315
SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123-03:49'::datetime) AS tz_mins;

   tz_mins
--------------
-229

DATE_TRUNC/DATETRUNC

edit

概要：

DATE_TRUNC(
    string_exp, 
    datetime_exp/interval_exp)

输入:

表示要将日期/时间/间隔截断到的单位的字符串表达式。如果为null，函数返回null。

日期/日期时间/时间间隔表达式。如果null，函数返回null。

输出: datetime/interval

描述: 将日期/日期时间/时间间隔截断到指定的单位，通过将所有比指定单位更不重要的字段设置为零（或者对于日、星期几和月份设置为一）。如果第一个参数是周，而第二个参数是时间间隔类型，则会抛出一个错误，因为时间间隔数据类型不支持周时间单位。

Datetime truncation units

单位

缩写

千年

千年

世纪

世纪

十年

几十年

年

年份, yy, yyyy

季度

季度, qq, q

月份

月份, mm, m

周

周, 周, 周

天

天数, dd, d

小时

小时, hh

分钟

分钟, mi, n

第二

秒, ss, s

毫秒

毫秒, ms

微秒

微秒, mcs

纳秒

纳秒, ns

 
SELECT DATE_TRUNC('millennium', '2019-09-04T11:22:33.123Z'::datetime) AS millennium;

      millennium
------------------------
2000-01-01T00:00:00.000Z
SELECT DATETRUNC('week', '2019-08-24T11:22:33.123Z'::datetime) AS week;

      week
------------------------
2019-08-19T00:00:00.000Z
SELECT DATE_TRUNC('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;

      mins
------------------------
2019-09-04T11:22:00.000Z
SELECT DATE_TRUNC('decade', CAST('2019-09-04' AS DATE)) AS decades;

      decades
------------------------
2010-01-01T00:00:00.000Z
SELECT DATETRUNC('quarters', CAST('2019-09-04' AS DATE)) AS quarter;

      quarter
------------------------
2019-07-01T00:00:00.000Z
SELECT DATE_TRUNC('centuries', INTERVAL '199-5' YEAR TO MONTH) AS centuries;

      centuries
------------------
 +100-0
SELECT DATE_TRUNC('hours', INTERVAL '17 22:13:12' DAY TO SECONDS) AS hour;

      hour
------------------
+17 22:00:00
SELECT DATE_TRUNC('days', INTERVAL '19 15:24:19' DAY TO SECONDS) AS day;

      day
------------------
+19 00:00:00

FORMAT

edit

概要：

FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

输入:

日期/日期时间/时间表达式。如果null，函数返回null。

格式模式。如果为null或空字符串，函数返回null。

输出: 字符串

描述: 返回日期/日期时间/时间作为字符串，使用在第二个参数中指定的格式。所使用的格式化模式是来自Microsoft SQL Server 格式规范。

如果第一个参数是类型 time，则第二个参数指定的模式不能包含与日期相关的单位（例如 dd、MM、yyyy 等）。如果包含此类单位，则会返回错误。
格式说明符 F 将类似于格式说明符 f 工作。它将返回秒的分数部分，并且数字的数量将与作为输入提供的 F 的数量相同（最多 9 位数字）。结果将在末尾附加 0 以匹配提供的 F 的数量。例如：对于时间部分 10:20:30.1234 和模式 HH:mm:ss.FFFFFF，函数的输出字符串将是：10:20:30.123400。
格式说明符 y 将返回年代年份而不是一位或两位低阶数字。例如：对于年份 2009，y 将返回 2009 而不是 9。对于年份 43，y 格式说明符将返回 43。 - 特殊字符如 "、\ 和 % 将按原样返回，不做任何更改。例如：格式化日期 17-sep-2020 使用 %M 将返回 %9

SELECT FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/yyyy') AS "date";

      date
------------------
05/04/2020
SELECT FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/yyyy HH:mm:ss.ff') AS "datetime";

      datetime
------------------
05/04/2020 11:22:33.98
SELECT FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.f') AS "time";

      time
------------------
11 22 33.9

TO_CHAR

edit

概要：

TO_CHAR(
    date_exp/datetime_exp/time_exp, 
    string_exp)

输入:

日期/日期时间/时间表达式。如果null，函数返回null。

格式模式。如果为null或空字符串，函数返回null。

输出: 字符串

描述: 使用第二个参数中指定的格式将日期/日期时间/时间返回为字符串。格式化模式符合PostgreSQL日期/时间格式化模板模式。

如果第一个参数是类型 time，则第二个参数指定的模式不能包含与日期相关的单位（例如 dd、MM、YYYY 等）。如果包含此类单位，则会返回错误。
模式 TZ 和 tz（时区缩写）的结果在某些情况下与 PostgreSQL 中的 TO_CHAR 返回的结果不同。原因是 JDK 指定的时区缩写与 PostgreSQL 指定的不同。 此函数可能会显示实际的时区缩写，而不是 PostgreSQL 实现返回的通用 LMT 或空字符串或偏移量。夏季/ daylight 标记在两个实现之间也可能不同（例如，将显示 HT 而不是 HST 用于夏威夷）。
不支持 FX、TM、SP 模式修饰符，它们将在输出中显示为字面量 FX、TM、SP。

SELECT TO_CHAR(CAST('2020-04-05' AS DATE), 'DD/MM/YYYY') AS "date";

      date
------------------
05/04/2020
SELECT TO_CHAR(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'DD/MM/YYYY HH24:MI:SS.FF2') AS "datetime";

      datetime
------------------
05/04/2020 11:22:33.98
SELECT TO_CHAR(CAST('23:22:33.987' AS TIME), 'HH12 MI SS.FF1') AS "time";

      time
------------------
11 22 33.9

DAY_OF_MONTH/DOM/DAY

edit

概要：

DAY_OF_MONTH(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取月份中的某一天。

SELECT DAY_OF_MONTH(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
19

DAY_OF_WEEK/DAYOFWEEK/DOW

edit

概要：

DAY_OF_WEEK(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取星期几。星期日是 1，星期一是 2，依此类推。

SELECT DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
2

一年中的第几天/DOY

edit

概要：

DAY_OF_YEAR(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取一年中的某一天。

SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50

DAY_NAME/DAYNAME

edit

概要：

DAY_NAME(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 字符串

描述: 从文本格式的日期/日期时间中提取星期几（星期一，星期二…​）。

SELECT DAY_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
Monday

一天中的小时/小时

edit

概要：

HOUR_OF_DAY(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取一天中的小时。

SELECT HOUR_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS hour;

     hour
---------------
10

ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW

edit

概要：

ISO_DAY_OF_WEEK(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取星期几，遵循ISO 8601标准。 星期一为1，星期二为2，以此类推。

SELECT ISO_DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
1

ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW

edit

概要：

ISO_WEEK_OF_YEAR(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取一年中的第几周，遵循ISO 8601标准。一年的第一周是第一个在1月份中有超过半数（4天或更多）天数的周。

SELECT ISO_WEEK_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS week;

     week
---------------
8

MINUTE_OF_DAY

edit

概要：

MINUTE_OF_DAY(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取一天中的分钟数。

SELECT MINUTE_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;

    minute
---------------
623

MINUTE_OF_HOUR/MINUTE

edit

概要：

MINUTE_OF_HOUR(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取小时中的分钟。

SELECT MINUTE_OF_HOUR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;

    minute
---------------
23

MONTH_OF_YEAR/MONTH

edit

概要：

MONTH(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取月份。

SELECT MONTH_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;

     month
---------------
2

MONTH_NAME/MONTHNAME

edit

概要：

MONTH_NAME(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 字符串

描述: 从文本格式的日期/日期时间中提取月份（一月，二月…​）。

SELECT MONTH_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;

     month
---------------
February

NOW

edit

概要：

NOW()

输入: 无

输出: 日期时间

描述: 此函数提供与 CURRENT_TIMESTAMP() 函数相同的功能：返回当前查询到达服务器时的日期时间。此方法在同一查询中的每次出现时总是返回相同的值。

SELECT NOW() AS result;

         result
------------------------
2018-12-12T14:48:52.448Z

通常，此函数（以及其孪生函数 CURRENT_TIMESTAMP()）用于相对日期/时间过滤：

SELECT first_name FROM emp WHERE hire_date > NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

SECOND_OF_MINUTE/SECOND

edit

概要：

SECOND_OF_MINUTE(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取分钟中的秒数。

SELECT SECOND_OF_MINUTE(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS second;

    second
---------------
27

QUARTER

edit

概要：

QUARTER(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 提取日期/日期时间所在的年季度。

SELECT QUARTER(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS quarter;

    quarter
---------------
1

TODAY

edit

概要：

TODAY()

输入: 无

输出: 日期

描述: 此函数提供与CURRENT_DATE()函数相同的功能：返回当前查询到达服务器时的日期。此方法在其每次出现在同一查询中时总是返回相同的值。

SELECT TODAY() AS result;

         result
------------------------
2018-12-12

通常，此函数（以及其孪生函数 CURRENT_TIMESTAMP()）用于相对日期过滤：

SELECT first_name FROM emp WHERE hire_date > TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5;

 first_name
------------
Alejandro
Amabile
Anoosh
Basil
Bojan

WEEK_OF_YEAR/WEEK

edit

概要：

WEEK_OF_YEAR(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取一年中的第几周。

SELECT WEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS week, ISOWEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS isoweek;

      week     |   isoweek
---------------+---------------
2              |1

年

edit

概要：

YEAR(datetime_exp)

输入:

日期/日期时间表达式。如果为null，函数返回null。

输出: 整数

描述: 从日期/日期时间中提取年份。

SELECT YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS year;

     year
---------------
2018

EXTRACT

edit

概要：

EXTRACT(
    datetime_function  
    FROM datetime_exp)

输入:

日期/时间函数名称

日期/日期时间表达式

输出: 整数

描述: 通过指定日期时间函数的名称，从日期/日期时间中提取字段。 以下

SELECT EXTRACT(DAY_OF_YEAR FROM CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50

相当于

SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50

全文搜索函数

edit

在执行全文搜索时，即使用MATCH或QUERY谓词时，应使用搜索函数。在所谓的搜索上下文之外，这些函数将返回默认值，例如0或NULL。

Elasticsearch SQL 根据评分需求优化所有针对 Elasticsearch 执行的查询。 在使用 track_scores 进行搜索请求或 _doc 排序 时， 这些操作会禁用评分计算，Elasticsearch SQL 会指示 Elasticsearch 在这些情况下不计算评分。 例如，每当在 SQL 查询中遇到 SCORE() 函数时，都会计算评分。

MATCH

edit

概要：

MATCH(
    field_exp,   
    constant_exp 
    [, options])

输入:

要匹配的字段

匹配文本

附加参数；可选

描述: 一种全文搜索选项，以谓词的形式，在Elasticsearch SQL中可用，使用户能够控制强大的匹配 和多匹配Elasticsearch查询。

第一个参数是用于匹配的字段或字段。如果只接收到一个值，Elasticsearch SQL 将使用 match 查询来执行搜索：

SELECT author, name FROM library WHERE MATCH(author, 'frank');

    author     |       name
---------------+-------------------
Frank Herbert  |Dune
Frank Herbert  |Dune Messiah
Frank Herbert  |Children of Dune
Frank Herbert  |God Emperor of Dune

然而，它也可以接收一个字段列表及其对应的可选boost值。在这种情况下，Elasticsearch SQL将使用multi_match查询来匹配文档：

SELECT author, name, SCORE() FROM library WHERE MATCH('author^2,name^5', 'frank dune');

    author     |       name        |    SCORE()
---------------+-------------------+---------------
Frank Herbert  |Dune               |11.443176
Frank Herbert  |Dune Messiah       |9.446629
Frank Herbert  |Children of Dune   |8.043278
Frank Herbert  |God Emperor of Dune|7.0029488

Elasticsearch 中的 multi_match 查询具有 按字段提升 的选项，该选项使用 ^ 字符为正在搜索的字段赋予优先权重（在评分方面）。在上面的示例中，当在 name 和 author 字段中搜索 frank dune 文本时，name 字段在最终得分中的权重比 author 字段更大。

上述两个选项都可以与MATCH()谓词的可选第三个参数结合使用，在该参数中可以指定额外的配置参数（用分号;分隔），用于match或multi_match查询。例如：

SELECT author, name, SCORE() FROM library WHERE MATCH(name, 'to the star', 'operator=OR;fuzziness=AUTO:1,5;minimum_should_match=1')
ORDER BY SCORE() DESC LIMIT 2;

     author      |                name                |    SCORE()
-----------------+------------------------------------+---------------
Douglas Adams    |The Hitchhiker's Guide to the Galaxy|3.1756816
Peter F. Hamilton|Pandora's Star                      |3.0997515

单字段 MATCH() 变体（用于 match Elasticsearch 查询）允许的可选参数包括：analyzer、auto_generate_synonyms_phrase_query、lenient、fuzziness、fuzzy_transpositions、fuzzy_rewrite、minimum_should_match、operator、max_expansions、prefix_length。

多字段MATCH()变体（用于multi_match Elasticsearch查询）允许的可选参数包括：analyzer、auto_generate_synonyms_phrase_query、lenient、fuzziness、fuzzy_transpositions、fuzzy_rewrite、minimum_should_match、operator、max_expansions、prefix_length、slop、tie_breaker、type。

查询

edit

概要：

QUERY(
    constant_exp 
    [, options])

输入:

查询文本

附加参数；可选

描述: 就像 MATCH 一样，QUERY 是一个全文搜索谓词，它让用户能够控制 Elasticsearch 中的 query_string 查询。

第一个参数基本上是作为输入传递给 query_string 查询的内容，这意味着 query_string 在其 query 字段中接受的任何内容也可以在这里使用：

SELECT author, name, SCORE() FROM library WHERE QUERY('name:dune');

    author     |       name        |    SCORE()
---------------+-------------------+---------------
Frank Herbert  |Dune               |2.2886353
Frank Herbert  |Dune Messiah       |1.8893257
Frank Herbert  |Children of Dune   |1.6086556
Frank Herbert  |God Emperor of Dune|1.4005898

一个更高级的示例，展示了query_string支持的更多功能，当然也可以使用Elasticsearch SQL实现：

SELECT author, name, page_count, SCORE() FROM library WHERE QUERY('_exists_:"author" AND page_count:>200 AND (name:/star.*/ OR name:duna~)');

      author      |       name        |  page_count   |    SCORE()
------------------+-------------------+---------------+---------------
Frank Herbert     |Dune               |604            |3.7164764
Frank Herbert     |Dune Messiah       |331            |3.4169943
Frank Herbert     |Children of Dune   |408            |3.2064917
Frank Herbert     |God Emperor of Dune|454            |3.0504425
Peter F. Hamilton |Pandora's Star     |768            |3.0
Robert A. Heinlein|Starship Troopers  |335            |3.0

上面的查询使用了 _exists_ 查询来选择在 author 字段中有值的文档，以及用于 page_count 的范围查询和用于 name 字段的正则表达式和模糊查询。

如果需要自定义query_string暴露的各种配置选项，可以使用第二个可选参数来实现。多个设置可以通过分号;分隔来指定：

SELECT author, name, SCORE() FROM library WHERE QUERY('dune god', 'default_operator=and;default_field=name');

    author     |       name        |    SCORE()
---------------+-------------------+---------------
Frank Herbert  |God Emperor of Dune|3.6984892

允许的可选参数为 QUERY() 的是：allow_leading_wildcard、analyze_wildcard、analyzer、 auto_generate_synonyms_phrase_query、default_field、default_operator、enable_position_increments、 escape、fuzziness、fuzzy_max_expansions、fuzzy_prefix_length、fuzzy_rewrite、fuzzy_transpositions、 lenient、max_determinized_states、minimum_should_match、phrase_slop、rewrite、quote_analyzer、 quote_field_suffix、tie_breaker、time_zone、type。

SCORE

edit

概要：

SCORE()

输入: 无

输出: double 数值

描述: 返回给定输入与执行查询的相关性。 得分越高，数据的相关性越强。

当在 WHERE 子句中进行多个文本查询时，它们的分数将按照与 Elasticsearch 的 bool 查询 相同的规则进行组合。

通常，SCORE 用于根据查询结果的相关性对其进行排序：

SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z
1.6086556      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z

然而，不按分数排序直接返回分数也是完全可以的：

SELECT SCORE() AS score, name, release_date FROM library WHERE QUERY('dune') ORDER BY YEAR(release_date) DESC;

     score     |       name        |    release_date
---------------+-------------------+--------------------
1.4005898      |God Emperor of Dune|1981-05-28T00:00:00Z
1.6086556      |Children of Dune   |1976-04-21T00:00:00Z
1.8893257      |Dune Messiah       |1969-10-15T00:00:00Z
2.2886353      |Dune               |1965-06-01T00:00:00Z

数学函数

edit

所有数学和三角函数都需要它们的输入（如果适用）为数值。

通用

edit

ABS

edit

概要：

ABS(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 数值

描述: 返回 绝对值 的 numeric_exp。返回类型与输入类型相同。

SELECT ABS(-123.5), ABS(55);

  ABS(-123.5)  |    ABS(55)
---------------+---------------
123.5          |55

立方根

edit

概要：

CBRT(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 立方根 的 numeric_exp。

SELECT CBRT(-125.5);

   CBRT(-125.5)
-------------------
-5.0066577974783435

CEIL/CEILING

edit

概要：

CEIL(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 整数或长数值

描述: 返回大于或等于 numeric_exp 的最小整数。

SELECT CEIL(125.01), CEILING(-125.99);

 CEIL(125.01)  |CEILING(-125.99)
---------------+----------------
126            |-125

E

edit

概要：

E()

输入: 无

输出: 2.718281828459045

描述: 返回 欧拉数。

SELECT E(), CEIL(E());

       E()       |   CEIL(E())
-----------------+---------------
2.718281828459045|3

EXP

edit

概要：

EXP(numeric_exp)

输入:

浮点数表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 欧拉数在幂次 numeric_exp enumeric_exp。

SELECT EXP(1), E(), EXP(2), E() * E();

     EXP(1)      |       E()       |     EXP(2)     |     E() * E()
-----------------+-----------------+----------------+------------------
2.718281828459045|2.718281828459045|7.38905609893065|7.3890560989306495

EXPM1

edit

概要：

EXPM1(numeric_exp)

输入:

浮点数表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 欧拉数在幂次 numeric_exp 减去 1 (enumeric_exp - 1)。

SELECT E(), EXP(2), EXPM1(2);

       E()       |     EXP(2)     |    EXPM1(2)
-----------------+----------------+----------------
2.718281828459045|7.38905609893065|6.38905609893065

FLOOR

edit

概要：

FLOOR(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 整数或长数值

描述: 返回小于或等于 numeric_exp 的最大整数。

SELECT FLOOR(125.01), FLOOR(-125.99);

 FLOOR(125.01) |FLOOR(-125.99)
---------------+---------------
125            |-126

LOG

edit

概要：

LOG(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 自然对数 的 numeric_exp。

SELECT EXP(3), LOG(20.085536923187668);

      EXP(3)      |LOG(20.085536923187668)
------------------+-----------------------
20.085536923187668|3.0

LOG10

edit

概要：

LOG10(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 以10为底的对数 的 numeric_exp。

SELECT LOG10(5), LOG(5)/LOG(10);

     LOG10(5)     |    LOG(5)/LOG(10)
------------------+-----------------------
0.6989700043360189|0.6989700043360187

PI

edit

概要：

PI()

输入: 无

输出: 3.141592653589793

描述: 返回 PI 数字。

SELECT PI();

      PI()
-----------------
3.141592653589793

POWER

edit

概要：

POWER(
    numeric_exp, 
    integer_exp)

输入:

数值表达式。如果为null，函数返回null。

整数表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 numeric_exp 的 integer_exp 次幂。

SELECT POWER(3, 2), POWER(3, 3);

  POWER(3, 2)  |  POWER(3, 3)
---------------+---------------
9.0            |27.0
SELECT POWER(5, -1), POWER(5, -2);

  POWER(5, -1) |  POWER(5, -2)
---------------+---------------
0.2            |0.04

随机/随机

edit

概要：

RANDOM(seed)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回使用给定种子生成的随机双精度浮点数。

SELECT RANDOM(123);

   RANDOM(123)
------------------
0.7231742029971469

ROUND

edit

概要：

ROUND(
    numeric_exp      
    [, integer_exp])

输入:

数值表达式。如果为null，函数返回null。

整数表达式；可选。如果为null，函数返回null。

输出: 数值

描述: 返回 numeric_exp 四舍五入到 integer_exp 位小数点右侧的值。如果 integer_exp 为负数， numeric_exp 将被四舍五入到 |integer_exp| 位小数点左侧。如果省略 integer_exp， 函数将执行，如同 integer_exp 为 0。返回的数值数据类型与 numeric_exp 的数据类型相同。

SELECT ROUND(-345.153, 1) AS rounded;

    rounded
---------------
-345.2
SELECT ROUND(-345.153, -1) AS rounded;

    rounded
---------------
-350.0

符号/符号函数

edit

概要：

SIGN(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: [-1, 0, 1]

描述: 返回 numeric_exp 符号的指示符。如果 numeric_exp 小于零，则返回 -1。如果 numeric_exp 等于零，则返回 0。如果 numeric_exp 大于零，则返回 1。

SELECT SIGN(-123), SIGN(0), SIGN(415);

  SIGN(-123)   |    SIGN(0)    |   SIGN(415)
---------------+---------------+---------------
-1             |0              |1

SQRT

edit

概要：

SQRT(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 平方根 的 numeric_exp。

SELECT SQRT(EXP(2)), E(), SQRT(25);

  SQRT(EXP(2))   |       E()       |   SQRT(25)
-----------------+-----------------+---------------
2.718281828459045|2.718281828459045|5.0

TRUNCATE/TRUNC

edit

概要：

TRUNCATE(
    numeric_exp      
    [, integer_exp])

输入:

数值表达式。如果为null，函数返回null。

整数表达式；可选。如果为null，函数返回null。

输出: 数值

描述: 返回将numeric_exp截断到integer_exp位小数点右侧的数值。如果integer_exp为负数，则numeric_exp将被截断到小数点左侧|integer_exp|位。如果省略integer_exp，则函数将执行，如同integer_exp为0。返回的数值数据类型与numeric_exp的数据类型相同。

SELECT TRUNC(-345.153, 1) AS trimmed;

    trimmed
---------------
-345.1
SELECT TRUNCATE(-345.153, -1) AS trimmed;

    trimmed
---------------
-340.0

三角函数

edit

ACOS

edit

概要：

ACOS(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 反余弦 的 numeric_exp 作为角度，以弧度表示。

SELECT ACOS(COS(PI())), PI();

 ACOS(COS(PI())) |      PI()
-----------------+-----------------
3.141592653589793|3.141592653589793

ASIN

edit

概要：

ASIN(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 反正弦 的 numeric_exp 作为角度，以弧度表示。

SELECT ROUND(DEGREES(ASIN(0.7071067811865475))) AS "ASIN(0.707)", ROUND(SIN(RADIANS(45)), 3) AS "SIN(45)";

  ASIN(0.707)  |    SIN(45)
---------------+---------------
45.0           |0.707

ATAN

edit

概要：

ATAN(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 反正切 的 numeric_exp 作为角度，以弧度表示。

SELECT DEGREES(ATAN(TAN(RADIANS(90))));

DEGREES(ATAN(TAN(RADIANS(90))))
-------------------------------
90.0

ATAN2

edit

概要：

ATAN2(
    ordinate, 
    abscisa)

输入:

数值表达式。如果为null，函数返回null。

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回以弧度表示的角度，该角度是指定的坐标ordinate和abscisa的反正切值。

SELECT ATAN2(5 * SIN(RADIANS(45)), 5 * COS(RADIANS(45))) AS "ATAN2(5*SIN(45), 5*COS(45))", RADIANS(45);

ATAN2(5*SIN(45), 5*COS(45))|   RADIANS(45)
---------------------------+------------------
0.7853981633974483         |0.7853981633974483

余弦

edit

概要：

COS(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 余弦 值，其中 numeric_exp 是以弧度表示的角度。

SELECT COS(RADIANS(180)), POWER(SIN(RADIANS(54)), 2) + POWER(COS(RADIANS(54)), 2) AS pythagorean_identity;

COS(RADIANS(180))|pythagorean_identity
-----------------+--------------------
-1.0             |1.0

双曲余弦

edit

概要：

COSH(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 双曲余弦 的 numeric_exp。

SELECT COSH(5), (POWER(E(), 5) + POWER(E(), -5)) / 2 AS "(e^5 + e^-5)/2";

     COSH(5)     | (e^5 + e^-5)/2
-----------------+-----------------
74.20994852478785|74.20994852478783

COT

edit

概要：

COT(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 余切 的 numeric_exp，其中 numeric_exp 是以弧度表示的角度。

SELECT COT(RADIANS(30)) AS "COT(30)", COS(RADIANS(30)) / SIN(RADIANS(30)) AS "COS(30)/SIN(30)";

     COT(30)      | COS(30)/SIN(30)
------------------+------------------
1.7320508075688774|1.7320508075688776

DEGREES

edit

概要：

DEGREES(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 从弧度 转换为度。

SELECT DEGREES(PI() * 2), DEGREES(PI());

DEGREES(PI() * 2)| DEGREES(PI())
-----------------+---------------
360.0            |180.0

RADIANS

edit

概要：

RADIANS(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 从度 转换为弧度。

SELECT RADIANS(90), PI()/2;

   RADIANS(90)    |      PI()/2
------------------+------------------
1.5707963267948966|1.5707963267948966

SIN

edit

概要：

SIN(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 正弦 值，其中 numeric_exp 是以弧度表示的角度。

SELECT SIN(RADIANS(90)), POWER(SIN(RADIANS(67)), 2) + POWER(COS(RADIANS(67)), 2) AS pythagorean_identity;

SIN(RADIANS(90))|pythagorean_identity
----------------+--------------------
1.0             |1.0

双曲正弦函数

edit

概要：

SINH(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 双曲正弦 的 numeric_exp。

SELECT SINH(5), (POWER(E(), 5) - POWER(E(), -5)) / 2 AS "(e^5 - e^-5)/2";

     SINH(5)     | (e^5 - e^-5)/2
-----------------+-----------------
74.20321057778875|74.20321057778874

TAN

edit

概要：

TAN(numeric_exp)

输入:

数值表达式。如果为null，函数返回null。

输出: 双精度数值

描述: 返回 正切 值，其中 numeric_exp 是以弧度表示的角度。

SELECT TAN(RADIANS(66)) AS "TAN(66)", SIN(RADIANS(66))/COS(RADIANS(66)) AS "SIN(66)/COS(66)=TAN(66)";

     TAN(66)      |SIN(66)/COS(66)=TAN(66)
------------------+-----------------------
2.2460367739042164|2.246036773904216

字符串函数

edit

用于执行字符串操作的函数。

ASCII

edit

概要：

ASCII(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 整数

描述: 返回 string_exp 最左边字符的 ASCII 码值作为整数。

SELECT ASCII('Elastic');

ASCII('Elastic')
----------------
69

BIT_LENGTH

edit

概要：

BIT_LENGTH(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 整数

描述: 返回输入表达式 string_exp 的位长度。

SELECT BIT_LENGTH('Elastic');

BIT_LENGTH('Elastic')
---------------------
56

CHAR

edit

概要：

CHAR(code)

输入:

介于 0 和 255 之间的整数表达式。如果为 null、负数或大于 255，该函数返回 null。

输出: 字符串

描述: 返回具有由数值输入指定的ASCII码值的字符。

SELECT CHAR(69);

   CHAR(69)
---------------
E

字符长度

edit

概要：

CHAR_LENGTH(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 整数

描述: 如果输入的字符串表达式是字符数据类型，则返回其字符长度；否则，返回字符串表达式的字节长度（即不小于位数除以8的最小整数）。

SELECT CHAR_LENGTH('Elastic');

CHAR_LENGTH('Elastic')
----------------------
7

CONCAT

edit

概要：

CONCAT(
    string_exp1, 
    string_exp2)

输入:

字符串表达式。将null视为空字符串。

字符串表达式。将null视为空字符串。

输出: 字符串

描述: 返回一个字符串，该字符串是将 string_exp1 连接到 string_exp2 的结果。

生成的字符串不能超过1 MB的字节长度。

SELECT CONCAT('Elasticsearch', ' SQL');

CONCAT('Elasticsearch', ' SQL')
-------------------------------
Elasticsearch SQL

INSERT

edit

概要：

INSERT(
    source,      
    start,       
    length,      
    replacement)

输入:

字符串表达式。如果为null，函数返回null。

整数表达式。如果为null，函数返回null。

整数表达式。如果为null，函数返回null。

字符串表达式。如果为null，函数返回null。

输出: 字符串

描述: 返回一个字符串，其中从source的start位置开始删除了length个字符，并在source的start位置插入了replacement。

生成的字符串不能超过1 MB的字节长度。

SELECT INSERT('Elastic ', 8, 1, 'search');

INSERT('Elastic ', 8, 1, 'search')
----------------------------------
Elasticsearch

LCASE

edit

概要：

LCASE(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 字符串

描述: 返回一个字符串，该字符串等于 string_exp 中的字符串，其中所有大写字符都转换为小写。

SELECT LCASE('Elastic');

LCASE('Elastic')
----------------
elastic

LEFT

edit

概要：

LEFT(
    string_exp, 
    count)

输入:

字符串表达式。如果为null，函数返回null。

整数表达式。如果为null，函数返回null。如果为0或负数，函数返回一个空字符串。

输出: 字符串

描述: 返回 string_exp 最左边的 count 个字符。

SELECT LEFT('Elastic',3);

LEFT('Elastic',3)
-----------------
Ela

LENGTH

edit

概要：

LENGTH(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 整数

描述: 返回string_exp中的字符数，不包括尾随空格。

SELECT LENGTH('Elastic   ');

LENGTH('Elastic   ')
--------------------
7

LOCATE

edit

概要：

LOCATE(
    pattern, 
    source   
    [, start]
)

输入:

字符串表达式。如果为null，函数返回null。

字符串表达式。如果为null，函数返回null。

整数表达式；可选。如果为null、0、1、负数或未指定，搜索将从第一个字符位置开始。

输出: 整数

描述: 返回 pattern 在 source 中首次出现的起始位置。可选的 start 指定开始搜索的字符位置。如果 pattern 在 source 中未找到，函数返回 0。

SELECT LOCATE('a', 'Elasticsearch');

LOCATE('a', 'Elasticsearch')
----------------------------
3
SELECT LOCATE('a', 'Elasticsearch', 5);

LOCATE('a', 'Elasticsearch', 5)
-------------------------------
10

LTRIM

edit

概要：

LTRIM(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 字符串

描述: 返回 string_exp 的字符，去除前导空格。

SELECT LTRIM('   Elastic');

LTRIM('   Elastic')
-------------------
Elastic

OCTET_LENGTH

edit

概要：

OCTET_LENGTH(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 整数

描述: 返回输入表达式 string_exp 的字节长度。

SELECT OCTET_LENGTH('Elastic');

OCTET_LENGTH('Elastic')
-----------------------
7

POSITION

edit

概要：

POSITION(
    string_exp1, 
    string_exp2)

输入:

字符串表达式。如果为null，函数返回null。

字符串表达式。如果为null，函数返回null。

输出: 整数

描述: 返回 string_exp1 在 string_exp2 中的位置。结果是一个精确的数值。

SELECT POSITION('Elastic', 'Elasticsearch');

POSITION('Elastic', 'Elasticsearch')
------------------------------------
1

REPEAT

edit

概要：

REPEAT(
    string_exp, 
    count)

输入:

字符串表达式。如果为null，函数返回null。

整数表达式。如果为0、负数或null，函数返回null。

输出: 字符串

描述: 返回一个由 string_exp 重复 count 次组成的字符串。

生成的字符串不能超过1 MB的字节长度。

SELECT REPEAT('La', 3);

 REPEAT('La', 3)
----------------
LaLaLa

REPLACE

edit

概要：

REPLACE(
    source,      
    pattern,     
    replacement)

输入:

字符串表达式。如果为null，函数返回null。

字符串表达式。如果为null，函数返回null。

字符串表达式。如果为null，函数返回null。

输出: 字符串

描述: 在 source 中搜索 pattern 的出现，并替换为 replacement。

生成的字符串不能超过1 MB的字节长度。

SELECT REPLACE('Elastic','El','Fant');

REPLACE('Elastic','El','Fant')
------------------------------
Fantastic

RIGHT

edit

概要：

RIGHT(
    string_exp, 
    count)

输入:

字符串表达式。如果为null，函数返回null。

整数表达式。如果为null，函数返回null。如果为0或负数，函数返回一个空字符串。

输出: 字符串

描述: 返回 string_exp 中最右边的字符数。

SELECT RIGHT('Elastic',3);

RIGHT('Elastic',3)
------------------
tic

RTRIM

edit

概要：

RTRIM(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 字符串

描述: 返回移除尾部空格的 string_exp 字符。

SELECT RTRIM('Elastic   ');

RTRIM('Elastic   ')
-------------------
Elastic

SPACE

edit

概要：

SPACE(count)

输入:

整数表达式。如果为null或负数，函数返回null。

输出: 字符串

描述: 返回一个由count个空格组成的字符串。

生成的字符串不能超过1 MB的字节长度。

SELECT SPACE(3);

   SPACE(3)
---------------

STARTS_WITH

edit

概要：

STARTS_WITH(
    source,   
    pattern)

输入:

字符串表达式。如果为null，函数返回null。

字符串表达式。如果为null，函数返回null。

输出: 布尔值

描述: 如果源表达式以指定的模式开头，则返回true，否则返回false。匹配是区分大小写的。

SELECT STARTS_WITH('Elasticsearch', 'Elastic');

STARTS_WITH('Elasticsearch', 'Elastic')
--------------------------------
true
SELECT STARTS_WITH('Elasticsearch', 'ELASTIC');

STARTS_WITH('Elasticsearch', 'ELASTIC')
--------------------------------
false

SUBSTRING

edit

概要：

SUBSTRING(
    source, 
    start,  
    length)

输入:

字符串表达式。如果为null，函数返回null。

整数表达式。如果为null，函数返回null。

整数表达式。如果为null，函数返回null。

输出: 字符串

描述: 返回一个从source派生的字符串，从由start指定的字符位置开始，长度为length个字符。

SELECT SUBSTRING('Elasticsearch', 0, 7);

SUBSTRING('Elasticsearch', 0, 7)
--------------------------------
Elastic

TRIM

edit

概要：

TRIM(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 字符串

描述: 返回 string_exp 的字符，去除前导和尾随的空白。

SELECT TRIM('   Elastic   ') AS trimmed;

trimmed
--------------
Elastic

UCASE

edit

概要：

UCASE(string_exp)

输入:

字符串表达式。如果为null，函数返回null。

输出: 字符串

描述: 返回一个与输入相等的字符串，其中所有小写字符都转换为大写。

SELECT UCASE('Elastic');

UCASE('Elastic')
----------------
ELASTIC

类型转换函数

edit

用于将一种数据类型表达式转换为另一种数据类型的函数。

CAST

edit

概要：

CAST(
    expression 
 AS data_type)

要转换的表达式。如果为null，函数返回null。

要转换的目标数据类型

描述: 将给定表达式的结果转换为目标数据类型。 如果转换不可能（例如因为目标类型太窄或因为值本身无法转换），查询将失败。

SELECT CAST('123' AS INT) AS int;

      int
---------------
123
SELECT CAST(123 AS VARCHAR) AS string;

    string
---------------
123
SELECT YEAR(CAST('2018-05-19T11:23:45Z' AS TIMESTAMP)) AS year;

     year
---------------
2018

ANSI SQL 和 Elasticsearch SQL 类型都受支持，前者优先。这仅影响 FLOAT，由于命名冲突，它被解释为 ANSI SQL，因此在 Elasticsearch 中映射为 double，而不是 float。要获取 Elasticsearch 的 float，请执行到其 SQL 等效类型 real 的类型转换。

CONVERT

edit

概要：

CONVERT(
    expression, 
    data_type)

要转换的表达式。如果为null，函数返回null。

目标数据类型，转换为

描述: 与CAST功能完全相同，但语法略有不同。 此外，除了标准的数据类型外，它还支持相应的 ODBC数据类型。

SELECT CONVERT('123', SQL_INTEGER) AS int;

      int
---------------
123
SELECT CONVERT('123', INTEGER) AS int;

      int
---------------
123

地理函数

edit

此功能处于测试阶段，可能会发生变化。设计和代码不如正式发布的功能成熟，并且是按原样提供的，不提供任何保证。测试功能不受正式发布功能的支持服务级别协议的约束。

地理函数用于处理存储在geo_point、geo_shape和shape字段中的几何图形，或由其他地理函数返回的几何图形。

限制

edit

geo_point, geo_shape 和 shape 类型在SQL中表示为几何图形，并且可以互换使用，但有以下例外：

  • geo_shape 和 shape 字段没有文档值，因此这些字段不能用于过滤、分组或排序。
  • geo_points 字段默认情况下被索引并具有文档值，然而只有纬度和经度被存储和索引，并且会从原始值中损失一些精度（纬度为4.190951585769653E-8，经度为8.381903171539307E-8）。高度分量被接受但不会存储在文档值中，也不会被索引。因此，在过滤、分组或排序中调用 ST_Z 函数将返回 null。

几何转换

edit

ST_AsWKT

edit

概要：

ST_AsWKT(
    geometry 
)

输入:

几何。如果 null，函数返回 null。

输出: 字符串

描述: 返回 geometry 的WKT表示形式。

SELECT city, ST_AsWKT(location) location FROM "geo" WHERE city = 'Amsterdam';

     city:s    |     location:s
Amsterdam      |POINT (4.850312 52.347557)

ST_WKTToSQL

edit

概要：

ST_WKTToSQL(
    string 
)

输入:

几何的WKT表示形式。如果null，函数返回null。

输出: 几何

描述: 返回从WKT表示的几何图形。

SELECT CAST(ST_WKTToSQL('POINT (10 20)') AS STRING) location;

   location:s
POINT (10.0 20.0)

几何属性

edit

ST_GeometryType

edit

概要：

ST_GeometryType(
    geometry 
)

输入:

几何。如果 null，函数返回 null。

输出: 字符串

描述: 返回geometry的类型，例如POINT、MULTIPOINT、LINESTRING、MULTILINESTRING、POLYGON、MULTIPOLYGON、GEOMETRYCOLLECTION、ENVELOPE或CIRCLE。

SELECT ST_GeometryType(ST_WKTToSQL('POINT (10 20)')) type;

      type:s
POINT

ST_X

edit

概要：

ST_X(
    geometry 
)

输入:

几何。如果 null，函数返回 null。

输出: 双精度浮点数

描述: 返回几何图形中第一个点的经度。

SELECT ST_X(ST_WKTToSQL('POINT (10 20)')) x;

      x:d
10.0

ST_Y

edit

概要：

ST_Y(
    geometry 
)

输入:

几何。如果 null，函数返回 null。

输出: double

描述: 返回几何图形中第一个点的纬度。

SELECT ST_Y(ST_WKTToSQL('POINT (10 20)')) y;

      y:d
20.0

ST_Z

edit

概要：

ST_Z(
    geometry 
)

输入:

几何。如果 null，函数返回 null。

输出: 双精度浮点数

描述: 返回几何图形中第一个点的海拔高度。

SELECT ST_Z(ST_WKTToSQL('POINT (10 20 30)')) z;

      z:d
30.0

ST_Distance

edit

概要：

ST_Distance(
    geometry, 
    geometry  
)

输入:

源几何。如果null，函数返回null。

目标几何。如果为null，函数返回null。

输出: 双精度

描述: 返回几何之间的距离，单位为米。两个几何都必须是点。

SELECT ST_Distance(ST_WKTToSQL('POINT (10 20)'), ST_WKTToSQL('POINT (20 30)')) distance;

   distance:d
1499101.2889383635

条件函数和表达式

edit

以if-else方式返回其参数之一的函数。

CASE

edit

概要：

CASE WHEN condition THEN result
    [WHEN ...]
    [ELSE default_result]
END

输入:

使用一个或多个 WHEN 条件 THEN 结果 子句，表达式可以选择性地包含一个 ELSE 默认结果 子句。每个 条件 都应是一个布尔表达式。

输出: 如果对应的 WHEN 条件 评估为 true，则为其中一个 结果 表达式，或者如果所有 WHEN 条件 子句评估为 false，则为 默认结果。如果缺少可选的 ELSE 默认结果 子句，并且所有 WHEN 条件 子句评估为 false，则返回 null。

描述: CASE表达式是一个通用的条件表达式，模拟了其他编程语言中的if/else语句。如果条件的判断结果为真，则返回紧随条件之后的结果表达式的值。后续的when子句将被跳过，不会被处理。

SELECT CASE WHEN 1 > 2 THEN 'elastic'
            WHEN 2 <= 3 THEN 'search'
       END AS "case";

    case
---------------
search
SELECT CASE WHEN 1 > 2 THEN 'elastic'
            WHEN 2 > 10 THEN 'search'
       END AS "case";

    case
---------------
null
SELECT CASE WHEN 1 > 2 THEN 'elastic'
            WHEN 2 > 10 THEN 'search'
            ELSE 'default'
       END AS "case";

    case
---------------
default

作为一种变体，case表达式可以使用类似于其他编程语言中switch-case的语法来表示：

CASE expression
     WHEN value1 THEN result1
    [WHEN value2 THEN result2]
    [WHEN ...]
    [ELSE default_result]
END

在这种情况下，它会在内部转换为：

CASE WHEN expression = value1 THEN result1
    [WHEN expression = value2 THEN result2]
    [WHEN ...]
    [ELSE default_result]
END
SELECT CASE 5
            WHEN 1 THEN 'elastic'
            WHEN 2 THEN 'search'
            WHEN 5 THEN 'elasticsearch'
       END AS "case";

    case
---------------
elasticsearch
SELECT CASE 5
            WHEN 1 THEN 'elastic'
            WHEN 2 THEN 'search'
            WHEN 3 THEN 'elasticsearch'
            ELSE 'default'
       END AS "case";

    case
---------------
default

所有结果表达式必须具有兼容的数据类型。更具体地说，所有结果表达式应与第一个非空结果表达式的数据类型兼容。例如：

对于以下查询：

CASE WHEN a = 1 THEN null
     WHEN a > 2 THEN 10
     WHEN a > 5 THEN 'foo'
END

将返回一条错误消息，指出foo的数据类型是keyword，这与预期的数据类型integer（基于结果10）不匹配。

条件分桶

edit

CASE 可以在查询中用作 GROUP BY 键，以方便自定义分桶并为这些分桶分配描述性名称。例如，如果某个键的值太多，或者仅仅是这些值的范围比每个单独的值更有趣，CASE 可以创建自定义分桶，如下例所示：

SELECT count(*) AS count,
  CASE WHEN NVL(languages, 0) = 0 THEN 'zero'
    WHEN languages = 1 THEN 'one'
    WHEN languages = 2 THEN 'bilingual'
    WHEN languages = 3 THEN 'trilingual'
    ELSE 'multilingual'
  END as lang_skills
FROM employees
GROUP BY lang_skills
ORDER BY lang_skills;

通过此查询，可以为值 0, 1, 2, 3 创建具有描述性名称的普通分组桶，并且每个值 >= 4 都归入 多语言 桶中。

COALESCE

edit

概要：

COALESCE(
    expression, 
    expression, 
    ...)

输入:

第一个表达式

第二个表达式

…​

第N个表达式

COALESCE 可以接受任意数量的参数。

输出: 其中一个表达式或null

描述: 返回其参数中第一个不为空的值。 如果所有参数都为空，则返回null。

SELECT COALESCE(null, 'elastic', 'search') AS "coalesce";

    coalesce
---------------
elastic
SELECT COALESCE(null, null, null, null) AS "coalesce";

    coalesce
---------------
null

GREATEST

edit

概要：

GREATEST(
    expression, 
    expression, 
    ...)

输入:

第一个表达式

第二个表达式

…​

第N个表达式

GREATEST 可以接受任意数量的参数，并且它们必须都是相同的数据类型。

输出: 其中一个表达式或null

描述: 返回值最大的非空参数。如果所有参数都为空，则返回null。

SELECT GREATEST(null, 1, 2) AS "greatest";

    greatest
---------------
2
SELECT GREATEST(null, null, null, null) AS "greatest";

    greatest
---------------
null

IFNULL

edit

概要：

IFNULL(
    expression, 
    expression)

输入:

第一个表达式

第二个表达式

输出: 如果第一个表达式为空，则输出第二个表达式，否则输出第一个表达式。

描述: COALESCE 的变体，只有两个参数。 返回其参数中第一个不为空的值。 如果所有参数都为空，则返回 null。

SELECT IFNULL('elastic', null) AS "ifnull";

    ifnull
---------------
elastic
SELECT IFNULL(null, 'search') AS "ifnull";

    ifnull
---------------
search

IIF

edit

概要：

IIF(
    expression,   
    expression,   
    [expression])

输入:

要检查的布尔条件

如果布尔条件评估为true，则返回值

如果布尔条件评估为false，则返回值；可选

输出: 如果第一个表达式（条件）计算为true，则返回第二个表达式。如果计算为false，则返回第三个表达式。如果未提供第三个表达式，则返回null。

描述: 实现编程语言标准 IF <条件> THEN <结果1> ELSE <结果2> 逻辑的条件函数。如果未提供第三个表达式且条件评估为 false，则返回 null。

SELECT IIF(1 < 2, 'TRUE', 'FALSE') AS result1, IIF(1 > 2, 'TRUE', 'FALSE') AS result2;

    result1    |    result2
---------------+---------------
TRUE           |FALSE
SELECT IIF(1 < 2, 'TRUE') AS result1, IIF(1 > 2 , 'TRUE') AS result2;

    result1    |    result2
---------------+---------------
TRUE           |null

IIF 函数可以组合起来实现更复杂的逻辑，模拟 CASE 表达式。例如：

IIF(a = 1, 'one', IIF(a = 2, 'two', IIF(a = 3, 'three', 'many')))

ISNULL

edit

概要：

ISNULL(
    expression, 
    expression)

输入:

第一个表达式

第二个表达式

输出: 如果第一个表达式为空，则输出第二个表达式，否则输出第一个表达式。

描述: COALESCE 的变体，只有两个参数。 返回其参数中第一个不为空的值。 如果所有参数都为空，则返回 null。

SELECT ISNULL('elastic', null) AS "isnull";

    isnull
---------------
elastic
SELECT ISNULL(null, 'search') AS "isnull";

    isnull
---------------
search

LEAST

edit

概要：

LEAST(
    expression, 
    expression, 
    ...)

输入:

第一个表达式

第二个表达式

…​

第N个表达式

LEAST 可以接受任意数量的参数，并且它们必须都是相同的数据类型。

输出: 其中一个表达式或null

描述: 返回值最小的非空参数。如果所有参数都为空，则返回null。

SELECT LEAST(null, 2, 1) AS "least";

    least
---------------
1
SELECT LEAST(null, null, null, null) AS "least";

    least
---------------
null

NULLIF

edit

概要：

NULLIF(
    expression, 
    expression)

输入:

第一个表达式

第二个表达式

输出: null 如果两个表达式相等，否则为第一个表达式。

描述: 当两个输入表达式相等时返回null，否则返回第一个表达式。

SELECT NULLIF('elastic', 'search') AS "nullif";
    nullif
---------------
elastic
SELECT NULLIF('elastic', 'elastic') AS "nullif";

    nullif:s
---------------
null

NVL

edit

概要：

NVL(
    expression, 
    expression)

输入:

第一个表达式

第二个表达式

输出: 如果第一个表达式为空，则输出第二个表达式，否则输出第一个表达式。

描述: COALESCE 的变体，只有两个参数。 返回其参数中第一个不为空的值。 如果所有参数都为空，则返回 null。

SELECT NVL('elastic', null) AS "nvl";

    nvl
---------------
elastic
SELECT NVL(null, 'search') AS "nvl";

    nvl
---------------
search

系统函数

edit

这些函数返回有关被查询系统的元数据类型信息。

数据库

edit

概要：

DATABASE()

输入: 无

输出: 字符串

描述: 返回正在查询的数据库名称。在Elasticsearch SQL的情况下，这是Elasticsearch集群的名称。此函数应始终返回一个非空值。

SELECT DATABASE();

   DATABASE
---------------
elasticsearch

USER

edit

概要：

USER()

输入: 无

输出: 字符串

描述: 返回执行查询的已认证用户的用户名。如果安全功能被禁用，此函数可能返回null。

SELECT USER();

     USER
---------------
elastic
« SQL Language Reserved keywords »