字面值

字面值(也稱為常數)代表固定資料值。Spark SQL 支援下列字面值

字串字面值

字串字面值用於指定字元字串值。

語法

[ r ] { 'char [ ... ]' | "char [ ... ]" }

參數

範例

SELECT 'Hello, World!' AS col;
+-------------+
|          col|
+-------------+
|Hello, World!|
+-------------+

SELECT "SPARK SQL" AS col;
+---------+
|      col|
+---------+
|Spark SQL|
+---------+

SELECT 'it\'s $10.' AS col;
+---------+
|      col|
+---------+
|It's $10.|
+---------+

SELECT r"'\n' represents newline character." AS col;
+----------------------------------+
|                               col|
+----------------------------------+
|'\n' represents newline character.|
+----------------------------------+

二進位字面值

二進位字面值用於指定位元組序列值。

語法

X { 'num [ ... ]' | "num [ ... ]" }

參數

範例

SELECT X'123456' AS col;
+----------+
|       col|
+----------+
|[12 34 56]|
+----------+

Null 字面值

Null 字面值用於指定 Null 值。

語法

NULL

範例

SELECT NULL AS col;
+----+
| col|
+----+
|NULL|
+----+

布林字面值

布林字面值用於指定布林值。

語法

TRUE | FALSE

範例

SELECT TRUE AS col;
+----+
| col|
+----+
|true|
+----+

數字字面值

數字字面值用於指定固定或浮點數。數字字面值有兩種:整數字面值和小數字面值。

整數字面值語法

[ + | - ] digit [ ... ] [ L | S | Y ]

整數字面值參數

整數文字範例

SELECT -2147483648 AS col;
+-----------+
|        col|
+-----------+
|-2147483648|
+-----------+

SELECT 9223372036854775807l AS col;
+-------------------+
|                col|
+-------------------+
|9223372036854775807|
+-------------------+

SELECT -32Y AS col;
+---+
|col|
+---+
|-32|
+---+

SELECT 482S AS col;
+---+
|col|
+---+
|482|
+---+

小數文字語法

十進位文字

decimal_digits { [ BD ] | [ exponent BD ] } | digit [ ... ] [ exponent ] BD

雙精度文字

decimal_digits  { D | exponent [ D ] }  | digit [ ... ] { exponent [ D ] | [ exponent ] D }

單精度文字

decimal_digits  { F | exponent [ F ] }  | digit [ ... ] { exponent [ F ] | [ exponent ] F }

當 decimal_digits 定義為

[ + | - ] { digit [ ... ] . [ digit [ ... ] ] | . digit [ ... ] }

且 exponent 定義為

E [ + | - ] digit [ ... ]

小數文字參數

小數文字範例

SELECT 12.578 AS col;
+------+
|   col|
+------+
|12.578|
+------+

SELECT -0.1234567 AS col;
+----------+
|       col|
+----------+
|-0.1234567|
+----------+

SELECT -.1234567 AS col;
+----------+
|       col|
+----------+
|-0.1234567|
+----------+

SELECT 123. AS col;
+---+
|col|
+---+
|123|
+---+

SELECT 123.BD AS col;
+---+
|col|
+---+
|123|
+---+

SELECT 5E2 AS col;
+-----+
|  col|
+-----+
|500.0|
+-----+

SELECT 5D AS col;
+---+
|col|
+---+
|5.0|
+---+

SELECT -5BD AS col;
+---+
|col|
+---+
| -5|
+---+

SELECT 12.578e-2d AS col;
+-------+
|    col|
+-------+
|0.12578|
+-------+

SELECT -.1234567E+2BD AS col;
+---------+
|      col|
+---------+
|-12.34567|
+---------+

SELECT +3.e+3 AS col;
+------+
|   col|
+------+
|3000.0|
+------+

SELECT -3.E-3D AS col;
+------+
|   col|
+------+
|-0.003|
+------+

日期時間字面值

日期時間文字用於指定日期或時間戳記值。

日期語法

DATE { 'yyyy' |
       'yyyy-[m]m' |
       'yyyy-[m]m-[d]d' |
       'yyyy-[m]m-[d]d[T]' }

注意:如果未指定月份或日期,預設為 01

日期範例

SELECT DATE '1997' AS col;
+----------+
|       col|
+----------+
|1997-01-01|
+----------+

SELECT DATE '1997-01' AS col;
+----------+
|       col|
+----------+
|1997-01-01|
+----------+

SELECT DATE '2011-11-11' AS col;
+----------+
|       col|
+----------+
|2011-11-11|
+----------+

時間戳記語法

TIMESTAMP { 'yyyy' |
            'yyyy-[m]m' |
            'yyyy-[m]m-[d]d' |
            'yyyy-[m]m-[d]d ' |
            'yyyy-[m]m-[d]d[T][h]h[:]' |
            'yyyy-[m]m-[d]d[T][h]h:[m]m[:]' |
            'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s[.]' |
            'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s.[ms][ms][ms][us][us][us][zone_id]'}

注意:如果未指定小時、分鐘或秒,預設為 00zone_id 應具有下列其中一種形式

注意:如果未指定 zone_id,預設為工作階段的當地時區(透過 spark.sql.session.timeZone 設定)。

時間戳記範例

SELECT TIMESTAMP '1997-01-31 09:26:56.123' AS col;
+-----------------------+
|                    col|
+-----------------------+
|1997-01-31 09:26:56.123|
+-----------------------+

SELECT TIMESTAMP '1997-01-31 09:26:56.66666666UTC+08:00' AS col;
+--------------------------+
|                      col |
+--------------------------+
|1997-01-30 17:26:56.666666|
+--------------------------+

SELECT TIMESTAMP '1997-01' AS col;
+-------------------+
|                col|
+-------------------+
|1997-01-01 00:00:00|
+-------------------+

間隔字面值

間隔文字用於指定一個固定時間段。間隔文字支援兩種語法:ANSI 語法和多單位語法。

ANSI 語法

ANSI SQL 標準定義間隔文字的格式為

INTERVAL [ <sign> ] <interval string> <interval qualifier>

其中 <interval qualifier> 可以是單一欄位或欄位對欄位的形式

<interval qualifier> ::= <start field> TO <end field> | <single field>

欄位名稱大小寫不敏感,可以是 YEARMONTHDAYHOURMINUTESECOND 之一。

區間文字可為年-月或日-時區間類型。區間子類型定義 <區間字串> 的格式

<interval string> ::= <quote> [ <sign> ] { <year-month literal> | <day-time literal> } <quote>
<year-month literal> ::= <years value> [ <minus sign> <months value> ] | <months value>
<day-time literal> ::= <day-time interval> | <time interval>
<day-time interval> ::= <days value> [ <space> <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ] ]
<time interval> ::= <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ]
  | <minutes value> [ <colon> <seconds value> ]
  | <seconds value>

支援的年-月區間文字及其格式

<區間限定詞> 區間字串樣式 文字實例
YEAR [+|-]'[+|-]y' INTERVAL -'2021' YEAR
YEAR TO MONTH [+|-]'[+|-]y-m' INTERVAL '-2021-07' YEAR TO MONTH
MONTH [+|-]'[+|-]m' interval '10' month

支援的日-時區間文字格式

<區間限定詞> 區間字串樣式 文字實例
DAY [+|-]'[+|-]d' INTERVAL -'100' DAY
DAY TO HOUR [+|-]'[+|-]d h' INTERVAL '-100 10' DAY TO HOUR
DAY TO MINUTE [+|-]'[+|-]d h:m' INTERVAL '100 10:30' DAY TO MINUTE
DAY TO SECOND [+|-]'[+|-]d h:m:s.n' INTERVAL '100 10:30:40.999999' DAY TO SECOND
HOUR [+|-]'[+|-]h' INTERVAL '123' HOUR
HOUR TO MINUTE [+|-]'[+|-]h:m' INTERVAL -'-123:10' HOUR TO MINUTE
HOUR TO SECOND [+|-]'[+|-]h:m:s.n' INTERVAL '123:10:59' HOUR TO SECOND
MINUTE [+|-]'[+|-]m' interval '1000' minute
MINUTE TO SECOND [+|-]'[+|-]m:s.n' INTERVAL '1000:01.001' MINUTE TO SECOND
SECOND [+|-]'[+|-]s.n' INTERVAL '1000.000001' SECOND

ANSI 範例

SELECT INTERVAL '2-3' YEAR TO MONTH AS col;
+----------------------------+
|col                         |
+----------------------------+
|INTERVAL '2-3' YEAR TO MONTH|
+----------------------------+

SELECT INTERVAL -'20 15:40:32.99899999' DAY TO SECOND AS col;
+--------------------------------------------+
|col                                         |
+--------------------------------------------+
|INTERVAL '-20 15:40:32.998999' DAY TO SECOND|
+--------------------------------------------+

多單位語法

INTERVAL interval_value interval_unit [ interval_value interval_unit ... ] |
INTERVAL 'interval_value interval_unit [ interval_value interval_unit ... ]' |

多單位參數

多單位範例

SELECT INTERVAL 3 YEAR AS col;
+-------+
|    col|
+-------+
|3 years|
+-------+

SELECT INTERVAL -2 HOUR '3' MINUTE AS col;
+--------------------+
|                 col|
+--------------------+
|-1 hours -57 minutes|
+--------------------+

SELECT INTERVAL '1 YEAR 2 DAYS 3 HOURS';
+----------------------+
|                   col|
+----------------------+
|1 years 2 days 3 hours|
+----------------------+

SELECT INTERVAL 1 YEARS 2 MONTH 3 WEEK 4 DAYS 5 HOUR 6 MINUTES 7 SECOND 8
    MILLISECOND 9 MICROSECONDS AS col;
+-----------------------------------------------------------+
|                                                        col|
+-----------------------------------------------------------+
|1 years 2 months 25 days 5 hours 6 minutes 7.008009 seconds|
+-----------------------------------------------------------+