主页 / dba / 问题 / 224620
Accepted
Ganesh_55
Asked: 2018-12-11 23:30:08 +0800 CST

根据当前日期和上一个日期获取市场数据

  • 772

表价：

ID  Date        OPEN    HIGH    Low    CLOSE    
417 9/23/1994   24.399  24.399  24.399  24.399  
417 9/28/1994   23.3    23.3    23.3    23.3    
417 9/29/1994   23.35   23.35   23.35   23.35   
417 9/30/1994   22.55   22.55   22.55   22.55   
418 5/22/2014   47.299  47.299  47.299  47.299  
418 5/23/2014   47.299  47.299  47.299  47.299  
418 5/26/2014   47.1    47.1    47.1    47.1    
418 5/27/2014   47.35   47.35   47.35   47.35

我想要这样的结果：

id  Open    HIGH    LOW     CLOSE   PervClose   Change     Change%
417 22.55   22.55   22.55   22.55   23.35     22.55-22.55   (22.55-22.55)/100
418 47.35   47.35   47.35   47.35   47.1      47.35-47.1    (47.35-47.1)/100

注：((prevclose为previousdateclose,change=close-prevclose),change%=(close-prevclose)/100)

sql-server-2008-r2

1 个回答

  1. Best Answer

    我不知道 columns 的逻辑HIGH，LOW...

    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL 
        DROP TABLE tempdb..#tmp;


CREATE TABLE #tmp
    ([ID] int, [Date] datetime, [OPEN] decimal(6,3), [HIGH] decimal(6,3), [Low] decimal(6,3), [CLOSE] decimal(6,3))
;

INSERT INTO #tmp
    ([ID], [Date], [OPEN], [HIGH], [Low], [CLOSE])
VALUES
    (417, '1994-09-23 00:00:00', 24.399, 24.399, 24.399, 24.399),
    (417, '1994-09-28 00:00:00', 23.3, 23.3, 23.3, 23.3),
    (417, '1994-09-29 00:00:00', 23.35, 23.35, 23.35, 23.35),
    (417, '1994-09-30 00:00:00', 22.55, 22.55, 22.55, 22.55),
    (418, '2014-05-22 00:00:00', 47.299, 47.299, 47.299, 47.299),
    (418, '2014-05-23 00:00:00', 47.299, 47.299, 47.299, 47.299),
    (418, '2014-05-26 00:00:00', 47.1, 47.1, 47.1, 47.1),
    (418, '2014-05-27 00:00:00', 47.35, 47.35, 47.35, 47.35)
;

;WITH cteTmp AS
(
SELECT
    [ID], [Date], [OPEN], [HIGH], [Low], [CLOSE]
    ,ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY [Date] DESC) AS RN
FROM #tmp as t
)

SELECT [ID],[OPEN]
            ,MAX([OPEN]) as [HIGH]
            ,MIN([OPEN]) as [LOW]
            ,[CLOSE] 
            ,oa.prevClose
            ,[CLOSE] - oa.prevClose as Change
            ,([CLOSE] - oa.prevClose)/100.0 as [Change%]
FROM cteTmp as t    
        OUTER APPLY
        ( SELECT TOP(1) prev.[CLOSE] as prevClose
            FROM cteTmp as prev
            WHERE prev.ID =t.ID
                AND prev.[Date]<t.[Date]
            ORDER By prev.[Date] DESC
        ) oa
WHERE t.rn = 1
GROUP BY  [ID],[OPEN],[CLOSE], oa.prevClose

    所需的输出：

    ID    OPEN       HIGH       LOW       CLOSE      prevClose   Change      Change%
417   22.550     22.550     22.550    22.550     23.350     -0.800      -0.00800000
418   47.350     47.350     47.350    47.350     47.100      0.250       0.00250000
    • 0

相关问题