I have a SQLite DB table like this:
[Stock_price_Table]:
+-----+-------------+--------+------------+--------+--------------+----------------------+
|code | date | first | open | close | sma_9 | sma_26 | rsi |
+-----+-------------+--------+------------+--------+--------------+----------------------+
|1223 | 20200213 | 205 | 205 | 300 | 225 | 300 | 20 |
|1223 | 20200313 | 209 | 209 | 340 | 235 | 330 | 70 |
|4556 | 20200213 | 205 | 205 | 300 | 225 | 300 | 67 |
|4443 | 20200213 | 205 | 205 | 300 | 225 | 300 | 33 |
|9877 | 20200218 | 205 | 205 | 300 | 225 | 300 | 39 | +-----+-------------+--------+------------+--------+--------------+------------+---------+
I want to compare last two records of each stock and get the stocks that have these conditions:
1. `sma_9[n] >= sma_26[n] && sma_9[n-1] <= sma_26[n-1]`
2. `rsi[n] >= rsi[n-1]`
where [n] is last record and [n-1] the next-to-last.
I can do this in python and dataframe like below, but I want to do it using SQL code:
stocks = db.session.query(Stocks).all()
for stock in enumerate(stocks):
query = f"select * from Stock_price_Table where code = {stock.code}"
df = pd.read_sql(query, db.engine)
result1=((df.iloc[-1]['SMA_9'] >= df.iloc[-1]['SMA_26']) & (df.iloc[-2]['SMA_9'] <= df.iloc[-2]['SMA_26']))
result2=(df.iloc[-1]['rsi'] >= df.iloc[-2]['rsi'])