Возвращает строки, где значение столбца является окончательным расширенным набором предыдущих строк
Я использую базу данных PostgreSQL, и у меня есть вариант использования, когда мне нужно вернуть все строки из таблицы, значение столбца которой является надмножеством того же значения столбца, что и другие предыдущие строки.
Пример. В таблице есть следующие строки:
-------------------------------------------
id | name | datetime
-------------------------------------------
1 | N | 2024-08-17 04:42:57
2 | NEW | 2024-08-17 04:42:58
3 | NEW YOR | 2024-08-17 04:42:58
4 | NEW YORK | 2024-08-17 04:42:59
5 | W | 2024-08-17 04:56:07
6 | WA | 2024-08-17 04:56:07
7 | WASHI | 2024-08-17 04:56:07
8 | WASHINGT | 2024-08-17 04:56:08
9 | WASHINGTON | 2024-08-17 04:56:08
10 | NEW Y | 2024-08-17 05:12:18
11 | NEW YORK | 2024-08-17 05:12:18
Мне нужен SQL, который может возвращать следующий результат в таблице выше:
-------------------------------------------
id | name | datetime
-------------------------------------------
4 | NEW YORK | 2024-08-17 04:42:59
9 | WASHINGTON | 2024-08-17 04:56:08
11 | NEW YORK | 2024-08-17 05:12:18
Ответы 2
Ваше требование может быть переведено на:
«Возьмите строки, в которых имя в следующей строке не начинается с имени текущей строки».
Так:
SELECT id, name, datetime
FROM (
SELECT *, lead(name, 1, '') OVER (ORDER BY id) AS next_name
FROM tbl
ORDER BY id
) sub
WHERE NOT (next_name ^@ name);
Предполагается, что строки действительно отсортированы по id, как вам кажется, и соответствующие столбцы определены NOT NULL.
Два шага:
В подзапросе
subвозьмите имя следующей строки в соответствии с порядком сортировки с помощью оконной функции lead(), назовем ееnext_name.
Чтобы закрыть угловой регистр последней строки, по умолчанию используется пустая строка (''), когда следующей строки нет.Во внешнем
SELECTотфильтруйте строки, в которыхnext_nameне начинается сname. Должно быть именно то, что вы просите.
^@ — оператор «начинается с», добавленный в Postgres 11. См.:
(Замените на LIKE или ~ в старых версиях.)
Другие возможные (более подробные) решения:
- рекурсивные CTE
- процедурное решение в функции PL/pgSQL.
Если таблица не отсортирована по name, этот запрос может это сделать, поскольку он не зависит от окна. Он довольно читабелен, хотя и не очень быстр, так как требует два подзапроса на строку:
SELECT * FROM t
WHERE EXISTS (
SELECT * FROM t t2
WHERE t.name ^@ t2.name AND t.name != t2.name
)
AND NOT EXISTS (
SELECT * FROM t t3
WHERE t3.name ^@ t.name AND t3.name != t.name
);
Выделите все строки, в которых где-то есть меньшее имя (начинающееся с той же строки) и нет большего имени в другом месте (начинающееся с той же строки).
Спасибо, это сработало для меня. Принимая это как ответ.