Урок курса
Дедупликация и выбор последней записи
Продвинутый SQL: сложные запросы и задачи для собеседованийДве одинаковые записи: что именно повторилось
Создадим таблицу событий заказов и добавим четыре записи:
CREATE TABLE order_events(
event_id INTEGER PRIMARY KEY,
order_id INTEGER,
status TEXT,
recorded_at TEXT
);
INSERT INTO order_events VALUES
(1, 10, 'new', '2024-01-01 09:00'),
(2, 10, 'paid', '2024-01-01 10:00'),
(3, 10, 'paid', '2024-01-01 10:00'),
(4, 20, 'new', '2024-01-02 08:00');
Посмотрим все строки:
SELECT * FROM order_events ORDER BY event_id;
| event_id | order_id | status | recorded_at |
|---|---|---|---|
| 1 | 10 | new | 2024-01-01 09:00 |
| 2 | 10 | paid | 2024-01-01 10:00 |
| 3 | 10 | paid | 2024-01-01 10:00 |
| 4 | 20 | new | 2024-01-02 08:00 |
Строки 2 и 3 имеют одинаковые значения в столбцах order_id, status и recorded_at. При этом в таблице у них разные технические ключи event_id (2 и 3), которые были заданы при вставке. Различие локальных идентификаторов само по себе не доказывает, что произошли два независимых события. Точно так же совпадение бизнес-полей не доказывает, что строка 3 — это сбойная повторная доставка строки 2.
Надёжно связать строки как повтор одного и того же действия можно только по внешнему подтверждённому идентификатору источника (например, номеру транзакции или idempotency key) либо по строгому составному бизнес-ключу. Без такого ключа совпадение атрибутов не позволяет сделать вывод ни о том, что это повтор одного события, ни о том, что это два независимых действия.
Кроме того, для заказа 10 строка 1 (new) и строки 2–3 (paid) — это не дубликаты, а история изменения статуса во времени. Повторная доставка одного сообщения и смена состояния требуют разной логики: в первом случае отсекают лишнюю копию, а во втором — сохраняют историю переходов либо выбирают текущую версию объекта.
Какие строки уберёт DISTINCT
Попытка решить задачу получения актуального состояния через DISTINCT приводит к неожиданным результатам в зависимости от списка столбцов.
Если запросить все столбцы таблицы:
SELECT DISTINCT event_id, order_id, status, recorded_at
FROM order_events
ORDER BY event_id;
| event_id | order_id | status | recorded_at |
|---|---|---|---|
| 1 | 10 | new | 2024-01-01 09:00 |
| 2 | 10 | paid | 2024-01-01 10:00 |
| 3 | 10 | paid | 2024-01-01 10:00 |
| 4 | 20 | new | 2024-01-02 08:00 |
В выводе остались все 4 строки. DISTINCT сравнивает строки целиком по всем указанным столбцам. Так как event_id везде уникален, ни одна строка не совпала с другой.
Если убрать технический ключ event_id из выборки:
SELECT DISTINCT order_id, status, recorded_at
FROM order_events
ORDER BY order_id, recorded_at, status;
| order_id | status | recorded_at |
|---|---|---|
| 10 | new | 2024-01-01 09:00 |
| 10 | paid | 2024-01-01 10:00 |
| 20 | new | 2024-01-02 08:00 |
Результат сократился до 3 строк, так как значения (10, 'paid', '2024-01-01 10:00') полностью совпали. Но для заказа 10 по-прежнему возвращаются две строки: со статусом new и со статусом paid. Оставить только последнее состояние DISTINCT не может.
Если же оставить только идентификатор заказа:
SELECT DISTINCT order_id
FROM order_events
ORDER BY order_id;
| order_id |
|---|
| 10 |
| 20 |
Строк стало две, но вместе со строками исчезли статус и время. DISTINCT отбрасывает идентичные проекции выбранных столбцов, но не позволяет задать правило: какую именно строку из группы оставить.
Как выбрать последнюю запись для каждого заказа
Чтобы выбрать ровно одну строку на каждый order_id по понятному правилу, используют оконную функцию ROW_NUMBER(). Зададим правило: берем самое позднее событие по recorded_at, а при равном времени — строку с наибольшим event_id.
Сначала пронумеруем строки внутри каждого заказа:
SELECT event_id, order_id, status, recorded_at,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY recorded_at DESC, event_id DESC
) AS rn
FROM order_events
ORDER BY order_id, rn;
| event_id | order_id | status | recorded_at | rn |
|---|---|---|---|---|
| 3 | 10 | paid | 2024-01-01 10:00 | 1 |
| 2 | 10 | paid | 2024-01-01 10:00 | 2 |
| 1 | 10 | new | 2024-01-01 09:00 | 3 |
| 4 | 20 | new | 2024-01-02 08:00 | 1 |
PARTITION BY order_id делит строки на независимые группы по заказам. Сортировка recorded_at DESC ставит строки с поздним временем в начало списка. Если времена совпадают, вступает в силу tiebreak — второй ключ event_id DESC. Строка 3 получила rn = 1, а строка 2 — rn = 2. Значение event_id здесь используется как явная договоренность для однозначности сортировки: само по себе большее число не доказывает, что физическое событие произошло позже.
Теперь необходимо отфильтровать строки, оставив только rn = 1. Попытка написать фильтр прямо в блоке WHERE того же запроса:
SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY recorded_at DESC, event_id DESC) rn
FROM order_events
WHERE rn = 1;
завершится ошибкой: misuse of aliased window function rn. Логический порядок выполнения SQL применяет WHERE до того, как вычисляются оконные функции. На этапе фильтрации столбца rn еще не существует.
Поэтому расчет номера выносят в обобщенное табличное выражение (CTE), а фильтрацию выполняют во внешнем запросе:
WITH ranked AS (
SELECT event_id, order_id, status, recorded_at,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY recorded_at DESC, event_id DESC
) AS rn
FROM order_events
)
SELECT event_id, order_id, status, recorded_at
FROM ranked
WHERE rn = 1
ORDER BY order_id;
| event_id | order_id | status | recorded_at |
|---|---|---|---|
| 3 | 10 | paid | 2024-01-01 10:00 |
| 4 | 20 | new | 2024-01-02 08:00 |
В результате возвращается ровно по одной строке на каждый order_id со всеми сопутствующими полями актуального события.
Задача 1
Таблица order_events(event_id INTEGER PRIMARY KEY, order_id INTEGER, status TEXT, recorded_at TEXT) содержит строки: (1,10,'new','2024-01-01 09:00'), (2,10,'paid','2024-01-01 10:00'), (3,20,'new','2024-01-02 08:00'). Выполняется запрос: SELECT DISTINCT order_id, status FROM order_events. Сколько строк вернёт запрос и почему он не решает задачу «одна строка на order_id с последним статусом»?
Задача 2
Требуется выбрать одну последнюю запись на каждый order_id. Сопоставьте ситуации с описаниями, выбрав полный набор соответствий.
A) У заказа 10 две записи с разными event_id, но одинаковыми recorded_at и status.
B) У заказа 20 две записи с разными recorded_at: 08:00 и 15:00 одной даты.
C) У заказа 30 единственная запись.
1 — последняя запись определяется временем без дополнительного правила равенства; 2 — время не различает записи, нужен tie-break по уникальному event_id; 3 — ROW_NUMBER даёт 1 единственной записи. Совпадение обычных атрибутов в A само по себе не доказывает повтор одного события.
Задача 3
Аналитик написал запрос для получения последнего события на каждый order_id: SELECT event_id, order_id, status, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY recorded_at DESC, event_id DESC) AS rn FROM order_events WHERE rn = 1; В SQLite этот запрос завершается ошибкой. Какова причина?
Задача 4
Таблица user_actions(action_id INTEGER PRIMARY KEY, user_id INTEGER, action_type TEXT, action_time TEXT) содержит строки:
(1,5,'click','2024-01-01 10:00'),
(2,5,'click','2024-01-01 10:00'),
(3,5,'click','2024-01-01 11:00'),
(4,5,'click','2024-01-01 11:00').
Вариант A: SELECT DISTINCT user_id, action_type, action_time FROM user_actions
Вариант B: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, action_type ORDER BY action_time DESC, action_id DESC) AS rn FROM user_actions) SELECT user_id, action_type, action_time FROM ranked WHERE rn = 1
По контракту именно этого источника комбинация (user_id, action_type, action_time) идентифицирует одно событие, а разные action_id при той же комбинации — повторные загрузки. Это специальное условие задачи: в другом источнике совпадение этих полей само по себе не доказывает повтор.
Выберите полный ответ: сколько строк вернут A и B, какое время останется в B и какую задачу решает каждый вариант?
Задача 5
Последний статус каждого заказа
Что уже дано
Проверяющая система создала и заполнила SQLite-таблицу order_events(event_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, status TEXT, recorded_at TEXT NOT NULL). event_id — положительный уникальный INTEGER, order_id — положительный INTEGER. status — 'new', 'paid', 'shipped', 'cancelled' или NULL. Неизвестный статус последней записи сохраняется как NULL. recorded_at — время UTC в едином формате YYYY-MM-DD HH:MM (точность до минуты), без NULL; строки можно сравнивать лексикографически. Во входе не более 1000 записей. Идентификаторы не обязаны возрастать вместе со временем: больший ID используется только при равенстве времени. Разные ID обозначают отдельные записи; совпадение других атрибутов не доказывает повторную загрузку одного события.
Что нужно сделать
Верните одну строку на каждый order_id, встречающийся в истории: целую запись с наибольшим recorded_at, а при равном времени — с наибольшим event_id. Значения всех столбцов должны принадлежать выбранной записи. Отбор по статусу или пользователю не нужен.
Ввод и вывод
Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно четыре столбца в таком порядке и с такими именами: event_id (INTEGER), order_id (INTEGER), status (TEXT или NULL), recorded_at (TEXT). Сортировка по order_id по возрастанию. На пустом входе результат пуст. В тестах NULL выглядит как пустое поле, столбцы разделены |. Допускается любое эквивалентное решение; ROW_NUMBER в CTE — изученный подход, а не ограничение реализации.
Без регистрации. Гостевая проверка не сохраняет прогресс. Для сохранения перейдите к уроку.
Отправьте решение, чтобы увидеть результаты тестов.
Задача 6
Неоднозначные последние регистрации
Что уже дано
Проверяющая система создала и заполнила SQLite-таблицу device_registrations(reg_id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL, user_id INTEGER, registered_at TEXT NOT NULL). reg_id — положительный уникальный INTEGER, device_id — положительный INTEGER, user_id — положительный INTEGER или NULL (пользователь неизвестен). Один пользователь может иметь несколько устройств. registered_at — время UTC в едином формате YYYY-MM-DD HH:MM, без NULL; строки можно сравнивать лексикографически. Во входе не более 1000 записей. Идентификаторы не обязаны возрастать вместе со временем. Разные reg_id обозначают отдельные регистрации, даже при совпадении остальных полей.
Аналитик собирает кандидат на отчёт о последней регистрации: для каждого устройства оставляет все строки с максимальным registered_at этого устройства. Дополнительного правила при равном времени в этом кандидате нет, поэтому он может содержать несколько строк на device_id. Это определение кандидата; отдельная таблица с ним не создана.
Что нужно сделать
По истории регистраций постройте диагностику кандидата: верните только устройства, у которых в нём больше одной строки. Для каждого такого устройства укажите число строк кандидата, наибольший reg_id среди этих строк (регистрацию, которую следовало бы оставить при разрешении равного времени) и их общее registered_at. Более ранние регистрации не входят в число кандидатов, даже если у них совпадает время или их reg_id больше. user_id не влияет на отбор и подсчёт; регистрация с NULL учитывается как строка.
Ввод и вывод
Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно четыре столбца в таком порядке и с такими именами: device_id (INTEGER), candidate_count (INTEGER), keep_reg_id (INTEGER), registered_at (TEXT). Сортировка по device_id по возрастанию. На пустом входе и при единственной последней регистрации у каждого устройства результат пуст. Столбцы результата разделены |. Допускается любое эквивалентное решение.
Без регистрации. Гостевая проверка не сохраняет прогресс. Для сохранения перейдите к уроку.
Отправьте решение, чтобы увидеть результаты тестов.
Задача 7
Документы с текущим окончательным решением
В системе проверки документов накапливается единый журнал событий: каждое событие принадлежит одному документу, имеет собственный уникальный идентификатор, время регистрации и статус. Жизнь документа в журнале нелинейна — после принятия он может вернуться на доработку, после отклонения — быть открыт заново. Поэтому интересно не то, были ли когда-либо у документа окончательные статусы, а то, каково его положение прямо сейчас.
Текущим событием документа считается последнее по времени регистрации, а если несколько событий зарегистрированы в один момент — то с наибольшим идентификатором. Именно этот момент и это событие определяют актуальное состояние документа. Если текущее событие имеет статус approved или rejected, документ попадает в отчёт; если же последним зафиксировано pending, in_review или reopened — документ из отчёта исключается, даже если прежде по нему было вынесено окончательное решение.
Отчёт должен содержать по одной строке на каждый такой документ. Для каждого из них нужно показать идентификатор документа, идентификатор выбранного события, время его регистрации, исходный статус и понятный итог: для approved — «документ принят», для rejected — «документ отклонён».
Что уже дано
В SQLite подготовлена одна таблица document_events(event_id INTEGER PRIMARY KEY, document_id INTEGER NOT NULL, recorded_at TEXT NOT NULL, status TEXT NOT NULL). event_id — глобально уникальный положительный INTEGER; document_id — положительный INTEGER. Идентификаторы лежат в диапазоне от 1 до 2 000 000 000. recorded_at — время регистрации UTC в фиксированном формате YYYY-MM-DDTHH:MM:SSZ, без NULL. Все времена имеют одинаковую точность до секунды и сравниваются как TEXT. status принимает ровно одно из пяти значений: 'approved', 'rejected', 'pending', 'in_review', 'reopened'. NULL во входных столбцах нет. Журнал содержит от 0 до 1000 записей. Порядок строк и величина event_id не гарантируют порядок времени. Разные event_id обозначают самостоятельные события, даже при совпадении остальных полей.
Что нужно сделать
Сформируйте описанный отчёт о текущем окончательном решении документа. Сохраните все поля выбранного события без подмены; ключ выходной строки — document_id.
Ввод и вывод
Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно пять столбцов в таком порядке и с такими именами: document_id (INTEGER), event_id (INTEGER), recorded_at (TEXT), status (TEXT), decision_label (TEXT). decision_label точно равен 'документ принят' для approved и 'документ отклонён' для rejected. NULL в выходных столбцах нет. Отсортируйте строки по document_id по возрастанию. Пустой журнал или отсутствие документов с текущим окончательным решением дают пустой результат. В тестах столбцы разделены |. Допускается любое эквивалентное решение; конкретные SQL-конструкции не ограничены.
Без регистрации. Гостевая проверка не сохраняет прогресс. Для сохранения перейдите к уроку.
Отправьте решение, чтобы увидеть результаты тестов.
Продолжить с сохранением прогресса
Перейдите к уроку в учебном кабинете.
