query 与 query_table 函数

The query_table and query 函数可实现更强大、更动态的 SQL。

query_table 函数会返回其字符串参数指定名称的表；query 函数会返回执行其字符串参数中 SQL 后得到的结果表。

两个函数都只接受常量字符串。例如，可在 prepared statement 中把表名作为参数传入：

CREATE TABLE my_table (i INTEGER);
INSERT INTO my_table VALUES (42);

PREPARE select_from_table AS SELECT * FROM query_table($1);
EXECUTE select_from_table('my_table');

i
42

结合 COLUMNS 表达式后，我们可以编写非常通用的纯 SQL 宏。比如下面是一个自定义版 SUMMARIZE，用于计算表中每列的 min 和 max：

CREATE OR REPLACE MACRO my_summarize(table_name) AS TABLE
SELECT
    unnest([*COLUMNS('alias_.*')]) AS column_name,
    unnest([*COLUMNS('min_.*')]) AS min_value,
    unnest([*COLUMNS('max_.*')]) AS max_value
FROM (
    SELECT
        any_value(alias(COLUMNS(*))) AS "alias_\0",
        min(COLUMNS(*))::VARCHAR AS "min_\0",
        max(COLUMNS(*))::VARCHAR AS "max_\0"
    FROM query_table(table_name::VARCHAR)
);

SELECT *
FROM my_summarize('https://${uri}/ontime.parquet')
LIMIT 3;

column_name	min_value	max_value
year	2017	2017
quarter	1	3
month	1	9

query 函数提供了更高灵活性。比如偏好 pandas stack 风格而非 SQL UNPIVOT 的用户，可使用：

CREATE OR REPLACE MACRO stack(table_name, index, name, values) AS TABLE 
FROM query(
    'UNPIVOT ' || table_name 
    || ' ON COLUMNS(* EXCLUDE (' || array_to_string(index, ', ') 
    || ')) INTO NAME ' || name || ' VALUES ' || values
);

WITH cities AS (
    FROM (
        VALUES 
            ('NL', 'Amsterdam', '10', '12', '15'),
            ('US', 'New York', '100', '120', '150')
    ) _(country, city, '2000', '2010', '2020')
)
SELECT *
FROM stack('cities', ['country', 'city'], 'year', 'population');

country	city	year	population
NL	Amsterdam	2000	10
NL	Amsterdam	2010	12
NL	Amsterdam	2020	15
US	New York	2000	100
US	New York	2010	120
US	New York	2020	150