sql相同值之间的间隔次数和连续出现的次数

[kemen]

数据如下：
ID hgg
2121 12
2122 3
2123 7
2124 5
2125 6
2126 7
2127 7
2128 7
2129 0
2130 11
2131 12
2132 12
2133 14
2134 0

select b.myriabit, ifnull(kk,0) as 连续期数 ,最大间隔期数 from
(with
wang as(
select t1.id,t1.myriabit from test t1
where not exists (select id,myriabit from test where myriabit=t1.myriabit and id=t1.id-1))

select tt.myriabit,min(diff)-1 as 最大间隔期数
from(
select s.myriabit,(t3.id-s.id) as diff from wang s join wang t3 on s.myriabit=t3.myriabit and s.id<t3.id and s.id!=t3.id-1
) tt
group by myriabit

) b
left join

( --连续出现次数
select t2.id,t2.myriabit, count(1) as kk from test as t2
where exists(select a.id,a.myriabit from test as a where a.myriabit=t2.myriabit and (a.id=t2.id+1 or a.id=t2.id-1)
and not exists(select b.id,b.myriabit from test b where b.myriabit=a.myriabit and b.id=t2.id-1)
)
group by myriabit ) c
on b.myriabit=c.myriabit

返回结果应该是
hgg 连续期数 最大间隔次数
12 2 9
7 3 2
0 1 4

问题： 我用的是SQLlite ,在数据少于1000条的情况下可以正常，但当数据大过1000条后，统计结果不正常 ，这是什么原因呢？

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT ,
 HGG VARCHAR(5))

INSERT INTO #T
SELECT 2121,'12' UNION ALL
SELECT 2122,'3' UNION ALL
SELECT 2123,'7' UNION ALL
SELECT 2124,'5' UNION ALL
SELECT 2125,'6' UNION ALL
SELECT 2126,'7' UNION ALL
SELECT 2127,'7' UNION ALL
SELECT 2128,'7' UNION ALL
SELECT 2129,'0' UNION ALL
SELECT 2130,'11' UNION ALL
SELECT 2131,'12' UNION ALL
SELECT 2132,'12' UNION ALL
SELECT 2133,'14' UNION ALL
SELECT 2134,'0' 

WITH CTE_1
AS
(SELECT *,
ROW_NUMBER() OVER (ORDER BY ID) AS ID_1,
ROW_NUMBER() OVER (PARTITION BY HGG ORDER BY ID) AS ID_2 FROM #T),

CTE_2
AS
(SELECT A.*,A.ID_1-A.ID_2 AS ID_GROUP,ISNULL(B.ID_1-A.ID_1-1,0) AS DIFF
FROM CTE_1 A
LEFT JOIN CTE_1 B ON A.HGG=B.HGG AND B.ID_2=A.ID_2+1)

SELECT A.HGG,MAX(QTY) AS QTY,MAX(DIFF) AS DIFF
FROM
(SELECT HGG,ID_GROUP,COUNT(1) AS QTY 
FROM CTE_2
GROUP BY HGG,ID_GROUP) AS A
JOIN
(SELECT HGG,MAX(DIFF) AS DIFF FROM CTE_2 GROUP BY HGG) AS B ON A.HGG=B.HGG
GROUP BY A.HGG

[qq_25073223]

连续出现的次数请查阅这里连续出现次数的方法查阅

[kemen]

引用 1 楼 RINK_1 的回复:

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT ,
 HGG VARCHAR(5))

INSERT INTO #T
SELECT 2121,'12' UNION ALL
SELECT 2122,'3' UNION ALL
SELECT 2123,'7' UNION ALL
SELECT 2124,'5' UNION ALL
SELECT 2125,'6' UNION ALL
SELECT 2126,'7' UNION ALL
SELECT 2127,'7' UNION ALL
SELECT 2128,'7' UNION ALL
SELECT 2129,'0' UNION ALL
SELECT 2130,'11' UNION ALL
SELECT 2131,'12' UNION ALL
SELECT 2132,'12' UNION ALL
SELECT 2133,'14' UNION ALL
SELECT 2134,'0' 

WITH CTE_1
AS
(SELECT *,
ROW_NUMBER() OVER (ORDER BY ID) AS ID_1,
ROW_NUMBER() OVER (PARTITION BY HGG ORDER BY ID) AS ID_2 FROM #T),

CTE_2
AS
(SELECT A.*,A.ID_1-A.ID_2 AS ID_GROUP,ISNULL(B.ID_1-A.ID_1-1,0) AS DIFF
FROM CTE_1 A
LEFT JOIN CTE_1 B ON A.HGG=B.HGG AND B.ID_2=A.ID_2+1)

SELECT A.HGG,MAX(QTY) AS QTY,MAX(DIFF) AS DIFF
FROM
(SELECT HGG,ID_GROUP,COUNT(1) AS QTY 
FROM CTE_2
GROUP BY HGG,ID_GROUP) AS A
JOIN
(SELECT HGG,MAX(DIFF) AS DIFF FROM CTE_2 GROUP BY HGG) AS B ON A.HGG=B.HGG
GROUP BY A.HGG

sqlite 不支持OVER

[kemen]

引用 2 楼 qq_25073223 的回复:

连续出现的次数请查阅这里连续出现次数的方法查阅

SQLITE 不支持over() row_number

[insus]

参考相似的方法：
https://www.cnblogs.com/insus/p/10931687.html
https://www.cnblogs.com/insus/p/10929956.html

[kemen]

SELECT t.myriabit,IFNULL(tt.maxlx, 0) AS 最大连续期数,IFNULL(te.maxjg, 0) AS 最大间隔期数
FROM (SELECT DISTINCT myriabit FROM EveryColor ) t

LEFT JOIN
(-- 最大连续次数
SELECT a.myriabit,max(b.id - a.id)+1 AS maxlx FROM EveryColor a, EveryColor b
WHERE a.myriabit = b.myriabit AND a.id < b.id AND
NOT EXISTS ( SELECT 1 FROM EveryColor c WHERE c.myriabit <> a.myriabit AND c.id > a.id AND c.id < b.id )
GROUP BY a.myriabit ) tt
ON t.myriabit = tt.myriabit

LEFT JOIN
(
--将自增ID设成连续
with TBT AS(SELECT (SELECT count(*)+1 FROM EveryColor WHERE id < T.id) AS ID,myriabit
FROM EveryColor T
WHERE id = T.id AND id!=t.id-1 )

-- 最大最大间隔
SELECT myriabit,MAX(maxjg) AS maxjg FROM (
SELECT a.myriabit,a.id,IFNULL(min(b.id-a.id-1),0) AS maxjg FROM TBT AS a
LEFT JOIN TBT AS b on a.myriabit=b.myriabit AND a.id<b.id
GROUP BY a.myriabit,a.id) GROUP BY myriabit ) te

on t.myriabit=te.myriabit ORDER BY t.myriabit

这样可以，本人基础差，有没有大神优化下执行效率

[RINK_1]

引用 3 楼 kemen 的回复:

[quote=引用 1 楼 RINK_1 的回复:]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT ,
 HGG VARCHAR(5))

INSERT INTO #T
SELECT 2121,'12' UNION ALL
SELECT 2122,'3' UNION ALL
SELECT 2123,'7' UNION ALL
SELECT 2124,'5' UNION ALL
SELECT 2125,'6' UNION ALL
SELECT 2126,'7' UNION ALL
SELECT 2127,'7' UNION ALL
SELECT 2128,'7' UNION ALL
SELECT 2129,'0' UNION ALL
SELECT 2130,'11' UNION ALL
SELECT 2131,'12' UNION ALL
SELECT 2132,'12' UNION ALL
SELECT 2133,'14' UNION ALL
SELECT 2134,'0' 

WITH CTE_1
AS
(SELECT *,
ROW_NUMBER() OVER (ORDER BY ID) AS ID_1,
ROW_NUMBER() OVER (PARTITION BY HGG ORDER BY ID) AS ID_2 FROM #T),

CTE_2
AS
(SELECT A.*,A.ID_1-A.ID_2 AS ID_GROUP,ISNULL(B.ID_1-A.ID_1-1,0) AS DIFF
FROM CTE_1 A
LEFT JOIN CTE_1 B ON A.HGG=B.HGG AND B.ID_2=A.ID_2+1)

SELECT A.HGG,MAX(QTY) AS QTY,MAX(DIFF) AS DIFF
FROM
(SELECT HGG,ID_GROUP,COUNT(1) AS QTY 
FROM CTE_2
GROUP BY HGG,ID_GROUP) AS A
JOIN
(SELECT HGG,MAX(DIFF) AS DIFF FROM CTE_2 GROUP BY HGG) AS B ON A.HGG=B.HGG
GROUP BY A.HGG

sqlite 不支持OVER[/quote] 如果SQLLITE支持临时表，而且ID也是唯一的，那就试试下面的。

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT ,
 HGG VARCHAR(5))

INSERT INTO #T
SELECT 2121,'12' UNION ALL
SELECT 2122,'3' UNION ALL
SELECT 2123,'7' UNION ALL
SELECT 2124,'5' UNION ALL
SELECT 2125,'6' UNION ALL
SELECT 2126,'7' UNION ALL
SELECT 2127,'7' UNION ALL
SELECT 2128,'7' UNION ALL
SELECT 2129,'0' UNION ALL
SELECT 2130,'11' UNION ALL
SELECT 2131,'12' UNION ALL
SELECT 2132,'12' UNION ALL
SELECT 2133,'14' UNION ALL
SELECT 2134,'0' 


SELECT *,
IDENTITY(INT,1,1) AS ID_1,
(SELECT COUNT(*) FROM #T WHERE A.HGG=HGG AND ID<=A.ID) AS ID_2
INTO #A
FROM #T A
ORDER BY ID


SELECT A.*,A.ID_1-A.ID_2 AS ID_GROUP,ISNULL(B.ID_1-A.ID_1-1,0) AS DIFF
INTO #B
FROM #A A
LEFT JOIN #A B ON A.HGG=B.HGG AND B.ID_2=A.ID_2+1

SELECT A.HGG,MAX(QTY) AS QTY,MAX(DIFF) AS DIFF
FROM
(SELECT HGG,ID_GROUP,COUNT(1) AS QTY 
FROM #B
GROUP BY HGG,ID_GROUP) AS A
JOIN
(SELECT HGG,MAX(DIFF) AS DIFF FROM #B GROUP BY HGG) AS B ON A.HGG=B.HGG
GROUP BY A.HGG

DROP TABLE #A
DROP TABLE #B

[kemen]

按七楼的大神做出适合SQLiter 的修改，一万多条的记录约80秒，

with T1 as (
With T0 as (
SELECT *,(select count(*)+1 from Test as T where id<a.id) as ID_1,
(SELECT COUNT(*) FROM Test WHERE A.myriabit=myriabit AND ID<=A.ID) AS ID_2 FROM test A ORDER BY ID
)

SELECT A.*,A.ID_1-A.ID_2 AS ID_GROUP,IfNULL(B.ID_1-A.ID_1-1,0) AS DIFF
FROM T0 A
LEFT JOIN T0 B ON A.myriabit=B.myriabit AND B.ID_2=A.ID_2+1)

SELECT A.myriabit,MAX(QTY) AS QTY,MAX(DIFF) AS DIFF
FROM
(SELECT myriabit,ID_GROUP,COUNT(1) AS QTY
FROM T1
GROUP BY myriabit,ID_GROUP) AS A
JOIN
(SELECT myriabit,MAX(DIFF) AS DIFF FROM T1 GROUP BY myriabit) AS B ON A.myriabit=B.myriabit
GROUP BY A.myriabit