如何在同一查询中获取个股180天及30天的Top5最高/最低价？

你的问题核心在于错误地将滑动窗口（rows between）用于基于日期范围的排名，而且没有先过滤出目标时间范围内的数据，导致rank()计算的是全表排名而非指定时间段内的排名。下面给出两种可行的实现方案，适配常见的SQL数据库（比如SQL Server、PostgreSQL、MySQL 8+）。

方案一：用UNION ALL分别获取四个Top5数据集 #

这种方式逻辑清晰，容易理解，每个子查询单独处理一个时间范围的Top5需求：

SELECT * INTO SRTREND180 FROM (
    -- 1. 过去180天的Top5最高High
    SELECT 
        '180d_top_high' AS trend_type,
        RANK() OVER(PARTITION BY Stock ORDER BY High DESC) AS rn,
        t.*
    FROM Historic t
    WHERE Date >= DATEADD(day, -180, CURRENT_DATE) -- 替换为对应数据库的日期函数，比如GETDATE() for SQL Server
    QUALIFY rn <=5 -- 部分数据库支持QUALIFY，不支持的话用子查询过滤
    
    UNION ALL
    
    -- 2. 过去180天的Top5最低Low
    SELECT 
        '180d_top_low' AS trend_type,
        RANK() OVER(PARTITION BY Stock ORDER BY Low ASC) AS rn,
        t.*
    FROM Historic t
    WHERE Date >= DATEADD(day, -180, CURRENT_DATE)
    QUALIFY rn <=5
    
    UNION ALL
    
    -- 3. 最近30天的Top5最高High
    SELECT 
        '30d_top_high' AS trend_type,
        RANK() OVER(PARTITION BY Stock ORDER BY High DESC) AS rn,
        t.*
    FROM Historic t
    WHERE Date >= DATEADD(day, -30, CURRENT_DATE)
    QUALIFY rn <=5
    
    UNION ALL
    
    -- 4. 最近30天的Top5最低Low
    SELECT 
        '30d_top_low' AS trend_type,
        RANK() OVER(PARTITION BY Stock ORDER BY Low ASC) AS rn,
        t.*
    FROM Historic t
    WHERE Date >= DATEADD(day, -30, CURRENT_DATE)
    QUALIFY rn <=5
) SR;

注意事项： #

• 如果你的数据库不支持QUALIFY（比如MySQL），需要把每个子查询嵌套一层，在外层过滤rn <=5：

  -- 以30天Top High为例，替换上面的子查询
  SELECT * FROM (
      SELECT 
          '30d_top_high' AS trend_type,
          RANK() OVER(PARTITION BY Stock ORDER BY High DESC) AS rn,
          t.*
      FROM Historic t
      WHERE Date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  ) sub WHERE rn <=5
  

• 日期函数要适配你的数据库：
  • SQL Server: DATEADD(day, -N, GETDATE())
  • PostgreSQL: CURRENT_DATE - INTERVAL 'N days'
  • MySQL: DATE_SUB(CURDATE(), INTERVAL N DAY)

方案二：用CTE先筛选时间范围，再计算多维度排名 #

如果希望减少重复的日期过滤逻辑，可以先把过去180天的数据筛选出来，再在里面标记是否属于最近30天，然后一次性计算所有排名：

WITH FilteredData AS (
    SELECT 
        *,
        -- 标记是否在最近30天内
        CASE WHEN Date >= DATEADD(day, -30, CURRENT_DATE) THEN 1 ELSE 0 END AS is_in_30d
    FROM Historic
    WHERE Date >= DATEADD(day, -180, CURRENT_DATE)
),
RankedData AS (
    SELECT
        *,
        -- 180天内High的排名
        RANK() OVER(PARTITION BY Stock ORDER BY High DESC) AS rn_high180,
        -- 180天内Low的排名
        RANK() OVER(PARTITION BY Stock ORDER BY Low ASC) AS rn_low180,
        -- 30天内High的排名（仅对30天内的数据计算）
        CASE WHEN is_in_30d =1 THEN RANK() OVER(PARTITION BY Stock, is_in_30d ORDER BY High DESC) END AS rn_high30,
        -- 30天内Low的排名（仅对30天内的数据计算）
        CASE WHEN is_in_30d =1 THEN RANK() OVER(PARTITION BY Stock, is_in_30d ORDER BY Low ASC) END AS rn_low30
    FROM FilteredData
)
SELECT * INTO SRTREND180
FROM RankedData
WHERE rn_high180 <=5 
   OR rn_low180 <=5 
   OR (rn_high30 <=5 AND rn_high30 IS NOT NULL)
   OR (rn_low30 <=5 AND rn_low30 IS NOT NULL);

方案优势： #

• 只做一次180天的日期过滤，性能更优
• 通过CASE控制仅对30天内的数据计算30天维度的排名，避免无效计算

为什么你的原SQL会报错？ #

你原语句中使用rank() over (partition by name order by high desc rows between 30 preceding and current row)，这里的rows between是滑动窗口，它是基于当前行往前数30条记录，而不是基于日期的最近30天。这种用法会导致排名窗口的范围是动态变化的（每一行的窗口都不同），而且如果没有先过滤日期，窗口会包含全表数据，完全不符合你要的“最近30天内Top5”的需求。

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