Блог

Я создал Snowflake SQL udf, который я вызываю со следующим кодом:

 select *
from table(drill_top_down('12345','XXX)) order by depth,path;
 

Если мне нужно выполнить запрос для нескольких элементов, можно ли затем ввести список или аналогичный udf, а затем выполнить цикл по моему входному списку?

Или я могу каким-то образом вызвать свою функцию более разумным способом, чтобы получить результат из нескольких входных данных?

Вы можете предоставить массив снежинок, объект или вариант с вложенными в него наборами аргументов и использовать их в качестве входных данных для функции таблицы.

Адаптируя ваш пример, используя конструкцию массива для предоставления двух наборов аргументов, входные данные будут выглядеть примерно так :

 select *
from table(drill_top_down( 
            array_construct(
                  array_construct('12345','XXX'),
                  array_construct('67890','YYY')
                       )::array;
 

Или я предпочитаю использовать parse_json, так как мне легче читать

 select *
from table(drill_top_down(parse_json('
                             [ ["12345","XXX"],
                               ["67890","YYY"]  ]')::array;
 

Вам нужно будет адаптировать свою табличную функцию для распаковки наборов аргументов с помощью выражения общей таблицы (CTE), чтобы свести входные аргументы в таблицу, а затем отменить их с помощью бокового выравнивания.

Вот тривиальный пример:

 CREATE OR REPLACE FUNCTION array_concat ( arr array)
  RETURNS TABLE ( concatenated_string varchar )
  AS 
  $
  With a as (Select arr)
  Select listagg(value)
  From a, table(flatten(input => arr))
  $
  ;
 

Вот немного более сложный пример, который выполняет операцию с каждым набором аргументов, используя row_number() для их группировки.

 CREATE OR REPLACE FUNCTION array_calcs ( arg_list array)
  RETURNS TABLE 
     ( arg_id integer,
       array_sz integer,
       array_sum integer,
       array_mean decimal(12,2) )
  AS 
  $
  With 
       -- CTE containing the ARGS
       arg_input as (select arg_list),
       -- CTE un-nest (flatten) first level of args list to each args set
       arg_sets as 
            (Select row_number() over (order by NULL desc) as arg_id, value as arg_set
             From arg_input, lateral flatten(input => arg_list))
  -- Do something with the Args. e.g. Perform some calculations with the Input arguments            
  Select  arg_id , count(*) array_sz, sum(value)::integer array_sum, array_sum/array_sz::decimal(12,2) array_mean
  From arg_sets, table(flatten(input => arg_set))
  Where is_decimal( value ) or  is_integer( value ) or is_double( value ) -- filter out non-numeric arguments i.e. validate inputs
  Group By arg_id
  $;  
 

Это работает, если мы приведем следующие входные аргументы

 Select * from table(array_calcs(parse_json('[ [1],
                                              [1,2],
                                              [1,2,3],
                                              [1,2,3,4],
                                              ["A","B"],
                                              ["A",1]
                                            ]')::array));
 

Производя следующее:

Но предупреждаю. Если ваша цель состояла в том, чтобы строить свои аргументы непосредственно из ваших данных, а не жестко кодировать их в вызове функции, вы, скорее всего, столкнетесь с этой проблемой:

 Create or replace View V_array_calcs_input as 
Select parse_json($1)::array arg_list
from (values ('[[1],[1,2],[1,2,3],[1,2,3,4],["A","B"], ["A",1]'));
 

 Select * 
  from V_array_calcs_input, 
       table(array_calcs(arg_list));
 

Ошибка компиляции SQL: Неподдерживаемый тип подзапроса не может быть оценен

Хранимая процедура или JavaScript UDF/UDTF могут быть лучшими вариантами решения этой проблемы, если вы можете построить необходимую вам функциональную логику в любом из них.