文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
社区首页 >问答首页 >使用SQL Server生成15分钟插槽的数据平均值

问使用SQL Server生成15分钟插槽的数据平均值
EN

Stack Overflow用户
提问于 2018-12-04 02:00:00
回答 3查看 66关注 0票数 0

我有一个SQL Server表，如下所示。

代码语言：javascript
复制
CREATE TABLE [dbo].[ChannelData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ChannelId] [int] NOT NULL,
[ChannelValue] [decimal](10, 2) NULL,
[ChannelDataLogTime] [datetime] NOT NULL,
[Active] [bit] NULL,CONSTRAINT [PK_ChannelData] 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]

示例数据如下：

代码语言：javascript
复制
+----+-----------+--------------+-------------------------+--------+
| Id | ChannelId | ChannelValue |   ChannelDataLogTime    | Active |
+----+-----------+--------------+-------------------------+--------+
|  1 |         9 |         5.46 | 2015-06-09 14:00:11.463 |      1 |
|  2 |         9 |         8.46 | 2015-06-09 14:01:11.503 |      1 |
|  3 |         9 |         3.46 | 2015-06-09 14:02:27.747 |      1 |
|  4 |         9 |         6.46 | 2015-06-09 14:03:11.503 |      1 |
|  5 |         9 |         1.46 | 2015-06-09 14:04:11.530 |      1 |
|  6 |         9 |         4.46 | 2015-06-09 14:05:11.537 |      1 |
|  7 |         9 |         7.46 | 2015-06-09 14:06:11.547 |      1 |
|  8 |         9 |         2.46 | 2015-06-09 14:07:33.983 |      1 |
|  9 |         9 |         5.46 | 2015-06-09 14:08:11.570 |      1 |
| 10 |         9 |         8.46 | 2015-06-09 14:09:11.603 |      1 |
| 11 |         9 |         3.46 | 2015-06-09 14:10:11.613 |      1 |
| 12 |         9 |         6.47 | 2015-06-09 14:11:11.623 |      1 |
| 13 |         9 |         1.47 | 2015-06-09 14:12:24.497 |      1 |
| 14 |         9 |         4.47 | 2015-06-09 14:13:11.623 |      1 |
| 15 |         9 |         7.47 | 2015-06-09 14:14:11.650 |      1 |
| 16 |         9 |         2.47 | 2015-06-09 14:15:11.707 |      1 |
| 17 |         9 |         5.47 | 2015-06-09 14:16:11.707 |      1 |
| 18 |         9 |         8.47 | 2015-06-09 14:17:25.647 |      1 |
| 19 |         9 |         3.47 | 2015-06-09 14:18:11.707 |      1 |
| 20 |         9 |         6.47 | 2015-06-09 14:19:11.753 |      1 |
| 21 |         9 |         1.47 | 2015-06-09 14:20:11.760 |      1 |
| 22 |         9 |         4.47 | 2015-06-09 14:21:11.790 |      1 |
| 23 |         9 |         7.47 | 2015-06-09 14:22:29.500 |      1 |
| 24 |         9 |         2.47 | 2015-06-09 14:23:11.907 |      1 |
| 25 |         9 |         5.47 | 2015-06-09 14:24:12.057 |      1 |
| 26 |         9 |         8.47 | 2015-06-09 14:25:11.817 |      1 |
| 27 |         9 |         3.47 | 2015-06-09 14:26:11.837 |      1 |
| 28 |         9 |         6.47 | 2015-06-09 14:27:32.253 |      1 |
| 29 |         9 |         1.47 | 2015-06-09 14:28:11.870 |      1 |
| 30 |         9 |         4.47 | 2015-06-09 14:29:11.870 |      1 |
| 31 |         9 |         7.50 | 2015-06-09 16:00:13.313 |      1 |
| 32 |         9 |         2.50 | 2015-06-09 16:01:13.260 |      1 |
| 33 |         9 |         5.50 | 2015-06-09 16:02:13.290 |      1 |
| 34 |         9 |         8.50 | 2015-06-09 16:03:13.270 |      1 |
| 35 |         9 |         3.50 | 2015-06-09 16:04:32.827 |      1 |
| 36 |         9 |         6.50 | 2015-06-09 16:05:13.323 |      1 |
| 37 |         9 |         1.50 | 2015-06-09 16:06:13.330 |      1 |
| 38 |         9 |         4.50 | 2015-06-09 16:07:13.337 |      1 |
| 39 |         9 |         7.50 | 2015-06-09 16:08:13.313 |      1 |
| 40 |         9 |         2.50 | 2015-06-09 16:09:28.497 |      1 |
| 41 |         9 |         5.50 | 2015-06-09 16:10:13.370 |      1 |
| 42 |         9 |         8.50 | 2015-06-09 16:11:13.417 |      1 |
| 43 |         9 |         3.50 | 2015-06-09 16:12:13.540 |      1 |
| 44 |         9 |         6.50 | 2015-06-09 16:13:13.577 |      1 |
| 45 |         9 |         1.50 | 2015-06-09 16:14:33.880 |      1 |
| 46 |         9 |         4.50 | 2015-06-09 16:15:13.453 |      1 |
| 47 |         9 |         7.50 | 2015-06-09 16:16:13.500 |      1 |
| 48 |         9 |         2.50 | 2015-06-09 16:17:13.497 |      1 |
| 49 |         9 |         5.50 | 2015-06-09 16:18:13.503 |      1 |
| 50 |         9 |         8.50 | 2015-06-09 16:19:38.717 |      1 |
| 51 |         9 |         3.50 | 2015-06-09 16:21:13.567 |      1 |
| 52 |         9 |         6.50 | 2015-06-09 16:22:13.557 |      1 |
| 53 |         9 |         1.50 | 2015-06-09 16:23:14.163 |      1 |
| 54 |         9 |         4.50 | 2015-06-09 16:24:13.607 |      1 |
| 55 |         9 |         7.50 | 2015-06-09 16:25:38.783 |      1 |
| 56 |         9 |         2.50 | 2015-06-09 16:27:13.660 |      1 |
| 57 |         9 |         5.51 | 2015-06-09 16:28:13.710 |      1 |
| 58 |         9 |         8.51 | 2015-06-09 16:29:13.703 |      1 |
| 59 |         9 |         3.51 | 2015-06-09 16:30:13.713 |      1 |
+----+-----------+--------------+-------------------------+--------+

现在，我正在生成一段时间内15分钟的平均数据，包括开始日期和结束日期。它工作得很好，没有任何问题。

我有一个场景，数据将在一段时间内丢失。这又错过了15分钟的时隙，因为对于该15分钟的时隙没有数据。我需要的是列出15分钟的时间段，即使数据在该时间段内使用SQL查询不可用。

代码语言：javascript
复制
SELECT
                            Avg(chnldata.ChannelValue)  AS ChannelValue,
                            DATEADD(minute,FLOOR(DATEDIFF(minute,0,ChannelDataLogTime)/15)*15,0) as HourlyDateTime,
                           chnldata.ChannelId as Id
                    FROM ChannelData as chnldata
                    WHERE  chnldata.ChannelId in (9) AND chnldata.ChannelDataLogTime >= '06/09/2015'  AND chnldata.ChannelDataLogTime < '06/11/2015 23:59:50' 
                     GROUP BY chnldata.ChannelId, DATEADD(minute,FLOOR(DATEDIFF(minute,0,ChannelDataLogTime)/15)*15,0)

这是现有的15分钟平均查询。但它不会显示丢失的15分钟插槽。当前输出为：

代码语言：javascript
复制
+--------------+-------------------------+----+
| ChannelValue |     HourlyDateTime      | Id |
+--------------+-------------------------+----+
|     5.129333 | 2015-06-09 14:00:00.000 |  9 |
|     4.803333 | 2015-06-09 14:15:00.000 |  9 |
|     5.033333 | 2015-06-09 16:00:00.000 |  9 |
|     5.270769 | 2015-06-09 16:15:00.000 |  9 |
|     3.510000 | 2015-06-09 16:30:00.000 |  9 |
+--------------+-------------------------+----+

所需输出为：

代码语言：javascript
复制
+--------------+-------------------------+----+
| ChannelValue |     HourlyDateTime      | Id |
+--------------+-------------------------+----+
| 5.129333     | 2015-06-09 14:00:00.000 |  9 |
| 4.803333     | 2015-06-09 14:15:00.000 |  9 |
| NULL         | 2015-06-09 14:30:00.000 |  9 |
| NULL         | 2015-06-09 14:45:00.000 |  9 |
| NULL         | 2015-06-09 15:00:00.000 |  9 |
| NULL         | 2015-06-09 15:15:00.000 |  9 |
| NULL         | 2015-06-09 15:30:00.000 |  9 |
| NULL         | 2015-06-09 15:45:00.000 |  9 |
| 5.033333     | 2015-06-09 16:00:00.000 |  9 |
| 5.270769     | 2015-06-09 16:15:00.000 |  9 |
| 3.510000     | 2015-06-09 16:30:00.000 |  9 |
+--------------+-------------------------+----+
sql
sql-server
tsql
EN

回答 3

Stack Overflow用户

发布于 2018-12-04 02:08:43

右外部加入到在您的时间范围内具有所有可能的15分钟间隔的CTE。

票数 1
EN

Stack Overflow用户

发布于 2018-12-04 03:05:09

构建时间范围CTE，这可以通过多种方式完成，但是笛卡尔乘积方法可能比许多方法更快。如果您想要更快，最好是构建一个静态日期表，也许还是一个日期和时间表

代码语言：javascript
复制
;WITH mins as (SELECT 0 as q union select 15 union select 30 union select 45),
      dats as (SELECT MIN(ChannelDataLogTime) as t1, max(ChannelDataLogTime) as t2 from channeldata),
      ranges as (SELECT CAST(t1 as date) s1 FROM dats
                union all
                SELECT dateadd(day,1,r.s1) from ranges r where r.s1< (select t2 from dats)
                ),
      hrs as (select 0 h union all select  h + 1 from hrs where h < 23),    --hours 0 to 23
      slots as (select dateadd(MINUTE,mins.q,dateadd(hour,hrs.h,cast(ranges.s1 as datetime2))) as strt from mins,ranges,hrs      ),
      ids as (SELECT distinct  ChannelId from  ChannelData),
      allslot as (select channelid, strt from slots,ids)
        SELECT    count(0) as x,
                            coalesce(Avg(chnldata.ChannelValue)   , 0) AS ChannelValue,
                            s.strt HourlyDateTime,
                           s.ChannelId as Id
                    FROM ChannelData as chnldata
                    RIGHT JOIN  allslot s on s.strt <= ChannelDataLogTime and ChannelDataLogTime < dateadd(minute,15,s.strt) and s.ChannelId = chnldata.ChannelId 
                    WHERE  chnldata.ChannelId is null or chnldata.ChannelId in (9) AND chnldata.ChannelDataLogTime >= '20150906'  AND chnldata.ChannelDataLogTime < '20151123' 
                     GROUP BY s.ChannelId, s.strt
票数 0
EN

Stack Overflow用户

发布于 2018-12-04 03:18:33

考虑到最大递归选项的局限性。

代码语言：javascript
复制
DECLARE @StartDT DATETIME = '2015-06-09';
DECLARE @EndDT DATETIME = '2015-06-12'; -- moved to the next day to use >= and < operators correctly

;WITH
[Interval]
AS
(
    SELECT
         [Start] = @StartDT
        ,[End] = DATEADD(MINUTE, 15, @StartDT)

    UNION ALL

    SELECT
         [Start] = [End]
        ,[End] = DATEADD(MINUTE, 15, [End])
    FROM [Interval]
    WHERE (1 = 1)
        AND ([End] < @EndDT)
),
[Available]
AS
(
    SELECT
         [Start] = CONVERT(SMALLDATETIME, MIN([CD].[ChannelDataLogTime]))
        ,[End] = CONVERT(SMALLDATETIME, MAX([CD].[ChannelDataLogTime]))
    FROM [dbo].[ChannelData] AS [CD]
    WHERE (1 = 1)
        AND (@StartDT <= [CD].[ChannelDataLogTime] AND [CD].[ChannelDataLogTime] < @EndDT)
)
SELECT
     [ChannelValue] = AVG([CD].[ChannelValue])
    ,[HourlyDateTime] = [I].[Start]
    ,[Id] = [CD].[ChannelId]
FROM [Available] AS [A]
INNER JOIN [Interval] AS [I]
    ON ([A].[Start] <= [I].[Start] AND [I].[Start] <= [A].[End])
LEFT OUTER JOIN [dbo].[ChannelData] AS [CD]
    ON
    (
            ([CD].[ChannelId] IN (9))
        AND ([I].[Start] <= [CD].[ChannelDataLogTime] AND [CD].[ChannelDataLogTime] < [I].[End])
    )
GROUP BY
     [I].[Start]
    ,[CD].[ChannelId]
ORDER BY
     [I].[Start]
OPTION (MAXRECURSION 32767);
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/53599292

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档