Snowflake SQL применяет функцию один раз для каждого раздела
В Snowflake мы можем выполнить функцию окна следующим образом:
array_agg(a) over (partition by b) as c
Поскольку мои разделы большие (большие данные), а мой f дорогой, мне нужно сделать f(c) оценку только один раз для каждого раздела. Я попытался:
f(array_agg(a)) over (partition by b) as c
но Снежинка говорит, что это не работает, потому что f не является оконной функцией.
я тоже пытался
f(array_agg(a) over (partition by b))
но это f, кажется, оценивается в каждой строке. Как мне заставить f вычислять только один раз для каждого раздела, а затем присвоить результат всему разделу?
Обновлено: безусловно, самый быстрый метод, который я пробовал до сих пор, это
with cte as
(
select
b,
array_agg(a) arr_a,
f(arr_a) c
from
tbl
group by
b
)
select
x.a, x.b, y.c
from
tbl x
left join
cte y on x.b = y.b
Если принять f за функцию array_to_string, то вот минимальный рабочий пример реализуемого в данный момент метода:
with tbl as (
select
value[0]::VARCHAR a,
value[1]::NUMBER b
from (
select array_construct(array_construct('a',1),array_construct('a',2),array_construct('a',3),array_construct('b',1),array_construct('b',3),array_construct('b',4)) arr
), lateral flatten(arr)
),
cte as (
select
b,
array_agg(a) arr_a,
ARRAY_TO_STRING(arr_a,'_') c
from tbl
group by b
)
select
x.a, x.b, y.c
from tbl x
left join cte y
on x.b = y.b
;
Но объединение кажется ненужным. Есть ли способ лучше?
Вот минимальный воспроизводимый пример метода, с которым я хочу работать:
with tbl as (
select
value[0]::VARCHAR a,
value[1]::NUMBER b
from (
select array_construct(array_construct('a',1),array_construct('a',2),array_construct('a',3),array_construct('b',1),array_construct('b',3),array_construct('b',4)) arr
), lateral flatten(arr)
)
select
a,
ARRAY_TO_STRING(array_agg(a) over (partition by b),'_') as c
from tbl x
;
К сожалению, в этом методе f оценивается для каждой строки (6 раз). Но в идеале он должен рассчитываться только один раз для каждой группы значений b (4 раза).
Кроме того, обратите внимание на lateral flatten, чтобы восстановить значения a из массива (вместо самосоединения).
Я работаю над созданием минимального рабочего примера, но, согласно вашему второму пункту, у меня есть большое количество столбцов, некоторые из которых имеют очень большие строки данных, и они превышают максимальный размер массива при свертывании в массив, поэтому агрегируем другие столбцы, а затем сгладить их не получится. Хотя я мог бы сделать это только с этим столбцом (в примере), я потерял бы другие столбцы, не обсуждаемые в этом вопросе.
(вот почему необходим минимальный воспроизводимый пример) Похоже, самостоятельное объединение — ваш единственный вариант.
Мне интересно, может ли кеширование работать - возможно, "дорогой" f() - это UDF в Python или что-то в этом роде?
Ответы 1
ваш CTE MVP можно записать так:
with data(a) as (
select parse_json($1)
from values
('[["a",1],["a",2],["a",3],["b",1],["b",3],["b",4]]')
), tbl as (
select
value[0]::VARCHAR a,
value[1]::NUMBER b
from data as arr
,lateral flatten(arr.a)
)
также можно было бы написать:
with tbl(a,b) as (
select * from values
('a', 1),
('a', 2),
('a', 3),
('b', 1),
('b', 3),
('b', 4)
)
что кажется более явным для состояния ввода:
Итак, теперь мы можем посмотреть на ваш самый быстрый метод:
with tbl(a,b) as (
select * from values
('a', 1),
('a', 2),
('a', 3),
('b', 1),
('b', 3),
('b', 4)
), expensive as (
select
b,
array_agg(a) as arr_a,
ARRAY_TO_STRING(arr_a, '_') as c
from tbl
group by b
)
select
x.a,
x.b,
y.c
from tbl as x
left join expensive as y
on x.b = y.b;
expensive cte может быть выстроен в линию:
), expensive as (
select
b,
ARRAY_TO_STRING(array_agg(a), '_') as c
from tbl
group by b
)
Я бы предположил, что это лучший способ. Он делает две вещи: дорогостоящая агрегатная функция переносит/обрабатывает только необходимые ей данные, а затем используется для поиска в «полной таблице». Это действительно быстрее, чем пытаться перебрать все строки в чистом виде.
Что касается использования формы оконной функции, она будет вызывать функцию F для каждой строки, поскольку для каждой строки создается МАССИВ, поэтому, учитывая, что функция звучит не запомненной (и, возможно, не может быть такой), тогда это «кэшированная версия». это СОЕДИНЕНИЕ. Таким образом, учитывая, что FOR EVERY ROW является JOIN, известно ли JOIN, что вы «кэшируете». УБЕДИТЕСЬ, что вы делаете это, используйте дружественный тип EQUI-JOIN, который в основном означает НОМЕР.
Пожалуйста, отредактируйте свой вопрос, добавив минимально воспроизводимый пример с фиктивными исходными данными и желаемыми результатами для этих фиктивных данных. Почему я должен предоставлять минимальный воспроизводимый пример для очень простого SQL-запроса?