tsql-2008.txt

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

-- 产品大类（dl）属性情况处理
select * from tccprck
select distinct dl from tccprck order by dl
select * from tccprck where dl is null order by dl -- 有大类空者？
select * from tccprck where dl='' order by dl      -- 有大类空者？
delete from tccprck where dl is null or dl=''      -- 删除大类空者
select * from tccprck WHERE DL=' 圆钢'
update tccprck set dl='圆钢' WHERE DL=' 圆钢'      -- ' 圆钢'改为'圆钢'
create table dimDL(dlid int identity(1,1) primary key,
                   dlmc varchar(6))                -- 创建大类编码表
select distinct dl from tccprck order by dl
insert into dimDL(DLMC) SELECT distinct dl from tccprck order by dl  -- 添加记录到大类编码表
SELECT * FROM dimDL

-- 产品规格（gg）属性情况处理
select distinct gg from tccprck order by gg
select * from tccprck where gg is null order by gg   -- 有规格空者？
delete from tccprck where gg is null                 -- 删除规格空者
create table dimGG(ggid int identity(1,1) primary key,
                   ggmc varchar(30))                 -- 创建规格编码表
select distinct gg from tccprck order by gg
insert into dimGG(GGMC) SELECT distinct gg from tccprck order by gg  -- 添加记录到规格编码表
SELECT * FROM dimGG

-- 产品材质（cz1）属性情况处理
select distinct cz1 from tccprck order by cz1
select * from tccprck where cz1 is null order by cz1   -- 有材质空者？
delete from tccprck where cz1 is null                  -- 删除材质空者
select * from tccprck where CZ1 LIKE '__16Mn'
UPDATE tccprck SET cz1='16Mn' where CZ1 LIKE '__16Mn'  -- '__16Mn'改为'16Mn'
create table dimCZ(czid int identity(1,1) primary key,
                   czmc varchar(10))                   -- 创建材质编码表
insert into dimCZ(czmc) SELECT distinct cz1 from tccprck order by cz1  -- 添加记录到材质编码表
SELECT * FROM dimCZ

-- 单位（dw）属性情况
select distinct dw from tccprck order by dw
select * from tccprck where dw is null order by dw
-- delete from tccprck where dw is null

-- 发货去向（fhqx）属性情况
select distinct fhqx from tccprck order by fhqx
select distinct fhqx from tccprck where fhqx=']厦门'
update tccprck set fhqx='厦门' where fhqx=']厦门'     -- ']厦门'改为'厦门'

select * from tccprck where fhqx is null order by fhqx
select * from tccprck where fhqx is not null order by fhqx

select distinct rq from tccprck order by rq   -- 查看日期（rq）属性情况


-- 发货去向字段的处理（把第一个字符换成X，起信息保护作用）
select * from tccprck where fhqx is null order by fhqx
select * from tccprck where fhqx is not null order by fhqx
select DISTINCT FHQX from tccprck where fhqx is not null order by fhqx
-- 对某个记录处理
select * from tccprck WHERE ID=66441
update tccprck set fhqx='X'+SUBSTRING(FHQX,2,DATALENGTH(fhqx)-1) 
WHERE ID=66441
-- 对所有fhqx非空，长度>=2的记录处理
update tccprck set fhqx='X'+SUBSTRING(FHQX,2,DATALENGTH(fhqx)-1) 
WHERE fhqx is not null AND DATALENGTH(fhqx)>=2
-- 查询确认
select DISTINCT FHQX,SUBSTRING(FHQX,1,1),
SUBSTRING(FHQX,2,DATALENGTH(fhqx)-1),
'X'+SUBSTRING(FHQX,2,DATALENGTH(fhqx)-1) from tccprck 
WHERE fhqx is not null AND DATALENGTH(fhqx)>=2

create table dimFHQX(fhqxid int identity(1,1) primary key,
                     fhqxmc varchar(50))            -- 创建发货去向编码表
-- 添加记录到发货去向编码表
insert into dimFHQX(fhqxmc) SELECT distinct fhqx from tccprck WHERE FHQX IS NOT NULL order by fhqx
SELECT * FROM dimFHQX

--  从tccprck生成事实表facttccprck
SELECT * into facttccprck FROM tccprck 

-- 创建时间维表
--CREATE TABLE [dbo].[DimTime](
--   [TimeKey] [int] IDENTITY(1,1) NOT NULL,
--   [FullDateAlternateKey] [datetime] NULL,
--   CONSTRAINT [PK_DimTime_TimeKey] PRIMARY KEY CLUSTERED ([TimeKey] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
--   CONSTRAINT [AK_DimTime_FullDateAlternateKey] UNIQUE NONCLUSTERED([FullDateAlternateKey] ASC )WITH (IGNORE_DUP_KEY = OFF) --      ON [PRIMARY]
--) ON [PRIMARY]

-- 创建时间维表DimRQ
CREATE TABLE [dbo].[DimRQ](
   [rqid] [int] IDENTITY(1,1) NOT NULL,
   [FullDateAlternateKey] [datetime] NULL,
   CONSTRAINT [PK_DimTime_TimeKey] PRIMARY KEY CLUSTERED(	[rqid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
   CONSTRAINT [AK_DimTime_FullDateAlternateKey] UNIQUE NONCLUSTERED([FullDateAlternateKey] ASC)WITH (IGNORE_DUP_KEY = OFF) ON      [PRIMARY]
) ON [PRIMARY]

GO
-- 添加记录到时间维表
insert into [dbo].[DimRQ]([FullDateAlternateKey]) select distinct rq from facttccprck order by rq

--alter table facttccprck add ttid int     --添加属性
--alter table facttccprck drop column ttid --删除属性

-- 在事实表facttccprck中添加dlid属性作外码
alter table facttccprck add dlid int
update facttccprck set dlid=(select dimdl.dlid from dimdl where facttccprck.dl=dimdl.dlmc) --对应替换外码值

-- 在事实表facttccprck中添加ggid属性作外码
alter table facttccprck add ggid int
update facttccprck set ggid=(select ggid from dimgg where facttccprck.gg=dimgg.ggmc)      --对应替换外码值

-- 在事实表facttccprck中添加czid属性作外码
alter table facttccprck add czid int
update facttccprck set czid=(select czid from dimcz where facttccprck.cz1=dimcz.czmc)     --对应替换外码值

-- 在事实表facttccprck中添加fhqxid属性作外码
alter table facttccprck add fhqxid int
update facttccprck set fhqxid=(select fhqxid from dimfhqx where facttccprck.fhqx=dimfhqx.fhqxmc)  --对应替换外码值

-- 在事实表facttccprck中添加rqid属性作外码
alter table facttccprck add rqid int
update facttccprck set rqid=(select rqid from dimtime where facttccprck.rq=dimtime.fulldatealternatekey) --对应替换外码值


--修改表DimRQ，添加其它时间维属性
alter table DimRQ add [DayNumberOfWeek] [tinyint] NULL;
alter table DimRQ add [Month] [tinyint] NULL;
alter table DimRQ add [Season] [tinyint] NULL;
alter table DimRQ add [Semester] [tinyint] NULL;
alter table DimRQ add [Year] [char](4) NULL;
--产生各列值
update dimrq set DayNumberOfWeek=DATEPART(dw, FullDateAlternateKey)  --对应替换星期几的值
update dimrq set [Month]=
     ( select substring(ny,5,2) from tccpny
       where dimrq.FullDateAlternateKey>=qsrq and 
             dimrq.FullDateAlternateKey<=zzrq 
      )                                                              --对应替换月份的值
update dimrq set Season=DATEPART(qq, FullDateAlternateKey)           --对应替换季节的值
update dimrq set [Year]=DATEPART(yyyy, FullDateAlternateKey)         --对应替换年份的值

--update dimrq set [Semester]=
--    CASE
--      WHEN dimrq.FullDateAlternateKey>=cast((cast(year--(dimrq.FullDateAlternateKey) as char(4))+'06-01') as datetime) THEN --         2
--      else 1
--    END

--对应替换半年的值
update dimrq set [Semester]=
    CASE
      WHEN convert(char(10),dimrq.FullDateAlternateKey,120)>=cast(year(dimrq.FullDateAlternateKey) as char(4))+'-07-01' THEN 2
      else 1
    END
或
update dimrq set [Semester]=
    CASE
      WHEN [Month]>6 THEN 2
      else 1
    END



-- 产生产品出库表
select * into facttccpck from facttccprck where crkz<0
-- 把数量改为正值
update facttccpck set crkz= -crkz
-- 删除不必要的列
alter table facttccpck drop column dl      -- 冗余
alter table facttccpck drop column gg      -- 冗余
alter table facttccpck drop column cz1     -- 冗余
alter table facttccpck drop column fhqx    -- 冗余
alter table facttccpck drop column rq      -- 冗余
alter table facttccpck drop column clbj  -- 无价值 处理标记
alter table facttccpck drop column jbr   -- 无价值 经办人
alter table facttccpck drop column dw    -- 无价值 单位
alter table facttccpck drop column dj    -- 无价值 单价
-- 添加外码 
ALTER TABLE [dbo].[facttccpck]  WITH CHECK ADD  CONSTRAINT [FK_facttccpck_dimCZ] FOREIGN KEY([czid])
REFERENCES [dbo].[dimCZ] ([czid])
GO
ALTER TABLE [dbo].[facttccpck]  WITH CHECK ADD  CONSTRAINT [FK_facttccpck_dimDL] FOREIGN KEY([dlid])
REFERENCES [dbo].[dimDL] ([dlid])
GO
ALTER TABLE [dbo].[facttccpck]  WITH CHECK ADD  CONSTRAINT [FK_facttccpck_dimFHQX] FOREIGN KEY([fhqxid])
REFERENCES [dbo].[dimFHQX] ([fhqxid])
GO
ALTER TABLE [dbo].[facttccpck]  WITH CHECK ADD  CONSTRAINT [FK_facttccpck_dimGG] FOREIGN KEY([ggid])
REFERENCES [dbo].[dimGG] ([ggid])
GO
ALTER TABLE [dbo].[facttccpck]  WITH CHECK ADD  CONSTRAINT [FK_facttccpck_DimRQ] FOREIGN KEY([rqid])
REFERENCES [dbo].[DimRQ] ([rqid])



-- 产生产品入库表
select * into facttccprk from facttccprck where crkz>0
-- 删除不必要的列
alter table facttccprk drop column dl      -- 冗余
alter table facttccprk drop column gg      -- 冗余
alter table facttccprk drop column cz1     -- 冗余
alter table facttccprk drop column fhqx    -- 冗余
alter table facttccprk drop column rq      -- 冗余
alter table facttccprk drop column clbj  -- 无价值 处理标记
alter table facttccprk drop column jbr   -- 无价值 经办人
alter table facttccprk drop column dw    -- 无价值 单位
alter table facttccprk drop column dj    -- 无价值 单价
-- 添加外码 
ALTER TABLE [dbo].[facttccprk]  WITH CHECK ADD  CONSTRAINT [FK_facttccprk_dimCZ] FOREIGN KEY([czid])
REFERENCES [dbo].[dimCZ] ([czid])
GO
ALTER TABLE [dbo].[facttccprk]  WITH CHECK ADD  CONSTRAINT [FK_facttccprk_dimDL] FOREIGN KEY([dlid])
REFERENCES [dbo].[dimDL] ([dlid])
GO
ALTER TABLE [dbo].[facttccprk]  WITH CHECK ADD  CONSTRAINT [FK_facttccprk_dimFHQX] FOREIGN KEY([fhqxid])
REFERENCES [dbo].[dimFHQX] ([fhqxid])