CASE expression WHEN value THEN result [ WHEN ... ] [ ELSE result ] END SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'many' END
CASE WHEN condition THEN result [ WHEN ... ] [ ELSE result ] END SELECT a, b, CASE WHEN a = 1 THEN 'aaa' WHEN b = 2 THEN 'bbb' ELSE 'ccc' END
IF function is actually a language construct that is equivalent to the following
CASE WHEN condition THEN true_value [ ELSE false_value ] END
Evaluates and returns
condition is true, otherwise null is returned and
true_value is not evaluated.
if(condition, true_value, false_value)
Evaluates and returns
condition is true, otherwise evaluates and returns
coalesce(value1, value2[, ...])
Returns the first non-null
value in the argument list. Like a
CASE expression, arguments are only evaluated if necessary.
Window functions perform calculations across rows of the query result. They run after the
HAVING clause but before the
ORDER BY clause. Invoking a window function requires special syntax using the
OVER clause to specify the window. A window has three components:
- The partition specification, which separates the input rows into different partitions. This is analogous to how the
GROUP BYclause separates rows into different groups for aggregate functions.
- The ordering specification, which determines the order in which input rows will be processed by the window function.
- The window frame, which specifies a sliding window of rows to be processed by the function for a given row. If the frame is not specified, it defaults to
RANGE UNBOUNDED PRECEDING, which is the same as
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This frame contains all rows from the start of the partition up to the last peer of the current row.
For example, the following query ranks orders for each clerk by price:
SELECT orderkey, clerk, totalprice, rank() OVER (PARTITION BY clerk ORDER BY totalprice DESC) AS rnk FROM orders ORDER BY clerk, rnk
rank() → bigint
Returns the rank of a value in a group of values. The rank is one plus the number of rows preceding the row that are not peer with the row. Thus, tie values in the ordering will produce gaps in the sequence. The ranking is performed for each window partition.
row_number() → bigint
Returns a unique, sequential number for each row, starting with one, according to the ordering of rows within the window partition.
如果 ... over( ... order by....)中不存在排名相同的,那么
All Aggregate Functions can be used as window functions by adding the
OVER clause. The aggregate function is computed for each row over the rows within the current row’s window frame.
For example, the following query produces a rolling sum of order prices by day for each clerk:
SELECT clerk, orderdate, orderkey, totalprice, sum(totalprice) OVER (PARTITION BY clerk ORDER BY orderdate) AS rolling_sum FROM orders ORDER BY clerk, orderdate, orderkey
WITH clause defines named relations for use within a query. It allows flattening nested queries or simplifying subqueries. For example, the following queries are equivalent:
SELECT a, b FROM ( SELECT a, MAX(b) AS b FROM t GROUP BY a ) AS x; WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a) SELECT a, b FROM x;
This also works with multiple subqueries:
WITH t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a), t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a) SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.a = t2.a;
Additionally, the relations within a
WITH clause can chain:
WITH x AS (SELECT a FROM t), y AS (SELECT a AS b FROM x), z AS (SELECT b AS c FROM y) SELECT c FROM z;