greenplum中实现teradata同样的sql时常常会遇到csum不存在的问题，本文提供一个解法。
一、不带group by
Teradata中执行
|
1
2
3
4
|
SELECT
salesdate, sales, csum(sales, salesdate)
FROM
daily_sales
WHERE
salesdate
BETWEEN
980101
AND
980301
AND
itemid = 10;
|
结果
|
1
2
3
4
5
6
|
salesdate sales Csum
98/01/01 150.00 150.00
98/01/02 200.00 350.00
98/01/03 250.00 600.00
98/01/05 350.00 950.00
98/01/10 550.00 1500.00
|
在greenplum实现csum(sales, salesdate)可替换为
|
1
|
SUM
(sales) over(
ORDER
BY
salesdate
ASC
)
|
二、带group by
Teradata中执行
|
1
2
3
4
5
6
7
|
SELECT
salesdate, sales, csum(sales, salesdate)
FROM
daily_sales ds, sys_calendar.calendar sc
WHERE
ds.salesdate = sc.calendar_date
AND
sc.year_of_calendar = 1998
AND
sc.month_of_year
in
(1,2)
AND
ds.itemid = 10
GROUP
BY
sc.month_of_year;
|
结果
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
salesdate sales Csum
98/01/01 150.00 150.00
98/01/02 200.00 350.00
98/01/03 250.00 600.00
98/01/05 350.00 950.00
98/01/10 550.00 1500.00
98/01/21 150.00 1650.00
98/01/25 200.00 1850.00
98/01/31 100.00 1950.00
98/02/01 150.00 150.00 重新累计
98/02/03 250.00 400.00
98/02/06 350.00 750.00
98/02/17 550.00 1300.00
98/02/20 450.00 1750.00
98/02/27 350.00 2100.00
|
在greenplum实现csum(sales, salesdate)可替换为
|
1
|
SUM
(sales) over(PARTITION
BY
sc.month_of_year
ORDER
BY
salesdate
ASC
)
|
本文转自 hexiaini235 51CTO博客，原文链接：http://blog.51cto.com/idata/1259498，如需转载请自行联系原作者
