Oracel 数据库中如果求去年同期最大值？

最新推荐文章于 2023-08-11 09:34:58 发布

入夏忆梦

最新推荐文章于 2023-08-11 09:34:58 发布

阅读量580

点赞数 1

文章标签：数据库 sql java

本文链接：https://blog.csdn.net/XikYu/article/details/129997158

版权

1：最近遇到一个需求：查询数据库表中的某一个字段，并同时要求查询该字段的去年同期最大值

首先我先解释一下什么是：去年同期最大值

解释如下：

-- 去年同期利润总额 (如果对应去年月份为空，则显示去年最新月份的利润总额,如果存在，不管它是不是最新的都选上一年对应的年份的数据)

-- 例子: 当年最新为202207 查去年202107 对应的利润总额 , 如果没有就查2022年最新的利润总额数据。
--（注：如果有2022年有一条11月的数据，有一条07 月的数据，去年同期数据取对应07月的数据，不取11月月份的数据。）

SQL脚本如下：（可能有点繁琐）

SELECT A.ORG_NO,
       A.YEAR AS 数据日期,
       A.MONTH,
       --DECODE(A.TOTAL_ASSETS, 0, 0, ROUND(NVL(A.TOTAL_DEBT, 0) / NULLIF(A.TOTAL_ASSETS, 0) * 100, 2)) AS 资产负债率,
       NVL(A.EMP_NUM,0 ) AS 职工数量,
       -- b.去年同月    c.去年最大
       COALESCE(b.EMP_NUM, c.EMP_NUM,0) AS 去年同期从业人员人数
       --NVL(A.TOTAL_DEBT, 0)                       as 当前负债总额,
       --COALESCE(b.TOTAL_DEBT, c.TOTAL_DEBT,0) AS 去年同期资产总额
       --A.STAT_TIME AS 创建时间
FROM (
         SELECT *
         FROM (
                  SELECT ROW_NUMBER()
                                 OVER (PARTITION BY TO_CHAR(DATA_TIME, 'YYYY') ORDER BY DATA_TIME DESC, STAT_TIME DESC) RN,
                         ORG_NO,
                         --当前年每月最新最新利润总额
                         EMP_NUM,
                         DATA_TIME,
                         TO_CHAR(DATA_TIME, 'YYYY') AS                                                                  YEAR,
                         TO_CHAR(DATA_TIME, 'MM')   AS                                                                  MONTH,
                         STAT_TIME
                  FROM BRAIN.DWS_HR_EMPLOYEE_STAT A
                  WHERE IS_SUM = 1
                  ORDER BY YEAR DESC
              )
         WHERE RN = 1
         ORDER BY YEAR DESC
         
     ) A
         LEFT JOIN (
    --去年同月
    SELECT *
    FROM (
             SELECT ROW_NUMBER()
                            OVER (PARTITION BY TO_CHAR(DATA_TIME, 'YYYYMM') ORDER BY DATA_TIME DESC, STAT_TIME DESC) RN,
                    DATA_TIME,
                    ORG_NO,
                    --当前年每月最新最新利润总额
                    EMP_NUM,                   
                    TO_CHAR(DATA_TIME, 'YYYY') AS                                                                    YEAR,
                    TO_CHAR(DATA_TIME, 'MM')   AS                                                                    MONTH,
                    STAT_TIME
             FROM BRAIN.DWS_HR_EMPLOYEE_STAT A
             WHERE IS_SUM = 1
             ORDER BY YEAR DESC
         )
    WHERE RN = 1
) B ON B.YEAR = A.YEAR - 1 AND A.MONTH = B.MONTH

         LEFT JOIN (
    --去年最大
    SELECT *
    FROM (
             SELECT ROW_NUMBER()
                            OVER (PARTITION BY TO_CHAR(DATA_TIME, 'YYYY') ORDER BY DATA_TIME DESC, STAT_TIME DESC) RN,
                    ORG_NO,
                    --当前年每月最新最新利润总额
                    EMP_NUM,
                    DATA_TIME,
                    TO_CHAR(DATA_TIME, 'YYYY') AS                                                                  YEAR,
                    TO_CHAR(DATA_TIME, 'MM')   AS                                                                  MONTH,
                    STAT_TIME
             FROM BRAIN.DWS_HR_EMPLOYEE_STAT A
             WHERE IS_SUM = 1
             ORDER BY YEAR DESC
         )
    WHERE RN = 1) C 
    ON C.YEAR = A.YEAR - 1
    ORDER BY 数据日期 DESC;

执行结果：