问从SQL DB中提取(抽样)时间序列
EN

Stack Overflow用户

提问于 2016-08-09 05:17:17

回答 4查看 184关注 0票数 1

我有一个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程序重新格式化结果表，但这似乎有点奇怪，因为我似乎再次查询了结果表.

sql-server

tsql

腾讯云会员中心

加入腾讯云会员，每月可领会员专属优惠券；会员云产品免费体验；云产品会员专享价；会员签到抽奖…

回答 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

谢谢大家的想法和支持！

票数 0

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;

结果：

票数 1

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