假设我在下面有一张桌子
ID SUB_ID YEAR Month Value
A1 A101 1 1 100
A1 A101 1 2 100
A1 A101 2 1 100
A1 A101 2 2 100
B1 B101 1 1 200
B1 B101 1 2 200
B1 B101 2 1 200
B1 B101 2 2 200
B1 B102 2 1 300 (So this B102 is a new record starting in YEAR 2 MONTH 1)
B1 B102 2 2 300
我想使用最新的总数获取每个ID的运行总额。输出应为:
ID YEAR RUNNING_SUM
A1 1 100
A1 2 100 (for A1,there is no value change or no new SUB_ID,so the sum is always 100)
B1 1 200
B1 2 500 (in YEAR 2,B102 was added)
我们如何实现这一目标?基本上,如果有新的SUB_ID传入,我将附加每个ID的值。在Tableau中使用LOD计算(固定ID)很容易
这是SQL提琴(http://sqlfiddle.com/#!18/95b63d/1)
谢谢!