Create a dynamic pivot function with multiple aggregate functions in Snowflake
This video follows on from • Create a Dynamic Pivot Stored Procedu...
The sql used in this video is below. Just one thing to note - YouTube does not allow less-than and greater-than signs in descriptions, so ive replaced that part with LESSTHANGREATERTHAN. That will need to be put back before you use the script.
execute immediate
$$
declare
column_list_in string default (select listagg(distinct category, ',') from "EXAMPLE_DB"."EXAMPLE_SCHEMA"."EXAMPLE_TABLE" WHERE CONTAINS("CATEGORY", 'A'));
table_in string default '"EXAMPLE_DB"."EXAMPLE_SCHEMA"."EXAMPLE_TABLE"';
pivot_column_name string default 'CATEGORY';
value_column_name string default 'VALUE';
pivot_function1 string default 'SUM';
pivot_function2 string default 'COUNT';
begin
--make a comma-seperated string of the columns to pivot
let column_selection string := '(\'' || regexp_replace(column_list_in, ',', '\',\'') || '\')';
--create a query to get the names of the unpivoted columns
let unpivoted_columns_sql string := 'select listagg(column_name, \',\') within group (order by ordinal_position)
from ' || split_part(table_in, '.', 1) || '.information_schema.columns
where table_catalog = ' || replace(split_part(table_in, '.', 1), '"', '\'') || '
and table_schema = ' || replace(split_part(table_in, '.', 2), '"', '\'') || '
and table_name = ' || replace(split_part(table_in, '.', 3), '"', '\'') || '
and column_name not in (\'' || value_column_name || '\',\'' || pivot_column_name || '\')';
--execute the query and bring the result into a string called column_labels
let column_labels string;
let unpivoted_columns resultset := (execute immediate : unpivoted_columns_sql);
let c cursor for unpivoted_columns;
open c;
fetch c into column_labels;
close c;
--add the name of the aggregate function to the pivoted column names
let column_labels_agg1 string := replace(column_list_in, ',' ,'_' || pivot_function1 || ',') || '_' || pivot_function1;
let column_labels_agg2 string := replace(column_list_in, ',' ,'_' || pivot_function2 || ',') || '_' || pivot_function2;
--create strings for aliases
let counter integer := 1;
let column_join string := '';
let column_w_alias string := '';
while (split_part(column_labels, ',', counter) LESSTHANGREATERTHAN '') do
--get a list of unpivoted columns with an alias, seperated by equals signs
column_join := column_join || 'agg1.' || split_part(column_labels, ',', counter) || ' = agg2.' || split_part(column_labels, ',', counter) || ' and ';
--get a list of un-pivoted column names to enter into the final select statement
column_w_alias := column_w_alias || 'agg1.' || split_part(column_labels, ',', counter) || ',';
counter := counter + 1;
end while;
--remove the trailing parts of the strings string
column_join := left(column_join, length(column_join) - 4);
column_w_alias := left(column_w_alias, length(column_w_alias) - 1);
--join together as a sql query
let final_sql string := 'with agg1_table as (
select *
from ' || table_in || '
pivot(' || pivot_function1 || '("' || value_column_name || '")
for ' || pivot_column_name || ' in ' || column_selection || ')
as p(' || column_labels || ',' || column_labels_agg1 || ')
),
agg2_table as (
select *
from ' || table_in || '
pivot(' || pivot_function2 || '("' || value_column_name || '")
for ' || pivot_column_name || ' in ' || column_selection || ')
as p(' || column_labels || ',' || column_labels_agg2 || ')
)
select ' || column_w_alias || ',' || column_labels_agg1 || ',' || column_labels_agg2 ||'
from agg1_table agg1
inner join agg2_table agg2
on ' || column_join;
--excecute the query
let output resultset := (execute immediate : final_sql);
--return the output of the query
return table(output);
$$;
Смотрите видео Create a dynamic pivot function with multiple aggregate functions in Snowflake онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь DotPi 26 Март 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 1,59 раз и оно понравилось 1 людям.