sql-server - SQL Server:10天累计数据
问题描述
现有查询:
DECLARE @symbol1 VARCHAR(50) = 'D',
@symbol2 VARCHAR(50) = 'N',
@barDurationSeconds INT = 60,
@daysOfData INT = 1 // it gets me one day and I want it for 10 days
DECLARE @bars INT = DATEDIFF(second, '2000-01-01 9:30', '2000-01-01 16:00') / @barDurationSeconds * @daysOfData;
WITH t AS
(
SELECT
DATEDIFF(second, '2000-01-01', time)/ @barDurationSeconds startOfBar,
last,
time,
symbol
FROM
ticks (nolock)
WHERE
symbol IN (@symbol1, @symbol2)
AND time < DATEADD(second, 5, GETDATE())
AND '9:30' <= CAST(time AS TIME)
AND CAST(time AS TIME) <= '16:00'
), m as
(
SELECT
(a.last + b.last) / 2 Last,
a.startOfBar,
a.time
FROM
(SELECT * FROM t WHERE t.Symbol = @symbol1) a
JOIN
(SELECT * FROM t WHERE t.Symbol = @symbol2) b ON a.time = b.time
), r AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY startofbar ORDER BY time DESC) r
FROM
m
)
SELECT TOP (@bars)
CONVERT(VARCHAR(MAX), DATEADD(second, startofbar * @barDurationSeconds, '2000-01-01'), 126) + 'Z' Time,
last [Close],
AVG([last]) OVER (ORDER BY startofbar DESC rows between current row and 20 following) SMAS,
SUM((r.last / 1.5)) OVER (ORDER BY startofbar DESC) addclose
FROM
r
WHERE
r = 1
ORDER BY
startofbar DESC
这将返回如下输出:
2018-06-15T10:13:00Z -12020805.50000000000 -12363199.40476190476 -12020805.50000000000
2018-06-15T10:12:00Z -11987902.00000000000 -12440169.00000000000 -24008707.50000000000
2018-06-15T10:11:00Z -11969724.50000000000 -12546814.04761904761 -35978432.00000000000
2018-06-15T10:10:00Z -11904352.00000000000 -12637389.83333333333 -47882784.00000000000
2018-06-15T10:09:00Z -11525636.00000000000 -12698771.90476190476 -59408420.00000000000
2018-06-15T10:08:00Z -11669141.00000000000 -12812096.64285714285 -71077561.00000000000
2018-06-15T10:07:00Z -11905796.00000000000 -12921254.45238095238 -82983357.00000000000
2018-06-15T10:06:00Z -11739935.50000000000 -13002297.35714285714 -94723292.50000000000
2018-06-15T10:05:00Z -12324505.50000000000 -13088795.45238095238 -107047798.00000000000
2018-06-15T10:04:00Z -12431126.50000000000 -13137961.54761904761 -119478924.50000000000
所以它总是显示 390 行,即今天或昨天的 9:30 到 16:00 之间(组合在一起将有 390 行)。
所需输出:这显示了一天的数据,但现在我想要过去 10 天的累积数据。请告知我该怎么做。
编辑:我有过去 6 个月数据的数据,并且有新要求它应该返回 10 行而不是 390
解决方案
推荐阅读
- python - 我们可以让 Selenium 专注于打开的浏览器窗口吗?
- kubernetes - ConfigMap 卷未与密钥一起安装为卷
- google-ads-api - 为什么我在 Google Ads 中尝试创建新的转化操作时收到“当前客户不允许操作”?
- .htaccess - 如何更改 .htaccess 以接受阿拉伯语?
- javascript - 来自非活动选项卡中视频元素的 CanvasRenderingContext2D drawImage
- go - 指针接收器与值的方法,值和指针之间的转换
- java - 不清楚 driver.getWindowHandles() 和 driver.getWindowHandle()
- python - Pandas 从在 python 中具有多个值的单元格创建新行
- flutter - 我如何更改图标按钮中的图标
- python - 没有名为“服务”的模块