listagg: удалить соседние дубликаты

У меня есть данные с временными метками, и я хочу создать список из одного столбца, в котором соседние дубликаты (но не все дубликаты) свернуты в один.

Например, при наличии следующих данных:

'2001-01-01 00:00:01' 'a'
'2001-01-01 00:00:02' 'a'
'2001-01-01 00:00:03' 'b'
'2001-01-01 00:00:04' 'b'
'2001-01-01 00:00:05' 'b'
'2001-01-01 00:00:06' 'a'
'2001-01-01 00:00:07' 'a'
'2001-01-01 00:00:08' 'c'
'2001-01-01 00:00:09' 'a'

— Я бы хотел, чтобы результат был 'a','b','a','c','a'.

Я использую Snowflake, в котором есть listagg(distinct foo), listagg(distinct foo) within group(order by bar) и даже listagg(distinct foo) within group(order by bar) over(partition by baz), но я не вижу способа сделать то, что мне нужно (и Google не помогает). Я бы очень, очень хотел избежать join.

Если вы знаете решение на другом диалекте, в котором есть listagg или group_concat, опубликуйте его, и я попытаюсь перевести его на Snowflake для собственного использования. Большое спасибо.

Вещи, которые не работают:

  • Я попробовал trim(regexp_replace('~' || listagg(foo, '~') || '~', '~([^~]+~)\\1', '~\\1'), '~'), но Snowflake не допускает \1 в шаблоне соответствия: я получаю сообщение об ошибке Invalid regular expression: '~([^~]+~)\1', invalid escape sequence: \1.
  • Я попробовал listagg(iff(lag(foo) ignore nulls over(partition by baz order by bar)=foo, null, foo), ',') within group(order by bar) over(partition by baz), но получил ошибку Window function [LAG(...)] may not be nested inside another window function.

duplicates snowflake-cloud-data-platform listagg group-concat
person Tyro    schedule 19.06.2018    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Я не думаю, что Snowflake поддерживает обратные ссылки в шаблонах регулярных выражений, к сожалению.

Возможные решения:

  • устранить дубликаты во входном потоке с помощью LAG, например.

    with sub as (select foo, bar, lag(bar) over (order by foo) barlag)
select listagg(foo) within group order by (bar) from foo 
where barlag is null or barlag <> lag;

  • используйте LISTAGG, но напишите UDF JavaScript, который разбивает результат LISTAGG и устраняет там дубликаты

  • напишите JavaScript UDTF (табличную функцию), которая выполняет LISTAGG с устранением дубликатов
person Marcin Zukowski    schedule 19.06.2018