时间戳

首页 > 解决方案 > 如何按条件获取动态累积数据

sql - 如何按条件获取动态累积数据

问题描述

我想根据条件获取累积数据

示例数据集（更新）：

CREATE TABLE stackoverflow (
DT date,
Part_number varchAr(10),
sales_volume int
)
insert into stackoverflow values('2018-03-01','HDD','190000');
insert into stackoverflow values('2018-03-03','HDD','20000');
insert into stackoverflow values('2018-03-03','HDD','70000');
insert into stackoverflow values('2018-03-03','SDD','100000');
insert into stackoverflow values('2018-03-03','SDD','20000');
insert into stackoverflow values('2018-03-05','HDD','90000');
insert into stackoverflow values('2018-03-05','SDD','50000');
insert into stackoverflow values('2018-03-05','SDD','50000');
insert into stackoverflow values('2018-03-06','SDD','20000');
insert into stackoverflow values('2018-03-07','HDD','50000');
insert into stackoverflow values('2018-03-07','HDD','40000');
insert into stackoverflow values('2018-03-07','SDD','100000');
insert into stackoverflow values('2018-03-07','SDD','40000');
insert into stackoverflow values('2018-03-08','HDD','20000');
insert into stackoverflow values('2018-03-08','HDD','30000');
insert into stackoverflow values('2018-03-08','SDD','60000');
insert into stackoverflow values('2018-04-08','SDD','80000');
insert into stackoverflow values('2018-04-09','HDD','10000');
insert into stackoverflow values('2018-05-11','HDD','20000');
select * from stackoverflow;

如果运行总和高于条件，则复位并以下列条件启动。

条件

  1. 如果系统小于等于200000，
    只要大于100000，备注栏就会显示“OK”。
  2. 如果系统小于等于30000，
    只要大于80000，备注栏就会显示“OK”。
  3. 如果保税系统小于等于400000，
    只要大于60000就会在备注栏显示“OK”。
  4. 如果系统小于等于500000，
    只要值大于40000，备注栏就会显示“OK”。
  5. 如果系统大于 500,000，
    则每超过 30000 在备注栏中显示“OK”。
    cum.sum <= 200000 || "OK" is displayed on DESC when it exceeds 100,000  
    cum.sum <= 300000 || "OK" is displayed on DESC when it exceeds 80,000
    cum.sum <= 400000 || "OK" is displayed on DESC when it exceeds 60,000
    cum.sum <= 500000 || "OK" is displayed on DESC when it exceeds 40,000
    cum.sum  > 500000 || "OK" is displayed on DESC when it exceeds 30,000

条件结果 - 描述。列只不过是对条件结果的提醒。

   DT     PARTS  COUNT      CUM.SUM    DESC.    
2018-03-01  HDD 190,000     190,000     OK
2018-03-03  HDD  20,000     210,000     
2018-03-03  HDD  70,000     280,000     
2018-03-03  SDD 100,000     100,000     OK
2018-03-03  SDD  20,000     120,000     
2018-03-05  HDD  90,000     370,000     OK
2018-03-05  SDD  50,000     170,000     
2018-03-05  SDD  50,000     220,000     OK
2018-03-06  SDD  20,000     240,000     
2018-03-07  HDD  50,000     420,000     
2018-03-07  HDD  40,000     460,000     OK
2018-03-07  SDD 100,000     340,000     OK
2018-03-07  SDD  40,000     380,000     
2018-03-08  HDD  20,000     480,000     
2018-03-08  HDD  30,000     510,000     OK
2018-03-08  SDD  60,000     440,000     OK
2018-04-08  SDD  80,000     520,000     OK
2018-04-09  HDD  10,000     520,000     
2018-05-11  HDD  20,000     540,000     OK

期望的结果

    DT    PARTS  COUNT     CUM.SUM       
2018-03-01  HDD 190,000     190,000        
2018-03-03  SDD 100,000     100,000      
2018-03-05  HDD  90,000     370,000      
2018-03-05  SDD  50,000     220,000     
2018-03-07  HDD  40,000     460,000     
2018-03-07  SDD 100,000     340,000      
2018-03-08  HDD  30,000     510,000     
2018-03-08  SDD  60,000     440,000     
2018-04-08  SDD  80,000     520,000      
2018-05-11  HDD  20,000     540,000

==================================================== ===

更新了样本数据集和所需结果。

我会向所有有“OK”问题的人解释。

基于 PARTS 列的 HDD，

示例：PARTS 列的 HDD

  1. 初始值为190000，超过100000，所以“ok”

  2. 该条件基于写为“ok”的最后日期的 cum.sum 数据。

标签： sqlsql-serversql-server-2017

解决方案

除了Desc我不理解逻辑的列（显然您将使用一些基本case..when表达式）之外，以下查询可以帮助您：

select dt as "DT", Part_number as "PARTS", sales_volume as "COUNT",
       sum(sales_volume) over (partition by Part_number order by dt) as "CUM.SUM",
       case when sales_volume >= 100000 then 'OK' end as "DESC."
  from stackoverflow
 order by dt, parts

Demo

推荐阅读