Похоже, что функция «ON OVERFLOW TRUNCATE» недоступна в Snowflake, который идет с LISTAGG в Oracle. Есть ли альтернативная функция или обходной путь?
Есть ли у снежинки функция LISTAGG (имя_столбца [, разделитель] ON OVERFLOW TRUNCATE)?
Ответы (2)
select listagg(str, ', ') within group (order by id) as listagg
from (
select id
,str
,sum(length(str))over(order by id) as s_str
from (
select * from values(1,'abcdef'),(2,'dfgsdfh'),(3,'jgeg'),(4,'ergegr') s(id,str)
)
qualify s_str <= 20
);
дает значения с итогом (до разделителей)
,sum(length(str)+2)over(order by id) as s_str
позволяет вам учитывать размер разделителя ','
или если вам нужен усекающий суффикс, например '...'
, это сделает это ..
select listagg(str, ', ') within group (order by id) as listagg
from (
select id
,s_str <= 20 as underlimit
,iff(underlimit, str, '...') as str
from (
select id
,str
,sum(length(str)+2)over(order by id) as s_str
from (
select * from values(1,'abcdef'),(2,'dfgsdfh'),(3,'jgeg'),(4,'ergegr') s(id,str)
)
)
qualify underlimit or row_number() over (partition by underlimit order by id) = 1
);
давая
LISTAGG
'abcdef, dfgsdfh, ...'
переписать как CTE, что ничего не меняет ...
with data as (
select id
,str
,sum(length(str)+2)over(order by id) as s_str
from values
(1,'abcdef'),
(2,'dfgsdfh'),
(3,'jgeg'),
(4,'ergegr')
s(id,str)
), check_length_and_limit as (
select id
,s_str <= 20 as underlimit
,iff(underlimit, str, '...') as str
from data
qualify underlimit or row_number() over (partition by underlimit order by id) = 1
)
select listagg(str, ', ') within group (order by id) as listagg
from check_length_and_limit;
person
Simeon Pilgrim
schedule
11.04.2020
Хорошая идея использовать qualify! Два комментария: вложенный код с 4-уровневыми встроенными элементами затрудняет чтение, предпочтительнее использовать версию с несколькими CTE. Во-вторых:
select id ,s_str <= 20 as underlimit ,iff(underlimit, str, '...') as str
позволяет ли Snowflake повторно использовать псевдоним на том же уровне (здесь нижний предел), известный как боковой столбец ссылка на псевдоним?
- person Lukasz Szozda; 11.04.2020
@LukaszSzozda готово ... обычно я пишу CTE, и они занимают кучу больше места и ничего не меняют, если вы знаете, как их читать ... но обязательно, если вам нравится видеть это в форме CTE. И да, как только псевдоним определен, вы можете использовать его в Snowflake ... но оконные функции и агрегации работают хорошо, но иногда вам нужно выбрать какой-то из скобок, чтобы охватить материал, поэтому я использовал четыре слоя, как вы заметили ... Я начал с всего один уровень но это не понравилось ...
- person Simeon Pilgrim; 11.04.2020
Вот это да!! Оно работало завораживающе. Хотелось бы, чтобы у снежинки были такие функции, которые было бы намного проще. Спасибо за помощь.
- person npool; 13.04.2020
Нет, Snowflake не предоставляет такой возможности. Два комментария, которые могут помочь объяснить, почему это так:
- Нет проблем с производительностью или хранением при определении строкового поля (текст, varchar и т. Д.) С ограничением по длине. Итак, вы можете определить свой столбец очень большим, чтобы справиться с этим.
- С этим можно легко справиться, просто используя функцию LEFT () вокруг вашей функции LISTAGG () для ограничения в зависимости от размера вашего определения поля, если вы ограничили его или ваши данные выходят за пределы строкового поля Snowflake.
person
Mike Walton
schedule
10.04.2020
Я не думаю, что можно использовать функцию LEFT для ограничения, если агрегированные данные выходят за пределы строкового поля Snowflake: выберите left (listagg (x), 100) from (выберите '1234567890123456' x from table (generator ( rowcount = ›1024 * 1024 + 1)));
- person Gokhan Atil; 11.04.2020
Может быть, вычислить текущую сумму длин строк и УКАЗАТЬ, что она меньше максимальной, перед LISTAGG ()
- person waldente; 11.04.2020