英文:
Continuous subtraction from previous row in Bigquery
问题
我需要知道是否有一种方法可以实现连续的减法，就像这样：
从可用列中取第一个值，然后不断减去一个常数值（这里是10），以获得所需的列。
| 可用 | 所需 |
|---|---|
| 100 | 90 |
| 100 | 80 |
| 100 | 70 |
我已经使用行号作为计数器来进行减法，但有一个问题，当所需列变为0时，计数器需要重新开始。（如果我使用行号）
| 可用 | 计数器 | 所需 |
|---|---|---|
| 100 | 1 | 100 - (1 * 10) = 90 |
| 100 | 2 | 100 - (2 * 10) = 80 |
在BigQuery中是否有一种方法实现这种连续减法？
英文:
I need to know if there is a way I can achieve a continuous subtraction like:
Where take first value of available column and keep on subtracting a constant value (10 here) to get the required column.
| Availabl | Required |
|---|---|
| 100 | 90 |
| 100 | 80 |
| 100 | 70 |
I have used row number as a counter to subtract the number but there is a problem as when the Required becomes 0, counter needs to restart. (If I used row number)
| Available | Counter | Required |
|---|---|---|
| 100 | 1 | 100-(1*10) = 90 |
| 100 | 2 | 100-(2*10) =80 |
Is there a method for this continuous subtraction in bigquery?
答案1
得分: 1
考虑以下（BigQuery标准SQL）。这可能是您的用例的良好起点
select Available, Counter, Required
from (
select *,
row_number() over(partition by div(pos, 1 + cast(Available/10 as int))) as Counter,
Available - 10 * row_number() over(partition by div(pos, 1 + cast(Available/10 as int))) as Required
from (
select *, row_number() over() as pos
from your_table
)
)
如果应用于您示例中的样本数据 - 输出是：
英文:
Consider below (BigQuery Standard SQL). Could be good start for your use case
select Available, Counter, Required
from (
select *,
row_number() over(partition by div(pos, 1 + cast(Available/10 as int))) as Counter,
Available - 10 * row_number() over(partition by div(pos, 1 + cast(Available/10 as int))) as Required
from (
select *, row_number() over() as pos
from your_table
)
)
if applied to sample data in your example - output is
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论