Подсчитайте количество событий 7 утра или 7 вечера между двумя датами

Есть ли простой способ сделать это? Под полным промежутком времени я имею в виду не считать 7 утра или 7 вечера, которые равны времени начала или окончания.

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

Я рад использовать что-то в PLSQL или обычном SQL.

Примеры: 

start             end               num_7am_7pm_between_dates
2012-06-16 05:00  2012-06-16 08:00  1  
2012-06-16 16:00  2012-06-16 20:00  1
2012-06-16 05:00  2012-06-16 07:00  0
2012-06-16 07:00  2012-06-16 19:00  0
2012-06-16 08:00  2012-06-16 15:00  0
2012-06-16 05:00  2012-06-16 19:01  2
2012-06-16 05:00  2012-06-18 20:00  6
4 5
sql oracle11g plsql

4 ответа:

Я думаю, что это можно было бы сократить еще больше, но у меня нет Oracle в моем распоряжении, чтобы полностью протестировать этот Oracle SQL: 

SELECT StartDate
     , EndDate
     , CASE WHEN TRUNC(EndDate) - TRUNC(StartDate) < 1
             AND TO_CHAR(EndDate, 'HH24') > 19
             AND TO_CHAR(StartDate, 'HH24') < 7
            THEN 2
            WHEN TRUNC(EndDate) - TRUNC(StartDate) < 1
             AND (TO_CHAR(EndDate, 'HH24') > 19
              OR  TO_CHAR(StartDate, 'HH24') < 7)
            THEN 1
            WHEN TRUNC(EndDate) - TRUNC(StartDate) > 0
             AND TO_CHAR(EndDate, 'HH24') > 19
             AND TO_CHAR(StartDate, 'HH24') < 7
            THEN 2 + ((TRUNC(EndDate) - TRUNC(StartDate)) * 2)
            WHEN TRUNC(EndDate) - TRUNC(StartDate) > 0
             AND TO_CHAR(EndDate, 'HH24') > 19
              OR TO_CHAR(StartDate, 'HH24') < 7
            THEN 1 + ((TRUNC(EndDate) - TRUNC(StartDate)) * 2)
            ELSE 0
       END
FROM MyTable;

Благодаря @A. B. Cade для скрипки, похоже, что моя логика случая может быть сокращена дальше:

SELECT SDate
     , EDate
     , CASE WHEN TO_CHAR(EDate, 'HH24') > 19
             AND TO_CHAR(SDate, 'HH24') < 7
            THEN 2 + ((TRUNC(EDate) - TRUNC(SDate)) * 2)
            WHEN TO_CHAR(EDate, 'HH24') > 19
              OR TO_CHAR(SDate, 'HH24') < 7
            THEN 1 + ((TRUNC(EDate) - TRUNC(SDate)) * 2)
            ELSE 0
       END AS MyCalc2
  FROM MyTable;
3

Я с удовольствием написал следующее решение: 

with date_range as (
  select min(sdate) as sdate, max(edate) as edate
  from t
),
all_dates as (
  select sdate + (level-1)/24 as hour
  from date_range
  connect by level <= (edate-sdate) * 24 + 1
),
counts as (
  select t.id, count(*) as c
  from all_dates, t
  where to_char(hour, 'HH') = '07'
  and hour > t.sdate and hour < t.edate
  group by t.id
)
select t.sdate, t.edate, nvl(counts.c, 0)
from t, counts
where t.id = counts.id(+)
order by t.id;

Я добавил столбец id в таблицу на случай, если диапазон дат не уникален.

Http://www.sqlfiddle.com/#!4 / 5fa19/13

3

Это может не иметь лучшей производительности, но может работать для вас: 

select sdate, edate, count(*)
  from (select distinct edate, sdate, sdate + (level / 24) hr
          from t
        connect by sdate + (level / 24) <= edate )
 where to_char(hr, 'hh') = '07'
group by sdate, edate

UPDATE: Что касается комментария @FlorinGhita-исправлен запрос на включение нулевых случаев 

select sdate, edate, sum( decode(to_char(hr, 'hh'), '07',1,0))
  from (select distinct edate, sdate, sdate + (level / 24) hr
          from t
        connect by sdate + (level / 24) <= edate )
group by sdate, edate
2

Сделайте так (в SQL) 

declare  @table table ( start datetime, ends datetime) 
insert into @table select'2012-06-16 05:00','2012-06-16 08:00'  --1  
insert into @table select'2012-06-16 16:00','2012-06-16 20:00'  --1
insert into @table select'2012-06-16 05:00','2012-06-16 07:00'  --0
insert into @table select'2012-06-16 07:00','2012-06-16 19:00'  --0
insert into @table select'2012-06-16 08:00','2012-06-16 15:00'  --0
insert into @table select'2012-06-16 05:00','2012-06-16 19:01'  --2
insert into @table select'2012-06-16 05:00','2012-06-18 20:00'  --6 
insert into @table select'2012-06-16 07:00','2012-06-18 07:00'  --3


Declare @From DATETIME 
Declare @To DATETIME 

select @From = MIN(start) from @table 
select @To = max(ends) from @table 

;with CTE AS 
( 
      SELECT distinct 
    DATEADD(DD,DATEDIFF(D,0,start),0)+'07:00' AS AimTime
    FROM @table 
 ),CTE1 AS 
 ( 
    Select AimTime
    FROM CTE 

    UNION ALL 

    Select DATEADD(hour, 12, AimTime)
From CTE1 
WHERE AimTime< @To 
 )


 select start,ends, count(AimTime)
 from CTE1 right join @table t 
 on t.start < CTE1.AimTime and t.ends > CTE1.AimTime
 group by start,ends
1