2.4 Adjacent reference

1. Generate random sales data of 12 months.
2. Generate a table sequence containing months and sales.
3. Add a column of monthly growth amount for the monthly sales table.
4. Subtract 5% from the sales in the even-numbered month in the sales table, and leave the sales in the odd-numbered months unchanged.

SPL

SQL

1. Generate random sales data of 12 months.

SQL has no the data structure ‘sequence’, it can only generate table.

2. Generate a table containing months and sales

CREATE TABLE sales_data_gen (
    month NUMBER,
    sales_amount NUMBER);
INSERT INTO sales_data_gen
SELECT level, FLOOR(DBMS_RANDOM.VALUE (100,1000)) FROM dual CONNECT BY level<=12;

3. Add a column of monthly growth amount for the monthly sales table.

CREATE TABLE sales_data_gen2 AS 
SELECT month, sales_amount, 
CASE
WHEN month = 1 THEN 0
ELSE sales_amount - LAG(sales_amount, 1) OVER (ORDER BY month) 
END AS month_growth 
FROM sales_data_gen;

4. Subtract 5% from the sales in the even-numbered month in the sales table, and leave the sales in the odd-numbered months unchanged.

UPDATE sales_data_gen2
SET sales_amount =
    CASE
        WHEN MOD(month,2) = 0 THEN sales_amount * 0.95
        ELSE sales_amount
END;

Python

sales = [random.randint(100, 999) for _ in range(12)]
month = [i for i in range(1,13)]
df = pd.DataFrame({'month':month,'sales':sales})
df['growth'] = df['sales'] - df['sales'].shift(1)
df['sales'] = df.apply(lambda x: x['sales'] * 0.95 if x['month'] % 2 == 0 else x['sales'], axis=1)

3.1 Equivalence grouping
Example codes for comparing SPL, SQL, and Python