CASE

CASE expression    WHEN value THEN result    [ WHEN ... ]    [ ELSE result ]ENDSELECT a,       CASE a           WHEN 1 THEN 'one'           WHEN 2 THEN 'two'           ELSE 'many'       END

CASE    WHEN condition THEN result    [ WHEN ... ]    [ ELSE result ]ENDSELECT a, b,       CASE           WHEN a = 1 THEN 'aaa'           WHEN b = 2 THEN 'bbb'           ELSE 'ccc'       END

IF

The IF function is actually a language construct that is equivalent to the following CASE expression:

CASE    WHEN condition THEN true_value    [ ELSE false_value ]END

if(condition, true_value)

Evaluates and returns true_value if condition is true, otherwise null is returned and true_value is not evaluated.

if(condition, true_value, false_value)

Evaluates and returns true_value if condition is true, otherwise evaluates and returns false_value.

COALESCE

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

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 BY clause 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 rnkFROM ordersORDER 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....)中不存在排名相同的,那么rank()和row_number()效果相同，若存在排名相同的，rank()会出现1，1,3，3这样的排名，row_number则是1,2,3,4

Aggregate Functions

All  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_sumFROM ordersORDER BY clerk, orderdate, orderkey

WITH Clause

The 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, bFROM (  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 t1JOIN 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;