問題描述

以下是表格數據，需要在同一查詢中找出整個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 |

這篇關于在同一查詢中選擇特定時期的前 5 個最大高點和 5 分鐘低點說過去 180 天和 30 天的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！