Создать последовательные месяцы

Question

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

drop table RUNNING_LOG;
create table running_log ( 
  run_date          date not null,  
  time_in_seconds   int  not null, 
  distance_in_miles int  not null,
  SERV_YRMO         VARCHAR2(6)
)

begin  
  insert into running_log values (date'2018-01-01', 420, 1,'201801');  
  insert into running_log values (date'2018-01-02', 2400, 5,'201801');  
  insert into running_log values (date'2018-01-03', 2430, 5,'201801');  
  insert into running_log values (date'2018-01-06', 2350, 5,'201801');  
  insert into running_log values (date'2018-02-07', 410, 1,'201802');  
  insert into running_log values (date'2018-02-10', 400, 1,'201802');  
  insert into running_log values (date'2018-02-13', 2300, 5,'201802');  
  insert into running_log values (date'2018-03-14', 425, 1,'201803');  
  insert into running_log values (date'2018-03-15', 422, 1,'201803');  
  commit;  
end;

select * from running_log

Я бы хотел, чтобы конечный результат был таким:

Каким образом я могу взять последовательные месяцы, разбить их в один ряд, где есть перерыв, и сгруппировать по нескольким клиентам?

with grps as (  
  select run_date
        , row_number() over (order by run_date) rn ,  
         run_date - row_number() over (order by run_date) grp_date  
  from   running_log  
)  
  select min(run_date) first_run, max(run_date) last_run,   
         count(*) runs,   
         row_number() over (order by min(run_date)) grp  
  from   grps  
  group  by grp_date  
  order  by min(run_date)

Answer 1

Не могли бы вы опубликовать полный код?

Да.

Я публикую подробный код, шаг за шагом. Если у вас есть желание, вы можете сократить его, объединив некоторые отдельные шаги.

WITH 
-- calculating data period borders
cte1 (mindate, maxdate) 
     AS ( SELECT ROUND(MIN(run_date), 'MONTH'),
                 ROUND(MAX(run_date), 'MONTH')
          FROM running_log ),
-- generating months list
cte2 (run_month) 
     AS (    SELECT mindate
             FROM cte1
         UNION ALL
             SELECT ADD_MONTHS(cte2.run_month, 1)
             FROM cte1, cte2 
             WHERE cte2.run_month < cte1.maxdate ),
-- marking empty months
cte3 (run_month, isempty)
     AS ( SELECT cte2.run_month, CASE WHEN 0 = COUNT(running_log.run_date) 
                                      THEN 1
                                      ELSE 0 END
          FROM cte2
          LEFT JOIN running_log 
              ON ROUND(running_log.run_date, 'MONTH') = cte2.run_month
          GROUP BY cte2.run_month ),
-- calculating group numbers for months
cte4 (run_month, group_num)
     AS ( SELECT run_month, SUM(isempty) OVER (ORDER BY run_month ASC)
          FROM cte3 ),
-- assigning group numbers to separate records
cte5 (run_date, time_in_seconds, distance_in_miles, SERV_YRMO, group_num)
     AS ( SELECT running_log.run_date, running_log.time_in_seconds, running_log.distance_in_miles, running_log.SERV_YRMO, cte4.group_num
          FROM cte4
          LEFT JOIN running_log 
              ON ROUND(running_log.run_date, 'MONTH') = cte4.run_month )
-- obtaining desured output
SELECT MIN(SERV_YRMO) "Min", MAX(SERV_YRMO) "Max", SUM(distance_in_miles) Runs, SUM(time_in_seconds) Seconds
FROM cte5
GROUP BY group_num
ORDER BY group_num

скрипка