OVER(PARTITION BY）的使用

最新推荐文章于 2022-01-04 18:44:03 发布

maggiejin

最新推荐文章于 2022-01-04 18:44:03 发布

阅读量557

点赞数

分类专栏： oracle 文章标签： branch 网格 bi

本文链接：https://blog.csdn.net/maggiejin/article/details/2774732

版权

oracle 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"> rel="themeData" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"> rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml">

SELECT T.季度,
       T.月份,
       T.免票张数,
       T.优惠票张数,
       T.免票抵扣金额,
       T.优惠票抵扣金额,
       T.总额度,
       T.总额度 - SUM(T.额度) OVER(PARTITION BY T.季度 ORDER BY T.月份) 额度剩余
FROM (SELECT CASE SUBSTR(B.BATCH_MONTH, 5, 2)
                 WHEN '01' THEN
                  '一季度'
                 WHEN '02' THEN
                  '一季度'
                 WHEN '03' THEN
                  '一季度'
                 WHEN '04' THEN
                  '二季度'
                 WHEN '05' THEN
                  '二季度'
                 WHEN '06' THEN
                  '二季度'
                 WHEN '07' THEN
                  '三季度'
                 WHEN '08' THEN
                  '三季度'
                 WHEN '09' THEN
                  '三季度'
                 WHEN '10' THEN
                  '四季度'
                 WHEN '11' THEN
                  '四季度'
                 WHEN '12' THEN
                  '四季度'
               END 季度,
               SUBSTR(B.BATCH_MONTH, 5, 2) 月份,
               SUM(DECODE(B.BATCH_TYPE, 0, 1, 0)) 免票张数,
               SUM(DECODE(B.BATCH_TYPE, 1, 1, 0)) 优惠票张数,
               SUM(DECODE(B.BATCH_TYPE, 0, 1, 0) * B.COUNTERACT_AMONT) 免票抵扣金额,
               SUM(DECODE(B.BATCH_TYPE, 1, 1, 0) * B.COUNTERACT_AMONT) 优惠票抵扣金额,
               T.YEAE_MONEY / 4 AS 总额度,
               SUM(B.COUNTERACT_AMONT) AS 额度,
               ROUND(SUM(B.COUNTERACT_AMONT) * 4 / T.YEAE_MONEY, 6) AS 占用比例
          FROM BRANCH_FREETICKET_YEARMONEY T,
               BATCHLIST_COUPON_ITEM       BI,
               BATCHLIST_COUPON            B
         WHERE T.BRANCH_CODE = B.BRANCH_CODE
           AND T.YEARS = '2008'
           AND BI.BATCH_CODE = B.BATCH_CODE
           AND B.BATCH_TYPE_ITEM IN ('3', '4')
           AND B.BRANCH_CODE = '08330015'
         GROUP BY T.YEAE_MONEY, B.BATCH_MONTH) T

查询结果为：
rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"> rel="themeData" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"> rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml">

	季度	月份	免票张数	优惠票张数	免票抵扣金额	优惠票抵扣金额	总额度	额度剩余
1	二季度	05	2	0	5960	0	40670	34710
2	二季度	06	8	2	13410	2680	40670	18620
3	三季度	07	1	0	2980	0	40670	37690
4	三季度	09	0	1	0	1340	40670	36350

FROM语句

SELECT CASE SUBSTR(B.BATCH_MONTH, 5, 2)
                 WHEN '01' THEN
                  '一季度'
                 WHEN '02' THEN
                  '一季度'
                 WHEN '03' THEN
                  '一季度'
                 WHEN '04' THEN
                  '二季度'
                 WHEN '05' THEN
                  '二季度'
                 WHEN '06' THEN
                  '二季度'
                 WHEN '07' THEN
                  '三季度'
                 WHEN '08' THEN
                  '三季度'
                 WHEN '09' THEN
                  '三季度'
                 WHEN '10' THEN
                  '四季度'
                 WHEN '11' THEN
                  '四季度'
                 WHEN '12' THEN
                  '四季度'
               END 季度,
               SUBSTR(B.BATCH_MONTH, 5, 2) 月份,
               SUM(DECODE(B.BATCH_TYPE, 0, 1, 0)) 免票张数,
               SUM(DECODE(B.BATCH_TYPE, 1, 1, 0)) 优惠票张数,
               SUM(DECODE(B.BATCH_TYPE, 0, 1, 0) * B.COUNTERACT_AMONT) 免票抵扣金额,
               SUM(DECODE(B.BATCH_TYPE, 1, 1, 0) * B.COUNTERACT_AMONT) 优惠票抵扣金额,
               T.YEAE_MONEY / 4 AS 总额度,
               SUM(B.COUNTERACT_AMONT) AS 额度,
               ROUND(SUM(B.COUNTERACT_AMONT) * 4 / T.YEAE_MONEY, 6) AS 占用比例
          FROM BRANCH_FREETICKET_YEARMONEY T,
               BATCHLIST_COUPON_ITEM       BI,
               BATCHLIST_COUPON            B
         WHERE T.BRANCH_CODE = B.BRANCH_CODE
           AND T.YEARS = '2008'
           AND BI.BATCH_CODE = B.BATCH_CODE
           AND B.BATCH_TYPE_ITEM IN ('3', '4')
           AND B.BRANCH_CODE = '08330015'
         GROUP BY T.YEAE_MONEY, B.BATCH_MONTH

执行结果为：
rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"> rel="themeData" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"> rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Cjiny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml">

	季度	月份	免票张数	优惠票张数	免票抵扣金额	优惠票抵扣金额	总额度	额度	占用比例
1	三季度	09	0	1	0	1340	40670	1340	0.032948
2	三季度	07	1	0	2980	0	40670	2980	0.073273
3	二季度	05	2	0	5960	0	40670	5960	0.146545
4	二季度	06	8	2	13410	2680	40670	16090	0.395623