6.2 Selection operation

There are two sequences: date sequence and stock price sequence; their members correspond one-to-one, and the date sequence is in order. Now we want to calculate:

1. The date sequence after sorting by stock price;

2. The percentage increase in stock price on the day with the highest price;

3. The percentage increase in stock price over three days with highest prices.

SPL

SQL

1. Calculate the date sequence sorted by stock price

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. Calculate the percentage increase in stock price on the day with the highest price

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. Calculate the percentage increase in stock price over three days with highest prices

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])
#Index sorted by stock price.
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]
#The percentage increase in stock price on the day with the highest price
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
#The percentage increase in stock price over three days with highest prices
increases = (max_prices - previous_prices) / previous_prices 

Python can only get the data of the day before the stock price reaches its highest point through subtracting 1 from the index, and can only calculate the percentage increase in stock price over three days with highest prices through selecting 3 members from the full sorting index.

Example codes for comparing SPL, SQL, and Python