2.5 循环产生和执行
1. 生成 12 个月随机销售额数据
2. 生成包含月份和销售额的序表
3. 为月销售额表增加一列月增长额
4. 将销售额表中偶数月份的销售额减 5%，单数月份销售额不变。
SPL
| A | B | |
|---|---|---|
| 1 | =12.(rand(900)+100) | / 产生序列 |
| 2 | =A1.new(#:month,~:sales) | / 产生序表 |
| 3 | =A2.derive(if(#>1,sales-sales[-1],0):growth) | / 导出函数 |
| 4 | =A2.run(sales=if(month%2==0,sales*0.95,sales)) | / 执行函数 |
SQL
1. 生成 12 个月随机销售额数据
SQL 没有序列这一数据结构，只能生成表。
2. 生成包含月份和销售额的序表
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. 为月销售额表增加一列月增长额
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. 将销售额表中偶数月份的销售额减 5%，单数月份销售额不变。
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)
