2.3 序号引用

现有某商家 1 年中每个月的销售额，请完成以下计算：

1. 计算一年中最大的月增长额是多少

2. 计算前两个月和当前月销售额的移动平均值。

SPL

SPL 用 ~[n] 来获取相邻成员，其中 n 表示到当前成员的距离，正负号表示当前成员之后或之前，用 ~[a:b] 来获取相邻成员集合。。

SQL

1. 计算一年中最大的月增长额是多少

WITH sales AS (
    SELECT COLUMN_VALUE AS sales_amount, ROWNUM AS month_number
    FROM TABLE(SYS.ODCINUMBERLIST(123,345,321,345,546,542,874,234,543,983,434,897))),
lagged_sales AS (
    SELECT sales_amount, month_number, 
        LAG(sales_amount) OVER (ORDER BY NULL) AS prev_sales_amount
    FROM sales)
SELECT MAX(sales_amount - prev_sales_amount) AS max_monthly_growth
FROM lagged_sales;

2. 计算前两个月和当前月销售额的移动平均值

WITH sales AS (
    SELECT COLUMN_VALUE AS sales_amount, ROWNUM AS month_number
    FROM TABLE(SYS.ODCINUMBERLIST(123,345,321,345,546,542,874,234,543,983,434,897))),
lagged_sales AS (
    SELECT sales_amount, month_number, 
        LAG(sales_amount, 2) OVER (ORDER BY month_number) AS prev_prev_month_sales,
        LAG(sales_amount, 1) OVER (ORDER BY month_number) AS prev_month_sales
    FROM sales)
SELECT
    CASE 
        WHEN month_number < 3 THEN NULL
        ELSE (sales_amount + prev_month_sales + prev_prev_month_sales) / 3 
    END AS moving_average
FROM lagged_sales;

Python

sales = [123,345,321,345,546,542,874,234,543,983,434,897]
month = [i for i in range(1,13)]
df = pd.DataFrame({'month':month,'sales':sales})
max_growth = df['sales'] - df['sales'].shift(1).max()		#最大月增长额
rolling_avg = s.rolling(window=3).mean()			#移动平均

两个相似的计算，Python 采用的方法却完全不同。

2.5 循环产生和执行
SPL SQL Python 代码示例对比