从SQL DB中提取(抽样)时间序列
从SQL DB中提取(抽样)时间序列
提问于 2016-08-09 05:17:17
我有一个MS数据库，其中包含带有时间戳的值。我的结果表如下所示：
date value
03.01.2016 11
19.01.2016 22
29.01.2016 33
17.02.2016 44
01.03.2016 55
06.03.2016 66时间戳实际上不太符合某种模式。现在，我需要从这里提取每周的数据：(例如，星期五抽样)
date value
01.01.2016 11 // friday
08.01.2016 11 // next friday
15.01.2016 11
22.01.2016 22
29.01.2016 33
05.02.2016 33
12.02.2016 33
19.02.2016 44
26.02.2016 44
04.03.2016 55
11.03.2016 66在T中有合理的方法直接这样做吗？
我可以使用C#或Matlab程序重新格式化结果表，但这似乎有点奇怪，因为我似乎再次查询了结果表.
回答 4
Stack Overflow用户
回答已采纳
发布于 2016-08-11 00:42:46
我找到了我自己的解决方案，我觉得这更容易读懂。我首先使用WHILE循环来生成我正在寻找的日期。然后，我使用外部应用程序将这些日期“连接”到实际数据表，该表查找“特定日期之前的最后一个值”。下面是代码：
-- prepare in-memory table
declare @tbl table ( [date] date, [value] int )
insert into @tbl
values
('2016-01-03','11'),
('2016-01-19','22'),
('2016-01-29','33'),
('2016-02-17','44'),
('2016-03-01','55'),
('2016-03-06','66')
-- query
declare @startDate date='2016-01-01';
declare @endDate date='2016-03-31';
with Fridays as (
select @startDate as fridayDate
union all
select dateadd(day,7,fridayDate) from Fridays where dateadd(day,7,fridayDate)<=@endDate
)
select *
from
Fridays f
outer apply (
select top(1) * from @tbl t
where f.fridayDate >= t.[date]
order by t.[value] desc
) as result
option (maxrecursion 10000)给我：
fridayDate date value
---------- ---------- -----------
2016-01-01 NULL NULL
2016-01-08 2016-01-03 11
2016-01-15 2016-01-03 11
2016-01-22 2016-01-19 22
2016-01-29 2016-01-29 33
2016-02-05 2016-01-29 33
2016-02-12 2016-01-29 33
2016-02-19 2016-02-17 44
2016-02-26 2016-02-17 44
2016-03-04 2016-03-01 55
2016-03-11 2016-03-06 66
2016-03-18 2016-03-06 66
2016-03-25 2016-03-06 66谢谢大家的想法和支持！
Stack Overflow用户
发布于 2016-08-09 05:43:19
您可以使用CROSS JOIN或INNER JOIN。我个人认为INNER JOIN更有效率。
样本数据：
CREATE TABLE #Temp(SomeDate DATE
, SomeValue VARCHAR(10));
INSERT INTO #Temp(SomeDate
, SomeValue)
VALUES
('20160103'
, 11),
('20160119'
, 22),
('20160129'
, 33),
('20160217'
, 44),
('20160301'
, 55),
('20160306'
, 66)使用交叉连接的查询：
;WITH T
AS (SELECT *
FROM #Temp),
D
AS (
SELECT SomeDate
, SomeValue
FROM #Temp AS A
UNION
SELECT DATEADD(day, 7, SomeDate)
, SomeValue
FROM #Temp AS B
UNION
SELECT DATEADD(day, 14, SomeDate)
, SomeValue
FROM #Temp AS C)
SELECT D.*
FROM T
CROSS JOIN D
WHERE T.SomeValue = D.SomeValue
ORDER BY SomeValue
, SomeDate;结果：
使用内部连接的查询：
;WITH T
AS (SELECT *
FROM #Temp),
D
AS (
SELECT SomeDate
, SomeValue
FROM #Temp AS A
UNION
SELECT DATEADD(day, 7, SomeDate)
, SomeValue
FROM #Temp AS B
UNION
SELECT DATEADD(day, 14, SomeDate)
, SomeValue
FROM #Temp AS C)
SELECT D.*
FROM T
INNER JOIN D
ON T.SomeValue = D.SomeValue
ORDER BY SomeValue
, SomeDate;结果：
Stack Overflow用户
发布于 2016-08-09 06:25:24
此解决方案支持从第一个值时间开始的252周的最长时间窗口。
您想要的输出的第一行丢失了，因为星期五在第一个值之前。如果需要的话，您可以通过一个带有一分钟表的UNION来添加它。
DECLARE @tbl TABLE ( [date] date, [value] int )
INSERT INTO @tbl
VALUES
('2016-01-03','11'),
('2016-01-19','22'),
('2016-01-29','33'),
('2016-02-17','44'),
('2016-03-01','55'),
('2016-03-06','66')
;WITH DATA
AS (
SELECT (S+P+Q) WeekNum, DATEADD( week, S + P + Q, MinDate ) Fridays, SubFri, [value]
FROM ( SELECT 1 S UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 ) A
CROSS JOIN ( SELECT 0 P UNION SELECT 7 UNION SELECT 14 UNION SELECT 21 UNION SELECT 28 UNION SELECT 35 ) B
CROSS JOIN ( SELECT 0 Q UNION SELECT 42 UNION SELECT 84 UNION SELECT 126 UNION SELECT 168 UNION SELECT 210 ) C
CROSS JOIN (
SELECT
min ( DATEADD( day, -8 - DATEPART(weekday,[date]), [date] ) ) MinDate,
max ( DATEADD( day, 13 - DATEPART(weekday,[date]), [date] ) ) MaxDate
FROM @tbl
) MD
LEFT JOIN ( SELECT DATEADD( day, 6 - DATEPART(weekday,[date]), [date] ) SubFri, [value] FROM @tbl ) Val
ON SubFri<=DATEADD( week, S + P + Q, MinDate )
WHERE DATEADD( week, S + P + Q, MinDate )<=MaxDate
)
SELECT DATA.Fridays, DATA.value
FROM DATA
INNER JOIN
(
SELECT Fridays, max(SubFri) MaxSubFri
FROM DATA
GROUP BY Fridays
) idx
ON DATA.Fridays=idx.Fridays
AND SubFri=MaxSubFri
ORDER BY Fridays页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38851896
复制相关文章
相似问题
腾讯云开发者
