【SQL分享】如何在oracle找到前12个月连续四个月的平均消费金额

表格结构

月份消费金额01/04/171000001/05/171000001/06/171000001/07/171300001/08/171100001/09/171200001/10/171000001/11/1712000

期望结果

日期区间平均消费金额04/17 - 07/171075005/17 - 08/171125006/17 - 09/171175007/17 - 10/171225008/17 - 11/171275009/17 - 12/171325010/17 - 01/181375011/17 - 02/181425012/17 - 03/1814750

回答

使用connect by level获取十二个月资料使用ADD_MONTHS将目前时间(sysdate)-(level+3)跟(level-1)取得连续四个月资料使用group + avg 取得平均资料

with CTE as (    select         --使用ADD_MONTHS-3跟-1取得连续四个月资料        TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- (level+3)),'YYYY/MM/')||'01','yyyy/mm/dd') sDate        ,TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- level),'YYYY/MM/')||'01','yyyy/mm/dd') eDate        ,level     from dual     --使用`connect by level`获取十二个月资料    connect by level <=12 ),CTE2 as (  select *   from CTE T1  left join T T2 on cast(T2."Month" as date) between T1.sDate and T1.eDate)select TO_CHAR(sDate,'MM/YY') || ' - ' || TO_CHAR(eDate,'MM/YY') as "Consecutive Months"  ,round(avg("Consumption")) as "Avg. of 4 Months Consumption"from CTE2group by sDate,eDateorder by eDate

得到结果

Consecutive MonthsAvg. of 4 Months Consumption04/17 - 07/171075005/17 - 08/171100006/17 - 09/171150007/17 - 10/171150008/17 - 11/171125009/17 - 12/171133310/17 - 01/181100011/17 - 02/181200012/17 - 03/18(null)01/18 - 04/18(null)02/18 - 05/18(null)03/18 - 06/18(null)

Test DDL:

CREATE TABLE T    ("Month" timestamp, "Consumption" int);INSERT ALL     INTO T ("Month", "Consumption")         VALUES ('01-Apr-2017 12:00:00 AM', 10000)    INTO T ("Month", "Consumption")         VALUES ('01-May-2017 12:00:00 AM', 10000)    INTO T ("Month", "Consumption")         VALUES ('01-Jun-2017 12:00:00 AM', 10000)    INTO T ("Month", "Consumption")         VALUES ('01-Jul-2017 12:00:00 AM', 13000)    INTO T ("Month", "Consumption")         VALUES ('01-Aug-2017 12:00:00 AM', 11000)    INTO T ("Month", "Consumption")         VALUES ('01-Sep-2017 12:00:00 AM', 12000)    INTO T ("Month", "Consumption")         VALUES ('01-Oct-2017 12:00:00 AM', 10000)    INTO T ("Month", "Consumption")         VALUES ('01-Nov-2017 12:00:00 AM', 12000)SELECT * FROM dual;

线上测试连结

---

假如大大们有更好做法，都可以留言讨论。