GroupBy数据部分函数缺失数据
GroupBy数据部分函数缺失数据
提问于 2016-08-30 17:35:31
因此，我有一个查询，我试图在按时间戳到分钟分组时，找到和平均值。但是，如果时间戳有相同的分钟，但在不同的时间内，则不会显示行数据。下面是我的查询和输出，以及各个行是什么：
NumCardsPassed ProcessingTime ProcessingDate
10 4 2016-08-29 11:13:44.000
1 0 2016-08-29 11:49:43.000
2 1 2016-08-29 12:19:42.000下面是单独的行，没有所有的分组。注意上面的查询中缺少了12:13和22:13的时间戳(上面的NumCardsPassed应该等于10，它将它添加到11:13的数字卡中)
NumCardsPassed ProcessingTime processingdate
1 2 2016-08-29 11:13:44.000
1 0 2016-08-29 11:49:43.000
8 10 2016-08-29 12:13:44.000
1 3 2016-08-29 12:19:42.000
1 0 2016-08-29 22:13:47.000
Select sum(numcardspassed) as "NumCardsPassed",
avg(processingtime) as "ProcessingTime", min(ProcessingDate) as "ProcessingDate"
From orderprocessormetrics
where ProcessingDate >= '8/27/2016' and ProcessingDate < '8/30/2016' and PreprocessorType = 'SOP' and numcardsPassed > 0 and clientid = 6820
Group by DatePart(minute, orderprocessormetrics.processingdate)
order by processingdate因此，由于datePart是基于分钟的，所以它将所有行与相同的分钟组合在一起，而不管时间长短。有没有办法考虑到时间和日期而不仅仅是分钟。我仍然需要它按分钟，而不是秒或毫秒分组。
Stack Overflow用户
发布于 2016-08-30 17:42:38
尝试使用下面的查询。
Select sum(numcardspassed) as "NumCardsPassed",
avg(processingtime) as "ProcessingTime", min(ProcessingDate) as "ProcessingDate"
From orderprocessormetrics
where ProcessingDate >= '8/27/2016' and ProcessingDate < '8/30/2016' and PreprocessorType = 'SOP' and numcardsPassed > 0 and clientid = 6820
Group by CAST(orderprocessormetrics.processingdate as date)
,DatePart(hour, orderprocessormetrics.processingdate)
,DatePart(minute, orderprocessormetrics.processingdate)
order by processingdate页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39233766
复制相关文章
相似问题
腾讯云开发者
