对开放、关闭、高、低的查询改进

Question

我试图找到一个货币对的开放，关闭，高，低的价格。我让它起作用了，但我想知道是否有更简单的方法。

我的源表如下所示：

CREATE TABLE [dbo].[RATES](
    [SYSID] [bigint] IDENTITY(1,1) NOT NULL,
    [Item] [nvarchar](255) NULL,
    [Bid] [float] NULL,
    [Ask] [float] NULL,
    [Spread] [float] NULL,
    [DT] [datetime] NULL
) ON [PRIMARY]

以下是一些样本数据

INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04805,1.04828,0.000230000000000175,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04832,0.000240000000000018,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04831,0.000230000000000175,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04832,0.000240000000000018,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04833,0.000250000000000083,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04832,0.000240000000000018,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04802,1.04827,0.000250000000000083,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04806,1.04831,0.000250000000000083,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04833,0.000250000000000083,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04801,1.04827,0.000259999999999927,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04806,1.04831,0.000250000000000083,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04806,1.04832,0.000259999999999927,'8/29/2013 5:02')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04832,0.000240000000000018,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04833,0.000250000000000083,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04829,0.000210000000000043,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04808,1.04833,0.000250000000000083,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04809,1.04833,0.000240000000000018,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04809,1.0483,0.000210000000000043,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04809,1.04833,0.000240000000000018,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04809,1.04832,0.000229999999999952,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04809,1.04833,0.000240000000000018,'8/29/2013 5:03')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04807,1.04833,0.000259999999999927,'8/29/2013 5:04')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04809,1.04828,0.000190000000000135,'8/29/2013 5:04')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04809,1.04833,0.000240000000000018,'8/29/2013 5:04')
INSERT INTO dbo.RATES (ITEM,BID,ASK,SPREAD,DT) VALUE ('USD/CAD',1.04809,1.04829,0.000199999999999978,'8/29/2013 5:04')

我的查询如下

DECLARE @OPENCLOSE AS TABLE (
    GRP INT,
    TF DATETIME,
    DT DATETIME,
    ASK FLOAT
)

DECLARE @CLOSE_ID AS TABLE (
    TF DATETIME,
    GRP INT
)

INSERT INTO @OPENCLOSE (GRP,TF,DT,ASK)
select
    ROW_NUMBER() OVER (PARTITION BY dateadd(mi, datediff(mi, 0, r.DT), 0) ORDER BY r.DT) AS GRP,
    dateadd(mi, datediff(mi, 0, r.DT), 0) AS TF,
    r.DT,
    r.Ask
from dbo.RATES r
group by dateadd(mi, datediff(mi, 0, r.DT), 0), r.DT,r.Ask
ORDER BY dateadd(mi, datediff(mi, 0, r.DT), 0)

INSERT INTO @CLOSE_ID
SELECT 
    TF,
    MAX(GRP) AS CLOSE_ID
FROM @OPENCLOSE
group by TF

SELECT X.TF,X.O,X.C,Y.HIGH,Y.LOW
FROM (
    SELECT 
        oc1.TF,
        oc2.ASK AS [O],
        oc1.ASK AS [C]
    FROM @OPENCLOSE oc1
    JOIN @OPENCLOSE oc2
      ON oc2.TF = oc1.TF
 AND oc2.GRP = 1
JOIN @CLOSE_ID cid
  ON cid.TF = oc1.TF
 AND cid.GRP = oc1.GRP
GROUP BY oc1.TF,Oc2.ASK,oc1.ASK
) x
JOIN 
(
    SELECT 
        oc1.TF,
        MAX(oc1.ASK) AS HIGH,
        MIN(oc1.ASK) AS LOW
    FROM @OPENCLOSE oc1
    GROUP BY oc1.TF
) y ON y.TF = x.TF

Answer 1

这个怎么样？

;with z as (select
    ROW_NUMBER() OVER (PARTITION BY dateadd(mi, datediff(mi, 0, r.DT), 0) ORDER BY r.DT) AS GRP,
    dateadd(mi, datediff(mi, 0, r.DT), 0) AS TF,
    r.DT,
    r.Ask
from dbo.RATES r
)
select 
    z.TF,
    O=MAX(case when z.grp=1 then ask else 0 end),
    C=MAX(case when z.grp=x.mx then ask else 0 end),
    HIGH=MAX(ask),
    LOW=MIN(ask)
from z
inner join (select tf,mx=max(grp) from z group by tf ) x on x.tf = z.tf
group by
    z.TF

Answer 2

也许这个查询会给您提供您想要的内容：

SELECT r.dt as TF,
       r_o.ask AS O,
       r_c.ask AS C,
       max(r.ask) AS HIGH, min(r.ask) AS LOW
FROM rates r
INNER JOIN (SELECT item, dt, 
                   min(SYSID) AS MINSYSID, max(SYSID) AS MAXSYSID
              FROM rates
             GROUP BY item, dt) r_id
        ON r_id.item = r.item AND
           r.dt = r_id.dt
INNER JOIN rates r_o ON r_o.SYSID = r_id.MINSYSID
INNER JOIN rates r_c ON r_c.SYSID = r_id.MAXSYSID
GROUP BY r.dt,
         r_o.ask,r_c.ask;

请参阅SQLFIDDLE

若要进行询问和出价，请查看以下查询：

一天：

SELECT r.item, convert(varchar(10),r.dt, 101),
       max(r.bid) AS bid_high, min(r.bid) AS bid_low,
       max(r.ask) AS ask_high, min(r.ask) AS ask_low,
       r_o.bid AS bid_open,r_o.ask AS ask_open,
       r_c.bid AS bid_close,r_c.ask AS ask_close
FROM rates r
INNER JOIN (SELECT item, convert(varchar(10),dt, 101) AS dt, 
                   min(SYSID) AS MINSYSID, max(SYSID) AS MAXSYSID
            FROM rates
            GROUP BY item, convert(varchar(10),dt, 101)) r_id
         ON r_id.item = r.item AND
            convert(varchar(10),r.dt, 101) = r_id.dt
INNER JOIN rates r_o ON r_o.SYSID = r_id.MINSYSID
INNER JOIN rates r_c ON r_c.SYSID = r_id.MAXSYSID
GROUP BY r.item, convert(varchar(10),r.dt, 101),
         r_o.bid,r_o.ask,r_c.bid,r_c.ask

请参阅SQLFIDDLE

每一分钟，都是这样的：

SELECT r.item, r.dt,
       max(r.bid) AS bid_high, min(r.bid) AS bid_low,
       max(r.ask) AS ask_high, min(r.ask) AS ask_low,
       r_o.bid AS bid_open,r_o.ask AS ask_open,
       r_c.bid AS bid_close,r_c.ask AS ask_close
FROM rates r
INNER JOIN (SELECT item, dt, 
                   min(SYSID) AS MINSYSID, max(SYSID) AS MAXSYSID
            FROM rates
            GROUP BY item, dt) r_id
         ON r_id.item = r.item AND
            r.dt = r_id.dt
INNER JOIN rates r_o ON r_o.SYSID = r_id.MINSYSID
INNER JOIN rates r_c ON r_c.SYSID = r_id.MAXSYSID
GROUP BY r.item, r.dt,
         r_o.bid,r_o.ask,r_c.bid,r_c.ask

请参阅SQLFIDDLE

Answer 3

您可以尝试此解决方案：

SELECT  d.Item, d.DT_HHMM,

        FirstAsk= MAX(d.FirstAsk), 
        FirstBid= MAX(d.FirstBid),
        LastAsk = MAX(d.LastAsk), 
        LastBid = MAX(d.LastBid),

        MaxAsk  = MAX(d.MaxAsk), 
        MaxBid  = MAX(d.MaxBid),
        MinAsk  = MAX(d.MinAsk), 
        MinBid  = MAX(d.MinBid)
FROM
(
SELECT  c.Item,
        c.DT_HHMM,
        CASE WHEN c.RowNumASC = 1 THEN c.Ask END AS FirstAsk,
        CASE WHEN c.RowNumASC = 1 THEN c.Bid END AS FirstBid,
        CASE WHEN c.RowNumDESC = 1 THEN c.Ask END AS LastAsk,
        CASE WHEN c.RowNumDESC = 1 THEN c.Bid END AS LastBid,       
        c.MinBid, c.MinAsk,
        c.MaxBid, c.MaxAsk
FROM
(
    SELECT  *,
            RowNumASC   = ROW_NUMBER() OVER(PARTITION BY b.Item, b.DT_HHMM ORDER BY b.DT ASC),
            RowNumDESC  = ROW_NUMBER() OVER(PARTITION BY b.Item, b.DT_HHMM ORDER BY b.DT DESC),
            MinBid      = MIN(Bid) OVER(PARTITION BY b.Item, b.DT_HHMM),
            MinAsk      = MIN(Ask) OVER(PARTITION BY b.Item, b.DT_HHMM),
            MaxBid      = MAX(Bid) OVER(PARTITION BY b.Item, b.DT_HHMM),
            MaxAsk      = MAX(Ask) OVER(PARTITION BY b.Item, b.DT_HHMM)
    FROM
    (
        SELECT  *,DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.DT), 0) AS DT_HHMM
        FROM    dbo.RATES a
    ) b 
) c
WHERE   c.RowNumASC = 1 OR c.RowNumDESC = 1 
) d
GROUP BY d.Item, d.DT_HHMM