如何用SQL/Python生成High_new与date_new列：匹配等值Open/Low对应最高High值

现有数据集df，需求如下：

• 当Open列与Low列数值相等时，计算二者的乘积（注：根据示例及初始代码，实际为二者的和，即Open*2；若需真实乘积，替换为Open*Low即可）
• 在High列中找到与该计算值最接近且数值最大的取值，将其存入High_new列，并对应存储其日期到date_new列

示例：当Open和Low均为100时，计算值为200，High列中200.8是最接近200且最大的数值，因此High_new取200.8，date_new取其对应日期。

初始代码片段：

import pandas as pd
import numpy as np
import re

data = {'Date':['11/1/18', '12/3/18', '1/1/19', '2/1/19', '3/1/19'],
        'Open':[72, 99, 101.25, 92.05, 100],
        'High':[115.4, 144.3, 200.8, 200.3, 135.5],
        'Low' :[72,91,88.3,84,100]}
  
# Create DataFrame
df = pd.DataFrame(data)
  
# Print the output.
df

df['high2'] = np.where(df['Open'] == df['Low'], df['Open']*2, np.nan)

import pandas as pd
import numpy as np

data = {'Date':['11/1/18', '12/3/18', '1/1/19', '2/1/19', '3/1/19'],
        'Open':[72, 99, 101.25, 92.05, 100],
        'High':[115.4, 144.3, 200.8, 200.3, 135.5],
        'Low' :[72,91,88.3,84,100]}
  
df = pd.DataFrame(data)

# 计算目标值：Open与Low相等时取Open*2，否则为NaN
# 若需真实乘积，替换为 df['Open'] * df['Low']
df['target'] = np.where(df['Open'] == df['Low'], df['Open'] * 2, np.nan)

# 定义函数：根据目标值匹配最接近且最大的High及对应日期
def match_best_high(target):
    if pd.isna(target):
        return (np.nan, np.nan)
    # 计算每个High与目标值的绝对差
    temp_df = df.assign(abs_diff=lambda x: abs(x['High'] - target))
    # 按绝对差升序、High降序排序，取第一行的结果
    best_record = temp_df.sort_values(by=['abs_diff', 'High'], ascending=[True, False]).iloc[0]
    return (best_record['High'], best_record['Date'])

# 应用函数到每一行，拆分结果到新列
df[['High_new', 'date_new']] = df['target'].apply(lambda x: pd.Series(match_best_high(x)))

# 清理临时列
df.drop(columns=['target'], inplace=True)

print(df)

执行后输出结果：

Date    Open   High    Low  High_new date_new
0  11/1/18   72.00  115.4   72.0     115.4  11/1/18
1  12/3/18   99.00  144.3   91.0       NaN      NaN
2   1/1/19  101.25  200.8   88.3       NaN      NaN
3   2/1/19   92.05  200.3   84.0       NaN      NaN
4   3/1/19  100.00  135.5  100.0     200.8   1/1/19

假设数据存储在名为stock_data的表中，字段为Date、Open、High、Low，以下是MySQL兼容的实现：

WITH target_records AS (
    SELECT 
        Date AS original_date,
        Open * 2 AS target  -- 需真实乘积则改为 Open * Low
    FROM stock_data
    WHERE Open = Low
),
ranked_high_records AS (
    SELECT 
        tr.original_date,
        sd.High,
        sd.Date,
        ABS(sd.High - tr.target) AS abs_diff,
        ROW_NUMBER() OVER (
            PARTITION BY tr.original_date 
            ORDER BY ABS(sd.High - tr.target) ASC, sd.High DESC
        ) AS rank_num
    FROM target_records tr
    CROSS JOIN stock_data sd
)
SELECT 
    sd.*,
    rhr.High AS High_new,
    rhr.Date AS date_new
FROM stock_data sd
LEFT JOIN ranked_high_records rhr 
    ON sd.Date = rhr.original_date AND rhr.rank_num = 1;

逻辑说明：

1. target_records筛选出Open=Low的行并计算目标值
2. ranked_high_records将每个目标行与全表交叉连接，计算绝对差后按规则排名，排名1的即为符合要求的记录
3. 最后通过左连接将结果合并到原表，非目标行的High_new和date_new为NULL

内容的提问来源于stack exchange，提问作者Darshika Verma