Запрос T-SQL для получения имени компьютера, который не отправлял запросы к базе данных в течение 60 дней
У меня есть таблица Queries, в которой отслеживается каждый запрос к базе данных с отметкой date_time и именем компьютера.
Для одного и того же компьютера могут быть тысячи строк, каждая из которых имеет разную отметку date_time.
Если компьютер не сделал запрос в течение 60 дней, то его необходимо идентифицировать.
Я знаю, как проверить каждую запись, чтобы узнать, не прошло ли для этой конкретной записи запроса больше 60 дней.
Я не знаю, как просмотреть все записи для этого конкретного компьютера, чтобы определить, превышает ли МАЛЕНЬКАЯ отметка date_time более 60 дней, и если да, то показать ее в результатах.
Пожалуйста, простите, так как я новичок в SQL-запросах. Спасибо.
Я пытался:
select computer_name
from queries
where date_time > 20240508000000
Я хочу показать только computer_name ЕСЛИ у него нет ни одной записи возрастом менее 60 дней, потому что это означает, что он не использовался для запроса в течение 2 месяцев и его необходимо идентифицировать.
Приведенный выше пример неверен, поскольку в нем будут показаны ВСЕ записи длительностью более 60 дней.
Ответы 1
Я предлагаю это без тестирования и при условии, что временная метка имеет тип данных datetime или аналогичный. Идея состоит в том, чтобы получить самую молодую временную метку для каждого компьютера с помощью группировки (агрегирования), а затем отфильтровать те, чья самая молодая временная метка старше 60 дней по сравнению с текущим моментом (GETDATE()).
После того, как стала доступна дополнительная информация, я обновил решение. Я не знаю тип данных, который создает такие значения, как 202407080000US или 20240508000000, поэтому я использую довольно грубый способ преобразования в тип данных DATE, игнорируя части времени. Я также не знаю, какая часть этих входных данных — месяц, а какая — день, поэтому проверьте это.
;WITH prep AS(
SELECT
computer_name
,date = CONVERT(DATE, LEFT(CONVERT(NVARCHAR(100), date_time), 8))
FROM queries
)
SELECT
computer_name
,youngest = MAX(date)
FROM prep
GROUP BY computer_name
HAVING MAX(date) <= DATEADD(DAY, -60, CONVERT(DATE,GETDATE()))
-- This works, too, but it is better to keep
-- the column as much as possible outside of functions:
--HAVING DATEDIFF(DAY, MAX(date), CONVERT(DATE,GETDATE())) > 60
Добавление индекса в queries(computer_name, date_time) может повысить производительность, если вы обнаружите, что ваш запрос выполняется медленнее, чем хотелось бы.
@TN — для запроса GROUP BY computer_name SQL Server не будет использовать преимущества того, что date_time является вторым ключевым столбцом в индексе при поиске MAX — если вам нужен такой план, вам нужно выполнить эффективный запрос TOP 1 для каждого computer_name (подпункт запрос, apply, рекурсивное CTE)
@ Der U — получена следующая ошибка: Сообщение 241, уровень 16, состояние 1, строка 1. Не удалось выполнить преобразование при преобразовании даты и/или времени из строки символов. Кроме того, я не упомянул, что данные/время такие же, как 202407080000US.
Я только что изменил ответ. Не могли бы вы уточнить тип данных date_time? Откуда это? Какое программное обеспечение? Другая СУБД?
@MartinSmith - Хорошая мысль. Похоже, я предполагал оптимизацию индекса, которая не реализована. Лучшее, что я мог сделать, это индексное сканирование. (рабочий пример). Однако если таблица содержит другие «широкие» столбцы, такие как текст запроса, «узкий» индекс все равно может сократить количество операций чтения.
Вместо преобразования каждого значения текста даты вы можете определить и применить ограничение текста даты с помощью чего-то вроде: DECLARE @CutoffDate DATE = DATEADD(day, -60, GETDATE()), DECLARE @CutoffText VARCHAR(50) = CONVERT(VARCHAR(8), @CutoffDate, 112) + '000000', а затем исходный ответ изменить с помощью HAVING MAX(date_time) < @CutoffDate. Полный запрос: SELECT computer_name, youngest = MAX(date_time) FROM queries GROUP BY computer_name HAVING MAX(date_time) < @CutoffDate.
@ Der U — Это может сработать, но я не уверен, как указать запросы таблицы в сложном операторе. Если я поставлю From в первый выбор, это выдаст ошибку «Неверный синтаксис рядом с «from», даже если имена столбцов разрешаются. Если я удалю это и поставлю query.date_time, возникнет ошибка: идентификатор, состоящий из нескольких частей, не может быть связан. Но я думаю, что оно почти у цели!
@ Der U - date_time равно varchar(16). Я не уверен в исходном программном обеспечении, просто отправляю запрос к базе данных с помощью SSMS на SQL-сервер.
@colemd1 Во-первых, я облажался и забыл предложение FROM в cte, надеюсь, мне наконец удалось отредактировать это и сделать работоспособным. Во-вторых, в зависимости от среды, в которой вы это запускаете, вам может потребоваться опустить точку с запятой в начале. Пожалуйста, попробуйте еще раз.
@ Дер Ю - ТЫ МУЖЧИНА!!! Спасибо, это работает.
@ colemd1 Рад помочь. Еще хотелось бы знать, откуда Вы взяли эти значения. varchar(16) просто означает, что сервер sql хранит его как строку. Этот формат может вызвать у вас проблемы, особенно когда временная часть становится актуальной. Почему иногда в последних двух позициях выдаются два нуля, а иногда «США»? Тебе стоит проверить, что там происходит.
Примеры данных и ожидаемые результаты будут очень полезны. У вас есть отдельный список всех машин? Почему вы храните даты как целые числа?