2.3 Sequence number reference

A company has monthly sales data for one year and wants to calculate:

1. The maximum monthly growth amount in the year.
2. The moving average of sales for the previous two months and the current month.

SPL

SPL uses ~[n] to retrieve adjacent members, where n represents the distance to the current member, uses a positive or negative sign to indicate ‘after or before the current member’, and uses ~[a:b] to retrieve a set of adjacent members.

SQL

1. Calculate the maximum monthly growth amount in the year.

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. Calculate the moving average of sales for the previous two months and the current month.

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()		#Maximum monthly growth amount
rolling_avg = s.rolling(window=3).mean()			        #Moving average

Python uses totally different methods for two similar calculations.

2.5 Loop to generate and execute
Example codes for comparing SPL, SQL, and Python