Erstellen Sie aufeinanderfolgende Monate

Question

Ich versuche, eine Datenbank zu erstellen, in der die Läufe jedes Kunden basierend auf aufeinanderfolgenden monatlichen Läufen zusammengefasst werden. Der folgende Code erstellt eine Tabelle, die folgendermaßen aussieht:

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

Ich hätte gerne folgendes Endergebnis:

Wie kann ich aufeinanderfolgende Monate nehmen, sie bei einer Unterbrechung in eine einzelne Zeile aufteilen und über mehrere Kunden gruppieren?

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

Könnten Sie den gesamten Code posten?

Ja.

Ich veröffentliche den detaillierten Code Schritt für Schritt. Wenn Sie möchten, können Sie ihn durch die Kombination einzelner Schritte reduzieren.

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

Geige