Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос записей с уникальным набором значений нескольких полей  [new]
kmi
Member

Откуда: Москва
Сообщений: 36
Добрый день, коллеги! Помогите с задачкой, пожалуйста!

Есть таблица items с полями id, type, name, value, targetDate, insertDate.

Она наполняется несколькими системами, в результате чего могут повторяться записи с одинаковыми полями type, name и targetDate, но разными id, value и insertDate. При этом, впрочем, гарантированно уникально только ключевое поле id.

Мне нужно сделать запрос, который вернул бы только уникальные записи по полям type, name и targetDate, в которых значение value поменялось последний раз (согласно значению id, т.к. insertDate может быть не уникальным).
22 сен 14, 14:10    [16604861]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
select  max(id) id, type, name, targetDate from tbl1 group by type, name, targetDate
22 сен 14, 14:19    [16604949]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SELECT TOP(1) WITH TIES *
FROM tbl1
ORDER BY ROW_NUMBER()OVER(PARTITION BY type, name, targetDate ORDER BY insertDate DESC, id DESC);
22 сен 14, 14:28    [16605058]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
kmi
Member

Откуда: Москва
Сообщений: 36
Владислав Колосов
select  max(id) id, type, name, targetDate from tbl1 group by type, name, targetDate


Не проходит. Мне нужно возвращать все поля записи, включая value и insertDate.
22 сен 14, 15:07    [16605374]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
kmi
Member

Откуда: Москва
Сообщений: 36
iap
SELECT TOP(1) WITH TIES *
FROM tbl1
ORDER BY ROW_NUMBER()OVER(PARTITION BY type, name, targetDate ORDER BY insertDate DESC, id DESC);


Спасибо! Заработало!
22 сен 14, 15:24    [16605479]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
kmi
Member

Откуда: Москва
Сообщений: 36
kmi
iap
SELECT TOP(1) WITH TIES *
FROM tbl1
ORDER BY ROW_NUMBER()OVER(PARTITION BY type, name, targetDate ORDER BY insertDate DESC, id DESC);


Спасибо! Заработало!


Только теперь наблюдается просадка производительности... Очень большая. 2,5 минуты против 11 секунд на выборку всех записей на указанную дату.
22 сен 14, 16:07    [16605702]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
kmi
Только теперь наблюдается просадка производительности
По сравнению с чем? Покажите свой эталонный запрос!
Какие индексы есть у таблицы? План-то смотрели вообще?
22 сен 14, 16:09    [16605717]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
WITH CTE AS(SELECT N=ROW_NUMBER()OVER(PARTITION BY type, name, targetDate ORDER BY insertDate DESC, id DESC),* FROM tbl1)
SELECT *
FROM CTE
WHERE N=1;
22 сен 14, 16:10    [16605728]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Кстати, возможны ещё пара десятков вариантов.
Воспользуйтесь поиском по форуму.
22 сен 14, 16:11    [16605734]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
kmi
Member

Откуда: Москва
Сообщений: 36
iap
kmi
Только теперь наблюдается просадка производительности
По сравнению с чем? Покажите свой эталонный запрос!
Какие индексы есть у таблицы? План-то смотрели вообще?


Индекс один - на поле id.

Делаю общий запрос по текущей дате:

SELECT id, type, name, value, targetDate, insertDate FROM items WHERE targetDate = '2014-09-22'

Итог выполнения 5 032 961 запись за 30 секунд. Но в этом наборе есть "дубликаты", т.е. записи с одинаковым значением type, name и targetDate. Их нужно отсечь так, чтобы осталась только последняя запись.

Второй заход:

SELECT id, type, name, value, targetDate, insertDate FROM items WHERE targetDate = '2014-09-22'
GROUP BY type, name, targetDate

Не работает, потому что id, value и insertDate не указаны в GROUP BY.

Третий заход:

SELECT id, type, name, value, targetDate, insertDate FROM items WHERE targetDate = '2014-09-22'
GROUP BY id, type, name, value, targetDate, insertDate

Работает 31 секунду, но возвращает всё те же 5 032 961 записей, т.к. id уникальный.

Четвёртый шаг, отсекаем поля, по которым не нужна уникальность:

SELECT type, name, targetDate FROM items WHERE targetDate = '2014-09-22'
GROUP BY type, name, targetDate

Работает 54 секунды, возвращает 19661 запись (те, что нужны), но мне не хватает полей id, value, insertDate.

Теперь делаю запрос, подобный Вашему (поправьте, если ошибся):

SELECT TOP(1) WITH TIES *
FROM items
WHERE targetDate = '2014-09-22'
ORDER BY ROW_NUMBER() OVER(PARTITION BY type, name, targetDate ORDER BY id DESC);

Получаю те же 19661 строку, но за 11 минут 2 секунды!
22 сен 14, 16:51    [16606012]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
kmi
Member

Откуда: Москва
Сообщений: 36
kmi
Получаю те же 19661 строку

Уточню. В последнем случае я получаю как раз тот набор данных, который нужен, со всеми полями. Но время очень велико.
22 сен 14, 16:55    [16606034]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
kmi
kmi
Получаю те же 19661 строку

Уточню. В последнем случае я получаю как раз тот набор данных, который нужен, со всеми полями. Но время очень велико.
Ну а про план-то что скажете?
Чем там сервер занимается?
22 сен 14, 16:57    [16606043]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
kmi,

кстати, а второй (чем-то похожий) запрос как отрабатывает.
Хотя, должен показать что-то такое же...
22 сен 14, 17:05    [16606094]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
kmi
Member

Откуда: Москва
Сообщений: 36
iap,
Clustered Index Scan (по первичному ключу) 94%
Паралеллизм 6%
22 сен 14, 17:26    [16606240]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
kmi
Member

Откуда: Москва
Сообщений: 36
iap
kmi,

кстати, а второй (чем-то похожий) запрос как отрабатывает.
Хотя, должен показать что-то такое же...


Да, примерно то же.
22 сен 14, 17:36    [16606287]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
Хитрый жук
Member

Откуда:
Сообщений: 9
Попробуйте так переделать
select *
from (
	select *
		,ROW_NUMBER() OVER(PARTITION BY type, name ORDER BY id DESC) as rnk
	from items
	where targetDate = '2014-09-22'
) i
where i.rnk = 1


и такой индекс ускорит выборку, но правда за вставкой следите
CREATE NONCLUSTERED INDEX IX_items ON dbo.items
(
	targetDate ASC,
	type ASC,
	[name] ASC,
	[id] DESC
)
INCLUDE ( 	value,
	insertDate)
22 сен 14, 17:57    [16606375]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Хитрый жук
Попробуйте так переделать
select *
from (
	select *
		,ROW_NUMBER() OVER(PARTITION BY type, name ORDER BY id DESC) as rnk
	from items
	where targetDate = '2014-09-22'
) i
where i.rnk = 1
Это то же самое, что и у меня с CTE
23 сен 14, 08:51    [16608012]     Ответить | Цитировать Сообщить модератору
 Re: Запрос записей с уникальным набором значений нескольких полей  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
c 2000 использовал такой подход

select * from tbl1
join (select  max(id) id from tbl1 group by type, name, targetDate) x on x.id = tbl1.id

на больших объемах вроде как имеет выигрыш по сравнению с оконными функциями
23 сен 14, 09:13    [16608078]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить