在同一查询中选择特定时期的前 5 个最大高点和 5 分钟低点说过去 180 天和 30 天

2021-09-10 00:00:00 sql tsql sql-server

以下是表格数据，需要在同一查询中找出整个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 |

相关文章