USE [RuleSet]
GO
/****** Object: StoredProcedure [dbo].[P_SplitTable] Script Date: 2016/10/15 9:02:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[P_SplitTable]
(
@table nvarchar(4000) --需要分析的查询 支持跨库如select* from database.dbo.tablex
,@column nvarchar(200) --需要分析的字段,必须是数值类型
,@split nvarchar(100)='1,2,7'
,@type tinyint =0 -- 0表示按照数据的数量分组。count(1),1表示按照金额汇总。sum(@column)
,@sort nvarchar(100)='desc' --表示对字段进行排序的排序类型 逆序表示要找到最大的那些。顺序asc表示要找到最小的那些
)
as
/*
数据分析----金字塔分组
根据列的值对表的数据进行百分比分组,如'1,2,7'代表按照数据
*/
begin
--set nocount on ;
declare @t table(id int identity(1,1),splitdata float,foot float,per float);
if OBJECT_ID('tempdb..#t_1') is not null
drop table #t_1;
create table #t_1 (id int identity(1,1) primary key ,col float,total float,total_percent float);
declare @total float,@null float,@nsql nvarchar(4000),@m int,@n int,@cur float,@foot float;
--基础统计
set @nsql='select @total='+(case when @type=0 then 'count' when @type=1 then 'sum' end)+'('+quotename(@column)+')
,@null=count(1)-count('+quotename(@column)+')
from ('+@table+') as aa'
exec sp_executesql @nsql,N'@total float output,@null float output',@total output,@null output;
insert into @t(splitdata)
select splitdata from [dbo].[fnSplitString](@split,',');
--写入规模累计百分比
update b
set b.per=a.per
from
(select a.id,(sum(b.splitdata)/(select sum(splitdata) from @t)) as per
from @t a join @t b on a.id>=b.id
group by a.id) a
join @t b
on a.id=b.id
--数据写入
set @nsql='select '+quotename(@column)+' ,'+(case when @type=0 then 'count' when @type=1 then 'sum' end)+'('+quotename(@column)+')
from ('+@table+') as aa
group by '+quotename(@column)+' order by '+quotename(@column)+' '+@sort+' ';
insert into #t_1(col,total) exec(@nsql);
--计算累计值得算法。当大量数据的时候,记录数cross join 将无法正常执行。
-- 引入分批次的概念。在累加基础上考虑初值即可。
select @m=min(id),@n=max(id) from #t_1
while @m<=@n
begin
;
with tb
as
(select a.id,(sum(b.total)+isnull((select sum(total) from #t_1 where id<@m),0))/@total as total_percent
from #t_1 a join #t_1 b on
a.id>=@m and a.id<@m+500 and b.id>=@m and b.id<@m+500
and a.id>=b.id
group by a.id
)
--数据百分比
update b
set b.total_percent=a.total_percent
from
tb a
join #t_1 b
on a.id=b.id and b.id >=@m and b.id<@m+500;
--print @m;
set @m=@m+500
end
update b
set b.foot=a.col
from
(select a.id,c.col from @t a join #t_1 b on a.per>b.total_percent
join #t_1 c on c.id=b.id+1 and a.per<=c.total_percent
) a
join @t b
on a.id=b.id
select id,splitdata,foot,@null as blank from @t;
--select * from @t;
--select * from #t_1 order by col desc
;
end
/*
--测试 count 的统计
declare @table nvarchar(4000) --需要分析的查询 支持跨库如select* from database.dbo.tablex
,@column nvarchar(200) --需要分析的字段,必须是数值类型
,@split nvarchar(100)='1,2,7'
,@type tinyint =0 -- 0表示按照数据的数量分组。count(1),1表示按照金额汇总。sum(@column)
,@sort nvarchar(100)='desc' --表示对字段进行排序的排序类型 逆序表示要找到最大的那些。顺序asc表示要找到最小的那些
set @table='select a.BO_ID,sum(b.nBalance) as nbalance
from bigdatabi..[NFB_BOAccount] a join bigdatabi..[NFB_Balance] b
on a.cAccount=b.cAccount and a.bUse=1
group by a.BO_ID
';
set @column='nbalance';
exec dbo.P_SplitTable @table,@column,@split,@type,@sort
-- 测试 sum的统计
declare @table nvarchar(4000) --需要分析的查询 支持跨库如select* from database.dbo.tablex
,@column nvarchar(200) --需要分析的字段,必须是数值类型
,@split nvarchar(100)='7,2,1'
,@type tinyint =1 -- 0表示按照数据的数量分组。count(1),1表示按照金额汇总。sum(@column)
,@sort nvarchar(100)='desc' --表示对字段进行排序的排序类型 逆序表示要找到最大的那些。顺序asc表示要找到最小的那些
set @table='select a.BO_ID,sum(b.nBalance) as nbalance
from bigdatabi..[NFB_BOAccount] a join bigdatabi..[NFB_Balance] b
on a.cAccount=b.cAccount and a.bUse=1
group by a.BO_ID
';
set @column='nbalance';
exec dbo.P_SplitTable @table,@column,@split,@type,@sort
select
(case when nbalance>470172523.44 then 'A'
when nbalance>1739249.07 then 'B'
when nbalance>250666.27 then 'C'
else 'D'
end)
as sig
,sum(nbalance)
from
(select a.BO_ID,sum(b.nBalance) as nbalance
from bigdatabi..[NFB_BOAccount] a join bigdatabi..[NFB_Balance] b
on a.cAccount=b.cAccount and a.bUse=1
group by a.BO_ID) as aa
group by
(case when nbalance>470172523.44 then 'A'
when nbalance>1739249.07 then 'B'
when nbalance>250666.27 then 'C'
else 'D'
end)
order by sig
*/
GO
/****** Object: StoredProcedure [dbo].[P_SplitTable] Script Date: 2016/10/15 9:02:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[P_SplitTable]
(
@table nvarchar(4000) --需要分析的查询 支持跨库如select* from database.dbo.tablex
,@column nvarchar(200) --需要分析的字段,必须是数值类型
,@split nvarchar(100)='1,2,7'
,@type tinyint =0 -- 0表示按照数据的数量分组。count(1),1表示按照金额汇总。sum(@column)
,@sort nvarchar(100)='desc' --表示对字段进行排序的排序类型 逆序表示要找到最大的那些。顺序asc表示要找到最小的那些
)
as
/*
数据分析----金字塔分组
根据列的值对表的数据进行百分比分组,如'1,2,7'代表按照数据
*/
begin
--set nocount on ;
declare @t table(id int identity(1,1),splitdata float,foot float,per float);
if OBJECT_ID('tempdb..#t_1') is not null
drop table #t_1;
create table #t_1 (id int identity(1,1) primary key ,col float,total float,total_percent float);
declare @total float,@null float,@nsql nvarchar(4000),@m int,@n int,@cur float,@foot float;
--基础统计
set @nsql='select @total='+(case when @type=0 then 'count' when @type=1 then 'sum' end)+'('+quotename(@column)+')
,@null=count(1)-count('+quotename(@column)+')
from ('+@table+') as aa'
exec sp_executesql @nsql,N'@total float output,@null float output',@total output,@null output;
insert into @t(splitdata)
select splitdata from [dbo].[fnSplitString](@split,',');
--写入规模累计百分比
update b
set b.per=a.per
from
(select a.id,(sum(b.splitdata)/(select sum(splitdata) from @t)) as per
from @t a join @t b on a.id>=b.id
group by a.id) a
join @t b
on a.id=b.id
--数据写入
set @nsql='select '+quotename(@column)+' ,'+(case when @type=0 then 'count' when @type=1 then 'sum' end)+'('+quotename(@column)+')
from ('+@table+') as aa
group by '+quotename(@column)+' order by '+quotename(@column)+' '+@sort+' ';
insert into #t_1(col,total) exec(@nsql);
--计算累计值得算法。当大量数据的时候,记录数cross join 将无法正常执行。
-- 引入分批次的概念。在累加基础上考虑初值即可。
select @m=min(id),@n=max(id) from #t_1
while @m<=@n
begin
;
with tb
as
(select a.id,(sum(b.total)+isnull((select sum(total) from #t_1 where id<@m),0))/@total as total_percent
from #t_1 a join #t_1 b on
a.id>=@m and a.id<@m+500 and b.id>=@m and b.id<@m+500
and a.id>=b.id
group by a.id
)
--数据百分比
update b
set b.total_percent=a.total_percent
from
tb a
join #t_1 b
on a.id=b.id and b.id >=@m and b.id<@m+500;
--print @m;
set @m=@m+500
end
update b
set b.foot=a.col
from
(select a.id,c.col from @t a join #t_1 b on a.per>b.total_percent
join #t_1 c on c.id=b.id+1 and a.per<=c.total_percent
) a
join @t b
on a.id=b.id
select id,splitdata,foot,@null as blank from @t;
--select * from @t;
--select * from #t_1 order by col desc
;
end
/*
--测试 count 的统计
declare @table nvarchar(4000) --需要分析的查询 支持跨库如select* from database.dbo.tablex
,@column nvarchar(200) --需要分析的字段,必须是数值类型
,@split nvarchar(100)='1,2,7'
,@type tinyint =0 -- 0表示按照数据的数量分组。count(1),1表示按照金额汇总。sum(@column)
,@sort nvarchar(100)='desc' --表示对字段进行排序的排序类型 逆序表示要找到最大的那些。顺序asc表示要找到最小的那些
set @table='select a.BO_ID,sum(b.nBalance) as nbalance
from bigdatabi..[NFB_BOAccount] a join bigdatabi..[NFB_Balance] b
on a.cAccount=b.cAccount and a.bUse=1
group by a.BO_ID
';
set @column='nbalance';
exec dbo.P_SplitTable @table,@column,@split,@type,@sort
-- 测试 sum的统计
declare @table nvarchar(4000) --需要分析的查询 支持跨库如select* from database.dbo.tablex
,@column nvarchar(200) --需要分析的字段,必须是数值类型
,@split nvarchar(100)='7,2,1'
,@type tinyint =1 -- 0表示按照数据的数量分组。count(1),1表示按照金额汇总。sum(@column)
,@sort nvarchar(100)='desc' --表示对字段进行排序的排序类型 逆序表示要找到最大的那些。顺序asc表示要找到最小的那些
set @table='select a.BO_ID,sum(b.nBalance) as nbalance
from bigdatabi..[NFB_BOAccount] a join bigdatabi..[NFB_Balance] b
on a.cAccount=b.cAccount and a.bUse=1
group by a.BO_ID
';
set @column='nbalance';
exec dbo.P_SplitTable @table,@column,@split,@type,@sort
select
(case when nbalance>470172523.44 then 'A'
when nbalance>1739249.07 then 'B'
when nbalance>250666.27 then 'C'
else 'D'
end)
as sig
,sum(nbalance)
from
(select a.BO_ID,sum(b.nBalance) as nbalance
from bigdatabi..[NFB_BOAccount] a join bigdatabi..[NFB_Balance] b
on a.cAccount=b.cAccount and a.bUse=1
group by a.BO_ID) as aa
group by
(case when nbalance>470172523.44 then 'A'
when nbalance>1739249.07 then 'B'
when nbalance>250666.27 then 'C'
else 'D'
end)
order by sig
*/