oceanbase

1
drop database if exists createsql;
2
create database createsql;
3
use createsql;
4
CREATE TABLE IF NOT EXISTS rpt_chenfeng_custid_shishi_000(
5
thedate datetime(6),
6
puttype int,
7
sourcetype int,
8
clickprice int,
9
click int,
10
custid int,
11
impression int,
12
PRIMARY KEY (custid,thedate,puttype,sourcetype)
13
) COMPRESSION='lz4_1.0' ;
14
CREATE TABLE IF NOT EXISTS rpt_solar_feed_bidword_ob_000(
15
bidwordid int,
16
searchtype int,
17
thedate datetime(6),
18
adgroupid int,
19
freeclick int,
20
directtransactionshipping int,
21
matchscope int,
22
memberid int,
23
feedid int,
24
favshoptotal int,
25
cost int,
26
campaignid int,
27
favitemtotal int,
28
indirecttransaction int,
29
gmt_created datetime default now(),
30
gmt_modified datetime default now(),
31
directtransaction int,
32
indirecttransactionshipping int,
33
network int,
34
invalidclick int,
35
bidwordstr varchar(300),
36
custid int,
37
impression int,
38
itemid int,
39
click int,
40
ranksum int,
41
PRIMARY KEY (custid,thedate,campaignid,adgroupid,bidwordid,feedid,network,matchscope)
42
) COMPRESSION='lz4_1.0' ;
43
CREATE TABLE IF NOT EXISTS rpt_chenfeng_campaignid_lixian_000(
44
thedate datetime(6),
45
sourcetype int,
46
custid int,
47
impression int,
48
puttype int,
49
campaignid int,
50
clickprice int,
51
click int,
52
PRIMARY KEY (custid,thedate,campaignid,puttype,sourcetype)
53
) COMPRESSION='lz4_1.0' ;
54
CREATE TABLE IF NOT EXISTS rpt_bpp4p_bidword_ob_000(
55
click int,
56
bidwordstr varchar(300),
57
custid int,
58
onefavshoptotal int,
59
directtransaction int,
60
invalidclick int,
61
network int,
62
campaignid int,
63
cost int,
64
favitemtotal int,
65
adgroupid int,
66
memberid int,
67
sevenindirecttransactionshipping int,
68
fifteenfavitemtotal int,
69
thedate datetime(6),
70
fifteenindirecttransactionshipping int,
71
isshop int,
72
fifteenfavshoptotal int,
73
fifteendirecttransactionshipping int,
74
threeindirecttransactionshipping int,
75
ranksum int,
76
sevenfavitemtotal int,
77
impression int,
78
threefavshoptotal int,
79
sevenindirecttransaction int,
80
indirecttransactionshipping int,
81
onedirecttransactionshipping int,
82
onefavitemtotal int,
83
indirecttransaction int,
84
threeindirecttransaction int,
85
sevendirecttransactionshipping int,
86
sevendirecttransaction int,
87
sevenfavshoptotal int,
88
fifteenindirecttransaction int,
89
favshoptotal int,
90
onedirecttransaction int,
91
oneindirecttransactionshipping int,
92
matchscope int,
93
directtransactionshipping int,
94
threefavitemtotal int,
95
fifteendirecttransaction int,
96
threedirecttransactionshipping int,
97
oneindirecttransaction int,
98
threedirecttransaction int,
99
bidwordid int,
100
searchtype int,
101
PRIMARY KEY (custid,thedate,campaignid,adgroupid,bidwordid,network,matchscope)
102
) COMPRESSION='lz4_1.0' ;
103
CREATE TABLE IF NOT EXISTS rpt_bpp4p_adgroupplace_name_000(
104
placeid int,
105
placename varchar(100),
106
thedate datetime(6),
107
PRIMARY KEY (placeid)
108
) COMPRESSION='lz4_1.0' ;
109
CREATE TABLE IF NOT EXISTS rpt_chenfeng_custid_shishi_accumulative_000(
110
custid int,
111
impression int,
112
sourcetype int,
113
puttype int,
114
thedate datetime(6),
115
clickprice int,
116
click int,
117
PRIMARY KEY (custid,thedate,puttype,sourcetype)
118
) COMPRESSION='lz4_1.0' ;
119
CREATE TABLE IF NOT EXISTS rpt_bpp4p_querysession_ob_000(
120
relword varchar(3000),
121
originalword varchar(300),
122
PRIMARY KEY (originalword)
123
) COMPRESSION='lz4_1.0' ;
124
CREATE TABLE IF NOT EXISTS rpt_bpp4p_area_ob_000(
125
onedirecttransaction int,
126
favshoptotal int,
127
threefavitemtotal int,
128
oneindirecttransactionshipping int,
129
directtransactionshipping int,
130
provinceid int,
131
threedirecttransactionshipping int,
132
fifteendirecttransaction int,
133
oneindirecttransaction int,
134
threedirecttransaction int,
135
ext2 int,
136
threeindirecttransactionshipping int,
137
threefavshoptotal int,
138
impression int,
139
sevenfavitemtotal int,
140
ext1 int,
141
onedirecttransactionshipping int,
142
indirecttransactionshipping int,
143
onefavitemtotal int,
144
sevenindirecttransaction int,
145
sevenfavshoptotal int,
146
fifteenindirecttransaction int,
147
sevendirecttransaction int,
148
cityid int,
149
sevendirecttransactionshipping int,
150
threeindirecttransaction int,
151
indirecttransaction int,
152
favitemtotal int,
153
productid int,
154
campaignid int,
155
cost int,
156
sevenindirecttransactionshipping int,
157
memberid int,
158
fifteenfavshoptotal int,
159
fifteenfavitemtotal int,
160
fifteenindirecttransactionshipping int,
161
thedate datetime(6),
162
fifteendirecttransactionshipping int,
163
click int,
164
traffictype int,
165
custid int,
166
invalidclick int,
167
productline int,
168
directtransaction int,
169
onefavshoptotal int,
170
mechanism int,
171
PRIMARY KEY (custid,thedate,campaignid,traffictype,mechanism,provinceid,cityid,productline,productid,ext1,ext2)
172
) COMPRESSION='lz4_1.0' ;
173
CREATE TABLE IF NOT EXISTS rpt_bpp4p_insight_bidword_area_000(
174
indirecttransaction int,
175
mechanism int,
176
competition int,
177
area int,
178
indirecttransactionshipping int,
179
network int,
180
ext1 int,
181
directtransaction int,
182
bidwordstr varchar(300),
183
impression int,
184
click int,
185
ext2 int,
186
thedate datetime(6),
187
directtransactionshipping int,
188
bidwordhash int,
189
cost int,
190
favshoptotal int,
191
favitemtotal int,
192
PRIMARY KEY (bidwordhash,thedate,area,network,mechanism,ext1,ext2,bidwordstr)
193
) COMPRESSION='lz4_1.0' ;
194
CREATE TABLE IF NOT EXISTS rpt_solar_feed_name_000(
195
gmt_created datetime default now(),
196
gmt_modified datetime default now(),
197
thedate datetime(6),
198
linkurl varchar(1024),
199
adgroupid int,
200
custid int,
201
title varchar(1024),
202
memberid int,
203
imgurl varchar(1024),
204
feedid int,
205
campaignid int,
206
PRIMARY KEY (custid,thedate,campaignid,adgroupid,feedid)
207
) COMPRESSION='lz4_1.0' ;
208
CREATE TABLE IF NOT EXISTS rpt_chenfeng_custid_lixian_accumulative_000(
209
clickprice int,
210
click int,
211
sourcetype int,
212
puttype int,
213
thedate datetime(6),
214
impression int,
215
custid int,
216
PRIMARY KEY (custid,thedate,puttype,sourcetype)
217
) COMPRESSION='lz4_1.0' ;
218
CREATE TABLE IF NOT EXISTS rpt_bpp4p_creative_name_000(
219
is_shop int,
220
thedate datetime(6),
221
linkurl varchar(1024),
222
adgroupid int,
223
title varchar(1024),
224
custid int,
225
creativeid int,
226
memberid int,
227
imgurl varchar(1024),
228
campaignid int,
229
PRIMARY KEY (custid,thedate,campaignid,adgroupid,creativeid)
230
) COMPRESSION='lz4_1.0' ;
231
CREATE TABLE IF NOT EXISTS rpt_bpp4p_demographic_name_000(
232
name varchar(100),
233
demographicid int,
234
thedate datetime(6),
235
PRIMARY KEY (demographicid)
236
) COMPRESSION='lz4_1.0' ;
237
CREATE TABLE IF NOT EXISTS rpt_bpp4p_demographic_name_star_000(
238
thedate datetime(6),
239
demographicid int,
240
name varchar(100),
241
PRIMARY KEY (demographicid)
242
) COMPRESSION='lz4_1.0' ;
243
CREATE TABLE IF NOT EXISTS rpt_solar_item_ob_000(
244
indirecttransactionshipping int,
245
ext1 int,
246
onedirecttransactionshipping int,
247
onefavitemtotal int,
248
sevenindirecttransaction int,
249
fifteenindirecttransaction int,
250
sevenfavshoptotal int,
251
productlineid int,
252
sevendirecttransaction int,
253
sevendirecttransactionshipping int,
254
threeindirecttransaction int,
255
indirecttransaction int,
256
ranksum int,
257
ext2 int,
258
threeindirecttransactionshipping int,
259
threefavshoptotal int,
260
impression int,
261
sevenfavitemtotal int,
262
creativeid int,
263
threedirecttransactionshipping int,
264
fifteendirecttransaction int,
265
searchtype int,
266
rptext2 int,
267
threedirecttransaction int,
268
oneindirecttransaction int,
269
onedirecttransaction int,
270
favshoptotal int,
271
entityid int,
272
creativepv int,
273
threefavitemtotal int,
274
oneindirecttransactionshipping int,
275
directtransactionshipping int,
276
invalidclick int,
277
directtransaction int,
278
onefavshoptotal int,
279
rptext1 int,
280
click int,
281
traffictype int,
282
custid int,
283
entitytype int,
284
fifteenfavshoptotal int,
285
thedate datetime(6),
286
fifteenfavitemtotal int,
287
fifteenindirecttransactionshipping int,
288
fifteendirecttransactionshipping int,
289
favitemtotal int,
290
productid int,
291
cost int,
292
areaid int,
293
campaignid int,
294
feedid int,
295
sevenindirecttransactionshipping int,
296
memberid int,
297
adgroupid int,
298
PRIMARY KEY (memberid,thedate,productlineid,campaignid,adgroupid,creativeid,areaid,entityid,entitytype,traffictype,searchtype,productid,ext1,ext2)
299
) COMPRESSION='lz4_1.0' ;
300
CREATE TABLE IF NOT EXISTS rpt_p4p_bidword_normtoorigin_000(
301
normalword varchar(300),
302
originalword varchar(300),
303
PRIMARY KEY (normalword)
304
) COMPRESSION='lz4_1.0' ;
305
CREATE TABLE IF NOT EXISTS rpt_chenfeng_campaignid_lixian_accumulative_000(
306
click int,
307
clickprice int,
308
puttype int,
309
campaignid int,
310
impression int,
311
custid int,
312
thedate datetime(6),
313
sourcetype int,
314
PRIMARY KEY (custid,thedate,campaignid,puttype,sourcetype)
315
) COMPRESSION='lz4_1.0' ;
316
CREATE TABLE IF NOT EXISTS rpt_chenfeng_custid_lixian_000(
317
custid int,
318
impression int,
319
sourcetype int,
320
puttype int,
321
thedate datetime(6),
322
click int,
323
clickprice int,
324
PRIMARY KEY (custid,thedate,puttype,sourcetype)
325
) COMPRESSION='lz4_1.0' ;
326
CREATE TABLE IF NOT EXISTS rpt_chenfeng_campaignid_shishi_000(
327
thedate datetime(6),
328
sourcetype int,
329
custid int,
330
impression int,
331
campaignid int,
332
puttype int,
333
click int,
334
clickprice int,
335
PRIMARY KEY (custid,thedate,campaignid,puttype,sourcetype)
336
) COMPRESSION='lz4_1.0' ;
337
CREATE TABLE IF NOT EXISTS rpt_chenfeng_campaignid_shishi_accumulative_000(
338
clickprice int,
339
click int,
340
puttype int,
341
campaignid int,
342
impression int,
343
custid int,
344
sourcetype int,
345
thedate datetime(6),
346
PRIMARY KEY (custid,thedate,campaignid,puttype,sourcetype)
347
) COMPRESSION='lz4_1.0' ;
348
CREATE TABLE IF NOT EXISTS rpt_bpp4p_custom_ob_000(
349
oneindirecttransaction int,
350
threedirecttransaction int,
351
fifteendirecttransaction int,
352
threedirecttransactionshipping int,
353
directtransactionshipping int,
354
oneindirecttransactionshipping int,
355
threefavitemtotal int,
356
favshoptotal int,
357
onedirecttransaction int,
358
indirecttransaction int,
359
threeindirecttransaction int,
360
sevendirecttransactionshipping int,
361
sevendirecttransaction int,
362
sevenfavshoptotal int,
363
fifteenindirecttransaction int,
364
sevenindirecttransaction int,
365
onedirecttransactionshipping int,
366
indirecttransactionshipping int,
367
onefavitemtotal int,
368
sevenfavitemtotal int,
369
impression int,
370
threefavshoptotal int,
371
threeindirecttransactionshipping int,
372
ranksum int,
373
fifteendirecttransactionshipping int,
374
fifteenfavitemtotal int,
375
fifteenindirecttransactionshipping int,
376
thedate datetime(6),
377
fifteenfavshoptotal int,
378
memberid int,
379
sevenindirecttransactionshipping int,
380
cost int,
381
favitemtotal int,
382
onefavshoptotal int,
383
directtransaction int,
384
invalidclick int,
385
custid int,
386
click int,
387
PRIMARY KEY (custid,thedate)
388
) COMPRESSION='lz4_1.0' ;
389
CREATE TABLE IF NOT EXISTS rpt_bpp4p_demographic_name_purchase_000(
390
demographicid int,
391
thedate datetime(6),
392
name varchar(100),
393
PRIMARY KEY (demographicid)
394
) COMPRESSION='lz4_1.0' ;
395
CREATE TABLE IF NOT EXISTS rpt_bpp4p_tag_000(
396
name varchar(300),
397
gmt_created datetime default now(),
398
id int,
399
type int,
400
gmt_modified datetime default now(),
401
PRIMARY KEY (id)
402
) COMPRESSION='lz4_1.0' ;
403
CREATE TABLE IF NOT EXISTS rpt_bpp4p_adgroup_ob_000(
404
adgroupid int,
405
sevenindirecttransactionshipping int,
406
memberid int,
407
cost int,
408
campaignid int,
409
favitemtotal int,
410
fifteendirecttransactionshipping int,
411
fifteenindirecttransactionshipping int,
412
fifteenfavitemtotal int,
413
thedate datetime(6),
414
isshop int,
415
fifteenfavshoptotal int,
416
custid int,
417
click int,
418
onefavshoptotal int,
419
invalidclick int,
420
network int,
421
directtransaction int,
422
oneindirecttransactionshipping int,
423
directtransactionshipping int,
424
matchscope int,
425
threefavitemtotal int,
426
favshoptotal int,
427
onedirecttransaction int,
428
threedirecttransaction int,
429
oneindirecttransaction int,
430
searchtype int,
431
auctionid int,
432
fifteendirecttransaction int,
433
threedirecttransactionshipping int,
434
sevenfavitemtotal int,
435
creativeid int,
436
threefavshoptotal int,
437
impression int,
438
threeindirecttransactionshipping int,
439
ranksum int,
440
sevendirecttransactionshipping int,
441
threeindirecttransaction int,
442
indirecttransaction int,
443
fifteenindirecttransaction int,
444
sevenfavshoptotal int,
445
sevendirecttransaction int,
446
onefavitemtotal int,
447
indirecttransactionshipping int,
448
onedirecttransactionshipping int,
449
sevenindirecttransaction int,
450
PRIMARY KEY (custid,thedate,campaignid,adgroupid,creativeid,network,matchscope,searchtype)
451
) COMPRESSION='lz4_1.0' ;
452
CREATE TABLE IF NOT EXISTS rpt_bpp4p_dim_000(
453
gmt_modified datetime default now(),
454
id int,
455
name varchar(300),
456
gmt_created datetime default now(),
457
PRIMARY KEY (id)
458
) COMPRESSION='lz4_1.0' ;
459
CREATE TABLE IF NOT EXISTS rpt_bpp4p_adgroup_name_000(
460
title varchar(1024),
461
custid int,
462
adgroupid int,
463
memberid int,
464
campaignid int,
465
imgurl varchar(1024),
466
auctionid int,
467
thedate datetime(6),
468
isshop int,
469
linkurl varchar(1024),
470
PRIMARY KEY (custid,thedate,campaignid,adgroupid)
471
) COMPRESSION='lz4_1.0' ;
472
CREATE TABLE IF NOT EXISTS rpt_bpp4p_insight_bidword_price_000(
473
indirecttransaction int,
474
competition int,
475
mechanism int,
476
directtransaction int,
477
ext1 int,
478
indirecttransactionshipping int,
479
network int,
480
bidwordstr varchar(300),
481
impression int,
482
ext2 int,
483
click int,
484
pricedistribution int,
485
thedate datetime(6),
486
directtransactionshipping int,
487
bidwordhash int,
488
favshoptotal int,
489
cost int,
490
favitemtotal int,
491
PRIMARY KEY (bidwordhash,thedate,pricedistribution,network,mechanism,ext1,ext2,bidwordstr)
492
) COMPRESSION='lz4_1.0' ;
493
CREATE TABLE IF NOT EXISTS rpt_bpp4p_sexpowerstar_ob_000(
494
directtransaction int,
495
invalidclick int,
496
onefavshoptotal int,
497
click int,
498
custid int,
499
fifteenfavshoptotal int,
500
fifteenfavitemtotal int,
501
thedate datetime(6),
502
fifteenindirecttransactionshipping int,
503
fifteendirecttransactionshipping int,
504
demographicstarid int,
505
demographicsexid int,
506
favitemtotal int,
507
campaignid int,
508
cost int,
509
memberid int,
510
sevenindirecttransactionshipping int,
511
sevenindirecttransaction int,
512
onefavitemtotal int,
513
indirecttransactionshipping int,
514
onedirecttransactionshipping int,
515
sevendirecttransaction int,
516
fifteenindirecttransaction int,
517
sevenfavshoptotal int,
518
indirecttransaction int,
519
threeindirecttransaction int,
520
sevendirecttransactionshipping int,
521
threeindirecttransactionshipping int,
522
impression int,
523
threefavshoptotal int,
524
sevenfavitemtotal int,
525
threedirecttransactionshipping int,
526
fifteendirecttransaction int,
527
demographicpurchaseid int,
528
threedirecttransaction int,
529
oneindirecttransaction int,
530
onedirecttransaction int,
531
favshoptotal int,
532
threefavitemtotal int,
533
directtransactionshipping int,
534
oneindirecttransactionshipping int,
535
PRIMARY KEY (custid,thedate,campaignid,demographicsexid,demographicpurchaseid,demographicstarid)
536
) COMPRESSION='lz4_1.0' ;
537
CREATE TABLE IF NOT EXISTS rpt_bpp4p_campaign_name_000(
538
memeberid int,
539
thedate datetime(6),
540
campaignid int,
541
campaigintitle varchar(1024),
542
custid int,
543
campaigntype int,
544
PRIMARY KEY (custid,thedate,campaignid)
545
) COMPRESSION='lz4_1.0' ;
546
CREATE TABLE IF NOT EXISTS rpt_bpp4p_insight_bidword_000(
547
mechanism int,
548
competition int,
549
indirecttransaction int,
550
ext1 int,
551
indirecttransactionshipping int,
552
network int,
553
directtransaction int,
554
impression int,
555
bidwordstr varchar(512),
556
click int,
557
ext2 int,
558
thedate datetime(6),
559
bidwordhash int,
560
directtransactionshipping int,
561
favitemtotal int,
562
cost int,
563
favshoptotal int,
564
PRIMARY KEY (bidwordhash,thedate,network,mechanism,ext1,ext2,bidwordstr)
565
) COMPRESSION='lz4_1.0' ;
566
CREATE TABLE IF NOT EXISTS rpt_bpp4p_insight_bidword_category_000(
567
click int,
568
ext2 int,
569
bidwordstr varchar(300),
570
impression int,
571
indirecttransactionshipping int,
572
ext1 int,
573
network int,
574
directtransaction int,
575
indirecttransaction int,
576
mechanism int,
577
competition int,
578
favshoptotal int,
579
cost int,
580
favitemtotal int,
581
directtransactionshipping int,
582
bidwordhash int,
583
thedate datetime(6),
584
category int,
585
PRIMARY KEY (category,bidwordhash,thedate,network,mechanism,ext1,ext2,bidwordstr)
586
) COMPRESSION='lz4_1.0' ;
587
CREATE TABLE IF NOT EXISTS rpt_bpp4p_demographic_name_sex_000(
588
name varchar(100),
589
thedate datetime(6),
590
demographicid int,
591
PRIMARY KEY (demographicid)
592
) COMPRESSION='lz4_1.0' ;
593
CREATE TABLE IF NOT EXISTS rpt_bpp4p_pos_ob_000(
594
threedirecttransactionshipping int,
595
fifteendirecttransaction int,
596
auctionid int,
597
oneindirecttransaction int,
598
threedirecttransaction int,
599
dimid int,
600
onedirecttransaction int,
601
favshoptotal int,
602
threefavitemtotal int,
603
oneindirecttransactionshipping int,
604
directtransactionshipping int,
605
sevenindirecttransaction int,
606
onedirecttransactionshipping int,
607
indirecttransactionshipping int,
608
onefavitemtotal int,
609
sevendirecttransaction int,
610
fifteenindirecttransaction int,
611
sevenfavshoptotal int,
612
indirecttransaction int,
613
threeindirecttransaction int,
614
sevendirecttransactionshipping int,
615
threeindirecttransactionshipping int,
616
impression int,
617
threefavshoptotal int,
618
placeid int,
619
sevenfavitemtotal int,
620
fifteenfavshoptotal int,
621
isshop int,
622
fifteenindirecttransactionshipping int,
623
fifteenfavitemtotal int,
624
thedate datetime(6),
625
fifteendirecttransactionshipping int,
626
favitemtotal int,
627
cost int,
628
campaignid int,
629
memberid int,
630
sevenindirecttransactionshipping int,
631
adgroupid int,
632
directtransaction int,
633
invalidclick int,
634
onefavshoptotal int,
635
click int,
636
custid int,
637
PRIMARY KEY (custid,thedate,campaignid,adgroupid,placeid)
638
) COMPRESSION='lz4_1.0' ;
639
CREATE TABLE IF NOT EXISTS rpt_bpp4p_campaign_ob_000(
640
searchtype int,
641
oneindirecttransaction int,
642
threedirecttransaction int,
643
threedirecttransactionshipping int,
644
fifteendirecttransaction int,
645
threefavitemtotal int,
646
matchscope int,
647
directtransactionshipping int,
648
oneindirecttransactionshipping int,
649
onedirecttransaction int,
650
favshoptotal int,
651
sevenfavshoptotal int,
652
fifteenindirecttransaction int,
653
sevendirecttransaction int,
654
sevendirecttransactionshipping int,
655
threeindirecttransaction int,
656
indirecttransaction int,
657
onefavitemtotal int,
658
indirecttransactionshipping int,
659
onedirecttransactionshipping int,
660
sevenindirecttransaction int,
661
threefavshoptotal int,
662
impression int,
663
sevenfavitemtotal int,
664
ranksum int,
665
threeindirecttransactionshipping int,
666
fifteendirecttransactionshipping int,
667
fifteenfavshoptotal int,
668
isshop int,
669
fifteenfavitemtotal int,
670
fifteenindirecttransactionshipping int,
671
thedate datetime(6),
672
sevenindirecttransactionshipping int,
673
memberid int,
674
favitemtotal int,
675
campaignid int,
676
cost int,
677
invalidclick int,
678
network int,
679
directtransaction int,
680
onefavshoptotal int,
681
custid int,
682
click int,
683
PRIMARY KEY (custid,thedate,campaignid,network,matchscope,searchtype,isshop)
684
) COMPRESSION='lz4_1.0' ;
685
CREATE TABLE IF NOT EXISTS rpt_bpp4p_area_name_000(
686
cityid int,
687
cityname varchar(100),
688
provinceid int,
689
provincename varchar(100),
690
PRIMARY KEY (provinceid,cityid)
691
) COMPRESSION='lz4_1.0' ;
692
drop database createsql;
693