如何根据列的值查找连续的行? [英] How to find consecutive rows based on the value of a column?
本文介绍了如何根据列的值查找连续的行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一些数据.我想根据 data
列的值对它们进行分组.如果有 3 个或更多连续行的数据大于 10,那么这些行就是我想要的.
I have some data. I want to group them based on the value of data
column. If there are 3 or more consecutive rows that have data bigger than 10, then those rows are what I want.
所以对于这个数据:
use tempdb;
go
set nocount on;
if object_id('t', 'U') is not null
drop table t;
go
create table t
(
id int primary key identity,
[when] datetime,
data int
)
go
insert into t([when], data) values ('20130801', 1);
insert into t([when], data) values ('20130802', 121);
insert into t([when], data) values ('20130803', 132);
insert into t([when], data) values ('20130804', 15);
insert into t([when], data) values ('20130805', 9);
insert into t([when], data) values ('20130806', 1435);
insert into t([when], data) values ('20130807', 143);
insert into t([when], data) values ('20130808', 18);
insert into t([when], data) values ('20130809', 19);
insert into t([when], data) values ('20130810', 1);
insert into t([when], data) values ('20130811', 1234);
insert into t([when], data) values ('20130812', 124);
insert into t([when], data) values ('20130813', 6);
select * from t;
我想要的是:
id when data
----------- ----------------------- -----------
2 2013-08-02 00:00:00.000 121
3 2013-08-03 00:00:00.000 132
4 2013-08-04 00:00:00.000 15
6 2013-08-06 00:00:00.000 1435
7 2013-08-07 00:00:00.000 143
8 2013-08-08 00:00:00.000 18
9 2013-08-09 00:00:00.000 19
怎么做?
推荐答案
试试这个
WITH cte
AS
(
SELECT *,COUNT(1) OVER(PARTITION BY cnt) pt FROM
(
SELECT tt.*
,(SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS cnt
FROM t tt
WHERE data > 10
) t1
)
SELECT id, [when], data FROM cte WHERE pt >= 3
输出
id when data
2 2013-08-02 00:00:00.000 121
3 2013-08-03 00:00:00.000 132
4 2013-08-04 00:00:00.000 15
6 2013-08-06 00:00:00.000 1435
7 2013-08-07 00:00:00.000 143
8 2013-08-08 00:00:00.000 18
9 2013-08-09 00:00:00.000 19
编辑
首先,内部查询计算数据 <= 10 的记录数
First the inner query counts the no of records where data <= 10
SELECT tt.*
,(SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS cnt
FROM t tt
输出
id when data cnt
1 2013-08-01 00:00:00.000 1 1
2 2013-08-02 00:00:00.000 121 1
3 2013-08-03 00:00:00.000 132 1
4 2013-08-04 00:00:00.000 15 1
5 2013-08-05 00:00:00.000 9 2
6 2013-08-06 00:00:00.000 1435 2
7 2013-08-07 00:00:00.000 143 2
8 2013-08-08 00:00:00.000 18 2
9 2013-08-09 00:00:00.000 19 2
10 2013-08-10 00:00:00.000 1 3
11 2013-08-11 00:00:00.000 1234 3
12 2013-08-12 00:00:00.000 124 3
13 2013-08-13 00:00:00.000 6 4
然后我们过滤数据> 10的记录
Then we filter the records with data > 10
WHERE data > 10
现在我们通过对 cnt 列进行分区来统计记录
Now we count the records by partitoning cnt column
SELECT *,COUNT(1) OVER(PARTITION BY cnt) pt FROM
(
SELECT tt.*
,(SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS cnt
FROM t tt
WHERE data > 10
) t1
输出
id when data cnt pt
2 2013-08-02 00:00:00.000 121 1 3
3 2013-08-03 00:00:00.000 132 1 3
4 2013-08-04 00:00:00.000 15 1 3
6 2013-08-06 00:00:00.000 1435 2 4
7 2013-08-07 00:00:00.000 143 2 4
8 2013-08-08 00:00:00.000 18 2 4
9 2013-08-09 00:00:00.000 19 2 4
11 2013-08-11 00:00:00.000 1234 3 2
12 2013-08-12 00:00:00.000 124 3 2
上面的查询和临时表一样放在 cte 中
The above query is put in cte just like temp table
现在选择连续计数 >= 3 的记录
Now select the records that are having the consecutive count >= 3
SELECT id, [when], data FROM cte WHERE pt >= 3
另一种解决方案
;WITH partitioned AS (
SELECT *, id - ROW_NUMBER() OVER (ORDER BY id) AS grp
FROM t
WHERE data > 10
),
counted AS (
SELECT *, COUNT(*) OVER (PARTITION BY grp) AS cnt
FROM partitioned
)
SELECT id, [when], data
FROM counted
WHERE cnt >= 3
这篇关于如何根据列的值查找连续的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文