gpt4 book ai didi

sql - OHCL(开-高-低-闭)T-SQL 查询

转载 作者:行者123 更新时间:2023-12-03 03:48:18 27 4
gpt4 key购买 nike

我正在尝试对 StockData 表执行 OHCL sql 查询 (SQL Server 2012)。每天有数千行添加到表中,我想获取每天的开盘价、最高价、最低价和收盘价数据。

建表sql如下:

  CREATE TABLE [dbo].[StockData](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[StockExchangeID] [bigint] NOT NULL,
[DataDateTime] [datetime] NOT NULL,
[ExportCost] [bigint] NOT NULL,
CONSTRAINT [PK_StockData] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

使用下面的查询,我可以获得最高价、最低价和平均值,但我很难获取当天的 ExportCost 的开盘价(基于 DataDateTime 的最早 ExportCost)和收盘价(基于 DataDateTime 的最新 ExportCost)。

到目前为止我的查询如下。如果有更有效的方法,欢迎提出任何建议。

DECLARE @IntervalDays INT, @StartDate DATETIME, @EndDate DATETIME;
SET @IntervalDays = 1;
SET @StartDate = '01/01/2017'
SET @EndDate = '01/01/2018'

-- Create the database table
SELECT
StockExchangeID,
DATEADD(DAY, DATEDIFF(DAY,0,[DataDateTime]) / @IntervalDays * @IntervalDays, 0) AS [DateDay],
MAX(ExportCost) AS DayHigh,
MIN(ExportCost) AS DayLow,
AVG(ExportCost) AS DayAverage,
'??????????' As DayOpen, --Not sure how to get the start price for the day i.e. ExportCost for the first record for that day based on [DataDateTime]
'??????????' As DatClose
FROM StockData
WHERE [DataDateTime] >= @StartDate AND [DataDateTime] <= @EndDate
GROUP BY DATEADD(DAY, DATEDIFF(DAY,0,[DataDateTime]) / @IntervalDays * @IntervalDays, 0), StockExchangeID
ORDER BY [DateDay]

最佳答案

对于旧版本的 SQL Server 使用可以使用相关子查询:

SELECT 
StockExchangeID,
DATEADD(DAY, DATEDIFF(DAY,0,[DataDateTime]) / @IntervalDays * @IntervalDays, 0) AS [DateDay],
MAX(ExportCost) AS DayHigh,
MIN(ExportCost) AS DayLow,
AVG(ExportCost) AS DayAverage,
(SELECT TOP 1 DO.ExportCost FROM StockData AS DO WHERE DO.[DataDateTime] = MIN( A.[DataDateTime] ) ORDER BY ID ASC ) As DayOpen,
(SELECT TOP 1 DC.ExportCost FROM StockData AS DC WHERE DC.[DataDateTime] = MAX( A.[DataDateTime] ) ORDER BY ID DESC ) As DatClose
FROM StockData AS A
WHERE [DataDateTime] >= @StartDate AND [DataDateTime] <= @EndDate
GROUP BY DATEADD(DAY, DATEDIFF(DAY,0,[DataDateTime]) / @IntervalDays * @IntervalDays, 0), StockExchangeID
ORDER BY [DateDay]

注意: Order By ID 仅用于以防您有 1 条以上具有相同日期的记录。

对于较新的版本,请查看 @Serg 关于 FIRST_VALUE 的评论

关于sql - OHCL(开-高-低-闭)T-SQL 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47309686/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com