boowaga
问题描述
以下是表格数据，需要在同一查询中找出整个180天和过去30天相同的前5个最大高点和最小低点.
Following is the table data, need to find out the top 5 max high and min low for the entire 180 days and the same for the last 30 days in the same query.
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() 出现错误不能与窗口函数一起使用.任何替代方案.
Query, which I tried, but getting an error at rank() cannot be used with window functions. any alternatives.
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
推荐答案
一种解决对分区中的记录不起作用的方法是添加一个子查询来虚拟化存储桶，然后根据需要使用存储桶标记作为分区的一部分.
One workaround to rank not working on records in partition is to add a subquery to virtualize the buckets and then use the bucket marker as part of the partition as needed.
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 |
