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

Откуда:
Сообщений: 34
Добрый день.
Задача: вычислить среднечасовые параметры из текущих показаний датчиков и вывести за последний час.
Почасовую группировку сделал, а с выводом последнего значения тупик.
Вот что получилось:

SELECT TOP 1 CONVERT(varchar(30), comp_date, 104) + ' ' +  SUBSTRING(CONVERT(varchar(30), comp_date, 114), 0, 3) + ':00', AVG(sens1), AVG(sens2), AVG(sens3), AVG(sens4), AVG(sens5), AVG(sens6) 
FROM Dev1_AI 
GROUP BY 
	CONVERT(varchar(30), comp_date, 104) + ' ' +  SUBSTRING(CONVERT(varchar(30), comp_date, 114), 0, 3) + ':00'
ORDER BY comp_date DESC


Естественно, он ругается на comp_date в части ORDER BY, поскольку это поле не участвует в группировке.
Не хотелось бы делать подзапросы с предварительной сортировкой, поскольку данных может быть очень много.
20 дек 13, 14:03    [15323232]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Glory
Member

Откуда:
Сообщений: 104751
Наверное тут нужен не ORDER BY comp_date DESC, а WHERE comp_date BETWEEN <последний час>
20 дек 13, 14:09    [15323265]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
aleks2
Guest
Бред. Сортируйте по
CONVERT(varchar(30), comp_date, 104) + ' ' +  SUBSTRING(CONVERT(varchar(30), comp_date, 114), 0, 3) + ':00'


Но все равно - феерический бред.
20 дек 13, 14:09    [15323271]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
Последний час я не знаю. Он может быть и не текущий.

Сортировать по CONVERT(varchar(30), comp_date, 104) + ' ' + SUBSTRING(CONVERT(varchar(30), comp_date, 114), 0, 3) + ':00'
нельзя, так как будет сортироваться по этой строке, а не по дате, как положено.

А как не бред?
20 дек 13, 14:14    [15323306]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Glory
Member

Откуда:
Сообщений: 104751
dandare
Последний час я не знаю. Он может быть и не текущий.

Тогда опишите, что для вас является "последним часом"
20 дек 13, 14:21    [15323352]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
Последний имеющийся в таблице.
20 дек 13, 14:22    [15323362]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Glory
Member

Откуда:
Сообщений: 104751
dandare
Последний имеющийся в таблице.

Замечательное описание.
Особенно в свете того, что данные у вас с точностью да секунды ?
20 дек 13, 14:23    [15323367]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Glory
Member

Откуда:
Сообщений: 104751
2013-12-20 11:59:59
2013-12-20 12:00:01
сколько тут записей из "последнего часа" ?
20 дек 13, 14:25    [15323376]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
Glory
2013-12-20 11:59:59
2013-12-20 12:00:01
сколько тут записей из "последнего часа" ?


Одна. В этом случае последним часом будет час с 2013-12-20 12:00:00 до 2013-12-20 12:59:59
20 дек 13, 14:26    [15323394]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Glory
Member

Откуда:
Сообщений: 104751
dandare
Одна. В этом случае последним часом будет час с 2013-12-20 12:00:00 до 2013-12-20 12:59:59

И в чем проблема найти сначала эту запись, взять из нее датувремя и выислить диапазон "последнего часа" ?
20 дек 13, 14:28    [15323400]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
Glory
dandare
Последний имеющийся в таблице.

Замечательное описание.
Особенно в свете того, что данные у вас с точностью да секунды ?


Данные с точностью до секунды. Это данные с датчиков. Они выводятся в веб-отчеты и веб-формы.
Есть отчеты по текущим значениям (данные выводятся как есть) и есть отчеты по усредненным значениям (берется среднее за каждый час из указанного диапазона).

На формы выводятся среднее значение за последний имеющийся целый час. Кстати вот и ошибка. За текущий нецелый, если они есть, сюда тоже попадут. Т.е. записей получается надо брать 2.
20 дек 13, 14:31    [15323425]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
Glory
dandare
Одна. В этом случае последним часом будет час с 2013-12-20 12:00:00 до 2013-12-20 12:59:59

И в чем проблема найти сначала эту запись, взять из нее датувремя и выислить диапазон "последнего часа" ?


Возможно так и правильнее с точки зрения производительности. Буду пробовать.
20 дек 13, 14:32    [15323437]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
SELECT c.last_hour, COUNT(*) qty, AVG(d.sens1) sens1, AVG(d.sens2) sens2
FROM (SELECT MAX(comp_date) FROM Dev1_AI) t(last_date)
CROSS APPLY (
  SELECT DATEADD(HOUR, DATEPART(HOUR, t.last_date), CONVERT(DATETIME, CONVERT(DATE, t.last_date)))
  ) c(last_hour)
JOIN Dev1_AI d ON d.comp_date >= c.last_hour
GROUP BY c.last_hour
20 дек 13, 14:34    [15323452]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
dandare
Последний час я не знаю. Он может быть и не текущий.

Сортировать по CONVERT(varchar(30), comp_date, 104) + ' ' + SUBSTRING(CONVERT(varchar(30), comp_date, 114), 0, 3) + ':00'
нельзя, так как будет сортироваться по этой строке, а не по дате, как положено.

А как не бред?

Если
CONVERT(varchar(30), comp_date, 104) + ' ' +  SUBSTRING(CONVERT(varchar(30), comp_date, 114), 0, 3) + ':00'
- строка, то может быть преобразовать это значение в более подходящий для сортировки тип данных?
20 дек 13, 14:34    [15323456]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
ambarka_max
dandare
Последний час я не знаю. Он может быть и не текущий.

Сортировать по CONVERT(varchar(30), comp_date, 104) + ' ' + SUBSTRING(CONVERT(varchar(30), comp_date, 114), 0, 3) + ':00'
нельзя, так как будет сортироваться по этой строке, а не по дате, как положено.

А как не бред?

Если
CONVERT(varchar(30), comp_date, 104) + ' ' +  SUBSTRING(CONVERT(varchar(30), comp_date, 114), 0, 3) + ':00'
- строка, то может быть преобразовать это значение в более подходящий для сортировки тип данных?


Она в отчет выводится. Можно конечно. Только в отчетах опять преобразования в человеческий вид делать. Подстроки резат и переставлять - как то мне показалось не красивым. Я такой вариант уже думал.
20 дек 13, 14:36    [15323475]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
SELECT  AVG(sens1), AVG(sens2), AVG(sens3), AVG(sens4), AVG(sens5), AVG(sens6) 
FROM	Dev1_AI 
		cross apply (select MAX(DATEADD(hour,datepart(hour,p.comp_date), convert(datetime,CONVERT(date, p.comp_date)))) 
					from Dev1_AI p) lastHour(val)
where 	comp_date >= 	lastHour.val
		and comp_date <dateadd(HOUR,1, lastHour.val)
20 дек 13, 14:38    [15323495]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
Мистер Хенки, Cygapb-007

Спасибо! Сейчас попробую разобраться.
Если прокомментируете, был бы рад и сэкономил время.
20 дек 13, 14:41    [15323527]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
aleks2
Guest
Я с вас тащусь...

-- от такого коду рука сам тянется к пистолету...
SELECT  AVG(sens1), AVG(sens2), AVG(sens3), AVG(sens4), AVG(sens5), AVG(sens6) 
  FROM Dev1_AI 
  where	comp_date between  dateadd(HOUR, -1, (select max(comp_date) from Dev1_AI)) and (select max(comp_date) from Dev1_AI)
20 дек 13, 14:46    [15323558]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
aleks2
Я с вас тащусь...


Смысл ясен, но немного не то с датами. Сам подправлю.
20 дек 13, 14:49    [15323582]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
o-o
Guest
зачем вообще apply, если можно посчитать 1 раз этот "последний час" в переменную @dt
и потом фильтровать уже сам запрос на comp_date >= @dt?

ТС, про "последний час" еще раз поясните?
если есть данные за
10:05
10:45
11:00
11:05
11:10

что есть "последний целый час"?
это с 10:05 по 11:05
или с 10:00 по 11:00
или вообще все даты берем, начиная с 10, ибо <с 10 до 11 это последний полный час,
но "За текущий нецелый, если они есть, сюда тоже попадут.">
20 дек 13, 14:52    [15323608]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
aleks2
-- от такого коду рука сам тянется к пистолету...
dateadd(HOUR, -1, getdate()) - это не последний час, а час назад, ощутите разницу...
20 дек 13, 14:55    [15323634]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
Cygapb-007
aleks2
-- от такого коду рука сам тянется к пистолету...
dateadd(HOUR, -1, getdate()) - это не последний час, а час назад, ощутите разницу...


Именно это я про даты и имел ввиду выше.
20 дек 13, 15:02    [15323688]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
Glory
Member

Откуда:
Сообщений: 104751
o-o
зачем вообще apply, если можно посчитать 1 раз этот "последний час" в переменную @dt
и потом фильтровать уже сам запрос на comp_date >= @dt?

Если вы посмотрите на план выполнения, то сервер именно так и сделает. Т.е. один раз вычислит cross apply и на его основе будет проверять все записи
20 дек 13, 15:03    [15323695]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
dandare
Member

Откуда:
Сообщений: 34
o-o
зачем вообще apply, если можно посчитать 1 раз этот "последний час" в переменную @dt
и потом фильтровать уже сам запрос на comp_date >= @dt?

ТС, про "последний час" еще раз поясните?
если есть данные за
10:05
10:45
11:00
11:05
11:10

что есть "последний целый час"?
это с 10:05 по 11:05
или с 10:00 по 11:00
или вообще все даты берем, начиная с 10, ибо <с 10 до 11 это последний полный час,
но "За текущий нецелый, если они есть, сюда тоже попадут.">


Это хороший вопрос! Я вот тоже думаю над этим. Звоню человеку, который такое неясное ТЗ поставил. :)
20 дек 13, 15:04    [15323701]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка и группировка  [new]
o-o
Guest
Glory
o-o
зачем вообще apply, если можно посчитать 1 раз этот "последний час" в переменную @dt
и потом фильтровать уже сам запрос на comp_date >= @dt?

Если вы посмотрите на план выполнения, то сервер именно так и сделает. Т.е. один раз вычислит cross apply и на его основе будет проверять все записи

ну и?
чем же тогда обосновать применение apply,
если, пардон, всего 1 значение на весь запрос требуется?
можно ище и вместо INNER JOIN писать CROSS APPLY, и тот же результат получить, но ЗАЧЕМ?
20 дек 13, 15:07    [15323721]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить