不按时间间隔分组SQL Server

问题描述：

我试图按时间间隔（小时）对结果进行分组，我无法为我的生活弄清楚这一点。我需要专家来帮助我，任何人都可以看到我要出错的地方吗？不按时间间隔分组SQL Server

DECLARE @IntervalMinutes AS INT 
SELECT @IntervalMinutes = 60 

SELECT  
     CONVERT(VARCHAR(5),DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',dbo.cmp_EEAddLines.CallTime)/@IntervalMinutes) * @IntervalMinutes, '19000101'),108) AS CallTime, 
     COUNT(dbo.Dial.HistoryID) AS TotalDials, 
     SUM(CONVERT(numeric, dbo.CRCTotal.Contact)) AS Contacts, 
     SUM(CONVERT(numeric, dbo.CRCTotal.FinalCRC)) AS Completes 
FROM   
     dbo.cmp_EEAddLines LEFT OUTER JOIN 
     dbo.Dial ON 
     dbo.cmp_EEAddLines.DialID = dbo.Dial.DialID LEFT OUTER JOIN 
     dbo.CRCTotal ON 
     dbo.Dial.CRC = dbo.CRCTotal.CRC 
WHERE 
     (dbo.cmp_EEAddLines.CallDate >= 29/03/2015) 
GROUP BY 
     CallTime

答

你可以打破每个要日期部分：

select year(e.cmp_EEAddLines.CallTime), month(e.cmp_EEAddLines.CallTime), 
     day(e.cmp_EEAddLines.CallTime), 
     datepart(hour, cmp_EEAddLines.CallTime), 
     . . . 
group by year(e.cmp_EEAddLines.CallTime), month(e.cmp_EEAddLines.CallTime), 
     day(e.cmp_EEAddLines.CallTime), 
     datepart(hour, cmp_EEAddLines.CallTime)

这不会形成最终的输出为datetime，但它可能对你的工作需要。

注意：e是带列的表的别名。

答

以下是按年/月/日/小时制作“点击次数”列表的一种方法。

只有存在几个小时才有活动的行 - 也就是说，会有间隙。
基于这些外部连接，你可以有TotalDials没有任何Contacts或Completes
结果列表中不包含一个日期时间值。如果需要，可以通过将其作为子查询并将日期组件“拼接”回日期时间来添加。

代码：

SELECT 
    datepart(yy, al.CallTime) Year 
    ,datepart(mm, al.CallTime) Month 
    ,datepart(dd, al.CallTime) Day 
    ,datepart(hh, al.CallTime) Hour 
    ,COUNT(di.HistoryID)    TotalDials 
    ,SUM(CONVERT(numeric, cr.Contact)) Contacts 
    ,SUM(CONVERT(numeric, cr.FinalCRC)) Completes 
FROM   
    dbo.cmp_EEAddLines al 
    INNER JOIN dbo.Dial di -- Left join to inner; counts and sums are identical, query will be faster 
     ON al.DialID = di.DialID 
    LEFT OUTER JOIN dbo.CRCTotal cr 
     ON di.CRC = cr.CRC 
group by 
    datepart(yy, al.CallTime) -- Year 
    ,datepart(mm, al.CallTime) -- Month 
    ,datepart(dd, al.CallTime) -- Day 
    ,datepart(hh, al.CallTime) -- Hour 
order by 
    datepart(yy, al.CallTime) -- Year 
    ,datepart(mm, al.CallTime) -- Month 
    ,datepart(dd, al.CallTime) -- Day 
    ,datepart(hh, al.CallTime) -- Hour

答

每个人似乎每个日期时间被分解成各个部分时，在现实中，你只需要两个部分。日期和小时。试试这个：

SELECT CAST(dbo.cmp_EEAddLines.CallTime AS DATE) as dates, 
     DATEPART(HOUR,dbo.cmp_EEAddLines.CallTime) AS hr 
... 

GROUP BY CAST(dbo.cmp_EEAddLines.CallTime AS DATE), 
      DATEPART(HOUR,dbo.cmp_EEAddLines.CallTime)

如果你想在一列中，你可以试试这个。它会将每个日期时间值四舍五入到最近的小时。

SELECT CAST(CONCAT(CAST(dbo.cmp_EEAddLines.CallTime AS DATE),' ',DATEPART(HOUR,dbo.cmp_EEAddLines.CallTime),':00:00.000') AS DATETIME) 

.... 

GROUP BY CAST(CONCAT(CAST(dbo.cmp_EEAddLines.CallTime AS DATE),' ',DATEPART(HOUR,dbo.cmp_EEAddLines.CallTime),':00:00.000') AS DATETIME)

不按时间间隔分组SQL Server

相关推荐