Sybase IQ Essay Learning (20151027)

BULK insert A from ' c:/cdr201301.txt '--BULK Insert Table
Sp_iqcolumn (' Tv_user ')--View data columns in a table

/*case. When condition selection */
Select DISTINCT case When Tv_vod.series_flag = 1 then tv_vod.series_id else tv_vod.program_id end item_id,
case if Tv_vod.series_flag = 1 then tv_vod.series_name else Tv_vod.program_name end Item_name,
Tv_castrolemap.castrole_type, tv_cast.cast_id, tv_cast.cast_name into Item_cast
From Tv_contentmapping, Tv_vod, Tv_castrolemap, Tv_cast
where tv_contentmapping.parent_id = tv_vod.program_id
and tv_contentmapping.element_id = tv_castrolemap.castrole_id
and tv_castrolemap.cast_id = tv_cast.cast_id
and Tv_contentmapping.element_type = 7

/* User order for RFM calculation */
(select L.r,max (L.R) as Gt,min (L.R) as Lt from
(Select S_userid,datediff (Dd,max (substring (s_maxdate,1,8)), ' 20140831 ') as R,sum (S_count)
As F,sum (S_fee) as M from Iac_fct_m201408_tmp Group by S_userid) L GROUP by L.R)
Select min (L.R), Max (L.R)-min (L.R), Min (L.F), Max (L.F)-min (l.f), Min (l.m), Max (L.M)-min (L.M) from
(Select S_userid,datediff (Dd,max (substring (s_maxdate,1,8)), ' 20140831 ') as R,sum (S_count)
As F,sum (S_fee) as M from iac_fct_m201408 Group by S_userid) L
Select L.s_userid,convert (Numeric (8,6),-avg (L.R)/30) as Rl,convert (numeric (8,6), (1-avg (L.F))/240) as F1,
Convert (numeric (8,6), (-avg (L.M))/2671) as M1 from
(Select S_userid,datediff (Dd,max (substring (s_maxdate,1,8)), ' 20140831 ') as R,sum (S_count)
As F,sum (S_fee) as M from iac_fct_m201408 Group by S_userid) L GROUP by L.s_userid

Select name from sysobjects where type= ' U '

Select Max (L.F), from
(Select S_userid,datediff (Dd,max (substring (s_maxdate,1,8)), ' 20140831 ') as R,sum (S_count)
As F,sum (S_fee) as M from Iac_fct_m201408_tmp Group by S_userid) L

Select L.s_userid,convert (Numeric (8,6), (1-avg (L.F))/41) as F1 from
(Select S_userid,datediff (Dd,max (substring (s_maxdate,1,8)), ' 20140831 ') as R,sum (S_count)
As F,sum (S_fee) as M from Iac_fct_m201408_tmp Group by S_userid) L GROUP by L.s_userid

Select Max (l.m), Min (l.m), Max (L.M)-min (L.M) from
(Select S_userid,datediff (Dd,max (substring (s_maxdate,1,8)), ' 20140831 ') as R,sum (S_count)
As F,sum (S_fee) as M from Iac_fct_m201408_tmp Group by S_userid) L

Select L.s_userid,convert (Numeric (8,6), (3-avg (L.M))/395) as M1 from
(Select S_userid,datediff (Dd,max (substring (s_maxdate,1,8)), ' 20140831 ') as R,sum (S_count)
As F,sum (S_fee) as M from Iac_fct_m201408_tmp Group by S_userid) L GROUP by L.s_userid

Select S.mdn, s.cdr_city_id, s.area_id, s.mdn_prov_id, T.s_cycle_date Is_find
from areaky00001 s left join
(select min (k.s_cycle_date) s_cycle_date, K.MDN, k.cdr_city_id
from areaky00001 k
where k.cdr_city_id! = K.mdn_ CITY_ID
Group by K.MDN, k.cdr_city_id) t
on s.s_cycle_date = t.s_cycle_date and S.mdn = T.mdn and s.cdr_city_id = t. cdr_city_idk.cdr_city_id

/* Number of people by province rank ratio */
Select M.cdr_city_id,m.city_name,m.f_rank,m.mdn_prov_id,m.cp_name,convert (char), f_count*100/f_total) | | ' % ' as F_rate
From (select Cdr_city_id,city_name,mdn_prov_id,cp_name,count (Distinct MDN) as F_count,rank () over (partition by cdr_ city_id ORDER BY f_count Desc) as F_rank from Tmp_lvj_quansheng,kyfx_cfg_city,kyfx_cfg_province
where CONVERT (int,city_id) =cdr_city_id and mdn_prov_id=cp_id
Group by cdr_city_id,city_name,cp_name,mdn_prov_id) M, (select Cdr_city_id,count (Distinct MDN) as f_total from Tmp_lvj_ Quansheng
Group by cdr_city_id) N where m.cdr_city_id=n.cdr_city_id

Sybase IQ Essay Learning (20151027)