我的示例 CTE 来自多个来源并从以下位置绘制数据：
with sample_cte as
(
ae.spvr as col1,
ae.startDate as start_dt,
ae.END_DT as end_dt,
round(ae.Scre, 2, 1) as Scre,
from
diff_sources ae
我有一个 CTE，它产生如下输出：
col1 | start_dt | end_dt | score
-----+----------+--------+-------
a | 10-01-19 |10-02-19| 50.50
a | 10-02-19 |10-03-19| 55.50
b | 10-01-19 |10-02-19| 60.50
b | 10-02-19 |10-03-19| 65.50
c | 10-01-19 |10-02-19| 70.50
c | 10-02-19 |10-03-19| 75.50
我正在寻找添加新列，该列根据日期获取分数总和（a 的分数 b 和 c 的总和，c 的 a 和 b 的总和类似）。
它看起来像下面这样
col1 | start_dt | end_dt | new_sum_score_column
-----+----------+--------+----------------------------------------------
a | 10-01-19 |10-02-19| sum of b and c on these dates = (60.50+70.50)
a | 10-02-19 |10-03-19| sum of b and c on these dates = (65.50+75.50)
b | 10-01-19 |10-02-19| sum of a and c on these dates = (50.50+70.50)
b | 10-02-19 |10-03-19| sum of a and c on these dates = (55.50+75.50)
c | 10-01-19 |10-02-19| similar logic
c | 10-02-19 |10-03-19|
假设日期参考是第一列，我认为这有效：
select t.*,
sum(score) over (partition by start_dt) - score
from t;
那是 。. . 计算总和并减去当前值。
编辑：
如果有多个开始日期，则：
select t.*,
(sum(score) over (partition by start_dt) -
sum(score) over (partition by start_dt, col1)
)
from t;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句