时间戳

首页 > 解决方案 > 使用增量值指定排名值

sql - 使用增量值指定排名值

问题描述

我在 Oracle 数据库中有一个包含 Line、Move_Date、Qty 的表。我想设置总计> = 5的行范围的等级。输出如下。

Line   |Move_Date   |Qty      |Rank      |
L1     |20191001    |        1|         1|
L1     |20191002    |        2|         1|
L1     |20191003    |        2|         1|
L1     |20191004    |        3|         2|
L1     |20191005    |        3|         2|
L1     |20191006    |        1|         3|
L1     |20191007    |        8|         3|
L1     |20191008    |        4|         4|
L1     |20191009    |        1|         4|
L1     |20191010    |        1|         5|
...

我尝试过使用“Sum()（按...分区）并使用除法”，但这是不对的。

标签： sqldatabaseoracleoracle11g

解决方案

你可能想要这样的东西

WITH   a as(
Select 'L1' Line, TO_DATE('20191001','yyyymmdd') Move_Date ,1  Qty from dual union 
all
Select 'L1',TO_DATE('20191002','yyyymmdd'), 2 from dual union all
Select 'L1',TO_DATE('20191003','yyyymmdd'), 2 from dual union all      
Select 'L1',TO_DATE('20191004','yyyymmdd'), 3 from dual union all       
Select 'L1',TO_DATE('20191005','yyyymmdd'), 3 from dual union all        
Select 'L1',TO_DATE('20191006','yyyymmdd'), 1 from dual union all        
Select 'L1',TO_DATE('20191007','yyyymmdd'), 8 from dual union all        
Select 'L1',TO_DATE('20191008','yyyymmdd'), 4 from dual union all        
Select 'L1',TO_DATE('20191009','yyyymmdd'), 1 from dual union all        
Select 'L1',TO_DATE('20191010','yyyymmdd'), 1 from dual union all
Select 'L1',TO_DATE('20191011','yyyymmdd'), 2 from dual union all
Select 'L1',TO_DATE('20191012','yyyymmdd'), 1 from dual union all
Select 'L1',TO_DATE('20191013','yyyymmdd'), 3 from dual union all
Select 'L1',TO_DATE('20191014','yyyymmdd'), 1 from dual
) 
,b as 
(
 Select a.* 
    ,mod(sum(qty) Over(order by move_date),5) mod
 from a
)
,C AS (
Select b.*
     ,CASE WHEN lag(mod) over(order by move_date) > MOD OR MOD =0 THEN 1 ELSE 0 END 
BRR
from b
) --Select * from c
,rnk as (select c.*
,sum(brr) Over (
    ORDER BY move_date 
    rows BETWEEN current row and unbounded following
    ) ran
from c 
order by move_date 
)
Select line,move_date,qty,dense_rank() Over(Order by ran desc) myrank from rnk

输出是：

L1  01/OCT/19   1   1
L1  02/OCT/19   2   1
L1  03/OCT/19   2   1
L1  04/OCT/19   3   2
L1  05/OCT/19   3   2
L1  06/OCT/19   1   3
L1  07/OCT/19   8   3
L1  08/OCT/19   4   4
L1  09/OCT/19   1   4
L1  10/OCT/19   1   5
L1  11/OCT/19   2   5
L1  12/OCT/19   1   5
L1  13/OCT/19   3   5
L1  14/OCT/19   1   6

诀窍是 BETWEEN current row and unbounded following

推荐阅读