☰

連続した月を作成する

tag-icon • tag-icon sql oracle-12c
連続した月を作成する

各クライアントの実行が連続した月次実行に基づいて要約されるデータベースを作成しようとしています。以下のコードは、次のようなテーブルを構築します。

ここに画像の説明を入力してください

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)

答え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

フィドル

関連情報