6.2 选出运算

现有两个序列，一个是日期序列，一个是股价序列，它们一一对应且日期序列有序，请完成以下计算任务：

1. 获取按股价排序后的日期序列；

2. 计算股价最高那天的涨幅；

3. 计算股价最高 3 天的涨幅。

SPL

SQL

1. 获取按股价排序后的日期序列

WITH dates AS (
    SELECT to_date(column_value, 'YYYY-MM-DD') AS stock_date，rownum rn
    FROM TABLE(SYS.ODCIVARCHAR2LIST('2023-12-01', '2023-12-04', '2023-12-05', 
        '2023-12-06', '2023-12-07', '2023-12-08', '2023-12-11', '2023-12-12', '2023-12-13', 
        '2023-12-14', '2023-12-15'))),
prices AS (
    SELECT column_value price, rownum rn
    FROM TABLE(SYS.ODCINUMBERLIST(102.62,102.56,100.02,100.9,104.2,108.32,103.0,
        106.72,103.96,104.14,100.84)))
SELECT stock_date
FROM dates
JOIN prices
ON dates.rn=prices.rn
ORDER BY prices. price;

2. 计算股价最高那天的涨幅

WITH stock_prices AS (
    SELECT COLUMN_VALUE AS price, ROW_NUMBER() OVER (ORDER BY ROWNUM) AS day
    FROM TABLE(sys.odcinumberlist(102.62, 102.56, 100.02, 100.9, 104.2, 108.32, 103.0, 106.72, 
        103.96, 104.14, 100.84))) , 
max_price AS (
    SELECT *
    FROM(
        SELECT price,day
        FROM stock_prices
        ORDER BY PRICE DESC)
    WHERE ROWNUM=1) , 
previous_day_price AS (
    SELECT price
    FROM stock_prices
    WHERE day = (SELECT day - 1 FROM max_price))
SELECT (SELECT price FROM max_price) / (SELECT price FROM previous_day_price)-1 AS increase
FROM dual;

3. 计算股价最高 3 天的涨幅

WITH stock_prices AS (
    SELECT COLUMN_VALUE AS price, ROW_NUMBER() OVER (ORDER BY ROWNUM) AS day
    FROM TABLE(sys.odcinumberlist(102.62, 102.56, 100.02, 100.9, 104.2, 108.32, 103.0, 106.72, 
        103.96, 104.14, 100.84))) , 
max3_price AS (
    SELECT *
    FROM(
        SELECT price,day
        FROM stock_prices
        ORDER BY PRICE DESC)
    WHERE ROWNUM<=3) , 
previous_day_price AS (
    SELECT max3_price.price AS m3_price,stock_prices.price AS pre_price, 
        max3_price.day AS m3_day, stock_prices.day as pre_day
    FROM stock_prices
    JOIN max3_price
    ON stock_prices.day=max3_price.day-1
    ORDER BY max3_price.price DESC)
SELECT m3_price/pre_price-1 FROM previous_day_price;

Python

date_series = pd.Series(["2023-12-01","2023-12-04","2023-12-05","2023-12-06","2023-12-07","2023-12-08","2023-12-11","2023-12-12","2023-12-13","2023-12-14","2023-12-15"])
price_series = pd.Series([102.62,102.56,100.02,100.9,104.2,108.32,103.0,106.72,103.96,104.14,100.84])
#股价排序后的索引
sorted_dates = date_series.iloc[price_series.argsort()]
max_price_index = price_series.argmax()
max_price = price_series[max_price_index]
previous_price = price_series[max_price_index - 1]
#股价最高那天的涨幅
increase = max_price/ previous_price - 1
max_price_indexes = price_series.argsort()[-3:].iloc[::-1]
max_prices = price_series.iloc[max_price_indexes].values
previous_prices = price_series.iloc[max_price_indexes - 1].values
#股价最高3天的涨幅
increases = (max_prices - previous_prices) / previous_prices 

Python 获取股价最高前一天的数据只能通过索引减 1 得到，获取股价最高 3 天的涨幅是从全排序后的索引中取 3 个。

7.1 序列排序
SPL SQL Python 代码示例对比