6.3 Positioning calculation
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
A | B | |
---|---|---|
1 | [“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”] | |
2 | [102.62,102.56,100.02,100.9,104.2,108.32,103.0,106.72,103.96,104.14,100.84] | |
3 | =A2.psort() | /Index sorted by stock price |
4 | =A1(A3) | /Date sequence sorted by stock price |
5 | =A2.pmax() | |
6 | =A2.calc(A5,/[-1]-1) | /The percentage increase in stock price on the day with the highest price |
7 | =A2.ptop(-3,~) | |
8 | =A2.calc(A7,/[-1]-1) | /The percentage increase in stock price over three days with highest prices |
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.
7.1 Sort a sequence
Example codes for comparing SPL, SQL, and Python
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL