下面是一个查询,其中我希望周期1,2,3是动态的,因为我希望它是可配置的。 表结构
CREATE TABLE "SalesTable"
(“ ID”始终以身份身份MINVALUE 1最大值(1,0)生成,最大值1 9999999999999999999999999999从1开始增加1缓存20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, “ UNIT” VARCHAR2(50 CHAR)NOT NULL ENABLE, “ GROUP” VARCHAR2(50 CHAR)NOT NULL ENABLE, “ LOC” VARCHAR2(50 CHAR)NOT NULL ENABLE, “ POSTDATE” DATE NOT NULL ENABLE, “销售”浮标(126) 主键(“ ID”) )
select a.UNIT,a.GROUP,a.LOC,a.mindate POSTDATE,
sum(CASE WHEN a.POSTDATE =mindate THEN sales ELSE 0 END) PERIOD1,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*1)) THEN sales ELSE 0 END) PERIOD2,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*2)) THEN sales ELSE 0 END) PERIOD3,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*3)) THEN sales ELSE 0 END) PERIOD4,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*4)) THEN sales ELSE 0 END) PERIOD5,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*5)) THEN sales ELSE 0 END) PERIOD6,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*6)) THEN sales ELSE 0 END) PERIOD7,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*7)) THEN sales ELSE 0 END) PERIOD8,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*8)) THEN sales ELSE 0 END) PERIOD9
from (
select UNIT,GROUP,LOC,POSTDATE,DUR,min(POSTDATE) OVER (PARTITION BY UNIT,GROUP,LOC) mindate, sum(sales) sales
from SalesTable GROUP BY UNIT,GROUP,LOC,POSTDATE,DUR
) a
GROUP BY a.UNIT,a.GROUP,a.LOC,a.mindate;