Presto cheatsheet #Presto #SQL

Table/partition management

drop table: drop table table_name
rename: ALTER TABLE old_table RENAME TO new_table
delete certain partitions: delete from shop_mall_post_attributes where ds <= '2020-06-14';
insert rows: INSERT INTO table_name QUERY

Important Topics

UDFs
array
flatten complex type
Json
Map
Lambda
Row
Enum
Window Function
- OVER: function_name(arguments) OVER ([PARTITION BY column1, column2, ...] [ORDER BY column3, column4, ...] [window_frame_clause])

WITH sales_data AS (
    SELECT 
        'North' AS region, 'Widget A' AS product, 1000 AS sales_amount UNION ALL
    SELECT 'North', 'Widget B', 1500 UNION ALL
    SELECT 'North', 'Widget C', 800 UNION ALL
    SELECT 'South', 'Widget A', 1200 UNION ALL
    SELECT 'South', 'Widget B', 900 UNION ALL
    SELECT 'South', 'Widget C', 1100
)
SELECT
    region,
    product,
    sales_amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY sales_amount DESC
    ) AS sales_rank_in_region,
    SUM(sales_amount) OVER (
        PARTITION BY region
        ORDER BY sales_amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_in_region
FROM sales_data
ORDER BY region, sales_amount DESC

Useful UDFs

JSON related: json_parse, json_extract_scalar, json_extract, json_format
arrauy operations: ARRAY_UNION, ARRAY_INTERSECT
transform, reduce
try/try_cast:
- Evaluate an expression and handle certain types of errors by returning NULL.
- Example: TRY(JSON_PARSE(sparse_features)) IS NOT NULL (JSON_PARSE is likely to fail due to data corruption. )
NTILE(n) OVER (ORDER BY col) as col_num: assign

Flatten Complex Type: UNNEST

FROM table
CROSS JOIN UNNEST(array_col) AS t1 (col_val)
CROSS JOIN UNNEST(map_col) AS t2 (map_key, map_val)

ROW: default field name: field0, field1, ...

Type Conversion

Convert json to array: CAST(JSON_PARSE(embedding_str) as ARRAY<REAL>) as embedding
int tiemstamp to date CAST(DATE(PARSE_DATETIME(create_time, 'YYYY-MM-dd HH:mm:ss')) AS VARCHAR)

Manually define a small table

SELECT
    *
FROM a 
CROSS JOIN (
    VALUES
        (0, 'imp'),
        (1, 'clk')
) AS t (event, event_name)

SQL fundamental

put filtering conditions of the join table to ON.
- it means only join when conditions are met

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

what would happen for LEFT JOIN if conditions are applied on WHERE
- conditions on left table: they will still be applied on every row in left table, so it is equivalent to filtering first.
- conditions on right table: as the table will joined first, then essentally all failed joined rows, i.e., b.col is null will be not met the conditions, so that only the joined row will be left; LEFT JOIN essentially becomes INNERT JOIN

razhangwei/presto.md

Table/partition management

Important Topics

Useful UDFs

SQL fundamental