Crie meses consecutivos

Question

Estou tentando criar um banco de dados onde as execuções de cada cliente são resumidas com base em execuções mensais consecutivas. O código abaixo constrói uma tabela semelhante a esta:

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

Gostaria que o resultado final fosse:

De que forma posso dividir meses consecutivos em uma única linha onde há uma pausa e agrupar vários clientes?

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

Você poderia postar o código total?

Sim.

Publico código detalhado, passo a passo. Se desejar, você pode reduzi-lo combinando algumas etapas individuais.

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

violino