Skip to content

Commit 59ab988

Browse files
authored
Add xb mhy usercases (apache#116)
1 parent 6185d77 commit 59ab988

31 files changed

Lines changed: 1179 additions & 13 deletions
Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q02 --
3+
1
4+
Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,13 @@
11
-- This file is automatically generated. You should know what you did if you want to edit this
22
-- !q01 --
3-
空运出口 10301 0 2022-06 0 0 800 0
4-
空运出口 10302 0 2022-06 0 0 1700 0
5-
空运出口 10303 0 2022-06 0 0 1400 0
6-
空运出口 10305 0 2022-06 0 0 15900 0
7-
空运出口 10306 0 2022-06 0 0 37700 0
8-
空运出口 10307 0 2022-06 0 0 41500 0
9-
空运国内 10307 0 2022-06 0 0 4200 0
10-
空运进口 10301 0 2022-06 0 0 400 0
11-
空运进口 10305 0 2022-06 0 0 100 0
12-
空运进口 10306 0 2022-06 0 0 14300 0
3+
空运出口 10301 0 2022-06 0E-9 0E-9 800.000000000 0E-9
4+
空运出口 10302 0 2022-06 0E-9 0E-9 1700.000000000 0E-9
5+
空运出口 10303 0 2022-06 0E-9 0E-9 1400.000000000 0E-9
6+
空运出口 10305 0 2022-06 0E-9 0E-9 15900.000000000 0E-9
7+
空运出口 10306 0 2022-06 0E-9 0E-9 37700.000000000 0E-9
8+
空运出口 10307 0 2022-06 0E-9 0E-9 41500.000000000 0E-9
9+
空运国内 10307 0 2022-06 0E-9 0E-9 4200.000000000 0E-9
10+
空运进口 10301 0 2022-06 0E-9 0E-9 400.000000000 0E-9
11+
空运进口 10305 0 2022-06 0E-9 0E-9 100.000000000 0E-9
12+
空运进口 10306 0 2022-06 0E-9 0E-9 14300.000000000 0E-9
1313

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q03 --
3+
Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q03 --
3+
4+
-- !q03_2 --
5+
Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q04 --
3+
1
4+
5+
-- !q04_2 --
6+
sada 2
7+
Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q05 --
3+
0
4+
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q06 --
3+
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q01 --
3+
Lines changed: 144 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,144 @@
1+
CREATE TABLE `ads_dy_dy360_room_ability_data_show_1d` (
2+
`date` date NOT NULL COMMENT '数据日期',
3+
`room_id` bigint(20) NOT NULL DEFAULT "0" COMMENT '房间号',
4+
`nickname` text NULL DEFAULT "" COMMENT '主播昵称',
5+
`all_score` double NULL DEFAULT "0.0" COMMENT '综合得分',
6+
`score_level` text NULL DEFAULT "" COMMENT '综合评级',
7+
`score_diff` text NULL DEFAULT "" COMMENT '距离上一名得分差值,为-1则表示主播为第一名',
8+
`score_level_next` text NULL DEFAULT "" COMMENT '下一等级,若当前等级为SS,则为-1',
9+
`level_summary` text NULL DEFAULT "" COMMENT '主播价值等级描述',
10+
`model_score_live` double NULL DEFAULT "0.0" COMMENT '开播勤奋度得分',
11+
`model_score_traffic` double NULL DEFAULT "0.0" COMMENT '流量能力得分',
12+
`model_score_flow` double NULL DEFAULT "0.0" COMMENT '吸金能力得分',
13+
`model_score_acquisition` double NULL DEFAULT "0.0" COMMENT '拉新能力得分',
14+
`model_score_retention` double NULL DEFAULT "0.0" COMMENT '用户粘性得分',
15+
`promotion_tips_1` text NULL DEFAULT "" COMMENT '提升建议1',
16+
`promotion_tips_2` text NULL DEFAULT "" COMMENT '提升建议2',
17+
`promotion_tips_3` text NULL DEFAULT "" COMMENT '提升建议3',
18+
`dt` varchar(8) NOT NULL DEFAULT "" COMMENT '数据日期,yyyyMMdd',
19+
`level_summary_show` text NULL DEFAULT "" COMMENT '排名总结'
20+
) ENGINE = OLAP UNIQUE KEY(`date`, `room_id`) COMMENT '斗鱼360-主播洞察-对外关播数据展示'
21+
PARTITION BY RANGE(`date`) (
22+
PARTITION p20221021 VALUES [('2022-10-21'), ('2022-10-22')),
23+
PARTITION p20221022 VALUES [('2022-10-22'), ('2022-10-23')),
24+
PARTITION p20221023 VALUES [('2022-10-23'), ('2022-10-24')),
25+
PARTITION p20221024 VALUES [('2022-10-24'), ('2022-10-25')),
26+
PARTITION p20221025 VALUES [('2022-10-25'), ('2022-10-26')),
27+
PARTITION p20221026 VALUES [('2022-10-26'), ('2022-10-27')),
28+
PARTITION p20221027 VALUES [('2022-10-27'), ('2022-10-28')),
29+
PARTITION p20221028 VALUES [('2022-10-28'), ('2022-10-29')),
30+
PARTITION p20221029 VALUES [('2022-10-29'), ('2022-10-30')),
31+
PARTITION p20221030 VALUES [('2022-10-30'), ('2022-10-31')),
32+
PARTITION p20221031 VALUES [('2022-10-31'), ('2022-11-01')),
33+
PARTITION p20221101 VALUES [('2022-11-01'), ('2022-11-02')),
34+
PARTITION p20221102 VALUES [('2022-11-02'), ('2022-11-03')),
35+
PARTITION p20221103 VALUES [('2022-11-03'), ('2022-11-04')),
36+
PARTITION p20221104 VALUES [('2022-11-04'), ('2022-11-05')),
37+
PARTITION p20221105 VALUES [('2022-11-05'), ('2022-11-06')),
38+
PARTITION p20221106 VALUES [('2022-11-06'), ('2022-11-07')),
39+
PARTITION p20221107 VALUES [('2022-11-07'), ('2022-11-08')),
40+
PARTITION p20221108 VALUES [('2022-11-08'), ('2022-11-09')),
41+
PARTITION p20221109 VALUES [('2022-11-09'), ('2022-11-10')),
42+
PARTITION p20221110 VALUES [('2022-11-10'), ('2022-11-11')),
43+
PARTITION p20221111 VALUES [('2022-11-11'), ('2022-11-12')),
44+
PARTITION p20221112 VALUES [('2022-11-12'), ('2022-11-13')),
45+
PARTITION p20221113 VALUES [('2022-11-13'), ('2022-11-14')),
46+
PARTITION p20221114 VALUES [('2022-11-14'), ('2022-11-15')),
47+
PARTITION p20221115 VALUES [('2022-11-15'), ('2022-11-16')),
48+
PARTITION p20221116 VALUES [('2022-11-16'), ('2022-11-17')),
49+
PARTITION p20221117 VALUES [('2022-11-17'), ('2022-11-18')),
50+
PARTITION p20221118 VALUES [('2022-11-18'), ('2022-11-19')),
51+
PARTITION p20221119 VALUES [('2022-11-19'), ('2022-11-20')),
52+
PARTITION p20221120 VALUES [('2022-11-20'), ('2022-11-21')),
53+
PARTITION p20221121 VALUES [('2022-11-21'), ('2022-11-22')),
54+
PARTITION p20221122 VALUES [('2022-11-22'), ('2022-11-23')),
55+
PARTITION p20221123 VALUES [('2022-11-23'), ('2022-11-24')),
56+
PARTITION p20221124 VALUES [('2022-11-24'), ('2022-11-25')),
57+
PARTITION p20221125 VALUES [('2022-11-25'), ('2022-11-26')),
58+
PARTITION p20221126 VALUES [('2022-11-26'), ('2022-11-27')),
59+
PARTITION p20221127 VALUES [('2022-11-27'), ('2022-11-28')),
60+
PARTITION p20221128 VALUES [('2022-11-28'), ('2022-11-29')),
61+
PARTITION p20221129 VALUES [('2022-11-29'), ('2022-11-30')),
62+
PARTITION p20221130 VALUES [('2022-11-30'), ('2022-12-01')),
63+
PARTITION p20221201 VALUES [('2022-12-01'), ('2022-12-02')),
64+
PARTITION p20221202 VALUES [('2022-12-02'), ('2022-12-03')),
65+
PARTITION p20221203 VALUES [('2022-12-03'), ('2022-12-04')),
66+
PARTITION p20221204 VALUES [('2022-12-04'), ('2022-12-05')),
67+
PARTITION p20221205 VALUES [('2022-12-05'), ('2022-12-06')),
68+
PARTITION p20221206 VALUES [('2022-12-06'), ('2022-12-07')),
69+
PARTITION p20221207 VALUES [('2022-12-07'), ('2022-12-08')),
70+
PARTITION p20221208 VALUES [('2022-12-08'), ('2022-12-09')),
71+
PARTITION p20221209 VALUES [('2022-12-09'), ('2022-12-10')),
72+
PARTITION p20221210 VALUES [('2022-12-10'), ('2022-12-11')),
73+
PARTITION p20221211 VALUES [('2022-12-11'), ('2022-12-12')),
74+
PARTITION p20221212 VALUES [('2022-12-12'), ('2022-12-13')),
75+
PARTITION p20221213 VALUES [('2022-12-13'), ('2022-12-14')),
76+
PARTITION p20221214 VALUES [('2022-12-14'), ('2022-12-15')),
77+
PARTITION p20221215 VALUES [('2022-12-15'), ('2022-12-16')),
78+
PARTITION p20221216 VALUES [('2022-12-16'), ('2022-12-17')),
79+
PARTITION p20221217 VALUES [('2022-12-17'), ('2022-12-18')),
80+
PARTITION p20221218 VALUES [('2022-12-18'), ('2022-12-19')),
81+
PARTITION p20221219 VALUES [('2022-12-19'), ('2022-12-20')),
82+
PARTITION p20221220 VALUES [('2022-12-20'), ('2022-12-21')),
83+
PARTITION p20221221 VALUES [('2022-12-21'), ('2022-12-22')),
84+
PARTITION p20221222 VALUES [('2022-12-22'), ('2022-12-23')),
85+
PARTITION p20221223 VALUES [('2022-12-23'), ('2022-12-24')),
86+
PARTITION p20221224 VALUES [('2022-12-24'), ('2022-12-25')),
87+
PARTITION p20221225 VALUES [('2022-12-25'), ('2022-12-26')),
88+
PARTITION p20221226 VALUES [('2022-12-26'), ('2022-12-27')),
89+
PARTITION p20221227 VALUES [('2022-12-27'), ('2022-12-28')),
90+
PARTITION p20221228 VALUES [('2022-12-28'), ('2022-12-29')),
91+
PARTITION p20221229 VALUES [('2022-12-29'), ('2022-12-30')),
92+
PARTITION p20221230 VALUES [('2022-12-30'), ('2022-12-31')),
93+
PARTITION p20221231 VALUES [('2022-12-31'), ('2023-01-01')),
94+
PARTITION p20230101 VALUES [('2023-01-01'), ('2023-01-02')),
95+
PARTITION p20230102 VALUES [('2023-01-02'), ('2023-01-03')),
96+
PARTITION p20230103 VALUES [('2023-01-03'), ('2023-01-04')),
97+
PARTITION p20230104 VALUES [('2023-01-04'), ('2023-01-05')),
98+
PARTITION p20230105 VALUES [('2023-01-05'), ('2023-01-06')),
99+
PARTITION p20230106 VALUES [('2023-01-06'), ('2023-01-07')),
100+
PARTITION p20230107 VALUES [('2023-01-07'), ('2023-01-08')),
101+
PARTITION p20230108 VALUES [('2023-01-08'), ('2023-01-09')),
102+
PARTITION p20230109 VALUES [('2023-01-09'), ('2023-01-10')),
103+
PARTITION p20230110 VALUES [('2023-01-10'), ('2023-01-11')),
104+
PARTITION p20230111 VALUES [('2023-01-11'), ('2023-01-12')),
105+
PARTITION p20230112 VALUES [('2023-01-12'), ('2023-01-13')),
106+
PARTITION p20230113 VALUES [('2023-01-13'), ('2023-01-14')),
107+
PARTITION p20230114 VALUES [('2023-01-14'), ('2023-01-15')),
108+
PARTITION p20230115 VALUES [('2023-01-15'), ('2023-01-16')),
109+
PARTITION p20230116 VALUES [('2023-01-16'), ('2023-01-17')),
110+
PARTITION p20230117 VALUES [('2023-01-17'), ('2023-01-18')),
111+
PARTITION p20230118 VALUES [('2023-01-18'), ('2023-01-19')),
112+
PARTITION p20230119 VALUES [('2023-01-19'), ('2023-01-20')),
113+
PARTITION p20230120 VALUES [('2023-01-20'), ('2023-01-21')),
114+
PARTITION p20230121 VALUES [('2023-01-21'), ('2023-01-22')),
115+
PARTITION p20230122 VALUES [('2023-01-22'), ('2023-01-23')),
116+
PARTITION p20230123 VALUES [('2023-01-23'), ('2023-01-24')),
117+
PARTITION p20230124 VALUES [('2023-01-24'), ('2023-01-25')),
118+
PARTITION p20230125 VALUES [('2023-01-25'), ('2023-01-26')),
119+
PARTITION p20230126 VALUES [('2023-01-26'), ('2023-01-27')),
120+
PARTITION p20230127 VALUES [('2023-01-27'), ('2023-01-28')),
121+
PARTITION p20230128 VALUES [('2023-01-28'), ('2023-01-29')),
122+
PARTITION p20230129 VALUES [('2023-01-29'), ('2023-01-30')),
123+
PARTITION p20230130 VALUES [('2023-01-30'), ('2023-01-31'))
124+
)
125+
DISTRIBUTED BY HASH(`room_id`) BUCKETS 2
126+
PROPERTIES (
127+
"replication_allocation" = "tag.location.default: 1",
128+
"dynamic_partition.enable" = "true",
129+
"dynamic_partition.time_unit" = "DAY",
130+
"dynamic_partition.time_zone" = "Asia/Shanghai",
131+
"dynamic_partition.start" = "-100",
132+
"dynamic_partition.end" = "1",
133+
"dynamic_partition.prefix" = "p",
134+
"dynamic_partition.replication_allocation" = "tag.location.default: 1",
135+
"dynamic_partition.buckets" = "32",
136+
"dynamic_partition.create_history_partition" = "true",
137+
"dynamic_partition.history_partition_num" = "-1",
138+
"dynamic_partition.hot_partition_num" = "0",
139+
"dynamic_partition.reserved_history_periods" = "NULL",
140+
"dynamic_partition.storage_policy" = "",
141+
"in_memory" = "false",
142+
"storage_format" = "V2",
143+
"disable_auto_compaction" = "false"
144+
);

‎regression-test/suites/usercases/DY/load.groovy‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,8 @@
2121
suite("load") {
2222
def tables =
2323
[
24-
"dwd_dy_action_event_detail_di"
24+
"dwd_dy_action_event_detail_di",
25+
"ads_dy_dy360_room_ability_data_show_1d"
2526
]
2627
for (String table in tables) {
2728
sql """DROP TABLE IF EXISTS ${table}"""
@@ -31,4 +32,15 @@ suite("load") {
3132
sql """
3233
insert into dwd_dy_action_event_detail_di values ("2022-11-06 12:05:04",234343,"ct_code_11111","page_code_1111",1667830212000,"action_code_111111",4334241123,10086,1001,1001,1676543566,3324234,324234,"172.21.0.24","中国","湖北","武汉","7.152","7.152","WIFI","fsdfsfdsafads");
3334
"""
35+
36+
sql """
37+
INSERT INTO `ads_dy_dy360_room_ability_data_show_1d` (`date`, `room_id`, `nickname`, `all_score`, `score_level`, `score_diff`, `score_level_next`, `level_summary`, `model_score_live`, `model_score_traffic`, `model_score_flow`, `model_score_acquisition`, `model_score_retention`, `promotion_tips_1`, `promotion_tips_2`, `promotion_tips_3`, `dt`, `level_summary_show`) VALUES ('2022-12-25', 222222, 'bbbbb', 90, 'SS', '-1', '-1', '您当前表现优异,坚持以下条件即可保持评级', 95, 90, 85, 85, 90, '保持每月开播24天', '保持日均流水达到300鱼翅', '积极与观众互动', '20221225', '')
38+
,('2022-12-25', 333333, 'cccccc', 99.18513223, 'SS', '-1', '-1', '您当前表现优异,坚持以下条件即可保持评级', 99, 98, 95.12534345, 90, 100, '保持每月开播24天', '保持日均流水达到300鱼翅', '积极与观众互动', '20221225', '')
39+
,('2022-12-25', 77619508, 'zhujin1', 80, 'S', '10', 'SS', '您距离下一等级差10分,完成以下条件即有机会升级为SS级主播', 100, 84, 75, 72, 83, '保证每日开播6小时', '保证日均流水达到100鱼翅', '参与平台活动获得更多流量扶持', '20221225', '')
40+
,('2022-12-25', 111111, 'aaaaa', 80, 'S', '10', 'SS', '您距离下一等级差10分,完成以下条件即有机会升级为SS级主播', 100, 84, 75, 72, 83, '保证每日开播6小时', '保证日均流水达到100鱼翅', '参与平台活动获得更多流量扶持', '20221225', '')
41+
,('2022-12-25', 444444, 'ffffff', 80, 'S', NULL, NULL, NULL, 100, 84, 75, 72, 83, '保证每日开播6小时', '保证日均流水达到100鱼翅', '参与平台活动获得更多流量扶持', '20221225', '')
42+
,('2022-12-25', 304467, 'gggggg', 99.18513223, 'SS', '-1', '-1', '您当前表现优异,坚持以下条件即可保持评级', 99, 98, 95.12534345, 90, 100, '保持每月开播24天', '保持日均流水达到300鱼翅', '积极与观众互动', '20221225', '')
43+
,('2022-12-25', 555555, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '20221225', '')
44+
,('2022-12-25', 77619489, 'wuwei111', 85, 'S', '10', 'SS', '您距离下一等级差10分,完成以下条件即有机会升级为SS级主播', 100, 84, 75, 72, 83, '保证每日开播6小时', '保证日均流水达到100鱼翅', '参与平台活动获得更多流量扶持', '20221225', '');
45+
"""
3446
}

0 commit comments

Comments
 (0)