oceanbase

1
#owner: jiangxiu.wt
2
#owner group: sql1
3
#description:
4

5
--disable_warnings
6
drop database if exists farm;
7
create database farm;
8
use farm;
9
--disable_warnings
10
#--result_format 1
11

12
#
13
# ========================begin: schema && data(from tpcds)=========================
14
#
15
create table customer
16
(
17
 c_customer_sk             integer               not null,
18
 c_customer_id             char(16)              not null,
19
 c_current_cdemo_sk        integer                       ,
20
 c_current_hdemo_sk        integer                       ,
21
 c_current_addr_sk         integer                       ,
22
 c_first_shipto_date_sk    integer                       ,
23
 c_first_sales_date_sk     integer                       ,
24
 c_salutation              char(10)                      ,
25
 c_first_name              char(20)                      ,
26
 c_last_name               char(30)                      ,
27
 c_preferred_cust_flag     char(1)                       ,
28
 c_birth_day               integer                       ,
29
 c_birth_month             integer                       ,
30
 c_birth_year              integer                       ,
31
 c_birth_country           varchar(20)                   ,
32
 c_login                   char(13)                      ,
33
 c_email_address           char(50)                      ,
34
 c_last_review_date        char(10)                      ,
35
 primary key (c_customer_sk)
36
 );
37
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");
38
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");
39
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");
40
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");
41
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");
42
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");
43
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");
44
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");
45
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");
46
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");
47
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");
48
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");
49
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");
50
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");
51
insert into customer values("15","AAAAAAAAPAAAAAAA",NULL,"134","30469",NULL,"2449010","Ms.","Tonya",NULL,NULL,NULL,"12","1969",NULL,NULL,NULL,"2452376");
52
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");
53
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");
54
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");
55
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");
56
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");
57
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");
58
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");
59
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);
60
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");
61
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");
62
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");
63
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");
64
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");
65
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");
66
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");
67
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");
68
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");
69
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");
70
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");
71
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");
72
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");
73
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");
74
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");
75
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");
76
insert into customer values("40","AAAAAAAAICAAAAAA","1795301",NULL,"21045","2451159",NULL,NULL,"Jacqueline","Hatfield",NULL,"26",NULL,"1942",NULL,NULL,NULL,NULL);
77
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");
78
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");
79
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");
80
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");
81
insert into customer values("45","AAAAAAAANCAAAAAA","1036174","2399","19777","2451118","2451088",NULL,NULL,"Hammonds",NULL,"5",NULL,NULL,"NIGER",NULL,NULL,NULL);
82
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");
83
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");
84
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");
85
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");
86
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");
87
#
88
# ========================end: schema && data(from tpcds)=========================
89
#
90

91
select * from
92
         (select *,
93
                row_number() over(partition by nv order by c_customer_sk) as rn
94
                from
95
                (select c_birth_month, c_birth_year, c_customer_sk, c_first_name,
96
                        group_concat(c_customer_sk) over w2,
97
                        ntile(10) over w3 as nv
98
                        from customer
99
                        window w as (partition by c_birth_month),
100
                        w2 as (w order by c_birth_year, c_customer_sk),
101
                        w3 as (order by c_customer_sk)
102
                ) v
103
         ) v
104
         where rn = 1;
105

106
# mysql 8.0 window_function bug:
107
# aone: 
108
#                select  c_birth_month, c_birth_year, c_customer_sk, c_first_name,
109
#first_value(c_first_name) over (partition by c_birth_month order by c_birth_year, c_customer_sk),
110
#                        last_value(c_first_name) over (partition by c_birth_month order by c_birth_year, c_customer_sk) ,
111
#                        nth_value(c_first_name, 1) over w2,
112
#                        lead(c_first_name, 2) over w2,
113
#                        lag(c_first_name, 2) over w2
114
#                        from customer
115
#                        window w as (partition by c_birth_month),
116
#                        w2 as (w order by c_birth_year, c_customer_sk);
117

118
select c_birth_month, c_birth_year, c_customer_sk, c_first_name,
119
       count(1) over w,
120
       max(c_birth_year) over w,
121
       min(c_birth_year) over w,
122
       cast(avg(c_birth_year) over w as number(10, 4)),
123
       sum(c_birth_month) over w,
124
       row_number() over w2,
125
       rank() over w2,
126
       dense_rank() over w2,
127
       cast(percent_rank() over w2 as number(10, 4)),
128
       cast(cume_dist() over w2 as number(10, 4)),
129
       first_value(c_first_name) over w2,
130
       last_value(c_first_name) over w2,
131
       nth_value(c_first_name, 1) over w2,
132
       ntile(10) over w2,
133
       lead(c_first_name, 2) over w2,
134
       lag(c_first_name, 2) over w2
135
       from customer
136
       window w as (partition by c_birth_month),
137
       w2 as (w order by c_birth_year, c_customer_sk);
138

139
--disable_warnings
140
drop database if exists farm;
141
--disable_warnings
142

143