tsql-2008.txt

SQL数据挖掘的具体方法实现！！

GO
ALTER TABLE [dbo].[facttccprk]  WITH CHECK ADD  CONSTRAINT [FK_facttccprk_dimGG] FOREIGN KEY([ggid])
REFERENCES [dbo].[dimGG] ([ggid])
GO
ALTER TABLE [dbo].[facttccprk]  WITH CHECK ADD  CONSTRAINT [FK_facttccprk_DimRQ] FOREIGN KEY([rqid])
REFERENCES [dbo].[DimRQ] ([rqid])

-- 创建时间维表[DimMonth]
CREATE TABLE [dbo].[DimMonth](
    [Year] [char](4) not NULL,
    [Month] [tinyint] not NULL,
    MonthName varchar(12) null,
    CONSTRAINT [PK_DimMonth_Year_Month] PRIMARY KEY CLUSTERED([Year] ASC,[Month] asc)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

update DimRQ set [Month]=8 where [month] is null  -- DimRQ表问题补救处理
select distinct [Year],[Month],'第'+[Year]+'年'+cast([Month] as varchar(2))+'月' from DimRQ  -- 查询
-- 添加记录到[DimMonth]表
insert into [DimMonth] select distinct [Year],[Month],'第'+[Year]+'年'+cast([Month] as varchar(2))+'月' as MonthName from DimRQ
-- 添加外码 
ALTER TABLE [dbo].[DimRQ]  WITH CHECK ADD  CONSTRAINT [FK_DimRQ_DimMonth] FOREIGN KEY([Year],[Month])
REFERENCES [dbo].[DimMonth] ([Year],[Month])
GO



-- 基于星型或雪花型结构，各种统计信息。其中一些统计信息可永久组织存放于表中以用于挖掘。
-- 基于星型或雪花型结构，各种统计信息。其中一些统计信息可永久组织存放于表中以用于挖掘。
-- 基于星型或雪花型结构，各种统计信息。其中一些统计信息可永久组织存放于表中以用于挖掘。
select * from facttccpck
select * from dimrq
select [year],[Month],crkz from facttccpck f,dimrq d 
where f.rqid=d.rqid order by [year],[month]
-- 年统计
select [year],sum(crkz) as '销售量'
from facttccpck f,dimrq d 
where f.rqid=d.rqid
group by [year]
order by [year]
-- 年季统计
select [year],cast([Season] as int),sum(crkz) as '销售量'
from facttccpck f,dimrq d 
where f.rqid=d.rqid
group by [year],[Season]
order by [year],2
-- 年季统计
select [year],[Season],sum(crkz) as '销售量'
from facttccpck f,dimrq d 
where f.rqid=d.rqid
group by [year],[Season]
order by [year],2

-- 年月统计
select [year],[Month],sum(crkz) as '销售量'
from facttccpck f,dimrq d 
where f.rqid=d.rqid
group by [year],[Month]
order by [year],2

-- 产品明细、年月统计
select d1.dlmc,d2.ggmc,d3.czmc,[year],[Month],sum(crkz) as '销售量'
from facttccpck f,dimrq d,dimdl d1,dimgg d2,dimcz d3 
where f.rqid=d.rqid and f.dlid=d1.dlid and f.ggid=d2.ggid and f.czid=d3.czid
group by d1.dlmc,d2.ggmc,d3.czmc,[year],[Month]
order by d1.dlmc,d2.ggmc,d3.czmc,[year],[Month]

-- 产品大类、年月统计
select d1.dlmc,[year],[Month],sum(crkz) as '销售量'
from facttccpck f,dimrq d,dimdl d1
where f.rqid=d.rqid and f.dlid=d1.dlid
group by d1.dlmc,[year],[Month]
order by d1.dlmc,[year],[Month]
-- 产品规格、年月统计
select d1.ggmc,[year],[Month],sum(crkz) as '销售量'
from facttccpck f,dimrq d,dimgg d1
where f.rqid=d.rqid and f.ggid=d1.ggid
group by d1.ggmc,[year],[Month]
order by d1.ggmc,[year],[Month]
-- 产品材质、年月统计
select d1.czmc,[year],[Month],sum(crkz) as '销售量'
from facttccpck f,dimrq d,dimcz d1
where f.rqid=d.rqid and f.czid=d1.czid
group by d1.czmc,[year],[Month]
order by d1.czmc,[year],[Month]

-- 产品大类、年统计
select d1.dlmc,[year],sum(crkz) as '销售量'
from facttccpck f,dimrq d,dimdl d1
where f.rqid=d.rqid and f.dlid=d1.dlid
group by d1.dlmc,[year]
order by d1.dlmc,[year]
-- 产品规格、年月统计
select d1.ggmc,[year],sum(crkz) as '销售量'
from facttccpck f,dimrq d,dimgg d1
where f.rqid=d.rqid and f.ggid=d1.ggid
group by d1.ggmc,[year]
order by d1.ggmc,[year]
-- 产品材质、年月统计
select d1.czmc,[year],sum(crkz) as '销售量'
from facttccpck f,dimrq d,dimcz d1
where f.rqid=d.rqid and f.czid=d1.czid
group by d1.czmc,[year]
order by d1.czmc,[year]

-- 产品大类统计
select d1.dlmc,sum(crkz) as '销售量'
from facttccpck f,dimdl d1
where f.dlid=d1.dlid
group by d1.dlmc
order by d1.dlmc
-- 产品规格统计
select d1.ggmc,sum(crkz) as '销售量'
from facttccpck f,dimgg d1
where f.ggid=d1.ggid
group by d1.ggmc
order by d1.ggmc
-- 产品材质统计
select d1.czmc,sum(crkz) as '销售量'
from facttccpck f,dimcz d1
where f.czid=d1.czid
group by d1.czmc
order by d1.czmc



-- 统计信息永久组织存放于表中用于分析与挖掘。
-- 统计信息永久组织存放于表中用于分析与挖掘。
-- 统计信息永久组织存放于表中用于分析与挖掘。
-- 产品大类、年统计
select d1.dlmc,[year],sum(crkz) as 'crkz' 
into facttccpck_dl_year
from facttccpck f,dimrq d,dimdl d1
where f.rqid=d.rqid and f.dlid=d1.dlid
group by d1.dlmc,[year]
order by d1.dlmc,[year]
-- 产品规格、年月统计
select d1.ggmc,[year],sum(crkz) as 'crkz'
into facttccpck_gg_year
from facttccpck f,dimrq d,dimgg d1
where f.rqid=d.rqid and f.ggid=d1.ggid
group by d1.ggmc,[year]
order by d1.ggmc,[year]
-- 产品材质、年月统计
select d1.czmc,[year],sum(crkz) as 'crkz'
into facttccpck_cz_year
from facttccpck f,dimrq d,dimcz d1
where f.rqid=d.rqid and f.czid=d1.czid
group by d1.czmc,[year]
order by d1.czmc,[year]

-- 产品大类、年月统计
select d1.dlmc,[year],[Month],sum(crkz) as 'crkz'
into facttccpck_dl_year_month
from facttccpck f,dimrq d,dimdl d1
where f.rqid=d.rqid and f.dlid=d1.dlid
group by d1.dlmc,[year],[Month]
order by d1.dlmc,[year],[Month]
-- 产品规格、年月统计
select d1.ggmc,[year],[Month],sum(crkz) as 'crkz'
into facttccpck_gg_year_month
from facttccpck f,dimrq d,dimgg d1
where f.rqid=d.rqid and f.ggid=d1.ggid
group by d1.ggmc,[year],[Month]
order by d1.ggmc,[year],[Month]
-- 产品材质、年月统计
select d1.czmc,[year],[Month],sum(crkz) as 'crkz'
into facttccpck_cz_year_month
from facttccpck f,dimrq d,dimcz d1
where f.rqid=d.rqid and f.czid=d1.czid
group by d1.czmc,[year],[Month]
order by d1.czmc,[year],[Month]



-- 发货去向、产品大类、年月统计
select d2.fhqxmc,d1.dlmc,[year],[Month],sum(crkz) as 'crkz'
into facttccpck_fhqx_dl_year_month
from facttccpck f,dimrq d,dimdl d1,dimfhqx d2
where f.rqid=d.rqid and f.dlid=d1.dlid  and f.fhqxid=d2.fhqxid 
group by d2.fhqxmc,d1.dlmc,[year],[Month]
order by d2.fhqxmc,d1.dlmc,[year],[Month]


-- 发货去向、产品大类、年月统计22222222用于建立多维数据集更优
select d2.fhqxid,d1.dlid,d3.[year],d3.[Month],sum(crkz) as 'crkz'
into facttccpck_fhqx_dl_year_month2
from facttccpck f,dimrq d,dimdl d1,dimfhqx d2,dimmonth d3
where f.rqid=d.rqid and f.dlid=d1.dlid  and f.fhqxid=d2.fhqxid 
    and d.[year]=d3.[year] and d.[month]=d3.[month]
group by d2.fhqxid,d1.dlid,d3.[year],d3.[Month]
order by d2.fhqxid,d1.dlid,d3.[year],d3.[Month]

DELETE FROM facttccpck_fhqx_dl_year_month2  -- 清空
-- ADD ID 
alter table facttccpck_fhqx_dl_year_month2 add id int identity(1,1)
alter table facttccpck_fhqx_dl_year_month2 add primary key(id) 
-- 再次生成
INSERT into facttccpck_fhqx_dl_year_month2(fhqxid,dlid,[year],[Month],crkz) 
select d2.fhqxid,d1.dlid,d3.[year],d3.[Month],sum(crkz) as 'crkz'
from facttccpck f,dimrq d,dimdl d1,dimfhqx d2,dimmonth d3
where f.rqid=d.rqid and f.dlid=d1.dlid  and f.fhqxid=d2.fhqxid 
    and d.[year]=d3.[year] and d.[month]=d3.[month]
group by d2.fhqxid,d1.dlid,d3.[year],d3.[Month]
order by d2.fhqxid,d1.dlid,d3.[year],d3.[Month]

-- 查询每年哪个单位销量最多
SELECT     d.fhqxmc, f.year, SUM(f.crkz) AS crkz
FROM         facttccpck_fhqx_dl_year_month2 AS f INNER JOIN
                      dimFHQX AS d ON f.fhqxid = d.fhqxid
group by d.fhqxmc, f.year
ORDER BY year desc,crkz DESC