IBExpert. Вычисление среднего значения за час - SQL, базы данных

aford · 27.03.2018, 10:20

Добрый день!
Подскажите пожалуйста, как в IBExpert сделать процедуру в базе, которая возвращает среднее значение работы сотрудника за час?
Имеется таблица вида:

USER_ID T_START T_STOP
Сотрудник 1 26.03.2018 11:24:11 26.03.2018 11:27:48
Сотрудник 1 26.03.2018 11:27:53 26.03.2018 11:31:54
Сотрудник 1 26.03.2018 11:31:54 26.03.2018 11:42:50
Сотрудник 1 26.03.2018 12:59:00 26.03.2018 13:24:00

Где T_START - начало работы, T_STOP соответственно конец.

В итоге нужно получить таблицу следующего вида:

USER_ID 10:00-11:00 11:00-12:00 12:00-13:00 13:00-14:00 и так до 18:00
Сотрудник1 0 ~18/60 1/60 24/60

Аватар · 27.03.2018, 11:44

А где среднее? Тут просто за каждый час просуммировать нужно. Так, например, во вложенном запросе выдернуть в секундах время с 12:00 до 13:00. Для других интервалов аналогично. Во внешнем просуммировать эти секунды. И отображай как удобно. Раздели на 60 получишь примерно минуты

Код:

    CASE WHEN EXTRACT(HOUR FROM T_START)<=12 and EXTRACT(HOUR FROM T_STOP)>=12
         THEN CASE WHEN EXTRACT(HOUR FROM T_START)<12 and EXTRACT(HOUR FROM T_STOP)>12 THEN CAST('13:00:00' AS TIME)-CAST('12:00:00' AS TIME)
                   WHEN EXTRACT(HOUR FROM T_START)<12 THEN CAST(T_STOP AS TIME)-CAST('12:00:00' AS TIME)
                   WHEN EXTRACT(HOUR FROM T_STOP)>12 THEN CAST('13:00:00' AS TIME)-CAST(T_START AS TIME)
                   ELSE CAST(T_STOP AS TIME)-CAST(T_START AS TIME) END
         ELSE 0 END AS Between12and13,

evg_m · 27.03.2018, 11:51

сделать ВЫЧИСЛЕНИЕ года, дня(от начала года) и часа(от начала дня)
как вариант число ЧАСОВ от заранее заданного(известного) момента.
группировка по данному значению(-ям)
и как результат AVG среднее за год+день+час(за час отстоящий от заданного на К шагов).
для FireBird

Цитата:

DATEDIFF ()
Функция возвращает количество лет, месяцев, недель, дней, часов, минут,
секунд или миллисекунд между двумя значениями даты/времени. Тип
возвращаемого значения BIGINT.

aford · 27.03.2018, 13:01

Цитата:

Сообщение от Аватар

А где среднее? Тут просто за каждый час просуммировать нужно. Так, например, во вложенном запросе выдернуть в секундах время с 12:00 до 13:00. Для других интервалов аналогично. Во внешнем просуммировать эти секунды. И отображай как удобно. Раздели на 60 получишь примерно минуты

Код:

    CASE WHEN EXTRACT(HOUR FROM T_START)<=12 and EXTRACT(HOUR FROM T_STOP)>=12
         THEN CASE WHEN EXTRACT(HOUR FROM T_START)<12 and EXTRACT(HOUR FROM T_STOP)>12 THEN CAST('13:00:00' AS TIME)-CAST('12:00:00' AS TIME)
                   WHEN EXTRACT(HOUR FROM T_START)<12 THEN CAST(T_STOP AS TIME)-CAST('12:00:00' AS TIME)
                   WHEN EXTRACT(HOUR FROM T_STOP)>12 THEN CAST('13:00:00' AS TIME)-CAST(T_START AS TIME)
                   ELSE CAST(T_STOP AS TIME)-CAST(T_START AS TIME) END
         ELSE 0 END AS Between12and13,

У меня нули выдает, может что не так сделал? Вложил код...

Аватар · 27.03.2018, 13:24

Аналогично не значит точно так же, там полно констант в примере 12 и 13 и это для интервала с 12 до 13. Как думаешь какие константы должны быть например для интервала с 7 до 8?

aford · 27.03.2018, 14:51

Цитата:

Сообщение от Аватар

для интервала с 7 до 8?

Константа 7?
Так у меня теперь вообще несколько строчек появилось в результате запроса, а должна быть одна..

aford · 29.03.2018, 08:50

Вопрос все еще актуален...

Serge_Bliznykov · 29.03.2018, 09:13

Цитата:

Сообщение от aford

Вопрос все еще актуален...

Выложите текст своего запроса, поясните, что с ним не так, тем самым Вы повысите шанс получить ответ.

Аватар · 29.03.2018, 09:14

1. Покажи как считаешь например для интервала с 7 до 8
2. Там только группировка нужна для user_name, а еще лучше для user_id на тот случай если user_name не уникально
3. Поля Between12and13 и прочие нужно суммировать, а не группировать по ним

aford · 29.03.2018, 09:56

Вот, что у меня на данный момент:
Исходные данные

Цитата:

USER_NAME T_START T_STOP STATE_ID
Сотрудник1 28.03.2018 09:44:00 28.03.2018 09:45:07 1
Сотрудник1 28.03.2018 09:45:34 28.03.2018 09:46:29 1
Сотрудник1 28.03.2018 09:47:05 28.03.2018 10:02:00 1
Сотрудник1 28.03.2018 10:12:08 28.03.2018 11:51:59 1
Сотрудник1 28.03.2018 11:53:05 28.03.2018 12:06:59 1
Сотрудник1 28.03.2018 12:11:52 28.03.2018 12:50:59 1
Сотрудник1 28.03.2018 13:17:30 28.03.2018 15:27:01 1
Сотрудник1 28.03.2018 15:33:22 28.03.2018 17:26:01 1
Сотрудник1 28.03.2018 17:27:08 28.03.2018 17:55:02 1

Код:

Цитата:

begin
for select
t1.user_name,
(CASE
WHEN EXTRACT(HOUR FROM t1.t_start)<=8 and EXTRACT(HOUR FROM t1.t_stop)>=8
THEN CASE WHEN EXTRACT(HOUR FROM t1.t_start)<8 and EXTRACT(HOUR FROM t1.t_stop)>8 THEN CAST('09:00:00' AS TIME)-CAST('08:00:00' AS TIME)
ELSE CAST(t1.t_stop AS TIME)-CAST(t1.t_start AS TIME) END
ELSE 0
END)/60 AS Between08and09,
(CASE
WHEN EXTRACT(HOUR FROM t1.t_start)<=9 and EXTRACT(HOUR FROM t1.t_stop)>=9
THEN CASE WHEN EXTRACT(HOUR FROM t1.t_start)<9 and EXTRACT(HOUR FROM t1.t_stop)>9 THEN CAST('10:00:00' AS TIME)-CAST('09:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_start)<9 THEN CAST(t1.t_stop AS TIME)-CAST('09:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_stop)>9 THEN CAST('10:00:00' AS TIME)-CAST(t1.t_start AS TIME)
ELSE CAST(t1.t_stop AS TIME)-CAST(t1.t_start AS TIME) END
ELSE 0
END)/60 AS Between09and10,
(CASE
WHEN EXTRACT(HOUR FROM t1.t_start)<=10 and EXTRACT(HOUR FROM t1.t_stop)>=10
THEN CASE WHEN EXTRACT(HOUR FROM t1.t_start)<10 and EXTRACT(HOUR FROM t1.t_stop)>10 THEN CAST('11:00:00' AS TIME)-CAST('10:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_start)<10 THEN CAST(t1.t_stop AS TIME)-CAST('10:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_stop)>10 THEN CAST('11:00:00' AS TIME)-CAST(t1.t_start AS TIME)
ELSE CAST(t1.t_stop AS TIME)-CAST(t1.t_start AS TIME) END
ELSE 0
END)/60 AS Between10and11,
(CASE
WHEN EXTRACT(HOUR FROM t1.t_start)<=11 and EXTRACT(HOUR FROM t1.t_stop)>=11
THEN CASE WHEN EXTRACT(HOUR FROM t1.t_start)<11 and EXTRACT(HOUR FROM t1.t_stop)>11 THEN CAST('12:00:00' AS TIME)-CAST('11:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_start)<11 THEN CAST(t1.t_stop AS TIME)-CAST('11:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_stop)>11 THEN CAST('12:00:00' AS TIME)-CAST(t1.t_start AS TIME)
ELSE CAST(t1.t_stop AS TIME)-CAST(t1.t_start AS TIME) END
ELSE 0
END)/60 AS Between11and12,
(CASE
WHEN EXTRACT(HOUR FROM t1.t_start)<=12 and EXTRACT(HOUR FROM t1.t_stop)>=12
THEN CASE WHEN EXTRACT(HOUR FROM t1.t_start)<12 and EXTRACT(HOUR FROM t1.t_stop)>12 THEN CAST('13:00:00' AS TIME)-CAST('12:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_start)<12 THEN CAST(t1.t_stop AS TIME)-CAST('12:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_stop)>12 THEN CAST('13:00:00' AS TIME)-CAST(t1.t_start AS TIME)
ELSE CAST(t1.t_stop AS TIME)-CAST(t1.t_start AS TIME) END
ELSE 0
END)/60 AS Between12and13,
(CASE
WHEN EXTRACT(HOUR FROM t1.t_start)<=13 and EXTRACT(HOUR FROM t1.t_stop)>=13
THEN CASE WHEN EXTRACT(HOUR FROM t1.t_start)<13 and EXTRACT(HOUR FROM t1.t_stop)>13 THEN CAST('14:00:00' AS TIME)-CAST('13:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_start)<13 THEN CAST(t1.t_stop AS TIME)-CAST('13:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_stop)>13 THEN CAST('14:00:00' AS TIME)-CAST(t1.t_start AS TIME)
ELSE CAST(t1.t_stop AS TIME)-CAST(t1.t_start AS TIME) END
ELSE 0
END)/60 AS Between13and14,

...
...
...

(CASE
WHEN EXTRACT(HOUR FROM t1.t_start)<=7 and EXTRACT(HOUR FROM t1.t_stop)>=7
THEN CASE WHEN EXTRACT(HOUR FROM t1.t_start)<7 and EXTRACT(HOUR FROM t1.t_stop)>7 THEN CAST('08:00:00' AS TIME)-CAST('07:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_start)<7 THEN CAST(t1.t_stop AS TIME)-CAST('07:00:00' AS TIME)
WHEN EXTRACT(HOUR FROM t1.t_stop)>7 THEN CAST('08:00:00' AS TIME)-CAST(t1.t_start AS TIME)
ELSE CAST(t1.t_stop AS TIME)-CAST(t1.t_start AS TIME) END
ELSE 0
END)/60 AS Between07and08

from table1 t1
where (wt.state_id = 1) and ( -- статус 1, т.е. в работе, другие не трогаем
cast(t1.t_start as Date) = :D and
cast(t1.t_stop as Date) = :D
)

group by
u1.user_name,
Between00and01,
Between01and02,
Between02and03,
Between03and04,
Between04and05,
Between05and06,
Between06and07,
Between07and08,
Between08and09,
Between09and10,
Between10and11,
Between11and12,
Between12and13,
Between13and14,
Between14and15,
Between15and16,
Between16and17,
Between17and18,
Between18and19,
Between19and20,
Between20and21,
Between21and22,
Between22and23,
Between23and00

into
:USER_NAME,
:"08:00 - 09:00",
:"09:00 - 10:00",
:"10:00 - 11:00",
:"11:00 - 12:00",
:"12:00 - 13:00",
:"13:00 - 14:00",
:"14:00 - 15:00",
:"15:00 - 16:00",
:"16:00 - 17:00",
:"17:00 - 18:00",
:"18:00 - 19:00",
:"19:00 - 20:00",
:"20:00 - 21:00",
:"21:00 - 22:00",
:"22:00 - 23:00",
:"23:00 - 00:00",
:"00:00 - 01:00",
:"01:00 - 02:00",
:"02:00 - 03:00",
:"03:00 - 04:00",
:"04:00 - 05:00",
:"05:00 - 06:00",
:"06:00 - 07:00",
:"07:00 - 08:00"

do
begin
suspend;
end
end

Вывод:

Цитата:

USER_NAME 08:00 - 09:00 09:00 - 10:00 10:00 - 11:00 11:00 - 12:00 12:00 - 13:00 13:00 - 14:00 14:00 - 15:00 15:00 - 16:00 16:00 - 17:00 17:00 - 18:00 18:00 - 19:00 19:00 - 20:00 20:00 - 21:00 21:00 - 22:00 22:00 - 23:00 23:00 - 00:00 00:00 - 01:00 01:00 - 02:00 02:00 - 03:00 03:00 - 04:00 04:00 - 05:00 05:00 - 06:00 06:00 - 07:00 07:00 - 08:00
Сотрудник 1 0 0 0 0 0 0 0 0 0 28 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Сотрудник 1 0 0 0 0 0 0 0 27 60 26 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Сотрудник 1 0 0 0 0 0 42 60 27 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Сотрудник 1 0 0 0 0 39 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Сотрудник 1 0 0 0 7 7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Сотрудник 1 0 0 48 52 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Сотрудник 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Сотрудник 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Сотрудник 1 0 13 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Я понимаю, что надо сложить результаты, для того, чтобы вывести все в одну строку, но как это сделать, увы, ума не хватает

Чуть поправил - пока все для одного сотрудника, в запрос ввожу соответственно :user id - Сотрудник1 и в :D дату

27.03.2018, 10:20	#1
aford Пользователь Регистрация: 27.03.2018 Сообщений: 13	IBExpert. Вычисление среднего значения за час Добрый день! Подскажите пожалуйста, как в IBExpert сделать процедуру в базе, которая возвращает среднее значение работы сотрудника за час? Имеется таблица вида: USER_ID T_START T_STOP Сотрудник 1 26.03.2018 11:24:11 26.03.2018 11:27:48 Сотрудник 1 26.03.2018 11:27:53 26.03.2018 11:31:54 Сотрудник 1 26.03.2018 11:31:54 26.03.2018 11:42:50 Сотрудник 1 26.03.2018 12:59:00 26.03.2018 13:24:00 Где T_START - начало работы, T_STOP соответственно конец. В итоге нужно получить таблицу следующего вида: USER_ID 10:00-11:00 11:00-12:00 12:00-13:00 13:00-14:00 и так до 18:00 Сотрудник1 0 ~18/60 1/60 24/60

27.03.2018, 11:44	#2
Аватар Старожил Регистрация: 17.11.2010 Сообщений: 18,922	А где среднее? Тут просто за каждый час просуммировать нужно. Так, например, во вложенном запросе выдернуть в секундах время с 12:00 до 13:00. Для других интервалов аналогично. Во внешнем просуммировать эти секунды. И отображай как удобно. Раздели на 60 получишь примерно минуты Код: `CASE WHEN EXTRACT(HOUR FROM T_START)<=12 and EXTRACT(HOUR FROM T_STOP)>=12 THEN CASE WHEN EXTRACT(HOUR FROM T_START)<12 and EXTRACT(HOUR FROM T_STOP)>12 THEN CAST('13:00:00' AS TIME)-CAST('12:00:00' AS TIME) WHEN EXTRACT(HOUR FROM T_START)<12 THEN CAST(T_STOP AS TIME)-CAST('12:00:00' AS TIME) WHEN EXTRACT(HOUR FROM T_STOP)>12 THEN CAST('13:00:00' AS TIME)-CAST(T_START AS TIME) ELSE CAST(T_STOP AS TIME)-CAST(T_START AS TIME) END ELSE 0 END AS Between12and13,` Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию

27.03.2018, 13:24	#5
Аватар Старожил Регистрация: 17.11.2010 Сообщений: 18,922	Аналогично не значит точно так же, там полно констант в примере 12 и 13 и это для интервала с 12 до 13. Как думаешь какие константы должны быть например для интервала с 7 до 8? Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию

29.03.2018, 09:14	#9
Аватар Старожил Регистрация: 17.11.2010 Сообщений: 18,922	1. Покажи как считаешь например для интервала с 7 до 8 2. Там только группировка нужна для user_name, а еще лучше для user_id на тот случай если user_name не уникально 3. Поля Between12and13 и прочие нужно суммировать, а не группировать по ним Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию

Похожие темы
Тема	Автор	Раздел	Ответов	Последнее сообщение
программа калькулятора выполняющего вычисление среднего значения логарифмов вводимых чисел и величины снеднеквадратического отклонения	Andreibond	Помощь студентам	1	22.12.2016 20:21
Вычисление среднего значения	Fil_1990	C# (си шарп)	13	08.10.2015 16:11
Вычисление среднего значения в формате дд/мм/гг	aMuratovna	Microsoft Office Excel	5	05.03.2015 16:09
Вычисление среднего значения	roxy7	Microsoft Office Excel	2	13.12.2011 19:41
Вычисление среднего значения из нескольких полей одной записи	yellowviolet	Microsoft Office Access	4	16.11.2011 22:55

29.03.2018, 08:50	#7
aford Пользователь Регистрация: 27.03.2018 Сообщений: 13	Вопрос все еще актуален...