Pandas代码等效于示例数据的以下SQL

Question

请求：需要具有以下样本数据的数据帧

详细信息：查询以获取状态为“NearHigh”的小于lastPrice的罢工记录，并按罢工顺序降序排列行分区符号，如果状态为“NearLow”，则反转逻辑

---

样本数据

|symbol|lastPrice|Status|tradingsymbol|expiry|strike|lot_size|instrument_type|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3700PE|26-08-2021|3700|250|PE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3750CE|26-08-2021|3750|250|CE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3750PE|26-08-2021|3750|250|PE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3800CE|26-08-2021|3800|250|CE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3800PE|26-08-2021|3800|250|PE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3850CE|26-08-2021|3850|250|CE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3850PE|26-08-2021|3850|250|PE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3900CE|26-08-2021|3900|250|CE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3900PE|26-08-2021|3900|250|PE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3950CE|26-08-2021|3950|250|CE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3950PE|26-08-2021|3950|250|PE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG4000CE|26-08-2021|4000|250|CE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG4000PE|26-08-2021|4000|250|PE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG4050CE|26-08-2021|4050|250|CE|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG4050PE|26-08-2021|4050|250|PE|
|IDEA|7.35|NearLow|IDEA21AUG6CE|26-08-2021|6|70000|CE|
|IDEA|7.35|NearLow|IDEA21AUG6PE|26-08-2021|6|70000|PE|
|IDEA|7.35|NearLow|IDEA21AUG7CE|26-08-2021|7|70000|CE|
|IDEA|7.35|NearLow|IDEA21AUG7PE|26-08-2021|7|70000|PE|
|IDEA|7.35|NearLow|IDEA21AUG8CE|26-08-2021|8|70000|CE|
|IDEA|7.35|NearLow|IDEA21AUG8PE|26-08-2021|8|70000|PE|
|IDEA|7.35|NearLow|IDEA21AUG9CE|26-08-2021|9|70000|CE|
|IDEA|7.35|NearLow|IDEA21AUG9PE|26-08-2021|9|70000|PE|

获取所需输出的SQL

select tradingsymbol, expiry, symbol, lastPrice, Status, strike, lot_size, instrument_type,
row_number() over(partition by symbol order by case when Status='NearHigh' then strike desc else strike asc end) as rnk
from Table
where instrument_type = case when Status='NearHigh' then 'PE' else 'CE' end
and instrument_type = case when Status='NearHigh' then strike < lastPrice else strike > lastPrice end

所需输出

|symbol|lastPrice|Status|tradingsymbol|expiry|strike|lot_size|instrument_type|rnk|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3700PE|26-08-2021|3700|250|PE|8|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3750PE|26-08-2021|3750|250|PE|7|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3800PE|26-08-2021|3800|250|PE|6|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3850PE|26-08-2021|3850|250|PE|5|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3900PE|26-08-2021|3900|250|PE|4|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG3950PE|26-08-2021|3950|250|PE|3|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG4000PE|26-08-2021|4000|250|PE|2|
|APOLLOHOSP|4090.35|NearHigh|APOLLOHOSP21AUG4050PE|26-08-2021|4050|250|PE|1|
|IDEA|7.35|NearLow|IDEA21AUG8CE|26-08-2021|8|70000|CE|1|
|IDEA|7.35|NearLow|IDEA21AUG9CE|26-08-2021|9|70000|CE|2|
|IDEA|7.35|NearLow|IDEA21AUG10CE|26-08-2021|10|70000|CE|3|