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

Откуда:
Сообщений: 545
Есть таблица со столбцами

id|value1|value2|datatime
--+------+------+-----------------------
1 |  23  |  45  |2011-05-01 10:12:02.543
1 |  54  |  21  |2011-05-01 17:12:02.353
2 |  98  |  50  |2011-05-01 13:13:02.383
2 |  76  |  31  |2011-05-01 15:15:02.381
2 |  22  |  99  |2011-05-01 17:11:02.311

Результат запроса должен быть такой

1 |  54  |  21  |2011-05-01 17:12:02.353
2 |  22  |  99  |2011-05-01 17:11:02.311

Как осуществит такое? Т.е. чтобы запрос выводил только последнюю по времени запись по каждому id
2 май 11, 19:00    [10595713]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
SELECT TOP(1) WITH TIES *
FROM T
ORDER BY ROW_NUMBER()OVER(PARTITION BY id ORDER BY [datatime] DESC);
2 май 11, 19:30    [10595789]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
ё
Guest
select t1.* 
from T t1
where t1.datatime >= all (select t2.datatime from T t2 where t1.id=t2.id)
2 май 11, 19:35    [10595806]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
2king2
Member

Откуда:
Сообщений: 545
iap, ё

Спасибо, беглым взглядом вроде то что нужно, ща подправим и потестим
3 май 11, 19:30    [10600501]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
Camill
Member

Откуда:
Сообщений: 1
Вариант iap вроде побыстрее, но он выдает данные без сортировки.
Подскажите, куда там можно дописать ORDER BY id?
4 май 11, 20:58    [10606667]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Camill
Вариант iap вроде побыстрее, но он выдает данные без сортировки.
Подскажите, куда там можно дописать ORDER BY id?
SELECT * FROM
(
 SELECT TOP(1) WITH TIES *
 FROM T
 ORDER BY ROW_NUMBER()OVER(PARTITION BY id ORDER BY [datatime] DESC)
) T
ORDER BY id;
Вот так, возможно, ещё лучше будет:
WITH CTE AS(SELECT N=ROW_NUMBER()OVER(PARTITION BY id ORDER BY [datatime] DESC),* FROM T)
SELECT *
FROM CTE
WHERE N=1
ORDER BY id;
4 май 11, 22:02    [10606891]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Ищу рецепт задачи для SQL 2000. Как? Уже мозоли... Подскажите пожалуйста.

Допустим, есть классическая конструкция:
SELECT t1.id, t1.datetime, t1.somevalue
FROM T t1
WHERE NOT EXISTS(
  SELECT 1 
  FROM T t2
  WHERE t2.id = t1.id AND t2.datetime > t1.datetime
)

Но не устраивает по быстродействию. Таблица большая (10-ки миллионов строк). Пробовал искусственно подзапрос ограничивать датой (AND t2.datetime < t1.datetime - 30)
- чтобы хотя бы не весь массив данных проверялся, но такая хитрость не всегда прокатывает есть случаи когда для id промежуток между datetime более 30 дней.
Что посоветуете?
11 апр 12, 16:11    [12400577]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
SELECT 
	t1.id
	, t1.datetime
	, t1.somevalue
FROM 
	T t1
INNER JOIN (
		SELECT 
			t2.id
			, max(t1.datetime) md
		FROM 
			T t2
		GROUP BY
			t2.id) On t2.id = t1.id And t2.md = t1.datetime  
11 апр 12, 16:39    [12400816]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
ambarka_max
Ищу рецепт задачи для SQL 2000. Как? Уже мозоли... Подскажите пожалуйста.

Допустим, есть классическая конструкция:
SELECT t1.id, t1.datetime, t1.somevalue
FROM T t1
WHERE NOT EXISTS(
  SELECT 1 
  FROM T t2
  WHERE t2.id = t1.id AND t2.datetime > t1.datetime
)

Но не устраивает по быстродействию. Таблица большая (10-ки миллионов строк). Пробовал искусственно подзапрос ограничивать датой (AND t2.datetime < t1.datetime - 30)
- чтобы хотя бы не весь массив данных проверялся, но такая хитрость не всегда прокатывает есть случаи когда для id промежуток между datetime более 30 дней.
Что посоветуете?



вот, быстрее трудно:
SELECT t1.id, t1.datetime, t1.somevalue
FROM T t1
INNER JOIN 
(
  SELECT id, max(datetime) as datetime
  FROM T 
  GROUP BY id
) t2 on t2.id = t1.id and t2.datetime = t1.datetime
11 апр 12, 16:40    [12400823]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
опередил посадовец на минуту :)
11 апр 12, 16:41    [12400830]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
А если усложнить задачу.
DECLARE @T TABLE (LookTime datetime, [ID] int)
INSERT @T (LookTime, [ID])
SELECT '20100404 22:18:00.000', 7934 UNION ALL
SELECT '20100404 20:45:00.000', 25746 UNION ALL
SELECT '20100404 20:45:00.000', 30763	

--SELECT * FROM @T

DECLARE @T2 TABLE (RowID bigint PRIMARY KEY, TrackTime datetime, [ID] int, Value int)
INSERT @T2 (RowID, TrackTime, [ID], Value)
SELECT 1, '20100119 10:40:55.107', 7934, 44 UNION ALL
SELECT 2, '20100130 14:24:44.293', 30763, 0 UNION ALL
SELECT 3, '20100315 11:39:38.733', 7934, 20 UNION ALL
SELECT 4, '20100315 22:18:01.000', 7934, 30 UNION ALL
SELECT 5, '20100404 20:45:02.397', 30763, 1 UNION ALL
SELECT 6, '20100519 10:40:55.107', 25746, 7 UNION ALL
SELECT 7, '20100615 11:39:38.733', 25746, 8 

SELECT * FROM @T2

SELECT t.*, t2.* 
FROM @T t
LEFT JOIN @T2 t2 ON t2.[ID] = t.[ID] AND t2.TrackTime < t.LookTime
WHERE NOT EXISTS(
	SELECT 1 FROM @T2 t22 
	WHERE t22.[ID] = t.[ID] AND t22.TrackTime < t.LookTime 
		AND t22.RowID > t2.RowID
)

Последний запрос возвращает все записи из таблицы @T, при этом для каждой строки из @T нужно вывести одну (или ничего) строку из @T2 - с таким же [ID] и TrackTime самым близким к @T.LookTime).
11 апр 12, 17:43    [12401252]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Так вот, на болшой таблице запрос c конструкцией NOT EXISTS не устраивает, т.к поздзапрос сканит всю таблицу @T2 а потом в объединении уже отсеивает ненужные строки (видно в плане)
11 апр 12, 17:49    [12401297]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
SELECT 
	t2.LookTime
	, t1.id
	, t1.RowID
	, t1.TrackTime
	, t1.Value
FROM 
(
		SELECT 
			t3.id
			, t3.LookTime
			, max(t2.TrackTime) md
		FROM 
			@T t3
		LEFT JOIN @T2 t2 On t3.ID = t2.ID And t3.LookTime >= t2.TrackTime 
		GROUP BY
			t3.id
			, t3.LookTime) t2 
LEFT JOIN @T2 t1 On t2.id = t1.id And t2.md = t1.TrackTime  
11 апр 12, 18:02    [12401389]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
токо вот у 2000 нет табличных переменных
11 апр 12, 18:03    [12401395]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
HandKot
токо вот у 2000 нет табличных переменных
Да что Вы говорите!
Они появились как раз в SQL2000 для поддержки появившихся тогда же функций.
11 апр 12, 20:21    [12401949]     Ответить | Цитировать Сообщить модератору
 Re: Как написать запрос выбирая только максимальные значения по времени  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Всем спасибо, действительно запрос с GROUP BY оказался более гибок для оптимизации. По плану идут Index Seek вместо TableScan. Супер.
12 апр 12, 15:17    [12406398]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить