请问如何做到按固定计数来分段汇总统计？

huangbochn 2015-10-27 03:43:03
示例数据如下，需要统计每10件为一个区间的次品率（一个CHECK表示意见，NG表示这件是次品）。目前我能想到的办法就是用触发器和临时表，但效率太低了。请教是否可以直接写SQL？
要求结果如下：
区间 检查数 次品数 次品率
1 10 2 0.2
2 12 1 0.08
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:35:01' AS dDateTime

UNION ALL

Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:35:21' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:35:51' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:36:01' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:36:21' AS dDateTime

UNION ALL

Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:36:59' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:38:19' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:39:56' AS dDateTime

UNION ALL

Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:39:44' AS dDateTime

UNION ALL

Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:40:33' AS dDateTime

UNION ALL

Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:41:22' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:42:11' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:43:23' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:44:10' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:45:11' AS dDateTime

UNION ALL

Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:46:56' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:47:46' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:48:34' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:49:26' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:50:19' AS dDateTime

UNION ALL

Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:51:06' AS dDateTime

UNION ALL

Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:52:24' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:53:33' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:54:12' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:55:02' AS dDateTime

UNION ALL

Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:56:11' AS dDateTime

...全文
219 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
huangbochn 2015-10-27
  • 打赏
  • 举报
 回复
引用 1 楼 yangb0803 的回复:

;with tb01 as
(
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:35:01' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:35:21' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:35:51' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:36:01' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:36:21' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:36:59' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:38:19' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:39:56' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:39:44' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:40:33' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:41:22' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:42:11' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:43:23' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:44:10' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:45:11' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:46:56' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:47:46' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:48:34' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:49:26' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:50:19' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:51:06' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:52:24' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:53:33' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:54:12' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:55:02' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:56:11' AS dDateTime
)
,tb02 as(
select 
    ROW_NUMBER() OVER(order by dDateTime)-1 as tid, 
    case [NG] when 'NG' then 1 else 0 end as NG1, 
	[Check], NG, dDateTime 
from tb01
)
--select * from tb02
select  
  min(dDateTime) as [开始时间], 
  max(dDateTime) as [结束时间], 
  convert(decimal(4,2), 1.0*sum(NG1)/10) as [次品率]
from tb02 group by tid/10


开始时间	结束时间	次品率
2015-10-27 15:35:01	2015-10-27 15:40:33	0.40
2015-10-27 15:41:22	2015-10-27 15:50:19	0.20
2015-10-27 15:51:06	2015-10-27 15:56:11	0.20
看过您的代码，核心的难点已经帮我解决了。我先结贴，不耽误您时间。如果后续我还搞不定，会再开贴邀请您。谢谢。
huangbochn 2015-10-27
  • 打赏
  • 举报
 回复
引用 2 楼 yangb0803 的回复:
額， 添加了条件了啊... 每次检查的数量区间都是变化的么？ 从你给的测试数据中， 貌似找不出连续的 12条纪录， 只有一条纪录标记为 ‘NG’ 的額
谢谢您的耐心回复。很抱歉，我表达不够清晰。 这是一个查货流水，查一件就会有一条记录，如果这条记录后面的NG不是NULL值（假设为'NG')，那就认为这件是次品。 第1件到第10件为一个区间，第11件到第20件为一个区间。次品率就是区间里面10件的次品数/10。每个区间的产品不重复统计的，一个产品只在某个区间统计一次。 您给的结果加了开始时间和结束时间，这也是需要的，这个我倒是忘记了。最后的结果是这样的： 区间 检查数 次品数 次品率 开始时间 结束时间 1 10 2 0.2 xx:xx xx:xx 2 12 1 0.08 xx:xx xx:xx
道玄希言 2015-10-27
  • 打赏
  • 举报
 回复
額， 添加了条件了啊... 每次检查的数量区间都是变化的么？ 从你给的测试数据中， 貌似找不出连续的 12条纪录， 只有一条纪录标记为 ‘NG’ 的額 ;with tb02 as( select ROW_NUMBER() OVER(order by dDateTime desc) as tid, case [NG] when 'NG' then 1 else 0 end as NG1, [Check], NG, dDateTime from tb01 ) --select * from tb02 select 1 as 区间, 10 as 检查数, convert(decimal(4,2), 1.0*sum(NG1)/10) as [次品率] from tb02 where tid <=10 union select 2 as 区间, 12 as 检查数, convert(decimal(4,2), 1.0*sum(NG1)/12) as [次品率] from tb02 where tid > 10 and tid <= 22
道玄希言 2015-10-27
  • 打赏
  • 举报
 回复 

;with tb01 as
(
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:35:01' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:35:21' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:35:51' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:36:01' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:36:21' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:36:59' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:38:19' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:39:56' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:39:44' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:40:33' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:41:22' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:42:11' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:43:23' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:44:10' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:45:11' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:46:56' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:47:46' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:48:34' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:49:26' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:50:19' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:51:06' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:52:24' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:53:33' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:54:12' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:55:02' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:56:11' AS dDateTime
)
,tb02 as(
select 
    ROW_NUMBER() OVER(order by dDateTime)-1 as tid, 
    case [NG] when 'NG' then 1 else 0 end as NG1, 
	[Check], NG, dDateTime 
from tb01
)
--select * from tb02
select  
  min(dDateTime) as [开始时间], 
  max(dDateTime) as [结束时间], 
  convert(decimal(4,2), 1.0*sum(NG1)/10) as [次品率]
from tb02 group by tid/10


开始时间	结束时间	次品率
2015-10-27 15:35:01	2015-10-27 15:40:33	0.40
2015-10-27 15:41:22	2015-10-27 15:50:19	0.20
2015-10-27 15:51:06	2015-10-27 15:56:11	0.20
excel中分段统计区间个数
在excel中进行区间个数统计也是我们工作中常遇到的问题，解决方法有很多种，比如万能的筛选大法，countif函数等。但是笔者这里给大家介绍两种特别简单的方法，frequercy函数（频率函数）法和数据透视表法，各位可以...
Tableue 实现数据分组统计 - 依据连续度量创建数据桶
版本：2020.4适用于： Tableau Desktop, ...您可以将**“State”（省/市/自治区）字段视为一组数据桶 — 每个利润值会按数据桶进行分类，该数据桶与从中记录值的省/市/自治区相对应。但是，如果想要查看分配给数据桶
LongAdder讲解
在大多数项目及开源组件中，计数统计使用最多的仍然还是AtomicLong，虽然是阿里巴巴这样说，但是我们仍然要根据使用场景来决定是否使用LongAdder。 今天主要是来讲讲LongAdder的实现原理，还是老方式，通过图文一...
Excel常用统计分析函数分享
文章目录1 平均值计算1.1 无条件平均值计算：AVERAGE1.2 单一条件下的平均值计算：AVERAGEIF1.3 多条件下的平均值计算：AVERAGEIFS2 按指定条件计数2.1 条件判断介绍2.2 单字段多条件计数2.3 多字段多条件计数3 认识...
比AtomicLong更优秀的LongAdder确定不来了解一下吗？
对于Java项目中计数统计的一些需求，如果是 JDK8，推荐使用 LongAdder 对象，比 AtomicLong 性能更好（减少乐观锁的重试次数） 在大多数项目及开源组件中，计数统计使用最多的仍然还是AtomicLong，虽然是阿里巴巴
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章