时间段内无记录数据查询

weixin_40101242 2017-11-30 02:38:30
我有这样的数据

5分钟一次观测频率。但是有的5分钟一次的观测频率丢失。现在想要查询出丢失的数据，并且判断丢失数据的连续个数，个数为1时，存入a表；个数为2-3时，存入b表；个数大于3时，存入c表。
由于数据量大，还需考虑效率问题。
...全文
525 23 打赏 收藏 转发到动态 举报
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
weixin_40101242 2017-12-01
  • 打赏
  • 举报
 回复
谢各位大神援手，今天一直在外边，等我回去运行一下。感激各位
砸死牛顿的苹果 2017-12-01
  • 打赏
  • 举报
 回复
我一般用 getnums 自定义函数 代替 master..spt_values 代码如下 : 
create function [dbo].[getnums]
(@n int)
returns table as
return
with t1 as(select 1 n union all select 1)
,t2 as(select 1 n from t1,t1 a,t1 b,t1 c)
,t3 as(select 1 n from t2,t2 a,t2 b,t2 c)
,t4 as(select 1 n from t3,t3 a)
select top(@n) row_number()over(order by(select 1)) n from t4 
order by n
砸死牛顿的苹果 2017-12-01
  • 打赏
  • 举报
 回复
我也来写个给你参考下 引用楼上的数据 , 不过我加了一行数据 select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',16,40 union all
declare @tab table([观测站编号] nvarchar(33),[观测站名称] nvarchar(25),[行驶方向] nvarchar(22),[观测日期] Date,[小时] int,[分钟] int)

Insert @tab

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',16,30 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',16,40 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',17,0 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',17,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',18,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',19,30 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',19,50 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',20,20 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',20,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',21,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',22,50 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',23,20 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',23,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',24,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',24,45 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',1,15 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',2,5 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',2,40 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',3,10 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',3,45 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',4,35 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',5,5 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',5,40

--[观测日期] Date,[小时] int,[分钟] int)

if object_id('tempdb..#t_0')is not null drop table #t_0

;with

	t as (select *,dt=(datediff(d,0,观测日期)*24+[小时])*12+[分钟]/5 from @tab) 

	,t1 as (select dt1=min(dt),dt2=max(dt) from t)

	,t2 as (select dt=(dt1+n-1) from t1 cross apply getnums((dt2-dt1)+1))

	,t3 as (select t2.* from t2 left join t on t2.dt =t.dt where t.dt is null)

	,t4 as (select rid=row_number()over(order by dt),dt from t3)

select 

	dt=dateadd(minute,dt*5,0)

	,ct=count(*)over(partition by dt-rid) 

into #t_0

from t4



if object_id('tempdb..#t_1')is not null drop table #t_1

if object_id('tempdb..#t_2')is not null drop table #t_2

if object_id('tempdb..#t_3')is not null drop table #t_3

select * into #t_1 from #t_0 where ct=1

select * into #t_2 from #t_0 where ct in(2,3)

select * into #t_3 from #t_0 where ct>3



select * from #t_1

select * from #t_2

select * from #t_3


听雨停了 2017-12-01
  • 打赏
  • 举报
 回复
引用 21 楼 weixin_40101242的回复:
[quote=引用 17 楼 qq_37170555 的回复:]
[quote=引用 8 楼 weixin_40101242 的回复:]
这是别人给的建议，可是我不太懂。



use Tempdb

go

--> --> 听雨停了-->测试数据

 

if not object_id(N'Tempdb..#tab') is null

	drop table #tab

Go

Create table #tab([观测站编号] nvarchar(33),[观测站名称] nvarchar(25),[行驶方向] nvarchar(22),[观测日期] Date,[小时] int,[分钟] int)

Insert #tab

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',16,30 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',17,0 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',17,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',18,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',19,30 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',19,50 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',20,20 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',20,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',21,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',22,50 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',23,20 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',23,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',24,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',24,45 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',1,15 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',2,5 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',2,40 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',3,10 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',3,45 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',4,35 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',5,5 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',5,40



--建立三个存放数据的表A B C

SELECT * into #tabA FROM #tab WHERE 1<>1

SELECT * into #tabB FROM #tab WHERE 1<>1

SELECT * into #tabC FROM #tab WHERE 1<>1



----测试数据结束



--建立一个数字辅助表

CREATE TABLE num(

number INT IDENTITY(0,1),

id INT

)

--插入数据进去

INSERT INTO num

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'





SELECT number FROM num









/*

* 你这个表的数据肯定很早以前就开始有了，一直到今天，数据量肯定好大。而且每个观测站最初的第一条数据

* 肯定不可能就是某个月1号的零点零分开始的，因此当你需要计算表数据产生第一个月中丢掉的数据的时候

* 必然是从每个观测站第一条记录的时间开始算起，到这个月月底结束。

* 

* 例如：你的数据从2016-01-05开始，到今天2017-11-30结束

*		如果按月计算就是这样的日期段：

*				2016-01-05----2016-01-31(1月1号到5号开始前的日期都不能算，因为第一条记录才刚产生呢，因此之前的不能算是丢掉的)

*				2016-02-01----2016-02-29(2016年是闰年，假如2016年的29号你的表中没有记录，那么这一天都丢掉了

*										如果你按2月28天算，那丢掉的一天数据就查询不到了,因此需要区分是否闰年)

*				2016-03-01----2016-03-31

*				2016-04-01----2016-04-30

*					...........................

* 

* */

--EXEC INSERT_data '2016','08',0,0

CREATE PROC insert_data

	@year VARCHAR(20),

	@month VARCHAR(20),

	@ifbegin_month BIT =1,	--默认为1代表是(是否从每月1号计算起)

	@ifend_month BIT =1		--默认为1代表是(是否计算到每月的月底)

AS

BEGIN

	DECLARE @maxnum INT

	

	--判断是否闰年

	IF (RIGHT(@year,2)='00' AND @year%400=0) OR ( @year%4=0)

		BEGIN

			SELECT @maxnum=CASE when @month IN ('01','03','05','07','08','10','12') THEN 31*288-1

								WHEN @month IN ('04','06','09','11') THEN 30*288-1

								ELSE 29*288-1

								END 

		END						

	ELSE

		BEGIN

		SELECT @maxnum=CASE when @month IN ('01','03','05','07','08','10','12') THEN 31*288-1

								WHEN @month IN ('04','06','09','11') THEN 30*288-1

						ELSE 28*288-1

						END			

		END

		

	PRINT @maxnum

	

	Select *,DAY(观测日期) as day,(DAY(观测日期)-1)*288+分钟/5+(小时-1)*12 as num

	INTO #tab1 

	from #tab

	WHERE YEAR(观测日期)=@year and month(观测日期)=@month



	SELECT DISTINCT 观测站编号,观测站名称,行驶方向,min(num) as minnum,max(num) as maxnum

	INTO #tab2 

	FROM #tab1

	GROUP BY 观测站编号,观测站名称,行驶方向



	SELECT  a.观测站编号,a.观测站名称,a.行驶方向,b.number 

	INTO #tab3

	FROM #tab2 a,

		 num b 

	WHERE b.number BETWEEN (case @ifbegin_month when 1 then 0 else a.minnum end) 

		AND (case @ifend_month when 1 then @maxnum else a.maxnum end)



	SELECT * 

	INTO #tab4

	FROM #tab3

	EXCEPT

	SELECT 观测站编号,观测站名称,行驶方向,num 

	FROM #tab1	



	SELECT *,

		@year+'-'+@month+'-'+RIGHT('00'+cast(number/288+1 AS VARCHAR(20)),2) AS 观测日期,

		number%288/12+1 as 小时,

		number%288%12*5 as 分钟,

		number-ROW_NUMBER() OVER(PARTITION BY 观测站编号,观测站名称,行驶方向 order by number)as rn

	INTO #tab5 

	FROM #tab4



	SELECT rn,COUNT(1) AS cnt 

	INTO #tab6

	FROM #tab5

	GROUP BY rn



	----丢失一条时插入#tabA表中

	--INSERT INTO #tabA(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt=1

	

	----丢失两条时插入#tabB表中

	--INSERT INTO #tabB(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt=2

	

	----丢失三条或以上时插入#tabC表中

	--INSERT INTO #tabC(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt>=3

	

	--清空临时表,以便下次使用

	TRUNCATE TABLE #tab1

	TRUNCATE TABLE #tab2

	TRUNCATE TABLE #tab3

	TRUNCATE TABLE #tab4

	TRUNCATE TABLE #tab5

	TRUNCATE TABLE #tab6

END


写了这么多也不知道你明白我的逻辑不。[/quote]
您真是费心了，可我的竟然报错了。
[/quote] 一段一段代码运行，不要一下运行
吉普赛的歌 2017-12-01
  • 打赏
  • 举报
 回复
引用 21 楼 weixin_40101242 的回复:
您真是费心了，可我的竟然报错了。
你在他的 CREATE 前面加一个 GO 再加车就可以了
weixin_40101242 2017-12-01
  • 打赏
  • 举报
 回复
引用 17 楼 qq_37170555 的回复:
[quote=引用 8 楼 weixin_40101242 的回复:]
这是别人给的建议，可是我不太懂。



use Tempdb

go

--> --> 听雨停了-->测试数据

 

if not object_id(N'Tempdb..#tab') is null

	drop table #tab

Go

Create table #tab([观测站编号] nvarchar(33),[观测站名称] nvarchar(25),[行驶方向] nvarchar(22),[观测日期] Date,[小时] int,[分钟] int)

Insert #tab

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',16,30 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',17,0 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',17,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',18,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',19,30 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',19,50 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',20,20 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',20,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',21,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',22,50 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',23,20 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',23,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',24,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',24,45 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',1,15 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',2,5 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',2,40 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',3,10 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',3,45 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',4,35 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',5,5 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',5,40



--建立三个存放数据的表A B C

SELECT * into #tabA FROM #tab WHERE 1<>1

SELECT * into #tabB FROM #tab WHERE 1<>1

SELECT * into #tabC FROM #tab WHERE 1<>1



----测试数据结束



--建立一个数字辅助表

CREATE TABLE num(

number INT IDENTITY(0,1),

id INT

)

--插入数据进去

INSERT INTO num

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'





SELECT number FROM num









/*

* 你这个表的数据肯定很早以前就开始有了，一直到今天，数据量肯定好大。而且每个观测站最初的第一条数据

* 肯定不可能就是某个月1号的零点零分开始的，因此当你需要计算表数据产生第一个月中丢掉的数据的时候

* 必然是从每个观测站第一条记录的时间开始算起，到这个月月底结束。

* 

* 例如：你的数据从2016-01-05开始，到今天2017-11-30结束

*		如果按月计算就是这样的日期段：

*				2016-01-05----2016-01-31(1月1号到5号开始前的日期都不能算，因为第一条记录才刚产生呢，因此之前的不能算是丢掉的)

*				2016-02-01----2016-02-29(2016年是闰年，假如2016年的29号你的表中没有记录，那么这一天都丢掉了

*										如果你按2月28天算，那丢掉的一天数据就查询不到了,因此需要区分是否闰年)

*				2016-03-01----2016-03-31

*				2016-04-01----2016-04-30

*					...........................

* 

* */

--EXEC INSERT_data '2016','08',0,0

CREATE PROC insert_data

	@year VARCHAR(20),

	@month VARCHAR(20),

	@ifbegin_month BIT =1,	--默认为1代表是(是否从每月1号计算起)

	@ifend_month BIT =1		--默认为1代表是(是否计算到每月的月底)

AS

BEGIN

	DECLARE @maxnum INT

	

	--判断是否闰年

	IF (RIGHT(@year,2)='00' AND @year%400=0) OR ( @year%4=0)

		BEGIN

			SELECT @maxnum=CASE when @month IN ('01','03','05','07','08','10','12') THEN 31*288-1

								WHEN @month IN ('04','06','09','11') THEN 30*288-1

								ELSE 29*288-1

								END 

		END						

	ELSE

		BEGIN

		SELECT @maxnum=CASE when @month IN ('01','03','05','07','08','10','12') THEN 31*288-1

								WHEN @month IN ('04','06','09','11') THEN 30*288-1

						ELSE 28*288-1

						END			

		END

		

	PRINT @maxnum

	

	Select *,DAY(观测日期) as day,(DAY(观测日期)-1)*288+分钟/5+(小时-1)*12 as num

	INTO #tab1 

	from #tab

	WHERE YEAR(观测日期)=@year and month(观测日期)=@month



	SELECT DISTINCT 观测站编号,观测站名称,行驶方向,min(num) as minnum,max(num) as maxnum

	INTO #tab2 

	FROM #tab1

	GROUP BY 观测站编号,观测站名称,行驶方向



	SELECT  a.观测站编号,a.观测站名称,a.行驶方向,b.number 

	INTO #tab3

	FROM #tab2 a,

		 num b 

	WHERE b.number BETWEEN (case @ifbegin_month when 1 then 0 else a.minnum end) 

		AND (case @ifend_month when 1 then @maxnum else a.maxnum end)



	SELECT * 

	INTO #tab4

	FROM #tab3

	EXCEPT

	SELECT 观测站编号,观测站名称,行驶方向,num 

	FROM #tab1	



	SELECT *,

		@year+'-'+@month+'-'+RIGHT('00'+cast(number/288+1 AS VARCHAR(20)),2) AS 观测日期,

		number%288/12+1 as 小时,

		number%288%12*5 as 分钟,

		number-ROW_NUMBER() OVER(PARTITION BY 观测站编号,观测站名称,行驶方向 order by number)as rn

	INTO #tab5 

	FROM #tab4



	SELECT rn,COUNT(1) AS cnt 

	INTO #tab6

	FROM #tab5

	GROUP BY rn



	----丢失一条时插入#tabA表中

	--INSERT INTO #tabA(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt=1

	

	----丢失两条时插入#tabB表中

	--INSERT INTO #tabB(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt=2

	

	----丢失三条或以上时插入#tabC表中

	--INSERT INTO #tabC(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt>=3

	

	--清空临时表,以便下次使用

	TRUNCATE TABLE #tab1

	TRUNCATE TABLE #tab2

	TRUNCATE TABLE #tab3

	TRUNCATE TABLE #tab4

	TRUNCATE TABLE #tab5

	TRUNCATE TABLE #tab6

END


写了这么多也不知道你明白我的逻辑不。[/quote]
您真是费心了，可我的竟然报错了。
听雨停了 2017-11-30
  • 打赏
  • 举报
 回复
引用 8 楼 weixin_40101242 的回复:
这是别人给的建议，可是我不太懂。



use Tempdb

go

--> --> 听雨停了-->测试数据

 

if not object_id(N'Tempdb..#tab') is null

	drop table #tab

Go

Create table #tab([观测站编号] nvarchar(33),[观测站名称] nvarchar(25),[行驶方向] nvarchar(22),[观测日期] Date,[小时] int,[分钟] int)

Insert #tab

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',16,30 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',17,0 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',17,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',18,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',19,30 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',19,50 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',20,20 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',20,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',21,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',22,50 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',23,20 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',23,55 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',24,25 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/3',24,45 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',1,15 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',2,5 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',2,40 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',3,10 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',3,45 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',4,35 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',5,5 union all

select N'G25L372130208',N'丰润收费站',N'上行','2016/8/4',5,40



--建立三个存放数据的表A B C

SELECT * into #tabA FROM #tab WHERE 1<>1

SELECT * into #tabB FROM #tab WHERE 1<>1

SELECT * into #tabC FROM #tab WHERE 1<>1



----测试数据结束



--建立一个数字辅助表

CREATE TABLE num(

number INT IDENTITY(0,1),

id INT

)

--插入数据进去

INSERT INTO num

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'

UNION ALL

SELECT number FROM [master]..spt_values WHERE TYPE='p'





SELECT number FROM num









/*

* 你这个表的数据肯定很早以前就开始有了，一直到今天，数据量肯定好大。而且每个观测站最初的第一条数据

* 肯定不可能就是某个月1号的零点零分开始的，因此当你需要计算表数据产生第一个月中丢掉的数据的时候

* 必然是从每个观测站第一条记录的时间开始算起，到这个月月底结束。

* 

* 例如：你的数据从2016-01-05开始，到今天2017-11-30结束

*		如果按月计算就是这样的日期段：

*				2016-01-05----2016-01-31(1月1号到5号开始前的日期都不能算，因为第一条记录才刚产生呢，因此之前的不能算是丢掉的)

*				2016-02-01----2016-02-29(2016年是闰年，假如2016年的29号你的表中没有记录，那么这一天都丢掉了

*										如果你按2月28天算，那丢掉的一天数据就查询不到了,因此需要区分是否闰年)

*				2016-03-01----2016-03-31

*				2016-04-01----2016-04-30

*					...........................

* 

* */

--EXEC INSERT_data '2016','08',0,0

CREATE PROC insert_data

	@year VARCHAR(20),

	@month VARCHAR(20),

	@ifbegin_month BIT =1,	--默认为1代表是(是否从每月1号计算起)

	@ifend_month BIT =1		--默认为1代表是(是否计算到每月的月底)

AS

BEGIN

	DECLARE @maxnum INT

	

	--判断是否闰年

	IF (RIGHT(@year,2)='00' AND @year%400=0) OR ( @year%4=0)

		BEGIN

			SELECT @maxnum=CASE when @month IN ('01','03','05','07','08','10','12') THEN 31*288-1

								WHEN @month IN ('04','06','09','11') THEN 30*288-1

								ELSE 29*288-1

								END 

		END						

	ELSE

		BEGIN

		SELECT @maxnum=CASE when @month IN ('01','03','05','07','08','10','12') THEN 31*288-1

								WHEN @month IN ('04','06','09','11') THEN 30*288-1

						ELSE 28*288-1

						END			

		END

		

	PRINT @maxnum

	

	Select *,DAY(观测日期) as day,(DAY(观测日期)-1)*288+分钟/5+(小时-1)*12 as num

	INTO #tab1 

	from #tab

	WHERE YEAR(观测日期)=@year and month(观测日期)=@month



	SELECT DISTINCT 观测站编号,观测站名称,行驶方向,min(num) as minnum,max(num) as maxnum

	INTO #tab2 

	FROM #tab1

	GROUP BY 观测站编号,观测站名称,行驶方向



	SELECT  a.观测站编号,a.观测站名称,a.行驶方向,b.number 

	INTO #tab3

	FROM #tab2 a,

		 num b 

	WHERE b.number BETWEEN (case @ifbegin_month when 1 then 0 else a.minnum end) 

		AND (case @ifend_month when 1 then @maxnum else a.maxnum end)



	SELECT * 

	INTO #tab4

	FROM #tab3

	EXCEPT

	SELECT 观测站编号,观测站名称,行驶方向,num 

	FROM #tab1	



	SELECT *,

		@year+'-'+@month+'-'+RIGHT('00'+cast(number/288+1 AS VARCHAR(20)),2) AS 观测日期,

		number%288/12+1 as 小时,

		number%288%12*5 as 分钟,

		number-ROW_NUMBER() OVER(PARTITION BY 观测站编号,观测站名称,行驶方向 order by number)as rn

	INTO #tab5 

	FROM #tab4



	SELECT rn,COUNT(1) AS cnt 

	INTO #tab6

	FROM #tab5

	GROUP BY rn



	----丢失一条时插入#tabA表中

	--INSERT INTO #tabA(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt=1

	

	----丢失两条时插入#tabB表中

	--INSERT INTO #tabB(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt=2

	

	----丢失三条或以上时插入#tabC表中

	--INSERT INTO #tabC(观测站编号,观测站名称,行驶方向,观测日期,小时,分钟)	

	SELECT 观测站编号,观测站名称,行驶方向,观测日期,小时,分钟 FROM #tab5 a

	INNER JOIN #tab6 b ON a.rn=b.rn

	WHERE b.cnt>=3

	

	--清空临时表,以便下次使用

	TRUNCATE TABLE #tab1

	TRUNCATE TABLE #tab2

	TRUNCATE TABLE #tab3

	TRUNCATE TABLE #tab4

	TRUNCATE TABLE #tab5

	TRUNCATE TABLE #tab6

END


写了这么多也不知道你明白我的逻辑不。
吉普赛的歌 2017-11-30
  • 打赏
  • 举报
 回复 
SELECT COUNT(1) AS cnt,COUNT(1)/(24*12*365) AS years FROM [master].dbo.spt_values AS s1 CROSS APPLY [master].dbo.spt_values AS s2
/*
cnt       years
5503716   52
*/
他分得太细了， 只有52年， 不过应该也够用了。
吉普赛的歌 2017-11-30
  • 打赏
  • 举报
 回复
引用 13 楼 qq_37170555 的回复:
[quote=引用 11 楼 _大约在冬季_的回复:]末尾还要加一个： 
DROP TABLE #tmp
如果它要统计去年到今年丢失的数据的话，这样的话还能出来不？而且他里面的数据肯定好大，他发出来的都是2016年的数据[/quote] 弄当然还是可以弄， 只是时间比较长了， master..spt_values 表自身再连接一次可以达到 上千年。 其实一次处理一天的数据就可以了， 这种报表形式的一般就是每晚跑一次。 历史数据可以循环来做， 没必要一次性处理完。
听雨停了 2017-11-30
  • 打赏
  • 举报
 回复
引用 6 楼 weixin_40101242的回复:
多谢各位大神帮助
等我到家给你弄，弄了一大半，结果到点下班了
听雨停了 2017-11-30
  • 打赏
  • 举报
 回复
引用 11 楼 _大约在冬季_的回复:
末尾还要加一个： 
DROP TABLE #tmp
如果它要统计去年到今年丢失的数据的话，这样的话还能出来不？而且他里面的数据肯定好大，他发出来的都是2016年的数据
RINK_1 2017-11-30
  • 打赏
  • 举报
 回复
你是不是要统计丢失的时间中，每个连续的时间段包含的5分钟为单位的时间个数。 试试下面的呢。 

 
with cte_1
as
(select *,
dateadd(minute,[分钟],dateadd(HOUR,[小时],cast([观测日期] as datetime))) as [time]
from table),
 
cte_2
as
(select ROW_NUMBER() over (order by (select null)) as seq 
from master..spt_values A,master..spt_values B
 where A.TYPE='P' and B.type='P' and A.number<1000 and B.number<1000)
 
select *,
count(1) over (partition by rn_1-rn_2) as 连续次数,
case when count(1) over (partition by rn_1-rn_2)=1 then 'A'
     when count(1) over (partition by rn_1-rn_2)>3 then 'C' 
     else 'B' 
     end as type 
from
(select *,
case when isnull(B.time,'')='' then '0' else '1' end as symbol,
ROW_NUMBER() over (order by schedule_time) as rn_1,
ROW_NUMBER() over (partition by case when isnull(B.time,'')='' then '0' else '1' end order by schedule_time) as rn_2
from 
(select DATEADD(MINUTE,(seq-1)*5,min_time) as schedule_time
from (select MIN(time) as min_time,MAX(time) as max_time from cte_1) as A
join cte_2 as B on (DATEDIFF(MINUTE,min_time,max_time)/5)+1>=B.seq) as A
left join cte_1 as B ON A.schedule_time=B.time) as A
where symbol=0
order by schedule_time
吉普赛的歌 2017-11-30
  • 打赏
  • 举报
 回复
末尾还要加一个： 
DROP TABLE #tmp
吉普赛的歌 2017-11-30
  • 打赏
  • 举报
 回复 
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
IF OBJECT_ID('b') IS NOT NULL DROP TABLE b
IF OBJECT_ID('c') IS NOT NULL DROP TABLE c
GO
CREATE TABLE t(d DATE,h INT,m INT)
CREATE TABLE b(d DATE,h INT,m INT)
CREATE TABLE c(d DATE,h INT,m INT)
--0. 插入测试数据
;WITH cte AS(
	SELECT DATEADD(n,sv.number*5,'2017-08-03 16:30') AS theTime
	FROM MASTER.dbo.spt_values AS sv 
	WHERE sv.[type]='P' AND DATEADD(n,sv.number*5,'2017-08-03 16:30')
	BETWEEN '2017-08-03 16:30' AND '2017-08-04 08:40'
)
INSERT INTO t
SELECT CONVERT(CHAR(10),theTime,120),DATEPART(hour,theTime),DATEPART(minute,theTime)
FROM cte

--1. 增加完整时间计算列
ALTER TABLE t ADD fullTime AS CAST( (CONVERT(CHAR(10),d,120)+' '+CAST(h AS VARCHAR(2))+':'+CAST(m AS VARCHAR(2))) AS DATETIME)
GO
--删除部分记录
DELETE FROM t WHERE fullTime IN ('2017-08-03 16:35:00.000','2017-08-03 16:40:00.000','2017-08-03 16:45:00.000','2017-08-03 16:50:00.000')
DELETE FROM t WHERE fullTime IN ('2017-08-03 17:10:00.000','2017-08-03 17:15:00.000')
GO

/*
--先查出来看看
;WITH cte AS(
	SELECT ROW_NUMBER() OVER (ORDER BY fullTime) AS rid,*	FROM t
)
SELECT a.*,DATEDIFF(n,a.fullTime,b.fullTime)/5-1 AS lostData 
FROM cte AS a LEFT JOIN cte AS b ON a.rid=b.rid-1 
*/

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
;WITH cte AS(
	SELECT ROW_NUMBER() OVER (ORDER BY fullTime) AS rid,*	FROM t
),cte2 AS(
	SELECT a.*,CAST( (DATEDIFF(n,a.fullTime,b.fullTime)/5-1) AS INT) AS lostData
	FROM cte AS a LEFT JOIN cte AS b ON a.rid=b.rid-1 
)
SELECT * 
INTO #tmp
FROM cte2
WHERE lostData>0

;WITH cte AS (
	SELECT DATEADD(n,t.number*5,a.fullTime) AS fullTime
	FROM #tmp AS a
	OUTER APPLY (SELECT sv.number
				   FROM MASTER.dbo.spt_values AS sv WHERE sv.type='P' AND sv.number>=1 AND sv.number<=a.lostData) AS t --ON 1=1
	WHERE lostData<3
)
INSERT INTO b
SELECT CONVERT(CHAR(10),fullTime,120),DATEPART(hour,fullTime),DATEPART(minute,fullTime) FROM cte

--SELECT * FROM #tmp
;WITH cte AS (
	SELECT DATEADD(n,t.number*5,a.fullTime) AS fullTime
	FROM #tmp AS a
	OUTER APPLY (SELECT sv.number
				   FROM MASTER.dbo.spt_values AS sv WHERE sv.type='P' AND sv.number>=1 AND sv.number<=a.lostData) AS t --ON 1=1
	WHERE lostData>=3
)
INSERT INTO c
SELECT CONVERT(CHAR(10),fullTime,120),DATEPART(hour,fullTime),DATEPART(minute,fullTime) FROM cte

SELECT * FROM b
/*
d	         h	m
2017-08-03	17	10
2017-08-03	17	15
*/
SELECT * FROM c
/*
d	         h	m
2017-08-03	16	35
2017-08-03	16	40
2017-08-03	16	45
2017-08-03	16	50
*/
听雨停了 2017-11-30
  • 打赏
  • 举报
 回复
引用 8 楼 weixin_40101242 的回复:
[quote=引用 7 楼 qq_37170555 的回复:] [quote=引用 6 楼 weixin_40101242 的回复:] 多谢各位大神帮助
这确实搞得有点复杂啊，数据量多的话建议按月去插入[/quote] 试下把 表的3个字段合到一起，然后做一个left join 连接的条件是 时间+5分钟， 然后 做一个标识，没有连接的为0 连接的为1 然后统计 两个标识0之间的1的个数 再插入到对应表。 这是别人给的建议，可是我不太懂。[/quote] 你这个表里面的数据量应该很大吧，看你的数据都有2016年的。我刚大概写了下，越写越觉得发杂，这个还关系到是不是闰年，每个月的天数的问题。现在的表中的，最小的日期是什么时候，也就是这个表的第一笔记录，最大的日期又是什么时候(最后一笔记录)，都查出来我看看
weixin_40101242 2017-11-30
  • 打赏
  • 举报
 回复
引用 7 楼 qq_37170555 的回复:
[quote=引用 6 楼 weixin_40101242 的回复:] 多谢各位大神帮助
这确实搞得有点复杂啊，数据量多的话建议按月去插入[/quote] 试下把 表的3个字段合到一起，然后做一个left join 连接的条件是 时间+5分钟， 然后 做一个标识，没有连接的为0 连接的为1 然后统计 两个标识0之间的1的个数 再插入到对应表。 这是别人给的建议，可是我不太懂。
听雨停了 2017-11-30
  • 打赏
  • 举报
 回复
引用 6 楼 weixin_40101242 的回复:
多谢各位大神帮助
这确实搞得有点复杂啊，数据量多的话建议按月去插入
weixin_40101242 2017-11-30
  • 打赏
  • 举报
 回复
多谢各位大神帮助
weixin_40101242 2017-11-30
  • 打赏
  • 举报
 回复
引用 4 楼 qq_37170555 的回复:
[quote=引用 3 楼 weixin_40101242 的回复:] [quote=引用 2 楼 qq_37170555 的回复:] [quote=引用 1 楼 weixin_40101242 的回复:] 将问题表达清楚好难啊
大概明白了你的意思。比如说你上图的第一二条数据，期间丢失了5次的观测数据(35,40,45,50,55)。你需要插入的数据又是什么样的呢，也就是abc表的结构，是否有一个开始时间，结束时间，丢失次数[/quote] 结构和图片上的一样，如果不可行，只包括时间就行。 一二条数据之间缺5条，那就是插入c表即可，别的不需要考虑。[/quote] 把上面两条记录都插进去？是的话发一下测试数据出来[/quote] 是插入缺失的数据，不是表中有的数据。 观测站编号 观测站名称 行驶方向 观测日期 小时 分钟 G25L372130208 丰润收费站 上行 2016/8/3 16 30 G25L372130208 丰润收费站 上行 2016/8/3 17 0 G25L372130208 丰润收费站 上行 2016/8/3 17 55 G25L372130208 丰润收费站 上行 2016/8/3 18 25 G25L372130208 丰润收费站 上行 2016/8/3 19 30 G25L372130208 丰润收费站 上行 2016/8/3 19 50 G25L372130208 丰润收费站 上行 2016/8/3 20 20 G25L372130208 丰润收费站 上行 2016/8/3 20 55 G25L372130208 丰润收费站 上行 2016/8/3 21 25 G25L372130208 丰润收费站 上行 2016/8/3 22 50 G25L372130208 丰润收费站 上行 2016/8/3 23 20 G25L372130208 丰润收费站 上行 2016/8/3 23 55 G25L372130208 丰润收费站 上行 2016/8/3 24 25 G25L372130208 丰润收费站 上行 2016/8/3 24 45 G25L372130208 丰润收费站 上行 2016/8/4 1 15 G25L372130208 丰润收费站 上行 2016/8/4 2 5 G25L372130208 丰润收费站 上行 2016/8/4 2 40 G25L372130208 丰润收费站 上行 2016/8/4 3 10 G25L372130208 丰润收费站 上行 2016/8/4 3 45 G25L372130208 丰润收费站 上行 2016/8/4 4 35 G25L372130208 丰润收费站 上行 2016/8/4 5 5 G25L372130208 丰润收费站 上行 2016/8/4 5 40
听雨停了 2017-11-30
  • 打赏
  • 举报
 回复
引用 3 楼 weixin_40101242 的回复:
[quote=引用 2 楼 qq_37170555 的回复:] [quote=引用 1 楼 weixin_40101242 的回复:] 将问题表达清楚好难啊
大概明白了你的意思。比如说你上图的第一二条数据，期间丢失了5次的观测数据(35,40,45,50,55)。你需要插入的数据又是什么样的呢，也就是abc表的结构，是否有一个开始时间，结束时间，丢失次数[/quote] 结构和图片上的一样，如果不可行，只包括时间就行。 一二条数据之间缺5条，那就是插入c表即可，别的不需要考虑。[/quote] 把上面两条记录都插进去？是的话发一下测试数据出来
加载更多回复（3）
Python数据处理实战：基于真实场景的数据
    目前数据分析已经深入到各个行业中，尤其以Python为工具的数据分析和数据挖掘将越来越流行，但在数据分析和挖掘中，最消耗时间的就是数据处理了，高效的数据处理技能已经成为工作中必不可少的技能之一了。熟练掌握和运用Python对数据进行高效的处理，可以大大提高数据分析和数据挖掘的效率。    Python数据处理实战: 基于真实场景的数据(Python数据处理和特征工程)作为Python数据清洗实战入门课程的升级版，本课程以真实的场景数据为案例进行教学，包括征信，电商，零售数据等, 本课程由浅入深详细讲解Python数据处理和特征工程在真实项目中的运用, 本课程专门针对想深入学习Python数据处理而量身定做的课程，是讲师在多年真实项目和实践工作的总结，涵盖实际项目中主要的知识点，内容详尽，代码可读性及实操性强。     掌握好数据处理和特征工程，有利于今后从事或者转行数据分析或者数据挖掘，以及解决工作和项目中遇到的各种数据处理问题。课程目标：1.熟悉数据处理的流程和方法 2.熟练掌握pandas和numpy的运用 3.举一反三,能够独立完成数据分析中数据处理阶段的任务 4.提高数据处理能力，在项目中能够事半功倍课程定位: 1.   零基础学员或者有一定基础学员、大中院校学生；2.   在职从事数据分析相关工作以及打算转行Python数据分析人员； 3.   对Python有兴趣人群。 课程特色   1.   相关代码老师课堂上全部打出，方便理解和记忆;   2.   提供源代码和数据方便同学们预习和复习;   3.   使用真实的数据进行教学，紧贴实战，避免枯燥的理论;   4.   在教学过程中，尽可能多的使用图表教学;  5.    每一个章节后面都配有相关练习题目以及习题答案，方便同学们进行自我测试。 课程学习环境: Python3.7版本 讲师介绍:    Peter， 某科技公司高级量化分析师，金融数学硕士毕业，擅长数据分析和数据挖掘，在公司长期从事机器学习建模，拥有多家银行，消费金融和互联网金融风控建模经验。熟练掌握Python编程软件和数据库等软件.
mysql时间段查询开始结束时间在此时间段内的数据
在项目中偶尔会遇到使用时间段查询数据，数据中包含有开始结束时间，时间有重合就查询出，现做个记录，以免重复费脑 select * from XXX (star_time > #{startTime} AND (#{endTime} > end_time OR (#{endTime} > star_time AND #{endTime} &lt; end_time))) OR (star_time &lt; #{startTime} AND (#{endTime}>end
oracle查询某个时间段的数据
oracle查询某个时间段的数据
mysql查询特定时间段内的数据
转载自：https://blog.csdn.net/EightSwords/article/details/79702101 建表语句： SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for t_user -- ---------------------------- ...
SQLServer 时间段分隔，时间段查询，查询时间段内的数据
我们经常碰到需要查询时间段内的数据，以下函数能有所有帮助， 有了这个table后可以将需要分段查询的数据left join 关联该表， 然后就可以分组求和了，具体如何使用得看大家的情景 将一天的时间进行时间段分隔，效果如下 --这是一个写好的函数，复制代码执行就好，调用方式： SELECT * FROM dbo.fun_getDateTimeSpacing('', 120...
疑难问题

22,209

社区成员

121,731

社区内容

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

加载中

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

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

+ 用AI写文章