时间戳

首页 > 解决方案 > 查询以获取最大月数的值的总和

sql - 查询以获取最大月数的值的总和

问题描述

Oracle 11 中的此查询获取最近 1 年的值的总和，并且在有 1 年的数据时有效。

当数据少于 1 年时，此查询返回 0，而不是值的总和，直到最旧的年份。

例如，如果只有 6 个月的数据，则查询应返回第 6 个月之前的值的总和。

SELECT SUM (DECODE (rnk, 11, rt, 0)) 1Y
FROM (SELECT entity_id,rnk,
             SUM (ABS(NVL (value, 0))) OVER (PARTITION BY TRIM (entity_id) ORDER BY rnk) rt
      FROM (SELECT psm.*,RANK () OVER (PARTITION BY entity_id ORDER BY period_end_date DESC) AS rnk
            FROM myTable psm
            WHERE psm.entity_id = '1'
            ORDER BY period_end_date DESC
           ) rank_tab
      WHERE rnk < 12
      );

如果最大排名为 6，则上述查询的结果为 0

我尝试了这个，但收到错误“ORA-00978：没有 GROUP BY 的嵌套组函数”

SELECT case when rnk < 11
                 then SUM (DECODE (rnk, Max(rnk), rt, 0))
            else SUM (DECODE (rnk, 11, rt, 0)) 
       end as Y
FROM (SELECT entity_id,rnk,
             SUM (ABS(NVL (value, 0))) OVER (PARTITION BY TRIM (entity_id) ORDER BY rnk) rt
      FROM (SELECT psm.*,RANK () OVER (PARTITION BY entity_id ORDER BY period_end_date DESC) AS rnk
            FROM myTable psm
            WHERE psm.entity_id = '1'
            ORDER BY period_end_date DESC
           ) rank_tab
      WHERE rnk < 12
     );

样本数据：

entity_id   value   period_end_date 
1           1       9/30/19
1           2       8/31/19
1           3       7/31/19
1           4       6/30/19
1           5       5/31/19
1           6       4/30/19

在上面的示例中，1Y 应该返回 1+2+3+4+5+6 = 21。相反，我的查询返回 0，因为它正在寻找不存在的 rnk = 11。

SUM (DECODE (rnk, 11, rt, 0)) 1Y

谢谢你。

编辑：

这行得通。但是，如果您知道更好的方法，请告诉我。谢谢你。

SELECT 
CASE WHEN MRank < 11 then maxY else OneY end as lc_incearned_1Y
FROM (
WITH R as
(SELECT MAX(RNK) MaxRank FROM (
SELECT RANK () OVER (PARTITION BY TRIM (entity_id) ORDER BY period_end_date 
DESC) AS rnk FROM myTbl psm
WHERE TRIM (psm.entity_id) = '1' AND period_end_date < 
to_date('9/30/2019','MM/DD/YYYY')
ORDER BY period_end_date DESC))
select MAX(MaxRank) MRank,
SUM (DECODE (rnk, MaxRank, rt, 0)) maxY,
SUM (DECODE (rnk, 11, rt, 0)) OneY,  --13051.97
FROM (SELECT entity_id,rnk,
SUM (ABS (NVL (value, 0))) OVER (PARTITION BY TRIM (entity_id) ORDER BY rnk) rt
FROM (SELECT psm.*,RANK () OVER (PARTITION BY TRIM (entity_id) ORDER BY period_end_date DESC) AS rnk FROM CREF.PORTFOLIO_SUMM_MTHEND psm
WHERE TRIM (psm.entity_id) = '1' AND period_end_date < to_date('9/30/2019','MM/DD/YYYY')
ORDER BY period_end_date DESC) rank_tab WHERE rnk < 12) T,R)

标签： sqloracleoracle11g

解决方案

看来您需要从最近的 period_end_date 到 11 个月范围内的最早日期总结您的值。将max(period_end_date) over (partition by entity_id order by period_end_date desc)分析函数与您当前的rank()函数一起使用是合适的。然后申请months_between(<max_period_end_date>,period_end_date)。如果您需要从当前日期开始查找，则去掉max()解析函数并替换<max_period_end_date>为trunc(sysdate)inmonths_between()函数。所以，使用：

with t as
(
select max(period_end_date) over (partition by entity_id order by period_end_date desc) as mx,
       rank() over (partition by entity_id order by period_end_date desc) as rnk,
       t.*
  from myTable t
)
select sum(nvl(value,0)) as sum_value
  from t
 where months_between(mx,period_end_date)<=11

Demo

推荐阅读