연속 월 생성

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

깡깡이