Предположим, что у меня есть следующие данные
CREATE TABLE sample_table (
YEAR INTEGER,
BRAND VARCHAR,
PRODUCT VARCHAR,
SALES INTEGER
);
INSERT INTO sample_table (YEAR, BRAND, PRODUCT, SALES) VALUES
(2023, 'AX', 'A', 10),
(2024, 'AX', 'A', 20),
(2024, 'AX', 'B', 70),
(2022, 'AY', 'C', 20),
(2023, 'AY', 'C', 90),
;
Есть ли способ создать макрос для достижения того же результата, что и ниже, где я могу просто использовать бренд и продукт в качестве аргументов списка?
SELECT YEAR BRAND, PRODUCT, SUM(SALES) FROM SAMPLE_TABLE
GROUP BY YEAR, GROUPING SETS(CUBE(BRAND, PRODUCT));
───────┬─────────┬────────────┐
│ BRAND │ PRODUCT │ sum(SALES) │
│ int32 │ varchar │ int128 │
├───────┼─────────┼────────────┤
│ 2024 │ │ 90 │
│ 2022 │ │ 20 │
│ 2022 │ C │ 20 │
│ 2022 │ │ 20 │
│ 2023 │ │ 90 │
│ 2023 │ │ 100 │
│ 2023 │ A │ 10 │
│ 2024 │ B │ 70 │
│ 2023 │ C │ 90 │
│ 2023 │ │ 10 │
│ 2024 │ │ 90 │
│ 2024 │ B │ 70 │
│ 2024 │ A │ 20 │
│ 2023 │ C │ 90 │
│ 2023 │ A │ 10 │
│ 2024 │ A │ 20 │
│ 2022 │ C │ 20 │
├───────┴─────────┴────────────┤
│ 17 rows 3 columns
То, что я имел в виду, это
CREATE OR REPLACE MACRO MSUM(
GRPCOLS
) AS TABLE (
FROM TBL
SELECT
COLUMNS(C -> (LIST_CONTAINS(GRPCOLS, C))),
SUM(SALES)
GROUP BY YEAR, GROUPING SETS(CUBE(COLUMNS(C -> LIST(CONTAINS(GRPCOLS, C)))))
);
WITH TBL AS (SELECT * FROM SAMPLE_TABLE)
FROM MSUM([BRAND, PRODUCT]);
но это невозможно сделать, потому что, если я правильно понял, COLUMNS — это звездное выражение, и его нельзя использовать в GROUP BY
Binder Error: STAR expression is not supported here
Есть идеи?
🤔 А знаете ли вы, что...
SQL может работать с большими объемами данных благодаря оптимизации и индексам.
Вот пример использования хранимой процедуры, которая динамически генерирует запрос SQL.
CREATE OR REPLACE PROCEDURE dynamic_grouping_sum(
IN GRPCOLS VARCHAR -- Pass column names as a comma-separated string like 'BRAND,PRODUCT'
)
BEGIN
DECLARE sql_query VARCHAR;
-- Construct the SQL query dynamically
SET sql_query = CONCAT(
'SELECT YEAR, ', GRPCOLS, ', SUM(SALES) AS total_sales ',
'FROM SAMPLE_TABLE ',
'GROUP BY YEAR, GROUPING SETS(CUBE(', GRPCOLS, '));'
);
-- Execute the dynamically created SQL
EXECUTE IMMEDIATE sql_query;
END;
например:
CALL dynamic_grouping_sum('BRAND, PRODUCT');
Это будет динамически генерировать запрос:
SELECT YEAR, BRAND, PRODUCT, SUM(SALES) AS total_sales
FROM SAMPLE_TABLE
GROUP BY YEAR, GROUPING SETS(CUBE(BRAND, PRODUCT));
Если вы хотите ограничить его только определенными столбцами, вы можете добавить дополнительную логику проверки внутри процедуры, чтобы проверить, действительны ли переданные столбцы.
Не уверен, что это именно то, о чем вы спрашиваете, но:
Можно создать строку запроса и использовать json_execute_serialized_sql() чтобы запустить его.
duckdb.sql("""
create or replace macro msum(tbl, grpcols) as table (
from json_execute_serialized_sql(json_serialize_sql(
format('
from {0}
select {1}, sum(sales)
group by year, grouping sets(cube({1}))',
tbl,
list_reduce(grpcols, (x, y) -> format('{},{}', x, y))
)
))
)
""")
duckdb.sql("""
from msum(sample_table, [brand, product])
""")
┌─────────┬─────────┬────────────┐
│ BRAND │ PRODUCT │ sum(sales) │
│ varchar │ varchar │ int128 │
├─────────┼─────────┼────────────┤
│ AY │ NULL │ 20 │
│ AY │ C │ 20 │
│ AY │ NULL │ 90 │
│ NULL │ B │ 70 │
│ NULL │ NULL │ 20 │
│ AX │ NULL │ 10 │
│ AX │ NULL │ 90 │
│ NULL │ A │ 10 │
│ NULL │ A │ 20 │
│ NULL │ C │ 20 │
│ NULL │ NULL │ 90 │
│ AX │ A │ 20 │
│ NULL │ C │ 90 │
│ NULL │ NULL │ 100 │
│ AX │ A │ 10 │
│ AX │ B │ 70 │
│ AY │ C │ 90 │
├─────────┴─────────┴────────────┤
│ 17 rows 3 columns │
└────────────────────────────────┘