我有一张Oracle数据库中有行、移动日期、数量的表。我想设置总计>=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()(over partition by…)”和“divide”,但这不正确。
最佳答案
你可能想要这样的东西
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
关于sql - 用增量值指定等级值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/58404814/