#description: --disable_warnings drop database if exists farm; create database farm; use farm; --disable_warnings # # ========================begin: schema && data(from tpcds)========================= # create table customer ( c_customer_sk integer not null, c_customer_id char(16) not null, c_current_cdemo_sk integer , c_current_hdemo_sk integer , c_current_addr_sk integer , c_first_shipto_date_sk integer , c_first_sales_date_sk integer , c_salutation char(10) , c_first_name char(20) , c_last_name char(30) , c_preferred_cust_flag char(1) , c_birth_day integer , c_birth_month integer , c_birth_year integer , c_birth_country varchar(20) , c_login char(13) , c_email_address char(50) , c_last_review_date char(10) , primary key (c_customer_sk) ); insert into customer values("1","AAAAAAAABAAAAAAA","980124","7135","32946","2452238","2452208","Mr.","Javier","Lewis","Y","9","12","1936","CHILE",NULL,"Javier.Lewis@VFAxlnZEvOx.org","2452508"); insert into customer values("2","AAAAAAAACAAAAAAA","819667","1461","31655","2452318","2452288","Dr.","Amy","Moses","Y","9","4","1966","TOGO",NULL,"Amy.Moses@Ovk9KjHH.com","2452318"); insert into customer values("3","AAAAAAAADAAAAAAA","1473522","6247","48572","2449130","2449100","Miss","Latisha","Hamilton","N","18","9","1979","NIUE",NULL,"Latisha.Hamilton@V.com","2452313"); insert into customer values("4","AAAAAAAAEAAAAAAA","1703214","3986","39558","2450030","2450000","Dr.","Michael","White","N","7","6","1983","MEXICO",NULL,"Michael.White@i.org","2452361"); insert into customer values("5","AAAAAAAAFAAAAAAA","953372","4470","36368","2449438","2449408","Sir","Robert","Moran","N","8","5","1956","FIJI",NULL,"Robert.Moran@Hh.edu","2452469"); insert into customer values("6","AAAAAAAAGAAAAAAA","213219","6374","27082","2451883","2451853","Ms.","Brunilda","Sharp","N","4","12","1925","SURINAME",NULL,"Brunilda.Sharp@T3pylZEUQjm.org","2452430"); insert into customer values("7","AAAAAAAAHAAAAAAA","68377","3219","44814","2451438","2451408","Ms.","Fonda","Wiles","Y","24","4","1985","GAMBIA",NULL,"Fonda.Wiles@S9KnyEtz9hv.org","2452360"); insert into customer values("8","AAAAAAAAIAAAAAAA","1215897","2471","16598","2449406","2449376","Sir","Ollie","Shipman","N","26","12","1938","KOREA, REPUBLIC OF",NULL,"Ollie.Shipman@be.org","2452334"); insert into customer values("9","AAAAAAAAJAAAAAAA","1168667","1404","49388","2452275","2452245","Sir","Karl","Gilbert","N","26","10","1966","MONTSERRAT",NULL,"Karl.Gilbert@Crg5KyP2IxX9C4d6.edu","2452454"); insert into customer values("10","AAAAAAAAKAAAAAAA","1207553","5143","19580","2451353","2451323","Ms.","Albert","Brunson","N","15","10","1973","JORDAN",NULL,"Albert.Brunson@62.com","2452641"); insert into customer values("11","AAAAAAAALAAAAAAA","1114415","6807","47999","2452288","2452258","Ms.","Betty","Williams","N","18","12","1963","BURKINA FASO",NULL,"Betty.Williams@xRtDqM1eLBVQNoYAJ.com","2452398"); insert into customer values("12","AAAAAAAAMAAAAAAA","502141","6577","47366","2451039","2451009","Ms.","Margaret","Farias","N","2","6","1956","TURKMENISTAN",NULL,"Margaret.Farias@cb.edu","2452634"); insert into customer values("13","AAAAAAAANAAAAAAA","1128748","2777","14006","2449658","2449628","Mrs.","Rosalinda","Grimes","N","1","3","1970","UKRAINE",NULL,"Rosalinda.Grimes@tC8pcU7Lt.edu","2452616"); insert into customer values("14","AAAAAAAAOAAAAAAA","929344","892","6440","2450318","2450288","Mr.","Jack","Wilcox","N","30","3","1937","SLOVENIA",NULL,"Jack.Wilcox@Y3Etqyv3.org","2452641"); insert into customer values("15","AAAAAAAAPAAAAAAA",NULL,"134","30469",NULL,"2449010","Ms.","Tonya",NULL,NULL,NULL,"12","1969",NULL,NULL,NULL,"2452376"); insert into customer values("16","AAAAAAAAABAAAAAA","1196373","3014","29302","2451346","2451316","Dr.","Margie","Browning","N","24","12","1933","PHILIPPINES",NULL,"Margie.Browning@LM674NrE2.org","2452573"); insert into customer values("17","AAAAAAAABBAAAAAA","707524","3876","2228","2451068","2451038","Dr.","Lee","Stovall","N","23","12","1972","PHILIPPINES",NULL,"Lee.Stovall@fqKC83UU0f.org","2452454"); insert into customer values("18","AAAAAAAACBAAAAAA","1361151","6580","18456","2450041","2450011","Sir","Brad","Lynch","Y","1","9","1950","URUGUAY",NULL,"Brad.Lynch@nAbai.edu","2452549"); insert into customer values("19","AAAAAAAADBAAAAAA","1161742","4238","45581","2449580","2449550","Dr.","Andre","Moore","N","20","10","1978","NICARAGUA",NULL,"Andre.Moore@cTZLGYi1ZJi.org","2452576"); insert into customer values("20","AAAAAAAAEBAAAAAA","1185612","89","38966","2450965","2450935","Mr.","Stanton","Dallas","Y","17","5","1976","SWITZERLAND",NULL,"Stanton.Dallas@DBXgl18FGo.edu","2452334"); insert into customer values("21","AAAAAAAAFBAAAAAA","442697","6538","25979","2451827","2451797","Miss","Naomi","Barnett","N","18","5","1956","BAHAMAS",NULL,"Naomi.Barnett@2T3V3OZOy4KBNAHsT.edu","2452433"); insert into customer values("22","AAAAAAAAGBAAAAAA","490494","4504","40467","2451465","2451435","Sir","Victor","Martinez","N","21","12","1991","CAYMAN ISLANDS",NULL,"Victor.Martinez@fC.edu","2452529"); insert into customer values("23","AAAAAAAAHBAAAAAA",NULL,"2107","686","2451012","2450982","Sir",NULL,"Garrison","N",NULL,"5",NULL,NULL,NULL,"Earl.Garrison@G3sM4P.com",NULL); insert into customer values("24","AAAAAAAAIBAAAAAA","17113","1102","14291","2450098","2450068","Dr.","Paul","Morris","Y","6","6","1951","GUINEA-BISSAU",NULL,"Paul.Morris@FMGalegqc3.com","2452499"); insert into customer values("25","AAAAAAAAJBAAAAAA","476176","3278","25933","2449246","2449216","Miss","Nancy","Mccormick","N","6","10","1973","BAHAMAS",NULL,"Nancy.Mccormick@DA26I9ZArLF9rxJ6Z.edu","2452425"); insert into customer values("26","AAAAAAAAKBAAAAAA","230278","1242","28206","2449816","2449786","Ms.","Monique","Baker","N","14","6","1991","MOROCCO",NULL,"Monique.Baker@9uEucNczY.org","2452356"); insert into customer values("27","AAAAAAAALBAAAAAA","111621","835","16096","2450611","2450581","Dr.","Shawn","Prather","N","7","3","1972","PUERTO RICO",NULL,"Shawn.Prather@8BusRYegn6.org","2452353"); insert into customer values("28","AAAAAAAAMBAAAAAA","889961","897","33480","2452615","2452585","Miss","Edith","Hernandez","Y","17","8","1959","C;ÔTE D'IVOIRE",NULL,"Edith.Hernandez@BNHL0k.com","2452631"); insert into customer values("29","AAAAAAAANBAAAAAA","1634269","1204","44139","2452216","2452186","Ms.","Margaret","Collins","Y","25","8","1973","NETHERLANDS ANTILLES",NULL,"Margaret.Collins@9obPr3UV.org","2452496"); insert into customer values("30","AAAAAAAAOBAAAAAA","84232","5869","45459","2449916","2449886","Dr.","Pamela","Luna","Y","4","7","1956","GUAM",NULL,"Pamela.Luna@QBGuhL36lnA.edu","2452410"); insert into customer values("31","AAAAAAAAPBAAAAAA","1275120","5724","7128","2452054","2452024","Dr.","William","Craig","N","13","6","1964","SPAIN",NULL,"William.Craig@prVDE1E8AHc.org","2452336"); insert into customer values("32","AAAAAAAAACAAAAAA","1369589","6163","48290","2449471","2449441","Dr.","Kenneth","Wood","N","2","8","1927","MOLDOVA, REPUBLIC OF",NULL,"Kenneth.Wood@RIA.edu","2452510"); insert into customer values("33","AAAAAAAABCAAAAAA","1680761","5823","32438","2451572","2451542","Mrs.","Donna","Betts","Y","25","3","1958","FRANCE",NULL,"Donna.Betts@YJ14k.edu","2452455"); insert into customer values("34","AAAAAAAACCAAAAAA",NULL,NULL,"37501","2452192","2452162",NULL,NULL,"Woods",NULL,NULL,NULL,NULL,NULL,NULL,"Brandon.Woods@hjKbf.edu","2452408"); insert into customer values("35","AAAAAAAADCAAAAAA","339036","2057","32231","2451937","2451907","Dr.","Marie","Peterson","N","19","6","1953","KIRIBATI",NULL,"Marie.Peterson@1zg9tydFHafA5.com","2452581"); insert into customer values("36","AAAAAAAAECAAAAAA","655414","2223","26585","2449222","2449192","Dr.","Anthony","Fisher","N","29","11","1968","KYRGYZSTAN",NULL,"Anthony.Fisher@jJrZfeDcz8P.com","2452470"); insert into customer values("37","AAAAAAAAFCAAAAAA","827972","2281","12943","2449848","2449818","Dr.","Dwight","Schneider","Y","9","1","1949","RUSSIAN FEDERATION",NULL,"Dwight.Schneider@koxO7zAysvOd.com","2452293"); insert into customer values("38","AAAAAAAAGCAAAAAA","766645","3879","36360","2450323","2450293","Mrs.","Ha","Carpenter","Y","30","3","1939","PARAGUAY",NULL,"Ha.Carpenter@XgcUt4svNz.com","2452351"); insert into customer values("39","AAAAAAAAHCAAAAAA","1617182","4013","22844","2452131","2452101","Sir","Neil","Cox","N","14","12","1951","ECUADOR",NULL,"Neil.Cox@FRuR2bFK.com","2452586"); insert into customer values("40","AAAAAAAAICAAAAAA","1795301",NULL,"21045","2451159",NULL,NULL,"Jacqueline","Hatfield",NULL,"26",NULL,"1942",NULL,NULL,NULL,NULL); insert into customer values("41","AAAAAAAAJCAAAAAA","941420","5068","18316","2449989","2449959","Miss","Maxine","Carlson","N","26","6","1939","ISLE OF MAN",NULL,"Maxine.Carlson@StyP5lAokmQ29QHYMLa.edu","2452464"); insert into customer values("42","AAAAAAAAKCAAAAAA","1867377","3327","11277","2449869","2449839","Dr.","Deborah","Burton","N","17","1","1950","COMOROS",NULL,"Deborah.Burton@xt.edu","2452498"); insert into customer values("43","AAAAAAAALCAAAAAA","385562","3676","43743","2451705","2451675","Mr.","William","Warner","Y","5","12","1965","GUAM",NULL,"William.Warner@zegnrzurU.org","2452537"); insert into customer values("44","AAAAAAAAMCAAAAAA","497758","5427","32296","2449750","2449720","Miss","Mabel","Richmond","Y","7","12","1932","CAMBODIA",NULL,"Mabel.Richmond@Tkla.edu","2452333"); insert into customer values("45","AAAAAAAANCAAAAAA","1036174","2399","19777","2451118","2451088",NULL,NULL,"Hammonds",NULL,"5",NULL,NULL,"NIGER",NULL,NULL,NULL); insert into customer values("46","AAAAAAAAOCAAAAAA","843672","4729","21386","2451857","2451827","Mrs.","Jane","Stephenson","Y","13","4","1990","TURKEY",NULL,"Jane.Stephenson@lq8ZQLAUMZhR.edu","2452301"); insert into customer values("47","AAAAAAAAPCAAAAAA","1634314","5368","38469","2449165","2449135","Mr.","Luis","Young","N","3","12","1982","BRUNEI DARUSSALAM",NULL,"Luis.Young@0DmV.edu","2452586"); insert into customer values("48","AAAAAAAAADAAAAAA","8817","311","38252","2449531","2449501","Ms.","Jill","Jackson","N","15","9","1961","LUXEMBOURG",NULL,"Jill.Jackson@n6I7SF.org","2452575"); insert into customer values("49","AAAAAAAABDAAAAAA","936800","1514","20581","2449364","2449334","Mr.","Steven","Venable","N","13","6","1961","NETHERLANDS ANTILLES",NULL,"Steven.Venable@0hA90vhfK7k9F4h.com","2452350"); insert into customer values("50","AAAAAAAACDAAAAAA","759177","72","32567","2449384","2449354","Mr.","Christopher","Eller","N","24","11","1965","AUSTRALIA",NULL,"Christopher.Eller@gV5Ua7HOmt.com","2452520"); # # ========================end: schema && data(from tpcds)========================= # select * from (select *, row_number() over(partition by nv order by c_customer_sk) as rn from (select c_birth_month, c_birth_year, c_customer_sk, c_first_name, group_concat(c_customer_sk) over w2, ntile(10) over w3 as nv from customer window w as (partition by c_birth_month), w2 as (w order by c_birth_year, c_customer_sk), w3 as (order by c_customer_sk) ) v ) v where rn = 1; # select c_birth_month, c_birth_year, c_customer_sk, c_first_name, #first_value(c_first_name) over (partition by c_birth_month order by c_birth_year, c_customer_sk), # last_value(c_first_name) over (partition by c_birth_month order by c_birth_year, c_customer_sk) , # nth_value(c_first_name, 1) over w2, # lead(c_first_name, 2) over w2, # lag(c_first_name, 2) over w2 # from customer # window w as (partition by c_birth_month), # w2 as (w order by c_birth_year, c_customer_sk); select c_birth_month, c_birth_year, c_customer_sk, c_first_name, count(1) over w, max(c_birth_year) over w, min(c_birth_year) over w, cast(avg(c_birth_year) over w as number(10, 4)), sum(c_birth_month) over w, row_number() over w2, rank() over w2, dense_rank() over w2, cast(percent_rank() over w2 as number(10, 4)), cast(cume_dist() over w2 as number(10, 4)), first_value(c_first_name) over w2, last_value(c_first_name) over w2, nth_value(c_first_name, 1) over w2, ntile(10) over w2, lead(c_first_name, 2) over w2, lag(c_first_name, 2) over w2 from customer window w as (partition by c_birth_month), w2 as (w order by c_birth_year, c_customer_sk); --disable_warnings drop database if exists farm; --disable_warnings