在SQL中根据四分之一月获取数据 [英] Fetch data based on month of a quarter in sql

问题描述

我有一张桌子:

Month      Data1    Data2

Jan-17       2       3
Feb-17       3       4
Mar-17       2       3
Apr-17       4       1
May-17       1       2

输出应为:

Month    Data
Jan-17   Data2(Jan)+Data2(Feb)+Data2(Mar)(3+4+3)
Feb-17   Data1(Jan)+Data2(Feb)+Data2(Mar)(2+4+3)
Mar-17   Data1(Jan)+Data1(Feb)+Data2(Mar)(2+3+3)

季度从1月开始，如果月份是季度的第一个月，则第一个月的输出应为:第一个月的Data1 +接下来两个月的Data2

Quarter starts from Jan, if the month is first month of quarter , output for first month should be should be :Data1 for firstmonth+Data2 for next 2 months

如果月份是季度的第二个月，则输出应为上个月的Data1，第二个月的data2 +第三个月的data2

If the month is 2nd month of quarter , output should be Data1 from last month and data2 for 2nd month+data2 for 3rd month

如果月份是季度的第三个月，则第三个月的输出应该是第一个月的Data1 +第二个月的Data1 +第三个月的Data2.

If the month is 3rd month of quarter , output for 3rd month should be Data1 from first month+Data1 from 2nd month +Data2 for 3rd month .

我正在使用oracle数据库. 有人可以帮忙吗.

I am using oracle database . Can someone help .

推荐答案

您需要这样的查询.确保在将来的日期或过去的日期缺少记录时，对Data1和Data2进行NULL检查，并在必要时将其默认设置为零.

You need a query like this. Make sure you include NULL checks for Data1 ,Data2 when records are missing for future dates or past dates and default it to zero if necessary.

select To_CHAR(Month,'MON-YY') Month,
        CASE 
                WHEN MOD ( EXTRACT ( MONTH FROM Month ), 3 ) = 1 
                THEN Data2 + LEAD ( Data2 ,1) OVER ( ORDER BY Month ) + LEAD ( Data2 ,2) OVER ( ORDER BY Month )
                WHEN MOD ( EXTRACT ( MONTH FROM Month ), 3 ) = 2 
                THEN LAG(Data1,1) OVER ( ORDER By Month ) + Data2 + LEAD ( Data2 ,1) OVER ( ORDER BY Month )
                WHEN MOD ( EXTRACT ( MONTH FROM Month ), 3 ) = 0 
                THEN LAG(Data1,2) OVER ( ORDER By Month ) + LAG(Data1,1) OVER ( ORDER By Month )+Data2
        END data
FROM 
        Table1;

SQLFiddle

这篇关于在SQL中根据四分之一月获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！