Cреднее количество рабочих часов за день

280

22 марта 2017, 16:14

Пример таблицы:

+-----------+-----------+---------------------+
| Empl_Name | Direction | Date_Time           |
+-----------+-----------+---------------------+
| a1        |         1 | 2017-03-18 10:00:00 |
| a1        |         0 | 2017-03-18 11:00:00 |
| a1        |         1 | 2017-03-19 12:00:00 |
| a1        |         0 | 2017-03-19 13:00:00 |
| a2        |         1 | 2017-03-19 07:00:00 |
| a2        |         0 | 2017-03-19 07:00:20 |
| a2        |         1 | 2017-03-19 14:00:00 |
| a2        |         0 | 2017-03-19 14:00:40 |
| a3        |         1 | 2017-03-19 10:00:00 |
| a3        |         0 | 2017-03-19 10:00:10 |
+-----------+-----------+---------------------+

Пример ответа: a1 проводит на рабочем месте 7200/30 секунд в месяц и у него нет длинных перерывов, a2 проводит 60/30 secs нет длинных перерывов, a3 проводит 10/30 секунд нет длинных перерывов.

Upd: Вот пример моего решения, но явно можно проще

                    FROM empl sub
                    WHERE sub.Date_Time <= t.Date_Time
                          AND sub.Empl_Name = t.Empl_Name
                          AND sub.Direction = 1)        AS TimeInRank
                 FROM empl t
                 WHERE t.Direction = 1) AS ins
                INNER JOIN
                (SELECT
                   t.Empl_Name,
                   DATE_FORMAT(t.Date_Time, '%Y-%m-%d') AS `TimeInDate`,
                   t.Date_Time                          AS `Date_Time_Out`,
                   (SELECT COUNT(*)
                    FROM empl sub
                    WHERE sub.Date_Time <= t.Date_Time
                          AND sub.Empl_Name = t.Empl_Name
                          AND sub.Direction = 0)        AS TimeOutRank
                 FROM empl t
                 WHERE t.Direction = 0) AS outs
                  ON ins.Empl_Name = outs.Empl_Name AND ins.TimeInRank = outs.TimeOutRank
                     AND DATE_FORMAT(ins.Date_Time_In, '%Y-%m-%d') = DATE_FORMAT(outs.Date_Time_Out, '%Y-%m-%d')
              WHERE Date_Time_In BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(
                  LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
              GROUP BY ins.Empl_Name, DATE_FORMAT(ins.Date_Time_In, '%Y-%m-%d')
            ) t1
       GROUP BY Empl_Name
     ) main LEFT JOIN (SELECT
                         s1.Empl_Name,
                         1 AS Has_Long_Breaks
                       FROM (
                              SELECT
                                ins.Empl_Name,
                                ins.TimeInDate,
                                TimeInRank,
                                TimeOutRank,
                                Date_Time_In,
                                Date_Time_Out
                              FROM
                                (SELECT
                                   t.Empl_Name,
                                   DATE_FORMAT(t.Date_Time, '%Y-%m-%d') AS `TimeInDate`,
                                   t.Date_Time                          AS `Date_Time_In`,
                                   (SELECT COUNT(*)
                                    FROM empl sub
                                    WHERE sub.Date_Time <= t.Date_Time
                                          AND sub.Empl_Name = t.Empl_Name
                                          AND sub.Direction = 1)        AS TimeInRank
                                 FROM empl t
                                 WHERE t.Direction = 1) AS ins
                                JOIN
                                (SELECT
                                   t.Empl_Name,
                                   DATE_FORMAT(t.Date_Time, '%Y-%m-%d') AS `TimeInDate`,
                                   t.Date_Time                          AS `Date_Time_Out`,
                                   (SELECT COUNT(*)
                                    FROM empl sub
                                    WHERE sub.Date_Time <= t.Date_Time
                                          AND sub.Empl_Name = t.Empl_Name
                                          AND sub.Direction = 0)        AS TimeOutRank
                                 FROM empl t
                                 WHERE t.Direction = 0) AS outs
                                  ON ins.Empl_Name = outs.Empl_Name AND ins.TimeInRank = outs.TimeOutRank
                                     AND DATE_FORMAT(ins.Date_Time_In, '%Y-%m-%d') =
                                         DATE_FORMAT(outs.Date_Time_Out, '%Y-%m-%d')
                              WHERE Date_Time_In BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH,
                                                                     '%Y-%m-01 00:00:00') AND DATE_FORMAT(
                                  LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
                            ) s1
                         JOIN (
                                SELECT
                                  ins.Empl_Name,
                                  ins.TimeInDate,
                                  TimeInRank,
                                  TimeOutRank,
                                  Date_Time_In,
                                  Date_Time_Out
                                FROM
                                  (SELECT
                                     t.Empl_Name,
                                     DATE_FORMAT(t.Date_Time, '%Y-%m-%d') AS `TimeInDate`,
                                     t.Date_Time                          AS `Date_Time_In`,
                                     (SELECT COUNT(*)
                                      FROM empl sub
                                      WHERE sub.Date_Time <= t.Date_Time
                                            AND sub.Empl_Name = t.Empl_Name
                                            AND sub.Direction = 1)        AS TimeInRank
                                   FROM empl t
                                   WHERE t.Direction = 1) AS ins
                                  JOIN
                                  (SELECT
                                     t.Empl_Name,
                                     DATE_FORMAT(t.Date_Time, '%Y-%m-%d') AS `TimeInDate`,
                                     t.Date_Time                          AS `Date_Time_Out`,
                                     (SELECT COUNT(*)
                                      FROM empl sub
                                      WHERE sub.Date_Time <= t.Date_Time
                                            AND sub.Empl_Name = t.Empl_Name
                                            AND sub.Direction = 0)        AS TimeOutRank
                                   FROM empl t
                                   WHERE t.Direction = 0) AS outs
                                    ON ins.Empl_Name = outs.Empl_Name AND ins.TimeInRank = outs.TimeOutRank
                                       AND DATE_FORMAT(ins.Date_Time_In, '%Y-%m-%d') =
                                           DATE_FORMAT(outs.Date_Time_Out, '%Y-%m-%d')
                                WHERE Date_Time_In BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH,
                                                                       '%Y-%m-01 00:00:00') AND DATE_FORMAT(
                                    LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
                              ) s2 ON s1.Empl_Name = s2.Empl_Name
                       WHERE s1.TimeInDate = s2.TimeInDate AND s1.TimeInRank = s2.TimeOutRank + 1 AND
                             TIMESTAMPDIFF(SECOND, s2.Date_Time_Out, s1.Date_Time_In) > 3600) longBreaks
    ON main.Empl_Name = longBreaks.Empl_Name;

Answer 1

Сделаем на стандартном SQL, функции работы с временем в нотации MySQL (во всех СУБД они свои).

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

select empl_name,month,
       sum(outp-inp) work_time,
       sum(ninp-outp>=3600) big_break
  from (
select empl_name, EXTRACT(YEAR_MONTH FROM date_time) as month,
       unix_timestamp(date_time) inp,
       (select unix_timestamp(min(date_time))
          from empl b
         where b.empl_name=a.empl_name and b.direction=0
           and b.date_time>a.date_time
       ) outp,
       (select unix_timestamp(min(date_time))
          from empl b
         where b.empl_name=a.empl_name and b.direction=1
           and b.date_time>a.date_time
           and to_days(b.date_time)=to_days(a.date_time)
       ) ninp
  from empl a
 where a.direction=1
) A
group by empl_name,month

Я решил выводить просто суммы времени работы и количество долгих отсутствий. Как считать среднее за месяц сами прикиньте, возможно надо сгруппировать сначала до дня, получить время работы в день и потом взять из них среднее (avg()), может еще как, смотря что такое "среднее за месяц". Получение количества больших перерывов выполнено в традиции MySQL (sum(ninp-outp>=3600)), для "стандартной" СУБД надо применять case when.