急急急，如何解决百万数据相邻行数据比较筛选问题

baidu_35876036 2016-08-15 12:13:45
原始数据：
ID 时间 标识
1 0000 0
1 0002 1
2 0007 1
2 0005 0
1 0001 1
2 0003 0
1 0003 0
1 0005 0
2 0006 0
2 0001 1
2 0004 0
1 0004 1
1 0007 0
1 0006 0
2 0002 0
......................
期望结果：
ID 时间 标识
1 0000 0
1 0001 1
1 0002 1
1 0003 0
1 0004 1
1 0005 0
1 0006 0 *
1 0007 0
2 0001 1
2 0002 0
2 0003 0 *
2 0004 0 *
2 0005 0 *
2 0006 0
2 0007 1
......................
数据结构大概是这样的，在这里面想找出按ID、时间排序后同一ID下标识字段中连续为0的行（结果中标*号的行）并将它们删掉。由于原数据是无序的，所以要先排序再筛选，数据量在8百万左右，请高手指点！
...全文
229 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
顾西昂 2016-08-16
  • 打赏
  • 举报
 回复
引用 3 楼 baidu_35876036 的回复:
[quote=引用 1 楼 SugarToffee 的回复:] 看看这个稳不稳，800w数据感觉会卡 重要的是思路 连接自身查探前一个后后一个的标识 

select a.id,a.时间,a.标识,b.标识,c.标识 from table a left join table b on a.id=b.id and cast(a.时间 as int)=cast(b.时间 as int)-1
left join table c on a.id=b.id and cast(a.时间 as int)=cast(c.时间 as int)+1
where a.标识=0 and b.标识=0 and c.标识=0
我是小白，能具体解释一下思路么？原数据是无序的，你这里排序体现在哪里了呢？[/quote] 我这个的是搜索出你打星号的数据。
顾西昂 2016-08-16
  • 打赏
  • 举报
 回复
引用 3 楼 baidu_35876036 的回复:
[quote=引用 1 楼 SugarToffee 的回复:] 看看这个稳不稳，800w数据感觉会卡 重要的是思路 连接自身查探前一个后后一个的标识 

select a.id,a.时间,a.标识,b.标识,c.标识 from table a left join table b on a.id=b.id and cast(a.时间 as int)=cast(b.时间 as int)-1
left join table c on a.id=b.id and cast(a.时间 as int)=cast(c.时间 as int)+1
where a.标识=0 and b.标识=0 and c.标识=0
我是小白，能具体解释一下思路么？原数据是无序的，你这里排序体现在哪里了呢？[/quote] cast(a.时间 as int)=cast(b.时间 as int)-1 cast(a.时间 as int)=cast(c.时间 as int)+1 就是看你目标的前一个和后一个数。 你排序也只是人看然后人工选择出周围是0并且自己也是0的那一列，我这是直接根据当前列寻找到排序后周围的列然后看是否为0的。
baidu_35876036 2016-08-16
  • 打赏
  • 举报
 回复
引用 13 楼 roy_88 的回复:
DELETE t2
 FROM (SELECT *,Grp2=ROW_NUMBER()OVER(PARTITION BY ID,Grp ORDER BY 时间)%COUNT(*)OVER(PARTITION BY ID,Grp) FROM (Select *,Grp=ROW_NUMBER()OVER(PARTITION BY ID ORDER BY [时间])-ROW_NUMBER()OVER(PARTITION BY ID,[标识] ORDER BY 时间) from #原始数据) t1 ) AS t2 
 WHERE Grp2 NOT IN(0,1) AND [标识]=0
加上AND [标识]=0
多谢大神，完美解决！
中国风 2016-08-16
  • 打赏
  • 举报
 回复 
DELETE t2
 FROM (SELECT *,Grp2=ROW_NUMBER()OVER(PARTITION BY ID,Grp ORDER BY 时间)%COUNT(*)OVER(PARTITION BY ID,Grp) FROM (Select *,Grp=ROW_NUMBER()OVER(PARTITION BY ID ORDER BY [时间])-ROW_NUMBER()OVER(PARTITION BY ID,[标识] ORDER BY 时间) from #原始数据) t1 ) AS t2 
 WHERE Grp2 NOT IN(0,1) AND [标识]=0
加上AND [标识]=0
baidu_35876036 2016-08-16
  • 打赏
  • 举报
 回复
引用 11 楼 roy_88 的回复:
[quote=引用 9 楼 baidu_35876036 的回复:] 大神，能帮我具体解释一下delete那一行的思路么？谢谢！
取ID分组，再取连续的 标识 分组，分组的总记录数同分组按时间顺序数取余数保留第一个和最后一个，其它删除[/quote] 我运行的时候它把连续的标识为1的行也删除了，应该如何修改呢？
中国风 2016-08-16
  • 打赏
  • 举报
 回复
引用 9 楼 baidu_35876036 的回复:
大神，能帮我具体解释一下delete那一行的思路么？谢谢！
取ID分组，再取连续的 标识 分组，分组的总记录数同分组按时间顺序数取余数保留第一个和最后一个，其它删除
baidu_35876036 2016-08-16
  • 打赏
  • 举报
 回复
引用 7 楼 SugarToffee 的回复:
[quote=引用 3 楼 baidu_35876036 的回复:] [quote=引用 1 楼 SugarToffee 的回复:] 看看这个稳不稳，800w数据感觉会卡 重要的是思路 连接自身查探前一个后后一个的标识 

select a.id,a.时间,a.标识,b.标识,c.标识 from table a left join table b on a.id=b.id and cast(a.时间 as int)=cast(b.时间 as int)-1
left join table c on a.id=b.id and cast(a.时间 as int)=cast(c.时间 as int)+1
where a.标识=0 and b.标识=0 and c.标识=0
我是小白，能具体解释一下思路么？原数据是无序的，你这里排序体现在哪里了呢？[/quote] cast(a.时间 as int)=cast(b.时间 as int)-1 cast(a.时间 as int)=cast(c.时间 as int)+1 就是看你目标的前一个和后一个数。 你排序也只是人看然后人工选择出周围是0并且自己也是0的那一列，我这是直接根据当前列寻找到排序后周围的列然后看是否为0的。[/quote] 我的数据里时间不一定是连续+1的，所以这样找可能不行
baidu_35876036 2016-08-16
  • 打赏
  • 举报
 回复
引用 6 楼 roy_88 的回复:
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#原始数据') is null
	drop table #原始数据
Go
Create table #原始数据([ID] int,[时间] nvarchar(24),[标识] int)
Insert #原始数据
select 1,N'0000',0 union all
select 1,N'0002',1 union all
select 2,N'0007',1 union all
select 2,N'0005',0 union all
select 1,N'0001',1 union all
select 2,N'0003',0 union all
select 1,N'0003',0 union all
select 1,N'0005',0 union all
select 2,N'0006',0 union all
select 2,N'0001',1 union all
select 2,N'0004',0 union all
select 1,N'0004',1 union all
select 1,N'0007',0 union all
select 1,N'0006',0 union all
select 2,N'0002',0
Go
DELETE t2 FROM (SELECT *,Grp2=ROW_NUMBER()OVER(PARTITION BY ID,Grp ORDER BY 时间)%COUNT(*)OVER(PARTITION BY ID,Grp) FROM (Select *,Grp=ROW_NUMBER()OVER(PARTITION BY ID ORDER BY [时间])-ROW_NUMBER()OVER(PARTITION BY ID,[标识] ORDER BY 时间) from #原始数据) t1 ) AS t2 WHERE Grp2 NOT IN(0,1)

SELECT * FROM #原始数据 ORDER BY 1,2
/*
ID	时间	标识
1	0000	0
1	0001	1
1	0002	1
1	0003	0
1	0004	1
1	0005	0
1	0007	0
2	0001	1
2	0002	0
2	0006	0
2	0007	1
*/
大神，能帮我具体解释一下delete那一行的思路么？谢谢！
中国风 2016-08-15
  • 打赏
  • 举报
 回复 
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#原始数据') is null
	drop table #原始数据
Go
Create table #原始数据([ID] int,[时间] nvarchar(24),[标识] int)
Insert #原始数据
select 1,N'0000',0 union all
select 1,N'0002',1 union all
select 2,N'0007',1 union all
select 2,N'0005',0 union all
select 1,N'0001',1 union all
select 2,N'0003',0 union all
select 1,N'0003',0 union all
select 1,N'0005',0 union all
select 2,N'0006',0 union all
select 2,N'0001',1 union all
select 2,N'0004',0 union all
select 1,N'0004',1 union all
select 1,N'0007',0 union all
select 1,N'0006',0 union all
select 2,N'0002',0
Go
DELETE t2 FROM (SELECT *,Grp2=ROW_NUMBER()OVER(PARTITION BY ID,Grp ORDER BY 时间)%COUNT(*)OVER(PARTITION BY ID,Grp) FROM (Select *,Grp=ROW_NUMBER()OVER(PARTITION BY ID ORDER BY [时间])-ROW_NUMBER()OVER(PARTITION BY ID,[标识] ORDER BY 时间) from #原始数据) t1 ) AS t2 WHERE Grp2 NOT IN(0,1)

SELECT * FROM #原始数据 ORDER BY 1,2
/*
ID	时间	标识
1	0000	0
1	0001	1
1	0002	1
1	0003	0
1	0004	1
1	0005	0
1	0007	0
2	0001	1
2	0002	0
2	0006	0
2	0007	1
*/
RINK_1 2016-08-15
  • 打赏
  • 举报
 回复
SELECT * FROM TABLE A WHERE EXISTS (SELECT 1 FROM TABLE WHERE ID=A.ID AND 时间=A.时间-1 AND 标识='0') AND EXISTS (SELECT 1 FROM TABLE WHERE ID=A.ID AND 时间=A.时间+1 AND 标识='0') AND 标识='0'
baidu_35876036 2016-08-15
  • 打赏
  • 举报
 回复
引用 2 楼 yangb0803 的回复:
1 0007 0 2 0006 0 这两行要留着? ;with tbl as ( select row_number() over(partion by ID order by 时间) as rn from 表 ), t2 as ( select t1.*, t2.rn as rn2, t2.标识 as 标识2 from tbl as t1 left join tbl as t2 on t1.rn = t2.rn -1 ) select * from t2 where 标识 =标识2 and 标识 = 0
那两行是要留着的，需要上下都对比，只 t1.rn = t2.rn -1不行吧？
baidu_35876036 2016-08-15
  • 打赏
  • 举报
 回复
引用 1 楼 SugarToffee 的回复:
看看这个稳不稳，800w数据感觉会卡 重要的是思路 连接自身查探前一个后后一个的标识 

select a.id,a.时间,a.标识,b.标识,c.标识 from table a left join table b on a.id=b.id and cast(a.时间 as int)=cast(b.时间 as int)-1
left join table c on a.id=b.id and cast(a.时间 as int)=cast(c.时间 as int)+1
where a.标识=0 and b.标识=0 and c.标识=0
我是小白，能具体解释一下思路么？原数据是无序的，你这里排序体现在哪里了呢？
道玄希言 2016-08-15
  • 打赏
  • 举报
 回复
1 0007 0 2 0006 0 这两行要留着? ;with tbl as ( select row_number() over(partion by ID order by 时间) as rn from 表 ), t2 as ( select t1.*, t2.rn as rn2, t2.标识 as 标识2 from tbl as t1 left join tbl as t2 on t1.rn = t2.rn -1 ) select * from t2 where 标识 =标识2 and 标识 = 0
顾西昂 2016-08-15
  • 打赏
  • 举报
 回复
看看这个稳不稳，800w数据感觉会卡 重要的是思路 连接自身查探前一个后后一个的标识 

select a.id,a.时间,a.标识,b.标识,c.标识 from table a left join table b on a.id=b.id and cast(a.时间 as int)=cast(b.时间 as int)-1
left join table c on a.id=b.id and cast(a.时间 as int)=cast(c.时间 as int)+1
where a.标识=0 and b.标识=0 and c.标识=0
大佬们，鹏新旭的一面主要会问什么问题啊，很急，明天面试了.
根据项目提出问题看你遇到过没，怎么解决4.问了我专利，随便说了说就过。一个场景题求最短路问题，使用了bfs。第一题，模拟，成功签到第二题，模拟，如何判断点在矩形内或者圆心内，用例过了，不知道为啥AC不了第三...
题解 | #从Products表中检索产品名称以及销售总数#
15分钟，问了个人经历，如何抗压，团队协作等问题反问分部门说是一二面面试官在的部门来挑，但二面面试官说是我们进去以后选择，不懂最后说意向可能节后发许愿招银+顺丰。本来觉得没机会，都不太想面了，又一想涨涨...
某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#
优缺点，项目合作中遇到的挑战和解决方法，有压力的时候怎么解决举例说明，做过。两轮面试全答上来了，专业也跟工作对口，也是江苏本地人，学历9本+藤校，算法论文一作，项目和大厂实习三段都过关，面试聊天面试官聊...
Etcd新增节点(开启SSL证书验证情况下)
淘天集团 使用内推码简历优先筛选，有任何问题包括进度查询可以私信我，内推后在评论区留言【姓名缩写+岗位】，方便捞人和确认投递状态，11月份还有hc！正缺简历！不卡学历！淘 小米测试一面面经 1、自我介绍2、...
题解 | #汇总各个部门当前员工的title类型的分配数目#
题解 | #计算字符串的编辑距离# #include #include #include using nam 题解 | #牛群的最大高度# 题目考察的知识点：这道题目主要考察了二叉树的遍历，特别是递归遍历，以及节点值的比较。题目解答方法的文字分析：...
疑难问题

22,298

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章