如何查询3月回报率最高的前5家上市公司？

背景说明 #

现有数据表table结构为(date, name, close)：

• 每个name对应多个唯一date（纯日期，无时间部分）
• 每条记录对应某公司某日期的收盘价close

示例数据：

date     name    close
2.3.23   A       100
3.3.23   A       120
2.3.23   B       110
3.3.23   B       90

查询需求 #

统计2023年3月回报率最高的前5家公司，返回格式如下：

name  returns
A     12
B     10.2

计算规则：

• 初始值：3月首个可用日期的收盘价（周末无数据则取当月第一个有数据的日期）
• 终值：3月最后一个可用日期的收盘价
• 回报率公式：100 * (终值 - 初始值) / 初始值，结果保留1位小数

完整SQL实现 #

补全子查询后的完整SQL如下：

SELECT name, ROUND(((last_close - first_close) / first_close) * 100, 1) AS returns
FROM (
  SELECT 
    name,
    -- 取每个公司3月最早可用日期的收盘价
    FIRST_VALUE(close) OVER (PARTITION BY name ORDER BY date) AS first_close,
    -- 取每个公司3月最晚可用日期的收盘价
    LAST_VALUE(close) OVER (
      PARTITION BY name 
      ORDER BY date 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_close
  FROM `table`
  WHERE date >= '2023-03-01' AND date <= '2023-03-31'
) subquery
GROUP BY name, first_close, last_close
ORDER BY returns DESC
LIMIT 5;

关键部分解释 #

1. 窗口函数获取首尾收盘价

   • FIRST_VALUE(close) OVER (PARTITION BY name ORDER BY date)：按公司分组，按日期升序排列，取组内第一条记录的收盘价作为初始值
   • LAST_VALUE需要显式指定窗口范围为整个分组，否则默认窗口是从起始行到当前行，无法取到最后一条记录的收盘价

2. 回报率计算与格式处理

   • 用ROUND函数将回报率结果保留1位小数，匹配需求中的输出格式
   • 外层GROUP BY确保每个公司只返回一条结果，避免因窗口函数重复计算导致的重复行

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