SQLite where 條件 - 比較最後兩行

我有一個這樣的 SQLite DB 表：

[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']) 

像這樣的東西應該​​可以工作（沒有小提琴很難驗證）：

WITH temp AS (
 SELECT code, sma_9, sma_26, rsi,
   RANK() OVER (PARTITION BY code ORDER BY date DESC) AS rank
 FROM Stock_price_Table
)
SELECT t1.code
 FROM temp AS t1
 INNER JOIN temp AS t2
   ON t1.code = t2.code
  AND t1.rank = 1
  AND t2.rank = 2
 WHERE t1.sma_9 >= t1.sma_26
   AND t2.sma_9 <= t2.sma_26
   AND t1.rsi >= t2.rsi

基本上，在temp表格中，您選擇股票記錄，RANK按日期編輯並按code（通過PARTITION BY子句）“分組”。在 mainSELECT中，您連結每個股票程式碼的排名第一和第二的記錄，並僅選擇符合您的條件（WHERE子句）的那些。

引用自：https://dba.stackexchange.com/questions/266700