Как разделить таблицу на основе шаблона (в данном случае возрастающей подпоследовательности), который находится в столбце в presto sql

Я сталкиваюсь со следующей проблемой. У меня есть таблица T, в которой есть столбец с именем offset, который всегда будет набором возрастающих подпоследовательностей целых чисел, начинающихся с нуля.

Table T:

**row_num, offset ,col_1, col_2, col_3 ....**
     1       0
     2      10
     3      22
     4       32
     5       0
     6       0
     7       12
     8       32 
     9       44

Я хочу разделить таблицу на основе столбца смещения, чтобы строки, принадлежащие каждой строго возрастающей подпоследовательности, помещались в один раздел. В приведенном выше примере строки [1,2,3,4], [0], [0,12,32,44] — это разделы, которые я ищу. Как мне поступить или это вообще возможно в sql?

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

sql amazon-athena presto

08.02.2023 06:18

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете использовать функцию текущей суммы, которая увеличивается на 1 всякий раз, когда обнаруживается нулевое значение смещения по сравнению с увеличением row_num:

select row_num, offset,
  sum(case when offset = 0 then 1 else 0 end) over (order by row_num) prt
from table_name

Вывод этого запроса:

row_num   offset    prt
1         0         1
2         10        1
3         22        1
4         32        1
5         0         2
6         0         3
7         12        3
8         32        3
9         44        3

08.02.2023 06:35

Вы можете использовать MATCH_RECOGNIZE для определения строк, которые удовлетворяют шаблону. В вашем случае вы можете определить шаблон увеличения значений столбца offset:

WITH t(row_num, offset) AS (
    VALUES
             (1, 0),
             (2, 10),
             (3, 22),
             (4, 32),
             (5, 0),
             (6, 0),
             (7, 12),
             (8, 32),
             (9, 44)
)
SELECT row_num, offset, partition FROM t
    MATCH_RECOGNIZE (
        ORDER BY row_num
        MEASURES MATCH_NUMBER() AS partition
        ALL ROWS PER MATCH
        AFTER MATCH SKIP PAST LAST ROW
        PATTERN (START UP*)
        DEFINE UP AS offset > PREV(offset)
    )

Предложение MATCH_RECOGNIZE в приведенном выше запросе говорит следующее:

Найдите последовательность строк (упорядоченную по row_num), которая соответствует шаблону START UP*. Элемент START должен появиться один раз, а элемент UP должен появиться после него ноль или более раз. START и UP — произвольные имена для представления строк в последовательности. START определяется неявно и соответствует любой строке, а UP определяется как строка, в которой столбец offset имеет значение больше, чем столбец offset предыдущей строки в последовательности (DEFINE UP AS offset > PREV(offset))
Вычислите выходной столбец с именем partition, значение которого представляет собой синтетическое число, связанное с каждым совпадением. (MEASURES MATCH_NUMBER() AS partition)
Выведите все входные строки с аннотацией столбца partition. (ALL ROWS PER MATCH)

Запрос выдает:

 row_num | offset | partition
---------+--------+-----------
       1 |      0 |         1
       2 |     10 |         1
       3 |     22 |         1
       4 |     32 |         1
       5 |      0 |         2
       6 |      0 |         3
       7 |     12 |         3
       8 |     32 |         3
       9 |     44 |         3
(9 rows)

Для получения более подробной информации см.:

Обратите внимание, что эта функция доступна только в Trino и Athena версии 3, основанной на Trino.

08.02.2023 07:16