Как получить самую последнюю запись для каждого пользователя с помощью SQL? Пошаговое руководство

В последних сериях…

Всем привет! Добро пожаловать в 12-й выпуск серии руководств по SQL и SQL Server Studio. Моя цель проста: сделать вас знакомыми и удобными с инструментом и языком. — Почему это вообще имеет значение? Я вижу, ты спрашиваешь. Оказывается, любопытство и второстепенные проекты часто являются ключевыми факторами при выборе новых проектов или даже при приеме на новую работу. Тот факт, что вы уже использовали важный инструмент, такой как SQL Server Studio, и написали несколько SQL-запросов, может и даст вам хороший старт.

И не забудьте вернуться 😉.

Что ожидать?

Данные поступают во многих формах и видах. Может случиться так, что набор данных, скажем, таблица журнала, будет содержать несколько записей для одного пользователя или машины в течение некоторого времени. Как мы могли бы разделить эти данные, чтобы получить только последнюю временную метку для данного пользователя, машины или X?

Здесь на помощь приходят оконные функции ранжирования. Их синтаксис немного сложнее, чем обычные операции SELECT или CRUD, которые мы видели в других эпизодах (см. выше). Честно говоря, я никогда не мог понять этот синтаксис, и мне всегда нужно обращаться к моему святому текстовому файлу 😉.

Знание того, как использовать его, решит многие головные боли и позволит ранжировать записи и отфильтровывать то, что не требуется. Без дальнейших церемоний…

Введение

Мы предполагаем, что у вас все настроено для среды SQL Server Studio. Если вам нужна помощь, пожалуйста, проверьте Эпизод 1, упомянутый выше.

Давайте сначала создадим новую таблицу «Журналы»:

CREATE TABLE Logs
(Timestamp datetime,
Username nvarchar(100))

Теперь давайте заполним его некоторыми данными:

INSERT INTO [HR_data].[dbo].[Logs]
VALUES
(GETUTCDATE(), 'Max'),
(GETUTCDATE()-1, 'Max'),
(GETUTCDATE()-2, 'Beth'),
(GETUTCDATE()-3, 'Beth')

Давайте добавим к нему еще немного шума, продублировав то, что мы только что вставили. Это будет вставлять те же строки, за исключением того, что отметка времени будет отличаться на несколько секунд, в зависимости от того, когда вы выполнили запрос.

/* This is to add a bit more records/noise in our dataset */
INSERT INTO [HR_data].[dbo].[Logs]
VALUES
(GETUTCDATE(), 'Max'),
(GETUTCDATE()-1, 'Max'),
(GETUTCDATE()-2, 'Beth'),
(GETUTCDATE()-3, 'Beth')

Запустите быстрый запрос SELECT, чтобы проверить набор данных:

SELECT * FROM [HR_data].[dbo].[Logs]

Это должно вернуть 8 записей.

Функция окна ранжирования

Как мы можем извлечь самую последнюю метку времени для любого отдельного пользователя в нашей таблице? Функция окна рейтинга приходит нам на помощь. Это собственная функция T-SQL.

SELECT Timestamp, Username
ROW_NUMBER() OVER(ORDER BY Timestamp DESC) AS Row#
FROM [HR_data].[dbo].[Logs]
  • Начнем с выбора Timestamp и Username. Все идет нормально.
  • Затем мы добавляем функцию Row_Number(), которая будет определять номер строки для каждой записи.
  • OVER() помогает определить нашу цель. В этом случае мы посмотрим на метку времени. В функции OVER() мы уточняем ORDER BY и определяем, будет ли он по возрастанию (ASC) или по убыванию (DESCENDING).
  • Наконец, мы устанавливаем псевдоним, здесь называя этот столбец Row#

Вывод:

Ну, по крайней мере, они ранжированы. Но что нам нужно, так это найти последнюю метку времени для любого данного пользователя. Нам нужно добавить еще один элемент в наш ROW_NUMBER(), PARTITION BY. Использование PARTITION BY поможет нам разделить набор данных, на который ссылается предложение FROM. Как мы видели выше, когда PARTITION BY не используется, все строки результата обрабатываются как одна группа.

Вот как мы можем написать наш запрос:

SELECT Timestamp, UserName,
ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY Timestamp DESC) AS Row#
FROM [HR_data].[dbo].[Logs]
  • PARTITION BY добавляется в предложение OVER.
  • PARTITION BY требует, чтобы мы определили хотя бы один столбец, в нашем случае мы разделяем по имени пользователя.

Вывод:

У Макса и Бет по 4 записи. Их записи отсортированы по отметке времени.

  • PARTITION BY предназначен для имя пользователя. Было 2 разных имени пользователя, ROW_NUMBER() ранжировал эти записи на основе разделов.
  • Вот почему мы получили записи Макса и Бет с 1 по 4, в то время как раньше они ранжировались с 1 по 8.

Заключительный этап

Наша первоначальная цель состояла в том, чтобы получить самую последнюю метку времени для каждого пользователя. Как видно из приведенного выше вывода, мы знаем, какая запись является самой последней для каждого пользователя. Просто посмотрите на Row# = 1. Как мы можем выделить эти строки? Нам поможет вложенный запрос.

Сначала мы повторно используем то, что сработало хорошо:

SELECT Timestamp, UserName,
ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY Timestamp DESC) AS Row#
FROM [HR_data].[dbo].[Logs]

Затем мы заключаем его с помощью круглых скобок в другой оператор SELECT и даем псевдоним «x» нашему вложенному запросу:

SELECT * FROM
(
SELECT Timestamp, UserName,
ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY Timestamp DESC) AS Row#
FROM [HR_data].[dbo].[Logs]
) x

Последний шаг — добавить условие WHERE, чтобы мы смотрели только на Row# = 1:

SELECT * FROM
(
SELECT Timestamp, UserName,
ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY Timestamp DESC) AS Row#
FROM [HR_data].[dbo].[Logs]
) x
where x.Row# = 1

Вывод:

Теперь у нас есть две самые последние временные метки для двух разных пользователей. Миссия выполнена 🙂

Заключительные слова

Теперь мы осваиваем новый инструмент, функции окна ранжирования. Это может решить многие проблемы, когда, например, мы сталкиваемся с идентичными строками, которые отличаются только временными метками. ROW_NUMBER() в сочетании с PARTITION BY позволит нам разделить наш набор данных на основе одного или нескольких атрибутов. В нашем случае имя пользователя. Затем мы можем ранжировать строки на основе метки времени.

Наконец, вложенный запрос позволяет выделить данный ранг. В нашем случае мы сосредоточились на ранге/строке №1, самой последней метке времени.

Я надеюсь, что вы нашли эту статью полезной, дайте мне знать, что вы думаете, или если есть тема, которую я должен осветить. А пока не стесняйтесь подписаться и следить за мной. До скорого!

Удачного кодирования 🎉!

Спасибо за прочтение! Понравилась эта история? Присоединиться к Medium, чтобы получить полный доступ ко всем моим историям.