摘要:按月份方式检查数据是否连续(1)
业务中,有份统计报表,记录小区每月的价格增长率(正常情况下,每个小区一个月一条数据),格式如下:
declare @tab table(project_name varchar(50)/*小区*/,ym varchar(10)/*月份,格式为yyyymm*/,increase float/*价格环比增长率*/);
数据测试如下：
insert into @tab(project_name,ym,increase) values
('某小区','200911','-0.000437162'),('某小区','200912','-0.000437353'),('某小区','201001','-0.000437544'),
('某小区','201002','-0.00509823'),('某小区','201003','-0.00512436'),('某小区','201004','-0.00163201'),
('某小区','201005','-0.00163468'),('某小区','201006','-0.00163736'),('某小区','201007','-0.00164004'),
('某小区','201008','-0.00164274'),('某小区','201009','-0.00164544'),('某小区','201010','-0.00164815'),
('某小区','201011','0.00154091'),('某小区','201012','0.00153854'),('某小区','201101','0.00153618'),
('某小区','201102','0.00153382'),('某小区','201103','0.00739988'),('某小区','201104','0.00734552'),
('某小区','201105','0.00729196'),('某小区','201106','-0.00497764'),('某小区','201107','-0.00500254'),
('某小区','201108','-0.00502769'),('某小区','201109','-0.0050531'),('某小区','201110','-0.00507876'),
--('某小区','201111','-0.00510469'),('某小区','201112','-0.00513088'),('某小区','201201','0.011223'),
('某小区','201202','0.0110985'),('某小区','201203','-0.0291962');
----结果如下----------------------------------------------------------------------
解决思路,按照每小区一个月一条数据的情况(更新频率是天,周,季度,半年,年的情况同理),那么如果数据是连续的话,该小区最早月份和最新月份差加1的结果与条数相同.
示例代码如下:
declare @tab table(project_name varchar(50)/*小区*/,ym varchar(10)/*月份,格式为yyyymm*/,increase float/*价格环比增长率*/);
insert into @tab(project_name,ym,increase) values
('某小区','200911','-0.000437162'),('某小区','200912','-0.000437353'),('某小区','201001','-0.000437544'),
('某小区','201002','-0.00509823'),('某小区','201003','-0.00512436'),('某小区','201004','-0.00163201'),
('某小区','201005','-0.00163468'),('某小区','201006','-0.00163736'),('某小区','201007','-0.00164004'),
('某小区','201008','-0.00164274'),('某小区','201009','-0.00164544'),('某小区','201010','-0.00164815'),
('某小区','201011','0.00154091'),('某小区','201012','0.00153854'),('某小区','201101','0.00153618'),
('某小区','201102','0.00153382'),('某小区','201103','0.00739988'),('某小区','201104','0.00734552'),
('某小区','201105','0.00729196'),('某小区','201106','-0.00497764'),('某小区','201107','-0.00500254'),
('某小区','201108','-0.00502769'),('某小区','201109','-0.0050531'),('某小区','201110','-0.00507876'),
--('某小区','201111','-0.00510469'),('某小区','201112','-0.00513088'),('某小区','201201','0.011223'),
('某小区','201202','0.0110985'),('某小区','201203','-0.0291962');
select project_name,count(*) as total_count
,datediff(month,convert(datetime,stuff(min(ym),5,0,'-')+'-01'),convert(datetime,stuff(max(ym),5,0,'-')+'-01'))+1 as diff_month_value
from @tab
group by project_name
having count(*)-datediff(month,convert(datetime,stuff(min(ym),5,0,'-')+'-01'),convert(datetime,stuff(max(ym),5,0,'-')+'-01'))-1!=0
----------运行结果------------------------------------------------
如果要检查具体的断档月份,请看下次文章.