总而言之,可以在前面的行中创建一条规则吗? [英] It's possible to create a rule in preceding rows in sum?
问题描述
我有一个历史库存变化值,必须计算库存值.但是我也有一个规则,即库存为max(stock,0),换句话说,如果库存为负,我认为它为零.
I have a historical stock variation values and have to calculate the stock value. But I also have a rule that the stock is max(stock, 0), or in other words, if stock is negative, I consider it as zero.
要在Big Query中做到这一点,我认为方法是这样的:
To make this in Big Query, I think the way is like this:
with stock as (
SELECT -7.34 variation, UNION ALL
SELECT -1.81 UNION ALL
SELECT -0.51 UNION ALL
SELECT 17.19 UNION ALL
SELECT -1.63 UNION ALL
SELECT 2.82 UNION ALL
SELECT -1.00 UNION ALL
SELECT 0.56 UNION ALL
SELECT -17.92 UNION ALL
SELECT 0.34 UNION ALL
SELECT 1.02 UNION ALL
SELECT 0.39 UNION ALL
SELECT 0.25 UNION ALL
SELECT 0.65 UNION ALL
SELECT 0.09 UNION ALL
SELECT -0.32 UNION ALL
SELECT -0.40
)
select variation, sum(variation) over (rows unbounded preceding) as stock from stock
我认为解决方案类似于说"previous必须为max(preceding,0)".但是我没有找到该怎么做的.
I think the solution is something like saying preceding has to be max(preceding, 0). But I didn't find how to do this.
我在查询中获得的结果是:
The results I obtained in query is:
|variation|stock|
|---------|-----|
|-7.34 |-7.34|
|-1.81 |-9.15|
|-0.51 |-9.66|
|17.19 |7.53 |
|-1.63 |5.9 |
|2.82 |8.72 |
|-1.0 |7.72 |
|0.56 |8.28 |
|-17.92 |-9.64|
|0.34 |-9.3 |
|1.02 |-8.28|
|0.39 |-7.89|
|0.25 |-7.64|
|0.65 |-6.99|
|0.09 |-6.9 |
|-0.32 |-7.22|
|-0.4 |-7.62|
但预期是:
|variation|stock|
|---------|-----|
|-7.34 |0 |
|-1.81 |0 |
|-0.51 |0 |
|17.19 |17.19|
|-1.63 |15.55|
|2.82 |18.37|
|-1.00 |17.37|
|0.56 |17.93|
|-17.92 |0 |
|0.34 |0.33 |
|1.02 |1.36 |
|0.39 |1.75 |
|0.25 |2 |
|0.65 |2.65 |
|0.09 |2.74 |
|-0.32 |2.42 |
|-0.40 |2.02 |
谢谢.
推荐答案
您可以使用array_agg()技巧.但是,它的可扩展性不如本地函数.
You may employ the array_agg() trick. It is not as scalable as a native function though.
CREATE TEMP FUNCTION special_sum(x ARRAY<FLOAT64>)
RETURNS FLOAT64
LANGUAGE js
AS """
var sum = 0;
for (const num of x)
{
sum += num;
if (sum < 0)
{
sum = 0;
}
}
return sum;
""";
with stock as (
SELECT -7.34 variation, UNION ALL
SELECT -1.81 UNION ALL
SELECT -0.51 UNION ALL
SELECT 17.19 UNION ALL
SELECT -1.63 UNION ALL
SELECT 2.82 UNION ALL
SELECT -1.00 UNION ALL
SELECT 0.56 UNION ALL
SELECT -17.92 UNION ALL
SELECT 0.34 UNION ALL
SELECT 1.02 UNION ALL
SELECT 0.39 UNION ALL
SELECT 0.25 UNION ALL
SELECT 0.65 UNION ALL
SELECT 0.09 UNION ALL
SELECT -0.32 UNION ALL
SELECT -0.40
)
select variation,
special_sum( array_agg(variation) over (rows unbounded preceding) ) as stock
from stock
输出与您提供的内容非常接近:
The output is pretty close to what you provided:
这篇关于总而言之,可以在前面的行中创建一条规则吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!