以下是表格数据，需要找出整个 5 天的最高价和最低价，以及同一查询中过去 30 天的相同。
Stock High Low Date prevclose ....
------------------------------------
ABB 100 75 29/12/2019 90
ABB 83 50 30/12/2019 87
ABB 73 45 30/12/2019 87
.
.
.
.
ABB 100 67 29/06/2019 90
ABB 83 65 30/06/2019 81
infy 100 75 29/12/2019 90
infy 830 650 30/12/2019 810
infy 730 645 30/12/2019 788
.
.
.
infy 1001 556 29/06/2019 904
infy 833 657 30/06/2019 812
infy 734 643 30/06/2019 735
查询，我尝试过，但在 rank(( 处出现错误不能与窗口函数一起使用。 任何替代方案。
select * into SRTREND180 from (
select *
from (
select
rank() over(partition by name order by high desc) rn_high180,
rank() over(partition by name order by low asc) rn_low180,
rank() over (partition by name order by high desc rows between 30 preceding and current row) rn_high30,
rank() over (partition by name order by low asc rows between 30 preceding and current row) rn_low30,
t.*
from Historic t
) Hist
where rn_high180 <= 5 or rn_low180 <= 5 or rn_high30 <=5 or rn_low30 <=5
) SR
"前面 30 行和当前行之间的行"是导致错误的窗口函数。 尝试将过去 30 天的两个选择字段分离到一个单独的查询中，或者将它们放在一个子查询中，选择数据的窗口部分移动到 where 子句。
编辑：尝试在查询开始时使用以下 CTE(将表/字段名称更改为您的(。 这将允许您在拥有两个单独的数据表的情况下编写查询，一个包含过去 180 天的排名低/高，另一个包含过去 30 天的排名低/高。
with last180 (name, closedate, low, high, lowrank, highrank) as
(
select name, closedate, low, high, rank() over(partition by name order by low asc), rank() over(partition by name order by high desc)
from @table where datediff(day, closedate, getdate()) <= 180
),
last30 (name, closedate, low, high, lowrank, highrank) as
(
select name, closedate, low, high, rank() over(partition by name order by low asc), rank() over(partition by name order by high desc)
from @table where datediff(day, closedate, getdate()) <= 30
)
对分区中的记录不起作用进行排名的一种解决方法是添加一个子查询来虚拟化存储桶，然后根据需要使用存储桶标记作为分区的一部分。
SQL 小提琴
MS SQL Server 2017 架构设置：
CREATE TABLE T (name NVARCHAR(20), High INT, Low INT, Date DATETIME, PrevClose INT)
INSERT T VALUES
('ABB', 100, 75,'12/29/2019',90),
('ABB', 83, 50,'12/30/2019',87),
('ABB', 73, 45,'12/30/2019',87),
('ABB', 100, 67,'06/29/2019',90),
('ABB', 83, 65,'06/30/2019',81),
('INFY', 100, 75,'12/29/2019',90),
('INFY', 830, 600,'12/30/2019',810),
('INFY', 730, 645,'12/30/2019',788),
('INFY', 1001, 556,'06/29/2019',904),
('INFY', 833, 657,'06/30/2019',812),
('INFY', 734,643, '06/30/2019',735),
('INFY', 734,643, '07/30/2019',735)
查询 1：
DECLARE @ReportDate DATETIME = GETDATE()
;WITH DataWithDayFlag AS
(
select
*,
DaysOut = DATEDIFF(DAY,date,@ReportDate),
Bucket30 = CASE WHEN DATEDIFF(DAY,date,@ReportDate) <= 30 THEN 1 ELSE NULL END,
Bucket180 = CASE WHEN DATEDIFF(DAY,date,@ReportDate) <= 180 THEN 1 ELSE NULL END
FROM
T
)
SELECT
CASE WHEN Bucket180 IS NOT NULL THEN rank() over (partition by name, Bucket180 order by high desc) ELSE NULL END rn_high180,
CASE WHEN Bucket180 IS NOT NULL THEN rank() over (partition by name, Bucket180 order by low asc) ELSE NULL END rn_low180,
CASE WHEN Bucket30 IS NOT NULL THEN rank() over (partition by name, Bucket30 order by high desc) ELSE NULL END rn_high30,
CASE WHEN Bucket30 IS NOT NULL THEN rank() over (partition by name, Bucket30 order by low asc) ELSE NULL END rn_low30,
t.*
from
DataWithDayFlag t
where
DaysOut <= 180
ORDER BY
name
结果：
| rn_high180 | rn_low180 | rn_high30 | rn_low30 | name | High | Low | Date | PrevClose | DaysOut | Bucket30 | Bucket180 |
|------------|-----------|-----------|----------|------|------|-----|----------------------|-----------|---------|----------|-----------|
| 3 | 1 | 3 | 1 | ABB | 73 | 45 | 2019-12-30T00:00:00Z | 87 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | ABB | 83 | 50 | 2019-12-30T00:00:00Z | 87 | 1 | 1 | 1 |
| 1 | 3 | 1 | 3 | ABB | 100 | 75 | 2019-12-29T00:00:00Z | 90 | 2 | 1 | 1 |
| 2 | 3 | (null) | (null) | INFY | 734 | 643 | 2019-07-30T00:00:00Z | 735 | 154 | (null) | 1 |
| 4 | 1 | 3 | 1 | INFY | 100 | 75 | 2019-12-29T00:00:00Z | 90 | 2 | 1 | 1 |
| 1 | 2 | 1 | 2 | INFY | 830 | 600 | 2019-12-30T00:00:00Z | 810 | 1 | 1 | 1 |
| 3 | 4 | 2 | 3 | INFY | 730 | 645 | 2019-12-30T00:00:00Z | 788 | 1 | 1 | 1 |