testdatasql语句.txt

基于决策树和贝叶斯的预测分析器

CREATE TABLE testdata1
(

	age      INT,
	workclass VARCHAR(30),
	fnlwgt   INT,
	education VARCHAR(20),
	education_num INT,
	marital_status VARCHAR(30),
	occupation  VARCHAR(30),
	relationship VARCHAR(30),
	race VARCHAR(20),
	sex VARCHAR(10),
	capital_gain INT,
	capital_loss INT,
	hours_per_week INT,
	native_country  VARCHAR(30),
	makeover VARCHAR (10)
)


BULK INSERT testdata1
FROM 'adult.test.txt'
WITH
(
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '.\n'
)


DROP TABLE testdata2
CREATE TABLE testdata2
(

	age      INT,
	workclass VARCHAR(30),
	fnlwgt   INT,
	education VARCHAR(20),
	education_num INT,
	marital_status VARCHAR(30),
	occupation  VARCHAR(30),
	relationship VARCHAR(30),
	race VARCHAR(20),
	sex VARCHAR(10),
	capital_gain INT,
	capital_loss INT,
	hours_per_week INT,
	native_country  VARCHAR(30),
	makeover50k VARCHAR (10)
)


BULK INSERT testdata2
FROM 'adult.test.txt'

WITH
(
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '.\n'
)

ALTER TABLE testdata2 DROP COLUMN workclass
ALTER TABLE testdata2 DROP COLUMN fnlwgt
ALTER TABLE testdata2 DROP COLUMN marital_status
ALTER TABLE testdata2 DROP COLUMN relationship
ALTER TABLE testdata2 DROP COLUMN race
ALTER TABLE testdata2 DROP COLUMN capital_gain
ALTER TABLE testdata2 DROP COLUMN capital_loss
ALTER TABLE testdata2 DROP COLUMN hours_per_week

//ageType分6类：<=20为year0_20,21-30为year21_30,31-40为year31_40,41-50为year41_50,51-60为year51_60,>=61为yearover60

UPDATE testdata2
SET age=0
WHERE age<=20

UPDATE testdata2
SET age=1
WHERE age>20 AND age<=30

UPDATE testdata2
SET age=2
WHERE age>30 AND age<=40

UPDATE testdata2
SET age=3
WHERE age>40 AND age<=50

UPDATE testdata2
SET age=4
WHERE age>50 AND age<=60

UPDATE testdata2
SET age=5
WHERE age>60 

ALTER TABLE testdata2 ALTER COLUMN age VARCHAR(15)

UPDATE testdata2
SET age = 'year0_20'
WHERE age = '0'

UPDATE testdata2
SET age= 'year21_30'
WHERE age = '1'

UPDATE testdata2
SET age = 'year31_40'
WHERE age = '2'

UPDATE testdata2
SET age = 'year31_40'
WHERE age = '3'

UPDATE testdata2
SET age = 'year41_50'
WHERE age = '4'

UPDATE testdata2
SET age = 'yearover60'
WHERE age = '5'

//原有education_numType: 1-16  ,分成6级：   edu1_3,edu4_6,edu7_9,edu10_12,edu13_14,edu15_16

UPDATE testdata2
SET education = 'edu1_3'
WHERE education_num >= 0 AND  education_num <= 3

UPDATE testdata2
SET education = 'edu4_6'
WHERE education_num >= 4 AND  education_num <= 6

UPDATE testdata2
SET education = 'edu7_9'
WHERE education_num >= 7 AND  education_num <= 9

UPDATE testdata2
SET education = 'edu10_12'
WHERE education_num >= 10 AND  education_num <= 12

UPDATE testdata2
SET education = 'edu13_14'
WHERE education_num >= 13 AND  education_num <= 14

UPDATE testdata2
SET education = 'edu15_16'
WHERE education_num >= 15 AND  education_num <= 16

ALTER TABLE testdata2 DROP COLUMN education_num


//occupationType共14类：Tech-support, Craft-repair, Other-service, Sales, Exec-managerial, Prof-specialty,
//                   Handlers-cleaners, Machine-op-inspct, Adm-clerical, Farming-fishing, Transport-moving, 
//                 Handlers-cleaners, Machine-op-inspct, Adm-clerical, Farming-fishing, Transport-moving
//                   Priv-house-serv, Protective-serv, Armed-Forces
//occupationType共14类(将原"-"改为"_"便于编程处理，并去掉前面的空格)：Tech_support, Craft_repair, Other_service, Sales, //Exec_managerial, Prof_specialty, Handlers_cleaners, Machine_op_inspct, Adm_clerical, Farming_fishing, Transport_moving, 
//Priv_house_serv, Protective_serv, Armed_Forces

UPDATE testdata2
SET occupation = 'Tech_support'
WHERE occupation =' Tech-support' 

UPDATE testdata2
SET occupation = 'Craft_repair'
WHERE occupation =' Craft-repair' 

UPDATE testdata2
SET occupation = 'Other_service'
WHERE occupation =' Other-service' 

UPDATE testdata2
SET occupation = 'Exec_managerial'
WHERE occupation =' Exec-managerial' 

UPDATE testdata2
SET occupation = 'Prof_specialty'
WHERE occupation =' Prof-specialty' 

UPDATE testdata2
SET occupation = 'Handlers_cleaners'
WHERE occupation =' Handlers-cleaners' 

UPDATE testdata2
SET occupation = 'Machine_op_inspct'
WHERE occupation =' Machine-op-inspct' 

UPDATE testdata2
SET occupation = 'Adm_clerical'
WHERE occupation =' Adm-clerical' 

UPDATE testdata2
SET occupation = 'Farming_fishing'
WHERE occupation =' Farming-fishing' 
UPDATE testdata2
SET occupation = 'Transport_moving'
WHERE occupation =' Transport-moving' 

UPDATE testdata2
SET occupation = 'Priv_house_serv'
WHERE occupation =' Priv-house-serv' 

UPDATE testdata2
SET occupation = 'Protective_serv'
WHERE occupation =' Protective-serv' 

UPDATE testdata2
SET occupation = 'Armed_Forces'
WHERE occupation =' Armed-Forces' 

UPDATE testdata2
SET occupation = 'Other_service'
WHERE occupation =' ?'

/*原有native-countryType：United-States, Cambodia, England, Puerto-Rico, Canada, Germany, 
          Outlying-US(Guam-USVI-etc), India, Japan, Greece, South, China, Cuba, Iran, Honduras, 
          Philippines, Italy, Poland, Jamaica, Vietnam, Mexico, Portugal, Ireland, France, 
          Dominican-Republic, Laos, Ecuador, Taiwan, Haiti, Columbia, Hungary, Guatemala, Nicaragua, 
          Scotland, Thailand, Yugoslavia, El-Salvador, Trinadad&Tobago, Peru, Hong, Holand-Netherlands}
               native_country分成5类developNO1,developNO2,developNO3,developNO4,developNO5后：
         developNO1:(<0.1)       Outlying-US(Guam-USVI-etc),Vietnam,Mexico,Dominican-Republic,Laos,Haiti,Hungary,
				Guatemala,Nicaragua,Scotland,El-Salvador,Trinadad&Tobago,Holand-Netherlands	
											
         developNO2:(>=0.1 <0.2)  Puerto-Rico,South,China,Cuba,Poland,Jamaica,Portugal,Ireland,Ecuador,Peru,?

         developNO3:(>=0.2 <0.3)  Honduras,France,Columbia,United-States,England,Germany,Greece,Philippines,Thailand,                                  Yugoslavia
         developNO4:(>=0.3 <0.4)  India.Japan.
         developNO5:(>=0.4 )      Cambodia,Canada,Iran,Italy,Taiwan,Hong 

*/
UPDATE testdata2
SET native_country = 'developNO1'
WHERE native_country = ' Outlying-US(Guam-USVI-etc)' OR native_country = ' Vietnam' 
   OR native_country = ' Mexico' OR native_country = ' Dominican-Republic' 
   OR native_country = ' Laos' OR native_country = ' Haiti' 
   OR native_country = ' Hungary' OR native_country = ' Guatemala' 
   OR native_country = ' Nicaragua' OR native_country = ' Scotland' 
   OR native_country = ' El-Salvador' OR native_country = ' Trinadad&Tobago' 
   OR native_country = ' Holand-Netherlands'

UPDATE testdata2
SET native_country = 'developNO2'
WHERE native_country = ' Puerto-Rico' OR native_country = ' South' 
   OR native_country = ' China' OR native_country = ' Cuba' 
   OR native_country = ' Poland' OR native_country = ' Jamaica' 
   OR native_country = ' Portugal' OR native_country = ' Ireland' 
   OR native_country = ' Ecuador' OR native_country = ' Peru' 
   OR native_country = ' ?' 

UPDATE testdata2
SET native_country = 'developNO3'
WHERE native_country = ' Honduras' OR native_country = ' France' 
   OR native_country = ' Columbia' OR native_country = ' United-States' 
   OR native_country = ' England' OR native_country = ' Germany' 
   OR native_country = ' Greece' OR native_country = ' Philippines' 
   OR native_country = ' Thailand' OR native_country = ' Yugoslavia' 
        UPDATE testdata2
SET native_country = 'developNO4'
WHERE native_country = ' India' OR native_country = ' Japan'

UPDATE testdata2
SET native_country = 'developNO5'
WHERE native_country = ' Cambodia' OR native_country = ' Canada' 
   OR native_country = ' Iran' OR native_country = ' Italy' 
   OR native_country = ' Taiwan' OR native_country = ' Hong'                           




UPDATE testdata2
SET makeover50k = 'yes'
WHERE makeover50k = ' >50K'

UPDATE testdata2
SET makeover50k = 'no'
WHERE makeover50k = ' <=50K'



SELECT *
FROM testdata2