从运行total(sql)snowflake中排除某些记录

0aydgbwb 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(329)

我有一张有费用和预算的table。不同的记录可以有不同的预算我不想增加运行总和，如果它是大于预算的记录。
就像在分区中运行sum大于budget一样，它停止增加，然后在budget大于budget时再次增加。
有可能吗？

create table spend 
(id number,
cents number,
budget number);

insert into spend(id,cents,budget) values(1,25,50);
insert into spend(id,cents,budget) values(2,25,50);
insert into spend(id,cents,budget) values(3,25,50);
insert into spend(id,cents,budget) values(4,25,50);
insert into spend(id,cents,budget) values(5,25,100);
insert into spend(id,cents,budget) values(6,25,100);
insert into spend(id,cents,budget) values(7,25,100);
insert into spend(id,cents,budget) values(8,25,200);
insert into spend(id,cents,budget) values(9,25,200);

这就是我得到的

vadimzilberleyb#TRANSFORM_WH@INSTADATA.DWH>select s.*, sum(cents) over(order by id)  from spend s;
+----+-------+--------+------------------------------+                          
| ID | CENTS | BUDGET | SUM(CENTS) OVER(ORDER BY ID) |
|----+-------+--------+------------------------------|
|  1 |    25 |     50 |                           25 |
|  2 |    25 |     50 |                           50 |
|  3 |    25 |     50 |                           75 |
|  4 |    25 |     50 |                          100 |
|  5 |    25 |    100 |                          125 |
|  6 |    25 |    100 |                          150 |
|  7 |    25 |    100 |                          175 |
|  8 |    25 |    200 |                          200 |
|  9 |    25 |    200 |                          225 |
+----+-------+--------+------------------------------+

这就是我想要的：）

id       cents  budget   cumul in run    cumulative desirable in run

1          25         50          25                 25
2.        25          50          50                 50
3          25         50          75                 50
4          25         50          100               50
5          25         100         125               75
6          25         100         150               100
7          25         100         175               100
8          25         200         200               125
9          25         200         225               150

sql window-functions sum recursive-query snowflake-cloud-data-platform

来源：https://stackoverflow.com/questions/63045066/exclude-certain-records-from-running-total-sql-snowflake

1条答案

按热度按时间

q7solyqu1#

仅使用窗口函数无法完成此操作。您需要某种迭代过程来逐步确定当前 cents 应添加到累计 sum . 在sql中，这通常通过递归查询实现：

with 
    data as (
        select id, cents, budget, row_number() over(order by id) rn
        from spend
    ),
    rec as (
        select d.*, cents cumul from data d where rn = 1
        union all
        select 
            d.*, 
            case when r.cumul + d.cents > d.budget 
                then r.cumul 
                else r.cumul + d.cents 
            end
        from rec r
        inner join data d on d.rn = r.rn + 1
    )
select * from rec

如果 id 总是从 1 无间隙递增，则不需要第一个cte：

with rec as (
    select s.*, cents cumul from spend s where id = 1
    union all
    select 
        s.*, 
        case when r.cumul + s.cents > s.budget 
            then r.cumul 
            else r.cumul + s.cents 
        end
    from rec r
    inner join spend s on s.id = r.id + 1
)
select * from rec

这里是演示-这是sql server（因为在野外没有雪花小提琴），但语法在snowlake上应该同样适用：

id | cents | budget | rn | cumul
-: | ----: | -----: | -: | ----:
 1 |    25 |     50 |  1 |    25
 2 |    25 |     50 |  2 |    50
 3 |    25 |     50 |  3 |    50
 4 |    25 |     50 |  4 |    50
 5 |    25 |    100 |  5 |    75
 6 |    25 |    100 |  6 |   100
 7 |    25 |    100 |  7 |   100
 8 |    25 |    200 |  8 |   125
 9 |    25 |    200 |  9 |   150

赞(0）回复(0）举报 2021-07-24

我来回答

从运行total(sql)snowflake中排除某些记录

1条答案

相关问题

热门标签

最新问答