sql - 未定义行数的计算
问题描述
我想添加一个计算,考虑到我们可以有一、二、三或更多行。我们可以使用关系获取所有数据,但我有点卡住了,因为关系的数量是未定义的。例如,作为来源:
SELECT 123 AS id
,250 AS amount
,225 AS debt
,NULL AS relation
,1 AS rn
UNION ALL
SELECT 124 AS id
,150 AS amount
,25 AS debt
,123 AS relation
,2 AS rn
UNION ALL
SELECT 125 AS id
,160 AS amount
,50.25 AS debt
,124 AS relation
,3 AS rn
UNION ALL
SELECT 126 AS id
,80 AS amount
,25 AS debt
,125 AS relation
,4 AS rn
源表
ID | 数量 | 债务 | 关系 | rn |
---|---|---|---|---|
123 | 250 | 225 | 空值 | 1 |
124 | 150 | 25 | 123 | 2 |
125 | 160 | 50.25 | 124 | 3 |
126 | 80 | 25 | 125 | 4 |
茶几
ID | 数量 | 债务 | 关系 | rn | 卡路里 |
---|---|---|---|---|---|
123 | 250 | 225 | 空值 | 1 | 250 |
124 | 150 | 25 | 123 | 2 | 22.5 |
125 | 160 | 50.25 | 124 | 3 | 7.5375 |
126 | 80 | 25 | 125 | 4 | 3.75 |
我需要应用如下计算:
- 第 1 行:金额
- 第 2 行:row2.debt*row1.debt/row1.amount
- 第 3 行:row3.debt*row2.debt/row2.amount*row1.debt/row1.amount
- 第 4 行:row4.debt*row3.debt/row3.debt*row2.debt/row2.amount*row1.debt/row1.amount
- ETC..
我正在使用 dbt,但很高兴听到有关 BigQuery 或其他 SQL 的信息,因为我真的很好奇如何做到这一点。
解决方案
一点数学可以帮助 - 见下文
select *,
ifnull(
round(exp(sum(ln(debt)) over(order by id rows between unbounded preceding and current row))
/ exp(sum(ln(amount)) over(order by id rows between unbounded preceding and 1 preceding)), 2),
amount
) cal
from `project.dataset.table`
如果应用于您问题中的样本数据 - 输出是
注意:我order by id
假设该id
列定义了行的顺序。您可以根据需要进行调整
推荐阅读
- c# - 如何在.net google api v3 中刷新令牌?
- python - 转置数据和拆分列
- laravel - Jetstream 和 Fortify 中的 Laravel 本地化路线
- java - Java,对象列表,循环更改值
- javascript - Firebase 推送通知在 yarn watch 中工作,但在 yarn build 中不工作
- python-3.x - 如何检查单个块中的两个条件而不是python中的2个
- java - 用字符串和数字对对象数组进行排序java
- django - Django如何使用ID记录日志页面
- node.js - 想将亚马逊 mws 与我个人网站的库存整合
- svn - sonar QualityGate 应该在 SVN 提交之前检查 - 如何实现这一点?