SELECT statement can be used for cross-database federated queries.SELECT [ ALL | DISTINCT ] select_expression [, ...][ FROM from_item [, ...] ][ WHERE condition ][ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ][ HAVING condition][ WINDOW window_definition_list][ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ][ ORDER BY expression [ ASC | DESC ] [, ...] ][ OFFSET count [ ROW | ROWS ] ][ LIMIT { count | ALL } ][ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
SELECT: Specify the column to retrieve or the expression to calculate.ALL: (Default) Return all records.DISTINCT: Return only unique records and eliminate duplicate results.select_expression: Specify the column name, expression, or calculation result to select, which can include one or more items separated by commas.FROM from_item [, ...]
FROM: Specify a data source for the query, such as a table, view, or subquery.from_item: Specify one or more data sources, such as tables, views, and subqueries.WHERE condition
WHERE: Used to filter records. condition is a conditional expression. Only records that meet the condition will be included in the result set.GROUP BY [ ALL | DISTINCT ] grouping_element [, ...]
GROUP BY: Group the result set by one or more columns, commonly used for aggregate functions.ALL: (Default) Group all records.DISTINCT: Group only unique records.grouping_element: Specify the columns or expressions used for grouping.HAVING condition
HAVING: Filter the grouped results, similar to WHERE, but it acts on the aggregated results.WINDOW window_definition_list
WINDOW: Define the windows for window functions, which can be used to calculate complex aggregations, such as moving averages.{ UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select
UNION: Combine the results of multiple queries, with deduplication (Alternatively, you can use ALL to retain all records.).INTERSECT: Return the intersection of all query results, that is, only records that appear in all queries.EXCEPT: Return records that exist in the result of the first query but not in the others.ALL and DISTINCT: Control the processing of duplicate records.ORDER BY expression [ ASC | DESC ] [, ...]
ORDER BY: Sort the result set.expression: Specify the columns or expressions to sort by.ASC: Ascending order (default).DESC: Descending order.OFFSET count [ ROW | ROWS ]
OFFSET: Skip the first "count" rows of the result set.ROW and ROWS: Specify the number of rows to skip. ROW and ROWS can usually be used interchangeably.LIMIT { count | ALL }
LIMIT: Limit the number of records returned.count: The number of records returned.ALL: No limit on the number of records returned (default, usually can be omitted).FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
FETCH: Control the number of rows returned from the result set and can specify whether to include rows with the same sort value.FIRST or NEXT: Obtain the first row or the subsequent rows in the result set.count: The number of rows to return.ROW and ROWS: Specify the number of rows to return.ONLY: Return only the specified number of rows.WITH TIES: Include records that have the same sorting value as the last row.Keyword | SQL:2016 |
ALTER | reserved |
AND | reserved |
AS | reserved |
BETWEEN | reserved |
BY | reserved |
CASE | reserved |
CAST | reserved |
CONSTRAINT | reserved |
CREATE | reserved |
CROSS | reserved |
CUBE | reserved |
CURRENT_CATALOG | reserved |
CURRENT_DATE | reserved |
CURRENT_PATH | reserved |
CURRENT_ROLE | reserved |
CURRENT_SCHEMA | reserved |
CURRENT_TIME | reserved |
CURRENT_TIMESTAMP | reserved |
CURRENT_USER | reserved |
DEALLOCATE | reserved |
DELETE | reserved |
DESCRIBE | reserved |
DISTINCT | reserved |
DROP | reserved |
ELSE | reserved |
END | reserved |
ESCAPE | reserved |
EXCEPT | reserved |
EXISTS | reserved |
EXTRACT | reserved |
FALSE | reserved |
FOR | reserved |
FROM | reserved |
FULL | reserved |
GROUP | reserved |
GROUPING | reserved |
HAVING | reserved |
IN | reserved |
INNER | reserved |
INSERT | reserved |
INTERSECT | reserved |
INTO | reserved |
IS | reserved |
JOIN | reserved |
JSON_ARRAY | reserved |
JSON_EXISTS | reserved |
JSON_OBJECT | reserved |
JSON_QUERY | reserved |
JSON_TABLE | reserved |
JSON_VALUE | reserved |
LEFT | reserved |
LIKE | reserved |
LISTAGG | reserved |
LOCALTIME | reserved |
LOCALTIMESTAMP | reserved |
NATURAL | reserved |
NORMALIZE | reserved |
NOT | reserved |
NULL | reserved |
ON | reserved |
OR | reserved |
ORDER | reserved |
OUTER | reserved |
PREPARE | reserved |
RECURSIVE | reserved |
RIGHT | reserved |
ROLLUP | reserved |
SELECT | reserved |
SKIP | reserved |
TABLE | reserved |
THEN | reserved |
TRIM | reserved |
TRUE | reserved |
UESCAPE | reserved |
UNION | reserved |
UNNEST | reserved |
USING | reserved |
VALUES | reserved |
WHEN | reserved |
WHERE | reserved |
WITH | reserved |
tablenameSchemaNameexample_dmc_resource.a_schema."table$partitions""identifierWith""double""quotes"
"table-name""123DbName""colum$name@field"
Function | Description | Example |
AND | If all values are true, the result is true. | a AND b |
OR | If any of the values is true, the result is true. | a OR b |
NOT | If the value is false, true is returned. | NOT a |
Function | Description | Example |
< | Less than | a > 1 |
> | Greater than | a < 1 |
<= | Less than or equal to | a = 1 |
>= | Greater than or equal to | a >= 1 |
= | Equal to | a <= 1 |
<> | Not equal to | a <> 1 |
!= | Not equal to | a != 1 |
Function | Description |
+ | Addition |
- | Subtraction |
* | Multiplication |
/ | Division |
% | Modulo |
Function | Type of Return Value | Description | Example |
mod(n, m) | Same as x type by default | Return the remainder of n divided by m. | mod(101, 4) |
sqrt(x) | double | Square root. | sqrt(9) |
abs(x) | Same as x type by default | Absolute value. | abs(3) |
cbrt(x) | double | Cube root. | cbrt(3) |
ceil(x) | Same as x type by default | Alias for the ceiling function. | - |
ceiling(x) | Same as x type by default | Return the smallest integer greater than the given numerical expression. | Ceiling(5.5), with 6 returned. |
degrees(x) | double | Convert radians to degrees. | Degrees(1), with 57 returned. |
e(x) | double | Return Euler's constant. | - |
exp(x) | double | Return Euler's constant raised to the power of x. | - |
floor(x) | Same as x type by default | Return the largest integer less than x. | floor(5.5), with 5 returned. |
ln(x) | double | Return the natural logarithm of x. | - |
log(b, x) | double | Return the logarithm of x with b to the base. | - |
log2(x) | double | Return the logarithm of 2 with x to the base. | log2(2), with 1 returned. |
log10(x) | double | Return the logarithm of 10 with x to the base. | log10(10), with 1 returned. |
pi() | double | Return the constant pi. | - |
pow(x,p) | double | Equivalent to power(). | pow(3, 2) |
power(x, p) | double | Return x raised to the power of p. | - |
radians(x) | double | Convert an angle x (in degrees) to radians. | - |
round(x) | Same as x type by default | Return x rounded to the nearest integer. | - |
round(x, d) | Same as x type by default | Return x rounded to d decimal places. | - |
sign(x) | Same as x type by default | Return the sign corresponding to x: if x > 0, 1 is returned; if x < 0, -1 is returned; if x = 0, 0 is returned. | - |
truncate(x) | double | Return the number with the decimal part truncated. | truncate(4.9), with 4 returned. |
rand() | double | Alias for the random function. | - |
random() | double | Return a random value in the range [0, 1.0). | - |
random(n) | Same as x type by default | Return a random value in the range [0, n). | - |
random(m, n) | Same as x type by default | Return a random value in the range [0, m). | - |
Function | Type of Return Value | Description | Example |
chr(n) | varchar | Return the Unicode code as a single string with n digits. | - |
codepoint(string) | integer | Return the Unicode code of unique characters in a string. | - |
concat(string1, ..., stringN) | varchar | A string concatenation function that combines two or more strings to form a new string. | concat('hello', ' ', 'world') , concatenate to form 'hello world'. |
concat_ws(string0, string1, ..., stringN) | varchar | Return the concatenation of string1, string2,..., stringN using string0 as the delimiter. If string0 is null, the return value is null. Any null provided in the parameters after the delimiter will be skipped. | - |
concat_ws(string0, array(varchar)) | varchar | Return the concatenation of elements in the array using string0 as the delimiter. If string0 is null, the return value is null. Any null in the array will be skipped. | - |
format(format, args...) | varchar | View format(). | |
hamming_distance(string1, string2) | bigint | Return the Hamming distance between string1 and string2, that is, the number of positions where the corresponding characters are different. Note: The lengths of the two strings should be identical. | - |
length(string) | bigint | Return the length of a string. | - |
levenshtein_distance(string1, string2) | bigint | Return the Levenshtein edit distance between string1 and string2, that is, the minimum number of single-character edits (insertions, deletions, or replacements) required to change string1 to string2. | - |
lower(string) | varchar | Convert a string to lowercase. | lower('ABC'), with 'abc' returned. |
lpad(string, size, padstring) | varchar | Use padstring to add strings to the left side of a string, making the length of the string become the size. If the size is less than the length of the string, the result will be truncated to the size length. The size cannot be negative, and padstring should not be null. | - |
ltrim(string) | varchar | Remove leading spaces from a string. | - |
position(substring IN string) | bigint | Return the position of the first occurrence of the substring in a string, which starts from 1. Return 0 if not found. | - |
replace(string, search) | varchar | Delete all occurrences of search from a string. | - |
replace(string, search, replace) | varchar | Replace all occurrences of search in a string with replace. | replace('hello old', 'old', 'new'), with 'hello new' returned. |
reverse(string) | varchar | Reverse the order of characters in a string and return the reversed string. | reverse('abc'), with 'cba' returned. |
rpad(string, size, padstring) | varchar | Use padstring to add strings to the right side of a string, making the length of the string become the size. If the size is less than the length of the string, the result will be truncated to the size length. The size cannot be negative, and padstring should not be null. | - |
rtrim(string) | varchar | Remove the space at the end of a string. | - |
split(string, delimiter) | - | Split a string into multiple parts according to the specified delimiter, and return an array. | - |
split(string, delimiter, limit) | - | Split a string into multiple parts according to the specified delimiter, and return an array with a maximum size of limit. | - |
split_part(string, delimiter, index) | varchar | Split a string into multiple parts according to the specified delimiter, and return the part at position index. | - |
split_to_map(string, entryDelimiter, keyValueDelimiter) | - | Return a map after splitting a string with entryDelimiter and keyValueDelimiter. | select split_to_map('a:1,b:2', ',', ':'), with {'a':'1','b':'2'} returned. |
strpos(string, substring) | bigint | Return the starting position of the first substring in a string. Traverse starting from the first character, and return 0 if not found. | - |
strpos(string, substring, instance) | bigint | Return the position of the nth substring in a string. The instance should be a positive number. Traverse from the first character. If not found, 0 is returned. | - |
starts_with(string, substring) | boolean | Determine whether a string starts with a substring. | - |
substr(string, start) | varchar | A function with the same name as the substring function. | - |
substr(string, start, length) | varchar | A function with the same name as the substring function. | - |
substring(string, start) | varchar | Return the substring of a string starting from start. | - |
substring(string, start, length) | varchar | Return the substring of a string starting from start with a length of length. | - |
translate(source, from, to) | varchar | Replace all occurrences of from in the source with to. | - |
trim(string) | varchar | Remove leading and trailing spaces from a string. | - |
trim([ [ specification ] [ string ] FROM ] source ) | varchar | Remove any specified leading and/or trailing characters from the source. | - |
upper(string) | varchar | Convert lowercase letters in a string to uppercase. | - |
Function | Type of Return Value | Description |
any_value(x) | Same as x type by default | Return any non-null value x (if it exists). x can be any valid expression. This allows values to be returned from columns that are not directly part of the aggregation, including the use of these column expressions in queries. |
arbitrary(x) | Same as x type by default | Equivalent to any_value(). |
array_agg(x) | Same as x type by default | Return an array created from the input x elements. |
avg(x) | double | Return the average. |
sum(x) | bigint | Return the sum. |
max(x) | bigint | Return the maximum value. |
max(x, n) | array | Return the largest n values from the input. |
max_by(x, y) | Same as x type by default | Return the value in x associated with the maximum value of y. |
max_by(x, y, n) | array | Return n values in x associated with the n smallest values in y, sorted in descending order of y. |
min(x) | bigint | Return the minimum value. |
min(x, n) | min_by(x, y, n) | Return the n smallest values in the input. |
min_by(x, y) | Same as x type by default | Return the value in x associated with the minimum value of y. |
min_by(x, y, n) | array | Return n values in x associated with the n smallest values in y, sorted in ascending order of y. |
checksum(x) | varbinary | Return the checksum of the input value. |
bool_and(boolean) | boolean | Return true if all input values are true; otherwise, return false. |
bool_or(boolean) | boolean | Return true if any of the input values is true; otherwise, return false. |
every(boolean) | boolean | Equivalent to bool_and(). |
count(*) | bigint | Return the number of rows. |
count(x) | bigint | The number of non-null values returned. |
count_if(x) | boolean | Return the number of input values that are true. |
geometric_mean(x) | double | Return the geometric mean. |
listagg(x, separator) | varchar | Return a string where each x is concatenated by the delimiter. |
histogram | - | Return a map containing the number of occurrences of each input value. |
Function | Type of Return Value | Description | Example |
current_date | date | Return the date when the current query was initiated. | SELECT current_date |
current_time | time with time zone | Return the time when the current query was initiated. | - |
current_timestamp | timestamp with time zone | Return the timestamp when the current query was initiated, with millisecond precision. | - |
current_timezone() | varchar | Return the current time zone in the format defined by IANA (such as America/Los_Angeles), or return the current time zone as a fixed offset from UTC (such as +08:35). | SELECT current_timezone() |
date(x) | date | Similar to the CAST(x AS date) function, it can convert the type of x to Date. | - |
date_add(unit, value, timestamp) | - | Add a time interval value to timestamp. The unit of the time interval is unit, and a negative value can be used for subtraction. | SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00'), with 2020-03-01 00:01:26.000 returned. |
date_diff(unit, timestamp1, timestamp2) | bigint | Return the difference between timestamp1 and timestamp2, with the unit of difference being unit. For specific unit values, see the interval function. | SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC') , with 24 returned. |
human_readable_seconds(double) | varchar | Format the seconds represented by double as a readable time string, including weeks, days, hours, minutes, and seconds. | SELECT human_readable_seconds(3762), with the string "1 hour, 2 minutes, 42 seconds" returned. |
parse_duration(string) | interval | Parse a string into a time interval with the unit of value unit. | -SELECT parse_duration('3.81 d'), with 3 19:26:24.000 returned. |
date_trunc(unit, x) | - | Truncate the date or timestamp x to the specified time unit, and return the truncated result. For specific unit values of the unit, see the interval function. | - |
date_format(timestamp, format) | varchar | Format the timestamp as a string using the format specified by format. | SELECT date_format(TIMESTAMP '2022-10-20 05:10:00', '%m-%d-%Y %H'), with 10-20-2022 05 returned. |
date_parse(string, format) | date | Use format to parse a string into a timestamp. | SELECT date_parse('2022/10/20/05', '%Y/%m/%d/%H'), with 2022-10-20 05:00:00.000 returned. |
last_day_of_month(x) | date | Return the last day of the current month for the date x. | last_day_of_month(current_date) |
from_iso8601_timestamp(string) | timestamp with time zone | Parse an ISO 8601-formatted string into a timestamp with a time zone. | SELECT from_iso8601_timestamp('2020-05-11'), with 2020-05-11 00:00:00.000 America/Vancouver returned. |
from_iso8601_timestamp_nanos(string) | timestamp with time zone | Parse an ISO 8601-formatted string into a timestamp with a time zone, with nanosecond precision. | SELECT from_iso8601_timestamp_nanos('2020-05-11T11:15:05'), with 2020-05-11 11:15:05.000000000 America/Vancouver returned. |
from_iso8601_date(string) | date | Parse an ISO 8601-formatted string into a date. | SELECT from_iso8601_date('2020-W10'), with 2020-03-02 returned. |
from_unixtime(unixtime) | timestamp with time zone | Return a Unix timestamp. | from_unixtime(1475996660), with 2016-10-09 15:04:20.000 returned. |
from_unixtime(unixtime, zone) | timestamp with time zone | Return a unixtime timestamp with the time zone specified by a string. | - |
from_unixtime(unixtime, hours, minutes) | timestamp with time zone | Return a unixtime timestamp in the time zone corresponding to hours and minutes. | - |
from_unixtime_nanos(unixtime) | timestamp with time zone | Return a Unix timestamp with nanosecond precision. | SELECT from_unixtime_nanos(DECIMAL '1234'), with 1970-01-01 00:00:00.000001234 UTC returned. |
localtime | time | Return the current time at the start of the query. | - |
localtimestamp | timestamp | Return the current timestamp at the start of the query. | - |
now() | timestamp with time zone | Alias for current_timestamp. | - |
to_iso8601(x) | varchar | Format x into an ISO 8601 string. x can be date, timestamp, or timestamp with a time zone. | - |
to_milliseconds(interval) | bigint | Convert to a time interval in milliseconds. | - |
to_unixtime(timestamp) | double | Convert to a Unix timestamp. | - |
extract(field FROM x) | bigint | Return field from x. | - |
day(x) | bigint | Return the number of days in the month for a specified date. | - |
day_of_month(x) | bigint | Alias for the day(x) function. | - |
day_of_week(x) | bigint | Return the day of the week for the specified date, with values ranging from 1 (Monday) to 7 (Sunday). | - |
day_of_year(x) | bigint | Return the day of the year for the specified date, with values ranging from 1 to 366. | - |
dow(x) | bigint | Alias for the day_of_week function. | - |
doy(x) | bigint | Alias for the day_of_year function. | - |
hour(x) | bigint | Return the hour for the specified date, with values ranging from 1 to 23. | - |
millisecond(x) | bigint | Return the number of milliseconds for the specified time. | - |
minute(x) | bigint | Return the number of minutes for the specified time. | - |
month(x) | bigint | Return the month for the specified date. | - |
quarter(x) | bigint | Return the number of minutes for the specified date. | - |
second(x) | bigint | Return the number of seconds for the specified date. | - |
timezone_hour(timestamp) | bigint | Return the number of hours offset from the specified timestamp corresponding to the time zone. | - |
timezone_minute(timestamp) | bigint | Return the number of minutes offset from the specified timestamp corresponding to the time zone. | - |
week(x) | bigint | Return the ISO week number for the given date x, with values ranging from 1 to 53. | - |
week_of_year(x) | bigint | Alias for the week function. | - |
year(x) | bigint | Return the year for the specified date. | - |
Unit | Description |
millisecond | Millisecond |
second | Seconds |
minute | Minute |
hour | Hour |
day | Day |
week | Week |
month | Month |
quarter | Quarter of a year |
year | Year |
SELECT clerk, orderdate, orderkey, totalprice,sum(totalprice) OVER (PARTITION BY clerkORDER BY orderdate) AS rolling_sumFROM ordersORDER BY clerk, orderdate, orderkey
Function | Type of Return Value | Description |
cume_dist() | bigint | Calculate the position of a value relative to all values in a partition. |
dense_rank() | bigint | Calculate the rank of a value within a group of values. Unlike the rank function, dense_rank does not leave gaps in the ranking sequence in the case of ties. |
ntile(n) | bigint | Divide the rows of a window partition into n buckets, and return the bucket number each row falls into, ranging from 1 to n. |
percent_rank() | double | Calculate the percentage rank of a value within a set of values. The return value is represented as a decimal ranging from 0 to 1. |
rank() | bigint | Calculate the ranking of a certain value in a group of values. If equal rankings occur, gaps are left in the ranking sequence. |
ow_number() | bigint | Assign a unique consecutive number to each row. |
Function | Type of Return Value | Description |
first_value(x) | Same as x type by default | Return the value of the first data in a certain column of the partition. |
last_value(x) | Same as x type by default | Return the value of the last data in a certain column of the partition. |
nth_value(x, offset) | Same as x type by default | Return the value of the nth row from the start of the window. n starts from 1. If ignoreNulls=true, null will be skipped when searching for the nth row. Otherwise, every row is counted in n. If there is no such nth row (for example, when n is 10 but the window size is less than 10), null is returned. The first parameter is the column name, and the second parameter is the nth previous row. |
lead(x[, offset[, default_value]]) | Same as x type by default | Return the value of the nth row down from the current row in the window. The default value of n is 1, and the default value of default is null. If the value of the nth row is null, null is returned. If there is no such offset row (for example, when the offset is 1 and the last row of the window has no downward row), default is returned. The first parameter is the column name, the second parameter is the nth previous row, and the third parameter is the default value. |
lag(x[, offset[, default_value]]) | Same as x type by default | Return the value of the nth row above the current row in the window. The default value for n is 1, and the default value for default is null. If the value of the nth row is null, null is returned. If there is no such an offset row (for example, the first row of the window has no row above it when the offset is 1), default is returned. The first parameter is the column name, the second parameter is the nth previous row, and the third parameter is the default value. |
Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback