我正在尝试创建一个数据库,其中每个客户端的运行情况都基于连续的每月运行进行汇总。下面的代码构建了一个如下所示的表:
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