Найти переходы от точных значений SQL

Есть набор данных. Я хотел бы сделать первые переходы из класса не в (Другое, Другие) в класс в (Другое, Другие)

| row_id | class   |
| ------ | ------- |
| 1      | Math    |
| 2      | Math    |
| 3      | Math    |
| 4      | Math    |
| 5      | Math    |
| 6      | Math    |
| 7      | Other   |
| 8      | Other   |
| 9      | Other   |
| 10     | Biology |
| 11     | Biology |
| 12     | Other   |
| 13     | Other   |
| 14     | Biology |
| 15     | Biology |
| 16     | Others  |
| 17     | Others  |
| 18     | Others  |
| 19     | Physics |
| 20     | Others  |

Итак, результат будет:

| row_id | class   | prev_row_id | prev_class |
| ------ | ------- | ----------- | ---------- |
| 6      | Math    | 7           | Other      |
| 11     | Biology | 12          | Other      |
| 15     | Biology | 16          | Other      |
| 19     | Physics | 20          | Others     |

Я нашел, как определить ПОСЛЕДНИЙ переход, но не в истории. Я использую ПТРЕСТО.

sql window-functions presto

31.03.2023 14:42

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

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

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

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

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

Ответы 3

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

Здесь мы можем использовать аналитическую функцию LEAD():

WITH cte AS (
    SELECT *, LEAD(class) OVER (ORDER BY row_id) AS lead_class,
              LEAD(row_id) OVER (ORDER BY row_id) AS lead_row_id
    FROM yourTable
)

SELECT
    row_id,
    class,
    lead_row_id AS next_row_id,
    lead_class AS next_class
FROM cte
WHERE class NOT IN ('Other', 'Others') AND
      lead_class IN ('Other', 'Others')
ORDER BY row_id;

Просто из любопытства, можно ли получить лид id? (Для случаев, когда id не является непрерывным)

— 31.03.2023 14:54

Да, и я отредактировал свой ответ, чтобы показать, как это сделать.

— 31.03.2023 14:57

31.03.2023 14:47

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

select t.row_id, t.class, th.row_id prev_row_id , th.class prev_class
  from my_table t
  join my_table th 
    on (th.row_id = t.row_id + 1 and th.class in ('Other', 'Others'))
 where t.class not in ('Other', 'Others')

*Внимание: это будет работать, только если id непрерывен

Row_id обычно не +1. Это был просто пример

— 31.03.2023 15:16

@Александра, тогда используй ответ Тима, он более общий.

— 31.03.2023 15:18

31.03.2023 14:49

Вы можете использовать оконные функции. Например lag:

-- sample data
with dataset(row_id, class) as(
    values (1,  'Math'),
        (2,  'Math'),
        (6,  'Math'),
        (7,  'Other'),
        (9,  'Other'),
        (10, 'Biology'),
        (11, 'Biology'),
        (12, 'Other'),
        (13, 'Other'),
        (14, 'Biology'),
        (15, 'Biology'),
        (16, 'Others'),
        (17, 'Others'),
        (18, 'Others'),
        (19, 'Physics'),
        (20, 'Others')
),

-- query parts
with_prev as(
 SELECT *,
    lag(row_id) OVER w AS prev_row_id,
    lag(class) OVER w AS prev_class
 FROM dataset
 WINDOW w AS (ORDER BY row_id) -- Trino allows sharing window
)

select *
from with_prev
where prev_class not like 'Other%' and class like 'Other%'; -- or in ('Other', 'Others')

Выход:

row_id сорт prev_row_id предыдущий_класс 7 Другой 6 Математика 12 Другой 11 Биология 16 Другие 15 Биология 20 Другие 19 Физика

Идентичен моему ответу, за исключением того, что вы используете LAG() вместо LEAD()

— 31.03.2023 15:39

@TimBiegeleisen также использую общее окно =))

— 31.03.2023 15:40

31.03.2023 15:33