Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Web программирование > SQL, базы данных
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 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
aford вне форума Ответить с цитированием
Старый 27.03.2018, 11:44   #2
Аватар
Старожил
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Сообщений: 19,042
По умолчанию

А где среднее? Тут просто за каждый час просуммировать нужно. Так, например, во вложенном запросе выдернуть в секундах время с 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, 11:51   #3
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

сделать ВЫЧИСЛЕНИЕ года, дня(от начала года) и часа(от начала дня)
как вариант число ЧАСОВ от заранее заданного(известного) момента.
группировка по данному значению(-ям)
и как результат AVG среднее за год+день+час(за час отстоящий от заданного на К шагов).
для FireBird
Цитата:
DATEDIFF ()
Функция возвращает количество лет, месяцев, недель, дней, часов, минут,
секунд или миллисекунд между двумя значениями даты/времени. Тип
возвращаемого значения BIGINT.
программа — запись алгоритма на языке понятном транслятору
evg_m вне форума Ответить с цитированием
Старый 27.03.2018, 13:01   #4
aford
Пользователь
 
Регистрация: 27.03.2018
Сообщений: 13
По умолчанию

Цитата:
Сообщение от Аватар Посмотреть сообщение
А где среднее? Тут просто за каждый час просуммировать нужно. Так, например, во вложенном запросе выдернуть в секундах время с 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,
У меня нули выдает, может что не так сделал? Вложил код...
Вложения
Тип файла: txt 1.txt (14.1 Кб, 128 просмотров)
aford вне форума Ответить с цитированием
Старый 27.03.2018, 13:24   #5
Аватар
Старожил
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Сообщений: 19,042
По умолчанию

Аналогично не значит точно так же, там полно констант в примере 12 и 13 и это для интервала с 12 до 13. Как думаешь какие константы должны быть например для интервала с 7 до 8?
Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию
Аватар вне форума Ответить с цитированием
Старый 27.03.2018, 14:51   #6
aford
Пользователь
 
Регистрация: 27.03.2018
Сообщений: 13
По умолчанию

Цитата:
Сообщение от Аватар Посмотреть сообщение
для интервала с 7 до 8?
Константа 7?
Так у меня теперь вообще несколько строчек появилось в результате запроса, а должна быть одна..
aford вне форума Ответить с цитированием
Старый 29.03.2018, 08:50   #7
aford
Пользователь
 
Регистрация: 27.03.2018
Сообщений: 13
По умолчанию

Вопрос все еще актуален...
aford вне форума Ответить с цитированием
Старый 29.03.2018, 09:13   #8
Serge_Bliznykov
Старожил
 
Регистрация: 09.01.2008
Сообщений: 26,238
По умолчанию

Цитата:
Сообщение от aford Посмотреть сообщение
Вопрос все еще актуален...
Выложите текст своего запроса, поясните, что с ним не так, тем самым Вы повысите шанс получить ответ.
Serge_Bliznykov вне форума Ответить с цитированием
Старый 29.03.2018, 09:14   #9
Аватар
Старожил
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Сообщений: 19,042
По умолчанию

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

Вот, что у меня на данный момент:
Исходные данные
Цитата:
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 дату

Последний раз редактировалось aford; 29.03.2018 в 10:13.
aford вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
программа калькулятора выполняющего вычисление среднего значения логарифмов вводимых чисел и величины снеднеквадратического отклонения 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