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

Откуда: Украина
Сообщений: 391
Прошу помощи в составлении.

Есть следующая таблица:

ObjectNameParametеrDateTime
Object1301.04.201908:00:00
Object1801.04.201912:00:00
Object1601.04.201917:00:00
Object1401.04.201921:00:00
Object11002.04.201908:00:00


Хочу запросом вычислить среднюю величину Parametеr, за период (в данном случае сутки) причём промежутки времени не равны.
3 апр 19, 10:08    [21850979]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6140
vizit73,
select objectName,avg(parametrer) as SR,myDate from tbl group by objectName,myDate
не обзывайте поля зарезервированными словами (Date)
3 апр 19, 10:50    [21851024]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
Панург
Member

Откуда: настоящему индейцу завсегда везде ништяк
Сообщений: 4295
vizit73, а результат где?
+
SELECT
  q1.ObjectName,
  q1.Date,
  SUM([q1].[Hours] * [q1].[Parametеr]) / SUM([q1].[Hours]) AS СрВзв
FROM (SELECT t2.ObjectName,
                     t2.Date,
                     HOUR([t2].[Time]) - Nz(HOUR(
                                                  (SELECT MAX(t1.Time)
                                                  FROM t AS t1
                                                  WHERE t1.[ObjectName] = t2.[ObjectName]
                                                           AND t1.[Date] = t2.[Date]
                                                           AND t1.[Time] < t2.[Time])
                     ), 0) AS Hours,
                     t2.Parametеr
         FROM t AS t2) AS q1
GROUP BY q1.ObjectName,
         q1.Date;
3 апр 19, 11:24    [21851054]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
Панург
Member

Откуда: настоящему индейцу завсегда везде ништяк
Сообщений: 4295
vizit73, в моём запросе я не учёл оставшиеся 3 часа до завершения суток. Тут следует подумать...
3 апр 19, 11:33    [21851060]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
Панург
Member

Откуда: настоящему индейцу завсегда везде ништяк
Сообщений: 4295
Панург
Тут следует подумать...
сейчас некогда
3 апр 19, 11:37    [21851066]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19117
Схема решения.

1) Объединяем дату и время в одно значение даты-времени.
2) Используя две копии таблицы, получаем данные в формате
Объект-Значение-ДатаВремя-СледующиеДатаВремя
При этом если СледующиеДатаВремя выскакивают за границу суток, то заменяем на 24:00 текущих суток.
3) Считаем
SUM(Значение * (СледующиеДатаВремя - ДатаВремя)) / SUM(СледующиеДатаВремя - ДатаВремя)
3 апр 19, 11:43    [21851077]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19117
В виде отдельных запросов (требует проверки/отладки):

Query1
SELECT ObjectName, 
       Parametеr, 
       [Date]+[Time] AS [DateTime]
FROM tablename

Query2
SELECT t1.ObjectName, 
       t1.Parametеr, 
       t1.[DateTime], 
       IIF(MIN(t2.[DateTime]) <= CDate("02.04.2019"), 
           MIN(t2.[DateTime]), 
           CDate("02.04.2019")) AS NextDateTime
FROM Query1 AS t1, 
     Query1 AS t2
WHERE t1.ObjectName = t2.ObjectName
  AND t1.[DateTime] < t2.t1.[DateTime]
GROUP BY t1.ObjectName, t1.Parametеr, t1.[DateTime]

Query3
SELECT t1.ObjectName, 
       SUM(Parametеr * (NextDateTime - [DateTime])) / SUM(NextDateTime - [DateTime]) AS AvgParameter
FROM Query2
WHERE DATE([DateTime]) = CDate("01.04.2019")
3 апр 19, 11:56    [21851103]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
vizit73
Member

Откуда: Украина
Сообщений: 391
Панург
vizit73, а результат где?


6,416667 если считать, как в файле Excel сделал

Но есть формула Средняя хронологическая, там иначе.

К сообщению приложен файл (Книга1.xls - 13Kb) cкачать
3 апр 19, 12:07    [21851127]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
vizit73
Member

Откуда: Украина
Сообщений: 391
sdku
vizit73,
select objectName,avg(parametrer) as SR,myDate from tbl group by objectName,myDate
не обзывайте поля зарезервированными словами (Date)


Ок, это я по быстрому пример хотел сделать.

К сообщению приложен файл (db6.mdb - 144Kb) cкачать
3 апр 19, 12:08    [21851128]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
Панург
Member

Откуда: настоящему индейцу завсегда везде ништяк
Сообщений: 4295
vizit73
Но есть формула Средняя хронологическая, там иначе.
Это называется средневзвешенное значение
3 апр 19, 12:15    [21851136]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6140
Akina
Схема решения.

1) Объединяем дату и время в одно значение даты-времени.
....
Объект-Значение-ДатаВремя-СледующиеДатаВремя
При этом если СледующиеДатаВремя выскакивают за границу суток, то заменяем на 24:00 текущих суток.
....
Че-то не понятно следующее:если поле "time"типа "краткий формат времени" как туда ввести неправильное (<0 или >=24) и объединив его с датой получить "СледующиеДатаВремя выскакивающие за границу суток"
(разве что "time" текст-что,по-моему, не правильно т.к.ведет к ошибкам-с временем,в данном случае, надо работать как со временем,а не текстом)
3 апр 19, 12:22    [21851143]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
Панург
Member

Откуда: настоящему индейцу завсегда везде ништяк
Сообщений: 4295
vizit73, как-то так...
+
SELECT q1.ObjectName, q1.Date, Sum([q1].[Hours]*[q1].[Parametеr])/Sum([q1].[Hours]) AS СрВзв
FROM (
SELECT t2.ObjectName, t2.Date, Hour([t2].[Time])-Nz(Hour((SELECT Max(t1.Time) FROM t as t1 WHERE t1.[ObjectName]=t2.[ObjectName] and  t1.[Date]=t2.[Date] and t1.[Time]<t2.[Time])),0) AS Hours, t2.Parametеr FROM t AS t2
UNION ALL
SELECT t2.ObjectName, t2.Date, 24-Hour(Max([Time])) AS Hours, (SELECT TOP 1 t1.[Parametеr] FROM t as t1 WHERE t1.[ObjectName]=t2.[ObjectName] and  t1.[Date]>t2.[Date] ORDER BY t1.[Date], t1.[Time]) 
FROM t AS t2
GROUP BY t2.ObjectName, t2.Date
)  AS q1
GROUP BY q1.ObjectName, q1.Date;
Нужно проверять на данных...
3 апр 19, 12:51    [21851190]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19117
sdku
Че-то не понятно следующее:если поле "time"типа "краткий формат времени" как туда ввести неправильное (<0 или >=24) и объединив его с датой получить "СледующиеДатаВремя выскакивающие за границу суток"
Ну дык у него ж ещё и поле даты... а при вычислениях надо использовать полную дату. См. две последние записи примера данных.
4 апр 19, 07:58    [21851980]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
vizit73
Member

Откуда: Украина
Сообщений: 391
Сделал таким образом.

SELECT qryThird.ObjectName, (Sum([qryThird].[expAvgParameter1HourPeriod])/Sum([qryThird].[expHourPeriod])) AS expAVGParameter1, (Sum([qryThird].[expAvgParameter2HourPeriod])/Sum([qryThird].[expHourPeriod])) AS expAVGParameter2
FROM [SELECT 
	qrySecond.ObjectName, 
	(qrySecond.Parameter1+ 
		(Select 
			Top 1 Parameter1 
		From 
			qryFirst 
		Where 
			[qryFirst].ObjectName=[qrySecond].ObjectName AND [qryFirst].expDateTime<[qrySecond].expDateTime 
		ORDER BY 
			expDateTime DESC))/2 AS expAvgParameter1, 
	(qrySecond.Parameter2+ 
		(Select 
			Top 1 Parameter2 
		From 
			qryFirst 
		Where 
			[qryFirst].ObjectName=[qrySecond].ObjectName AND [qryFirst].expDateTime<[qrySecond].expDateTime 
		ORDER BY 
			expDateTime DESC))/2 AS expAvgParameter2,
	Hour (qrySecond.expDateTime- 
		(Select 
			Top 1 expDateTime 
		From 
			qryFirst 
		Where 
			[qryFirst].ObjectName=[qrySecond].ObjectName AND [qryFirst].expDateTime<[qrySecond].expDateTime 
		ORDER BY 
			expDateTime DESC)) AS expHourPeriod, 
	expAvgParameter1*expHourPeriod As expAvgParameter1HourPeriod,
	expAvgParameter2*expHourPeriod As expAvgParameter2HourPeriod
FROM 
	qryFirst AS qrySecond]. AS qryThird
GROUP BY qryThird.ObjectName;


А вот как быть если одно значение Parameter равно NULL?

К сообщению приложен файл (db6.mdb.zip - 81Kb) cкачать
9 май 19, 20:48    [21881904]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
aleks222
Member

Откуда:
Сообщений: 708
vizit73
Прошу помощи в составлении.

Есть следующая таблица:

ObjectNameParametеrDateTime
Object1301.04.201908:00:00
Object1801.04.201912:00:00
Object1601.04.201917:00:00
Object1401.04.201921:00:00
Object11002.04.201908:00:00


Хочу запросом вычислить среднюю величину Parametеr, за период (в данном случае сутки) причём промежутки времени не равны.


Хотеть - не вредно.

Но, чтобы что-то там "вычислить" надо огласить как значение Parametеr и интервал времени соотносятся.

1. Если Parametеr измерен в МОМЕНТ времени Time, то
select objectName, avg(parametrer) as SR, myDate from tbl group by objectName, myDate

и никак ты интервалов не учтешь. Ибо невозможно.

2. Если Parametеr измерен с МОМЕНТа времени Time до следующего МОМЕНТа времени Time

select objectName, sum( parametrer * (Time_Следующий -  Time) ) / sum( (Time_Следующий -  Time) ) as SR, myDate from tbl group by objectName, myDate


если интервал усреденения сутки

select objectName, sum( parametrer * (Time_Следующий -  Time) ) / [сутки] as SR, myDate from tbl group by objectName, myDate

причем для разницы времени и длительности суток надо выбрать в чем измерять: в часах, минутах или наносекундах.
10 май 19, 06:24    [21881990]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вычисление средней хронологической за неравные интервалы  [new]
vizit73
Member

Откуда: Украина
Сообщений: 391
aleks222
vizit73
Прошу помощи в составлении.

Есть следующая таблица:

ObjectNameParametеrDateTime
Object1301.04.201908:00:00
Object1801.04.201912:00:00
Object1601.04.201917:00:00
Object1401.04.201921:00:00
Object11002.04.201908:00:00


Хочу запросом вычислить среднюю величину Parametеr, за период (в данном случае сутки) причём промежутки времени не равны.


Хотеть - не вредно.

Но, чтобы что-то там "вычислить" надо огласить как значение Parametеr и интервал времени соотносятся.

1. Если Parametеr измерен в МОМЕНТ времени Time, то
select objectName, avg(parametrer) as SR, myDate from tbl group by objectName, myDate

и никак ты интервалов не учтешь. Ибо невозможно.

2. Если Parametеr измерен с МОМЕНТа времени Time до следующего МОМЕНТа времени Time

select objectName, sum( parametrer * (Time_Следующий -  Time) ) / sum( (Time_Следующий -  Time) ) as SR, myDate from tbl group by objectName, myDate


если интервал усреденения сутки

select objectName, sum( parametrer * (Time_Следующий -  Time) ) / [сутки] as SR, myDate from tbl group by objectName, myDate

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


Спасибо за ответ, но данный момент уже решён.
Используется Хронологическая взвешенная. В качестве интервала между соседними записями параметров используются часы, среднее (хронологическое взвешенное) вычисляется да за сутки.

На данный момент меня интересует как построить запрос, что бы он игнорировал в вычислении значения допустим Parameter1 равный NULL на определённое время, при этом Parameter2 если он не равен NULL на тот же час из вычислений не выпадал.
10 май 19, 11:40    [21882071]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Access Ответить