Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помощь с запросом  [new]
helpsql
Guest
Приветствую! Буду признателен за помощь.

Есть таблица вида

Datetime Value
-------------- --------
2015-11-12 09:00:00 403 100 000

2015-11-12 09:15:00 600 100 100

2015-11-12 09:30:00 120 100 110

2015-11-12 09:45:00 000 100 110

2015-11-12 10:00:00 000 100 110

2015-11-12 10:15:00 220 100 231

и так далее, т.е каждые 15 минут пишутся увеличивающиеся значения (могут и не увеличиваться, но никогда не уменьшаются) кумулятивное вообщем значение.

Нужно получить собственно среднее за день, среднее с 09:00 до 17:00, максимальное за день.
SQL 2008\2012
14 дек 15, 21:54    [18559738]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21042
helpsql
Нужно получить собственно среднее

RTFM AVG()

helpsql
максимальное

RTFM MAX()

helpsql
за день

helpsql
с 09:00 до 17:00

RTFM WHERE
RTFM DATEPART()
14 дек 15, 22:14    [18559814]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
krolig
Member [заблокирован]

Откуда: Владимир
Сообщений: 9
helpsql,

Вот, например, я так делал (может что-то пригодится):

SELECT /* три строки конвертации формата даты */
CONVERT(varchar(4),YEAR(dTime)) + '.' + " &_
RIGHT('0'+CONVERT(varchar(2),MONTH(dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(dTime)),2) as DT,
AVG(AmbTmpr) as AmbTmpr, /* средняя температура за выбранный период */
FROM HourStat_2
WHERE dTime > '12/23/2014 16:00' AND dTime <= '11/23/2014 16:00' /* а выборку делаем с точностью до минут от и до */
GROUP BY /* группируем результаты по дням */
CONVERT(varchar(4),Year(dTime)) + '.' + " &_
RIGHT('0'+CONVERT(varchar(2),MONTH(dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(dTime)),2)
ORDER BY DT /* упорядочиваем по времени (по дням, т. к. результаты кратны дням) */
14 дек 15, 22:17    [18559829]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
helpsql
Guest
Akina,
Благодарю за ответ, но среднее, максимальное и т.д нужны инкрементальные значения, т.е было 100 000 через 15 минут стало 101 000
нужно получить разницу 101 000 -100 000 =1 000 \15

алгоритм я думаю такой
получить начало-конец дня разделить на количество значений\15 получим среднее инкрементальное в минуту за день.
собственно как это реализовать?
14 дек 15, 22:37    [18559921]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
за помощь
Guest
krolig,

автор
GROUP BY /* группируем результаты по дням */
CONVERT(varchar(4),Year(dTime)) + '.' + " &_
RIGHT('0'+CONVERT(varchar(2),MONTH(dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(dTime)),2)

прелестно.
кто-то потом придет, такие чудеса оптимизации показывать будет! аж искры из глаз посыпятся. режим "ракета" в базе данных обнаружится.
14 дек 15, 22:41    [18559942]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
krolig
Member [заблокирован]

Откуда: Владимир
Сообщений: 9
helpsql,

Вот для инкрементных (накопленных) значений я делал разностную выборку (по часовую):

SELECT
CONVERT(varchar(4),YEAR(T2.dTime)) + '.' +
RIGHT('0'+CONVERT(varchar(2),MONTH(T2.dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(T2.dTime)),2) + ' ' +
LEFT(Convert(varchar(5),T2.dTime,108),5) AS DT,
AVG(T1.AmbTmpr) AS AT,
MAX(T2.Val_001)-MAX(T1.Val_001) AS V1,
MAX(T2.Val_002)-MAX(T1.Val_002) As V2,
MAX(T2.Val_003)-MAX(T1.Val_003) AS V3,
MAX(T2.Val_004)-MAX(T1.Val_004) AS V4,
MAX(T2.Val_005)-MAX(T1.Val_005) AS V5,
MAX(T2.Val_006)-MAX(T1.Val_006) AS V6
FROM " & TableName & " As T1, " & TableName & " As T2
WHERE
T1.dTime > '" & datn & "' AND
T1.dTime <= '" & datk & "' AND
DATEADD(hh,1,CONVERT(varchar(4),YEAR(T1.dTime)) + '.' +
RIGHT('0'+CONVERT(varchar(2),MONTH(T1.dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(T1.dTime)),2) + ' ' +
LEFT(Convert(varchar(5),T1.dTime,108),5)) =
CONVERT(varchar(4),YEAR(T2.dTime)) + '.' +
RIGHT('0'+CONVERT(varchar(2),MONTH(T2.dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(T2.dTime)),2) + ' ' +
LEFT(Convert(varchar(5),T2.dTime,108),5)
GROUP BY
CONVERT(varchar(4),YEAR(T2.dTime)) + '.' +
RIGHT('0'+CONVERT(varchar(2),MONTH(T2.dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(T2.dTime)),2) + ' ' +
LEFT(Convert(varchar(5),T2.dTime,108),5)
ORDER BY DT

-чтобы сделать по дням, нужно аналогично предыдущему моему примеру удалить все LEFT().
14 дек 15, 22:44    [18559962]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
krolig
Member [заблокирован]

Откуда: Владимир
Сообщений: 9
за помощь
krolig,

автор
GROUP BY /* группируем результаты по дням */
CONVERT(varchar(4),Year(dTime)) + '.' + " &_
RIGHT('0'+CONVERT(varchar(2),MONTH(dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(dTime)),2)

прелестно.
кто-то потом придет, такие чудеса оптимизации показывать будет! аж искры из глаз посыпятся. режим "ракета" в базе данных обнаружится.


Эти строки настраданы мною неделями кропотливого добросовестного труда, милчеловек, если покажешь как оптимизировать - буду оочень благодарен!!!
14 дек 15, 22:46    [18559969]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
aleks2
Guest
krolig
за помощь
krolig,

пропущено...

прелестно.
кто-то потом придет, такие чудеса оптимизации показывать будет! аж искры из глаз посыпятся. режим "ракета" в базе данных обнаружится.


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


SELECT 
CONVERT( varchar(64),cast( dTime as date), 104 ) as DT,
AVG(AmbTmpr) as AmbTmpr, /* средняя температура за выбранный период */ 
FROM HourStat_2 
WHERE dTime > '12/23/2014 16:00' AND dTime <= '11/23/2014 16:00' /* а выборку делаем с точностью до минут от и до */
GROUP BY /* группируем результаты по дням */
cast( dTime as date)
-- вариант для серверов постарше
-- datediff(day, 0, dTime)
ORDER BY DT /* упорядочиваем по времени (по дням, т. к. результаты кратны дням) */ 

-----------------------------------------------------------------
--Но настоящий джигит сделал бы так
declare @Days(day datetime primary key);
insert @Days --...ну на сто лет вперед

SELECT 
CONVERT( varchar(64), d.Day, 104 ) as DT,
AVG(AmbTmpr) as AmbTmpr, /* средняя температура за выбранный период */ 
FROM HourStat_2 as h
     inner join @Days as d on d.Day <= h.dTime and h.dTime < d.Day +1
-- ну, эту хрень ты сам поправь
--WHERE dTime > '12/23/2014 16:00' AND dTime <= '11/23/2014 16:00' /* а выборку делаем с точностью до минут от и до */
GROUP BY /* группируем результаты по дням */
d.Day
ORDER BY d.Day /* упорядочиваем по времени (по дням, т. к. результаты кратны дням) */ 


Головой работать надо
15 дек 15, 06:13    [18560631]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
krolig
Member [заблокирован]

Откуда: Владимир
Сообщений: 9
aleks2
krolig
пропущено...


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


SELECT 
CONVERT( varchar(64),cast( dTime as date), 104 ) as DT,
AVG(AmbTmpr) as AmbTmpr, /* средняя температура за выбранный период */ 
FROM HourStat_2 
WHERE dTime > '12/23/2014 16:00' AND dTime <= '11/23/2014 16:00' /* а выборку делаем с точностью до минут от и до */
GROUP BY /* группируем результаты по дням */
cast( dTime as date)
-- вариант для серверов постарше
-- datediff(day, 0, dTime)
ORDER BY DT /* упорядочиваем по времени (по дням, т. к. результаты кратны дням) */ 

-----------------------------------------------------------------
--Но настоящий джигит сделал бы так
declare @Days(day datetime primary key);
insert @Days --...ну на сто лет вперед

SELECT 
CONVERT( varchar(64), d.Day, 104 ) as DT,
AVG(AmbTmpr) as AmbTmpr, /* средняя температура за выбранный период */ 
FROM HourStat_2 as h
     inner join @Days as d on d.Day <= h.dTime and h.dTime < d.Day +1
-- ну, эту хрень ты сам поправь
--WHERE dTime > '12/23/2014 16:00' AND dTime <= '11/23/2014 16:00' /* а выборку делаем с точностью до минут от и до */
GROUP BY /* группируем результаты по дням */
d.Day
ORDER BY d.Day /* упорядочиваем по времени (по дням, т. к. результаты кратны дням) */ 


Головой работать надо



Давно бы я так и сделал, если бы мой MS SQL Server 2005 на слово date не ругался бы "не является определённым системным типом". А в случае замены всех кастов на datediff в результате запроса вместо дат - 41234; 41235; 41236...
Не работает Ваш вариант для MS SQL Server 2005.
15 дек 15, 09:22    [18560947]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
Glory
Member

Откуда:
Сообщений: 104751
krolig
Эти строки настраданы мною неделями кропотливого добросовестного труда, милчеловек, если покажешь как оптимизировать - буду оочень благодарен!!!

Не надо постить сюда в качестве советов ваш первый sql запрос
15 дек 15, 09:28    [18560968]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
krolig
Member [заблокирован]

Откуда: Владимир
Сообщений: 9
И, всётаки, предложенный by "Aleks2" вариант оптимизации не работает на MS SQL Server 2005:

SELECT 
CONVERT( varchar(64),cast( dTime as date), 104 ) as DT,
AVG(AmbTmpr) as AmbTmpr, /* средняя температура за выбранный период */ 
FROM HourStat_2 
WHERE dTime > '12/23/2014 16:00' AND dTime <= '11/23/2014 16:00' /* а выборку делаем с точностью до минут от и до */
GROUP BY /* группируем результаты по дням */
cast( dTime as date)
-- вариант для серверов постарше
-- datediff(day, 0, dTime)
ORDER BY DT /* упорядочиваем по времени (по дням, т. к. результаты кратны дням) */ 


- ругается на date - не является определённым в системе типом данных.

Так в чём же дело, обманул товарищь или есть, всётаки, способ оптимизировать так, чтобы не только на странице форума
красиво выглядело, но и на MS SQL Server 2005 работало?
15 дек 15, 16:06    [18563611]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
iap
Member

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

напишите вместо CAST( AS DATE) обнуление времени по-другому, делов-то!
15 дек 15, 16:09    [18563622]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
krolig
Member [заблокирован]

Откуда: Владимир
Сообщений: 9
iap
krolig,

напишите вместо CAST( AS DATE) обнуление времени по-другому, делов-то!


Всё на что меня хватило:

CONVERT(varchar(4),YEAR(dTime)) + '.' + " &_
RIGHT('0'+CONVERT(varchar(2),MONTH(dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(dTime)),2)


- разве есть ещё способы для MS SQL Server 2005?
15 дек 15, 16:10    [18563638]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
iap
Member

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

напишите вместо CAST( AS DATE) обнуление времени по-другому, делов-то!


Всё на что меня хватило:

CONVERT(varchar(4),YEAR(dTime)) + '.' + " &_
RIGHT('0'+CONVERT(varchar(2),MONTH(dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(dTime)),2)



- разве есть ещё способы для MS SQL Server 2005?
DATEADD(DAY,DATEDIFF(DAY,0,dTime),0)
Ещё - читайте FAQ
15 дек 15, 16:24    [18563721]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
krolig,

ну или так
convert(datetime, floor(convert(float, getdate())))
15 дек 15, 16:30    [18563754]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 47063
112-й стиль CONVERTа тоже поможет в этом деле
15 дек 15, 16:34    [18563779]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
krolig
Member [заблокирован]

Откуда: Владимир
Сообщений: 9
iap
krolig
пропущено...


Всё на что меня хватило:

CONVERT(varchar(4),YEAR(dTime)) + '.' + " &_
RIGHT('0'+CONVERT(varchar(2),MONTH(dTime)),2) + '.' +
RIGHT('0'+CONVERT(varchar(2),DAY(dTime)),2)



- разве есть ещё способы для MS SQL Server 2005?
DATEADD(DAY,DATEDIFF(DAY,0,dTime),0)
Ещё - читайте FAQ



Работает!!!

SELECT 
	DATEADD(DAY,DATEDIFF(DAY,0,dTime),0) as DT,
	AVG(AmbTmpr) as AmbTmpr		
FROM UEL002_Sb002_HourStat 
WHERE dTime > '05/23/2015 16:00' AND dTime <= '06/23/2015 16:00'
GROUP BY
	DATEDIFF(DAY,0,dTime)
ORDER BY DT


Вот сейчас в коментариях к ФАКу нашёл такой вариант,
но до ентого дойти до ентого - енто было слишком...

Резюмируя: хорошо, что есть ФАКи и хорошо, что кроме ФАКов есть ещё живые форумы и хорошо, что на эти формуы заходят умные в области SQL люди, которые подсказывают по SQL умным людям из другой области.
15 дек 15, 16:37    [18563790]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
helpsql
Guest
Граждане, даты это хорошо. Но хотелось бы вернуться к истокам.
Как получать значение инкремента?

Вот так получается т.н. нарастающий итог, есть возможность не суммировать, а вычитать?
SELECT Datetime ,Value
,ROW_NUMBER() OVER(PARTITION BY CAST(Datetime as date) ORDER BY Datetime ,Value asc) AS 'id'
,SUM(Value) OVER(PARTITION BY CAST(Datetime as date) ORDER BY Datetime ,Value asc) AS 'Total'
FROM Table1
ORDER BY Datetime ASC


т.е 100 000 следующее значение 100 100, разница 100, вот эту цифру как получить простым(любым) способом?
15 дек 15, 18:15    [18564259]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом  [new]
aleks2
Guest
helpsql
Граждане, даты это хорошо. Но хотелось бы вернуться к истокам.
Как получать значение инкремента?

Вот так получается т.н. нарастающий итог, есть возможность не суммировать, а вычитать?
SELECT Datetime ,Value
,ROW_NUMBER() OVER(PARTITION BY CAST(Datetime as date) ORDER BY Datetime ,Value asc) AS 'id'
,SUM(Value) OVER(PARTITION BY CAST(Datetime as date) ORDER BY Datetime ,Value asc) AS 'Total'
FROM Table1
ORDER BY Datetime ASC


т.е 100 000 следующее значение 100 100, разница 100, вот эту цифру как получить простым(любым) способом?


Ну... изучить lead/lag
15 дек 15, 19:42    [18564807]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить