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是基于分钟的，所以它将所有行与相同的分钟组合在一起，而不管时间长短。有没有办法考虑到时间和日期而不仅仅是分钟。我仍然需要它按分钟，而不是秒或毫秒分组。
回答 4
Stack Overflow用户
回答已采纳
发布于 2016-08-30 19:14:21
试试这个(好名字，顺便说一句)：
Select sum(numcardspassed) as "NumCardsPassed", sum(numcardsfailed) as "NumCardsFailed",
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 convert(varchar, ProcessingDate, 108)
order by processingdateStack Overflow用户
发布于 2016-08-30 17:38:15
但是，如果时间戳有相同的分钟，但在不同的小时内，则不会显示行数据。
试试这个..。
group by
DatePart(minute, orderprocessormetrics.processingdate),
DatePart(hour, orderprocessormetrics.processingdate),
DatePart(day, orderprocessormetrics.processingdate)Stack Overflow用户
发布于 2016-08-30 17:38:22
您需要将日期/时间缩短到分钟。有一种方法：
select dateadd(minute, datediff(minute, 0, processingdate), 0) as to_the_minute,
sum(numcardspassed) as NumCardsPassed,
avg(processingtime) as ProcessingTime
From orderprocessormetrics
where ProcessingDate >= '2016-08-27' and ProcessingDate < '2016-08-30' and
PreprocessorType = 'SOP' and
numcardsPassed > 0 and
clientid = 6820
group by dateadd(minute, datediff(minute, 0, processingdate), 0)
order by to_the_minute;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39233766
复制相关文章
相似问题
腾讯云开发者
