背景资料贴
1 . http://topic.csdn.net/u/20081019/16/cdb4718f-315e-427c-867d-39082b6ab6da.html
2 . http://topic.csdn.net/u/20081022/09/a1753e9e-f67f-4288-942a-faf2ad969ee0.html下面是两种月表统计的sql,思路都是先计算本月存在哪些周,然后将这些周的记录求和统计,然后减去这些周里不在本月的那几天的数据总和
sql1
select distinct week into #week from wapcq.Time_Date_Dim where year=@year and month=@month
select timeidd into #timeidd from wapcq.Time_Date_Dim where year=@year and week in (select week from #week) and month<>@month insert into PhoneNumber_month
select @year,@month,PhoneNumber,Type,
Col1=sum(Col1)-isnull((select sum(Col1) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
Col2=sum(Col2)-isnull((select sum(Col2) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
Col3=sum(Col3)-isnull((select sum(Col3) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0)
from PhoneNumber_week a
where a.year=@year and a.week in (select week from #week)
group by PhoneNumber,Type drop table #week
drop table #timeidd
sql2
select distinct week into #week from wapcq.Time_Date_Dim where year=@year and month=@month
select timeidd into #timeidd from wapcq.Time_Date_Dim where year=@year and week in (select week from #week) and month<>@monthinsert into PhoneNumber_month
select @year,@month,PhoneNumber,Type,sum(Col1),sum(Col2),sum(Col3)
from
(select PhoneNumber,Type,Col1=sum(Col1),Col2=sum(Col2),Col3=sum(Col3)
from PhoneNumber_week a
where a.year=@year and a.week in (select week from #week)
group by a.PhoneNumber,a.Type
union all
select PhoneNumber,Type,Col1=-sum(Col1),Col2=-sum(Col2),Col3=-sum(Col3)
from PhoneNumber_date b
where b.timeidd in (select timeidd from #timeidd)
group by b.PhoneNumber,b.Type) z
group by z.PhoneNumber,z.Type
Time_Date_Dim 表结构(tiemidd,year,month,week,date) ,对于单独的一天分配唯一的timeidd号
PhoneNumber_data 表结构(timeidd,PhoneNumber,Type,Col1,Col2,Col3)
PhoneNumber_week 表结构(year,week,PhoneNumber,Type,Col1,Col2,Col3)
PhoneNumber_month 表结构(year,month,PhoneNumber,Type,Col1,Col2,Col3)
1 . http://topic.csdn.net/u/20081019/16/cdb4718f-315e-427c-867d-39082b6ab6da.html
2 . http://topic.csdn.net/u/20081022/09/a1753e9e-f67f-4288-942a-faf2ad969ee0.html下面是两种月表统计的sql,思路都是先计算本月存在哪些周,然后将这些周的记录求和统计,然后减去这些周里不在本月的那几天的数据总和
sql1
select distinct week into #week from wapcq.Time_Date_Dim where year=@year and month=@month
select timeidd into #timeidd from wapcq.Time_Date_Dim where year=@year and week in (select week from #week) and month<>@month insert into PhoneNumber_month
select @year,@month,PhoneNumber,Type,
Col1=sum(Col1)-isnull((select sum(Col1) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
Col2=sum(Col2)-isnull((select sum(Col2) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
Col3=sum(Col3)-isnull((select sum(Col3) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0)
from PhoneNumber_week a
where a.year=@year and a.week in (select week from #week)
group by PhoneNumber,Type drop table #week
drop table #timeidd
sql2
select distinct week into #week from wapcq.Time_Date_Dim where year=@year and month=@month
select timeidd into #timeidd from wapcq.Time_Date_Dim where year=@year and week in (select week from #week) and month<>@monthinsert into PhoneNumber_month
select @year,@month,PhoneNumber,Type,sum(Col1),sum(Col2),sum(Col3)
from
(select PhoneNumber,Type,Col1=sum(Col1),Col2=sum(Col2),Col3=sum(Col3)
from PhoneNumber_week a
where a.year=@year and a.week in (select week from #week)
group by a.PhoneNumber,a.Type
union all
select PhoneNumber,Type,Col1=-sum(Col1),Col2=-sum(Col2),Col3=-sum(Col3)
from PhoneNumber_date b
where b.timeidd in (select timeidd from #timeidd)
group by b.PhoneNumber,b.Type) z
group by z.PhoneNumber,z.Type
Time_Date_Dim 表结构(tiemidd,year,month,week,date) ,对于单独的一天分配唯一的timeidd号
PhoneNumber_data 表结构(timeidd,PhoneNumber,Type,Col1,Col2,Col3)
PhoneNumber_week 表结构(year,week,PhoneNumber,Type,Col1,Col2,Col3)
PhoneNumber_month 表结构(year,month,PhoneNumber,Type,Col1,Col2,Col3)
使用临时表的方法,在速度和支持的数据量方面好
何解?先把所有周表的数据总和放到临时表里,然后在临时表里做减法,然后把结果放到最终的PhoneNumber_month表里?
如果是临时计算,使用临时表的方法处理,因业务的数据量大,可以支持大数据
如果周和年的数据,不存在倒着录的情况,可以采用增加多个汇总表的方法,每天整理或写单据时直接加入到汇总表的数据中,避免临时计算的时间长,用户的领导才看这种汇总表,不可能长期等待计算结果
ERP、POS系统等经常会用这种方法,理论方面可参照<<CRM数据仓库和数据挖掘>>
如果是临时计算,使用临时表的方法处理,因业务的数据量大,可以支持大数据
如果周和年的数据,不存在倒着录的情况,可以采用增加多个汇总表的方法,每天整理或写单据时直接加入到汇总表的数据中,避免临时计算的时间长,用户的领导才看这种汇总表,不可能长期等待计算结果
ERP、POS系统等经常会用这种方法,理论方面可参照<<CRM数据仓库和数据挖掘>>
如果是临时计算,使用临时表的方法处理,因业务的数据量大,可以支持大数据
如果周和年的数据,不存在倒着录的情况,可以采用增加多个汇总表的方法,每天整理或写单据时直接加入到汇总表的数据中,避免临时计算的时间长,用户的领导才看这种汇总表,不可能长期等待计算结果
ERP、POS系统等经常会用这种方法,理论方面可参照<<CRM数据仓库和数据挖掘>>
建议将跨月的周分为两条数据,既周表里包含月标识
例如:
日:2008-09-30 --> 周:Y2008,M09,W40 --> 月:Y2008,M09
日:2008-10-01 --> 周:Y2008,M10,W40 --> 月:Y2008,M10
insert into PhoneNumber_Week
select PhoneNumber,Type,
Col1=sum(Col1)-isnull((select sum(Col1) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
Col2=sum(Col2)-isnull((select sum(Col2) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
Col3=sum(Col3)-isnull((select sum(Col3) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
[Year]=year([date]),[Month]=month([date]),[Week]=datepart(week,[date])
from PhoneNumber_Day a
where [date] between @bgnDate and @endDate
group by PhoneNumber,Type,
year([date]),month([date]),datepart(week,[date])-- 每月一次
insert into PhoneNumber_Month
select PhoneNumber,Type,
Col1=sum(Col1)-isnull((select sum(Col1) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
Col2=sum(Col2)-isnull((select sum(Col2) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
Col3=sum(Col3)-isnull((select sum(Col3) from PhoneNumber_date b where b.PhoneNumber=a.PhoneNumber and b.Type=a.Type and timeidd in (select timeidd from #timeidd)),0),
[Year]=year([date]),[Month]=month([date])
from PhoneNumber_Week a
where [Year]=@year and [Month] = @month
group by PhoneNumber,Type,
[Year],[Month]