Skip to content

Commit ae14649

Browse files
committed
add two case (apache#49)
* TC case * HCBD case
1 parent 8d8e685 commit ae14649

14 files changed

Lines changed: 626 additions & 1 deletion

File tree

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q02 --
3+
a c 4.0 8.0 8.0 14.0 -6.0 -0.75 4.0 -1.0
4+
a e 2.0 1.0 9.0 6.0 -5.0 -5 2.0 -5.0
5+
c d 15.0 15.0 7.0 8.0 7.0 0.4666666666666667 4.0 0.0
6+
d a 1.0 1.0 1.0 8.0 -7.0 -7 16.0 -7.0
7+
d c 5.0 2.0 1.0 2.0 0.0 0 8.0 0.0
8+
Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q01 --
3+
2022-07-17 a b a c d e a 7 b c \N \N
4+
2022-07-17 a b a c d e a 7 b c \N \N
5+
2022-07-17 a d a d c e e 1 e b \N \N
6+
Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !q02 --
3+
2022-07-17 a b a c d e a 7 b c \N \N
4+
2022-07-17 a b a c d e a 7 b c \N \N
5+
2022-07-17 a d a d c e e 1 e b \N \N
6+
Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,105 @@
1+
CREATE TABLE `dws_railway_revenue`
2+
(
3+
`docaindex` varchar(100) NULL COMMENT "费用项索引",
4+
`checkdate` varchar(100) NULL COMMENT "结账时间",
5+
`orderno` varchar(100) NULL COMMENT "委托单号",
6+
`referenceno` varchar(100) NULL COMMENT "参考号",
7+
`orderdate` varchar(100) NULL COMMENT "委托日期",
8+
`exportsendtime` varchar(100) NULL COMMENT "发运日期",
9+
`soano` varchar(100) NULL COMMENT "账单号",
10+
`internalsettlementlinkid` varchar(100) NULL COMMENT "关联交易",
11+
`feeno` varchar(100) NULL COMMENT "费用项编码",
12+
`feename` varchar(100) NULL COMMENT "费用项名称",
13+
`creditdebit` varchar(100) NULL COMMENT "收付方向",
14+
`redbilltype` varchar(100) NULL COMMENT "红冲标记",
15+
`ordertypeid` varchar(50) NULL COMMENT "大委托类型编码",
16+
`ordertypename` varchar(50) NULL COMMENT "大委托类型名称",
17+
`smallordertypeid` varchar(50) NULL COMMENT "小委托类型编码",
18+
`smallordertypename` varchar(50) NULL COMMENT "小委托类型名称",
19+
`is_lcl` varchar(100) NULL COMMENT "是否拼箱",
20+
`currencyno` varchar(100) NULL COMMENT "币别",
21+
`cnyrate` varchar(100) NULL COMMENT "汇率",
22+
`sponsoropid` varchar(100) NULL COMMENT "账单关系人",
23+
`sponsoropname` varchar(300) NULL COMMENT "账单关系人",
24+
`ramount` double NULL COMMENT "收含税金额",
25+
`rntaxamount` double NULL COMMENT "收非含税金额",
26+
`rcamount` double NULL COMMENT "收含税金额CNY",
27+
`rcntaxamount` double NULL COMMENT "收非含税金额CNY",
28+
`pamount` double NULL COMMENT "付含税金额",
29+
`pcontainerfee` double NULL COMMENT "付含税金额(租箱费)",
30+
`prailwayfee` double NULL COMMENT "付含税金额(铁路运费)",
31+
`pother` double NULL COMMENT "付含税金额(杂费)",
32+
`pntaxamount` double NULL COMMENT "付非含税金额",
33+
`pncontainerfee` double NULL COMMENT "付非含税金额(租箱费)",
34+
`pnrailwayfee` double NULL COMMENT "付非含税金额(铁路运费)",
35+
`pnother` double NULL COMMENT "付非含税金额(杂费)",
36+
`pcamount` double NULL COMMENT "付含税金额CNY",
37+
`pccontainerfee` double NULL COMMENT "付含税金额(租箱费)CNY",
38+
`pcrailwayfee` double NULL COMMENT "付含税金额(铁路运费)CNY",
39+
`pcother` double NULL COMMENT "付含税金额(杂费)CNY",
40+
`pcntaxamount` double NULL COMMENT "付非含税金额CNY",
41+
`pcncontainerfee` double NULL COMMENT "付非含税金额(租箱费)CNY",
42+
`pcnrailwayfee` double NULL COMMENT "付非含税金额(铁路运费)CNY",
43+
`pcnother` double NULL COMMENT "付非含税金额(杂费)CNY",
44+
`customerno` varchar(100) NULL COMMENT "委托客户编码",
45+
`customername` varchar(500) NULL COMMENT "委托客户名称",
46+
`salesno` varchar(100) NULL COMMENT "销售编码(结算)",
47+
`salesname` varchar(300) NULL COMMENT "销售名称(结算)",
48+
`saleareaname` varchar(500) NULL COMMENT "销售区域(结算)",
49+
`salecompanyname` varchar(500) NULL COMMENT "销售公司(结算)",
50+
`saledeptname` varchar(500) NULL COMMENT "销售部门(结算)",
51+
`saleid` varchar(100) NULL COMMENT "销售编码(人力)",
52+
`salev1_name` varchar(300) NULL COMMENT "一级销售部门(人力)",
53+
`salev2_name` varchar(300) NULL COMMENT "二级销售部门(人力)",
54+
`salev3_name` varchar(300) NULL COMMENT "三级销售部门(人力)",
55+
`salev4_name` varchar(300) NULL COMMENT "四级销售部门(人力)",
56+
`salev5_name` varchar(300) NULL COMMENT "五级销售部门(人力)",
57+
`salev6_name` varchar(300) NULL COMMENT "六级销售部门(人力)",
58+
`opno` varchar(100) NULL COMMENT "操作编码(结算)",
59+
`opname` varchar(300) NULL COMMENT "操作名称(结算)",
60+
`operatorareaname` varchar(300) NULL COMMENT "操作区域(结算)",
61+
`operatorcompanyname` varchar(300) NULL COMMENT "操作公司(结算)",
62+
`operatordeptname` varchar(300) NULL COMMENT "操作部门(结算)",
63+
`operatorid` varchar(100) NULL COMMENT "操作编码(人力)",
64+
`oplev1_name` varchar(300) NULL COMMENT "一级操作部门(人力)",
65+
`oplev2_name` varchar(300) NULL COMMENT "二级操作部门(人力)",
66+
`oplev3_name` varchar(300) NULL COMMENT "三级操作部门(人力)",
67+
`oplev4_name` varchar(300) NULL COMMENT "四级操作部门(人力)",
68+
`oplev5_name` varchar(300) NULL COMMENT "五级操作部门(人力)",
69+
`oplev6_name` varchar(300) NULL COMMENT "六级操作部门(人力)",
70+
`splev1_name` varchar(300) NULL COMMENT "一级关系人部门(人力)",
71+
`splev2_name` varchar(300) NULL COMMENT "二级关系人部门(人力)",
72+
`splev3_name` varchar(300) NULL COMMENT "三级关系人部门(人力)",
73+
`splev4_name` varchar(300) NULL COMMENT "四级关系人部门(人力)",
74+
`splev5_name` varchar(300) NULL COMMENT "五级关系人部门(人力)",
75+
`splev6_name` varchar(300) NULL COMMENT "六级关系人部门(人力)",
76+
`platform` varchar(100) NULL COMMENT "平台",
77+
`trainname` varchar(100) NULL COMMENT "班列(航线)",
78+
`productcode` varchar(100) NULL COMMENT "产品编码",
79+
`startstation` varchar(50) NULL COMMENT "发站编码",
80+
`sstation_namecn` varchar(300) NULL COMMENT "发站中文名称",
81+
`sprovince` varchar(100) NULL COMMENT "发站省份",
82+
`endstation` varchar(50) NULL COMMENT "到站编码",
83+
`estation_namecn` varchar(300) NULL COMMENT "到站中文名称",
84+
`eprovince` varchar(100) NULL COMMENT "到站省份",
85+
`exportportcode` varchar(100) NULL COMMENT "出/入境口岸编码",
86+
`entrusttypecode` varchar(100) NULL COMMENT "客户类型编码",
87+
`entrusttypename` varchar(100) NULL COMMENT "客户类型名称",
88+
`industrytypecode` varchar(100) NULL COMMENT "客户行业编码",
89+
`industrytypename` varchar(100) NULL COMMENT "客户行业名称",
90+
`isadd` varchar(50) NULL COMMENT "是否增补",
91+
`iscancel` varchar(50) NULL COMMENT "是否销账",
92+
`canceldate` varchar(50) NULL COMMENT "销账日期",
93+
`isinvoive` varchar(50) NULL COMMENT "是否开票",
94+
`invoicedate` varchar(50) NULL COMMENT "发票时间 ",
95+
`sourcesys` varchar(100) NULL COMMENT "数据来源",
96+
`op_datetime` datetime NULL COMMENT ""
97+
) ENGINE=OLAP
98+
UNIQUE KEY(`docaindex`, `checkdate`)
99+
COMMENT "OLAP"
100+
DISTRIBUTED BY HASH(`checkdate`) BUCKETS 8
101+
PROPERTIES (
102+
"replication_allocation" = "tag.location.default: 1",
103+
"in_memory" = "false",
104+
"storage_format" = "V2"
105+
);
Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
CREATE TABLE `dws_railway_xnop`
2+
(
3+
`checkmonth` varchar(50) NULL COMMENT "月",
4+
`referenceno` varchar(100) NULL COMMENT "委托单号",
5+
`ordertypename` varchar(100) NULL COMMENT "委托类型",
6+
`checkdate` varchar(50) NULL COMMENT "结账日期",
7+
`is_railway` varchar(50) NULL COMMENT "委托类型_铁运其他",
8+
`customername` varchar(300) NULL COMMENT "销售客户",
9+
`salesno` varchar(100) NULL COMMENT "销售",
10+
`salesname` varchar(200) NULL COMMENT "销售",
11+
`salesareaname` varchar(200) NULL COMMENT "销售区域",
12+
`salescompany` varchar(100) NULL COMMENT "销售公司",
13+
`salescompanyname` varchar(300) NULL COMMENT "销售公司",
14+
`salesdeptno` varchar(100) NULL COMMENT "销售部门",
15+
`salesdeptname` varchar(200) NULL COMMENT "销售部门",
16+
`opno` varchar(100) NULL COMMENT "操作",
17+
`opname` varchar(100) NULL COMMENT "操作",
18+
`areaname` varchar(200) NULL COMMENT "操作区域",
19+
`opcompany` varchar(100) NULL COMMENT "操作公司",
20+
`opcompanyname` varchar(300) NULL COMMENT "操作公司",
21+
`opdeptno` varchar(100) NULL COMMENT "操作部门",
22+
`opdeptname` varchar(200) NULL COMMENT "操作部门",
23+
`pxn_fee` double NULL COMMENT "付虚拟成本",
24+
`rxn_fee` double NULL COMMENT "收虚拟成本",
25+
`pop_fee` double NULL COMMENT "付操作费",
26+
`rop_fee` double NULL COMMENT "收操作费",
27+
`total_fee` double NULL COMMENT "合计",
28+
`orderdate` varchar(50) NULL COMMENT "业务日期"
29+
) ENGINE=OLAP
30+
DUPLICATE KEY(`checkmonth`)
31+
COMMENT "OLAP"
32+
DISTRIBUTED BY HASH(`checkmonth`) BUCKETS 8
33+
PROPERTIES (
34+
"replication_allocation" = "tag.location.default: 1",
35+
"in_memory" = "false",
36+
"storage_format" = "V2"
37+
);

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

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,12 +21,38 @@
2121
suite("load") {
2222
def tables =
2323
[
24-
"dwm_air_theme_loading_port_test"
24+
"dwm_air_theme_loading_port_test",
25+
"dws_railway_revenue",
26+
"dws_railway_xnop"
2527
]
2628
for (String table in tables) {
2729
sql """DROP TABLE IF EXISTS ${table}"""
2830
sql new File("""${context.file.parent}/ddl/${table}.sql""").text
2931
}
3032
sql new File("""${context.file.parent}/ddl/data1.sql""").text
3133
sql new File("""${context.file.parent}/ddl/data2.sql""").text
34+
35+
sql """
36+
INSERT INTO dws_railway_revenue (docaindex,checkdate,orderno,referenceno,orderdate,exportsendtime,soano,internalsettlementlinkid,feeno,feename,creditdebit,redbilltype,ordertypeid,ordertypename,smallordertypeid,smallordertypename,is_lcl,currencyno,cnyrate,sponsoropid,sponsoropname,ramount,rntaxamount,rcamount,rcntaxamount,pamount,pcontainerfee,prailwayfee,pother,pntaxamount,pncontainerfee,pnrailwayfee,pnother,pcamount,pccontainerfee,pcrailwayfee,pcother,pcntaxamount,pcncontainerfee,pcnrailwayfee,pcnother,customerno,customername,salesno,salesname,saleareaname,salecompanyname,saledeptname,saleid,salev1_name,salev2_name,salev3_name,salev4_name,salev5_name,salev6_name,opno,opname,operatorareaname,operatorcompanyname,operatordeptname,operatorid,oplev1_name,oplev2_name,oplev3_name,oplev4_name,oplev5_name,oplev6_name,splev1_name,splev2_name,splev3_name,splev4_name,splev5_name,splev6_name,platform,trainname,productcode,startstation,sstation_namecn,sprovince,endstation,estation_namecn,eprovince,exportportcode,entrusttypecode,entrusttypename,industrytypecode,industrytypename,isadd,iscancel,canceldate,isinvoive,invoicedate,sourcesys,op_datetime) VALUES
37+
('b','2022-07-17','a','d','2022-07-16','d','a','e','b','e','c','d','70','a','70','a','e','e','b','a','e',3.0,4.0,1.0,1.0,6.0,8.0,4.0,2.0,8.0,1.0,2.0,2.0,1.0,9.0,9.0,4.0,8.0,5.0,4.0,1.0,'c','b','d','e','c','成都供应链','b','b','c','c','c','e','d','a','b','c','c','b','c','d','铁运事业部','北区','a','b','c','d','e','b','c','b','c','b','d','b','d','b','d','d','a','a','c','b','b','d','c','d','e','b','2022-07-16','b','2022-07-17','c','2022-07-16 00:00:00'),
38+
('c','2022-07-17','a','c','2022-07-16','b','b','d','a','c','d','a','70','a','70','a','c','e','b','e','a',8.0,5.0,7.0,5.0,2.0,6.0,7.0,6.0,1.0,4.0,9.0,6.0,5.0,6.0,9.0,8.0,2.0,3.0,4.0,3.0,'b','b','c','c','c','成都供应链','e','b','d','d','d','e','a','e','b','e','b','c','e','d','铁运事业部','北区','d','e','e','e','e','e','b','e','e','b','c','e','c','b','a','e','e','b','a','b','b','c','e','c','d','e','2022-07-17','d','2022-07-16','c','2022-07-16 00:00:00'),
39+
('e','2022-07-17','a','e','2022-07-16','d','b','a','b','e','b','d','70','b','70','a','a','a','c','a','d',5.0,3.0,2.0,1.0,6.0,6.0,5.0,9.0,2.0,5.0,5.0,4.0,9.0,7.0,9.0,8.0,6.0,6.0,7.0,6.0,'c','c','a','a','b','成都供应链','c','d','c','d','a','d','c','e','d','a','d','d','d','c','铁运事业部','北区','e','b','b','d','b','a','d','d','a','d','a','c','e','d','b','c','b','a','c','e','a','c','c','d','b','c','2022-07-17','b','2022-07-16','d','2022-07-17 00:00:00'),
40+
('a','2022-07-16','e','b','2022-07-16','e','d','e','a','c','b','b','70','b','70','c','b','b','d','a','b',9.0,1.0,2.0,4.0,4.0,6.0,3.0,1.0,9.0,3.0,1.0,7.0,4.0,4.0,6.0,7.0,7.0,8.0,7.0,6.0,'b','a','c','b','d','成都供应链','a','e','c','e','d','c','c','b','a','e','e','b','a','a','铁运事业部','北区','c','e','d','d','c','a','d','a','b','c','a','e','e','c','b','a','e','e','d','d','e','a','a','e','d','b','2022-07-17','d','2022-07-16','b','2022-07-16 00:00:00'),
41+
('c','2022-07-16','b','c','2022-07-16','a','a','e','b','c','d','d','70','b','70','d','e','a','d','d','b',3.0,4.0,5.0,2.0,9.0,9.0,9.0,7.0,1.0,4.0,6.0,7.0,1.0,9.0,9.0,9.0,2.0,6.0,7.0,9.0,'d','e','e','e','d','成都供应链','a','b','e','d','c','b','d','e','d','c','c','e','c','b','铁运事业部','北区','c','e','e','c','a','d','e','b','e','e','d','b','e','a','e','b','a','e','e','c','a','a','c','d','a','c','2022-07-16','c','2022-07-17','e','2022-07-17 00:00:00'),
42+
('d','2022-07-16','a','b','2022-07-16','c','d','b','a','c','b','a','70','a','70','c','b','c','b','b','c',1.0,6.0,4.0,5.0,4.0,7.0,1.0,7.0,2.0,5.0,9.0,4.0,1.0,7.0,8.0,9.0,3.0,7.0,8.0,8.0,'a','e','e','c','d','成都供应链','b','e','a','a','a','e','a','c','b','b','c','d','e','a','铁运事业部','北区','d','a','b','d','c','b','d','d','b','d','c','a','d','b','e','b','b','e','d','d','a','a','b','e','b','b','2022-07-17','c','2022-07-17','c','2022-07-16 00:00:00');
43+
"""
44+
45+
sql """
46+
INSERT INTO dws_railway_xnop (checkmonth,referenceno,ordertypename,checkdate,is_railway,customername,salesno,salesname,salesareaname,salescompany,salescompanyname,salesdeptno,salesdeptname,opno,opname,areaname,opcompany,opcompanyname,opdeptno,opdeptname,pxn_fee,rxn_fee,pop_fee,rop_fee,total_fee,orderdate) VALUES
47+
('b','d','b','2022-07-17','d','d','e','a','a','d','e','c','e','c','e','b','c','d','c','b',6.0,7.0,4.0,4.0,8.0,'2022-07-17'),
48+
('b','b','e','2022-07-17','e','e','d','c','d','d','b','a','e','d','b','d','e','a','b','e',9.0,7.0,6.0,8.0,8.0,'2022-07-16'),
49+
('d','c','b','2022-07-16','a','c','a','d','a','c','e','c','e','a','a','b','e','d','a','a',1.0,3.0,5.0,8.0,7.0,'2022-07-16'),
50+
('a','e','a','2022-07-17','b','b','c','a','d','d','a','e','b','a','a','e','c','a','c','a',9.0,2.0,6.0,2.0,7.0,'2022-07-17'),
51+
('a','b','d','2022-07-16','b','c','c','c','e','b','a','c','b','d','d','e','b','e','e','d',2.0,1.0,6.0,1.0,5.0,'2022-07-17'),
52+
('a','a','c','2022-07-17','c','b','c','b','b','b','d','b','c','b','d','a','d','c','a','e',9.0,5.0,5.0,8.0,8.0,'2022-07-16'),
53+
('a','b','b','2022-07-17','e','d','d','a','e','d','b','e','d','c','e','d','b','a','a','c',8.0,2.0,2.0,3.0,2.0,'2022-07-17'),
54+
('a','b','c','2022-07-16','b','c','b','b','a','e','e','a','d','b','d','e','a','d','d','e',2.0,5.0,7.0,1.0,5.0,'2022-07-16'),
55+
('a','d','e','2022-07-17','d','e','a','c','b','e','d','a','c','b','b','b','d','a','e','e',9.0,7.0,1.0,6.0,6.0,'2022-07-17'),
56+
('e','d','a','2022-07-17','d','d','b','e','b','b','c','e','a','b','d','b','b','c','e','b',3.0,6.0,4.0,6.0,8.0,'2022-07-17');
57+
"""
3258
}
Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,78 @@
1+
with aa as (
2+
select
3+
referenceno ,
4+
checkdate,
5+
sum(rop_fee) rop_fee,
6+
sum(pop_fee) pop_fee
7+
from dws_railway_xnop drx2
8+
group by referenceno ,checkdate
9+
),
10+
bb as (
11+
select
12+
referenceno
13+
, checkdate
14+
, platform
15+
, oplev3_name
16+
, sum(rcamount) rcamount
17+
, sum(rcntaxamount) rcntaxamount
18+
, sum(pcamount) pcamount
19+
, sum(pcntaxamount) pcntaxamount
20+
from dws_railway_revenue drr
21+
where substr(drr.checkdate,1,7) between '2022-01' and '2022-07'
22+
and oplev1_name='铁运事业部'
23+
and oplev2_name='北区'
24+
and ordertypeid = 70
25+
group by referenceno, checkdate,platform, oplev3_name
26+
-- order by platform, oplev3_name
27+
),
28+
cc1 as (
29+
select
30+
referenceno
31+
-- , checkdate
32+
, platform
33+
from dws_railway_revenue drr
34+
where substr(drr.checkdate,1,7) between '2022-01' and '2022-07'
35+
and oplev1_name='铁运事业部'
36+
and oplev2_name='北区'
37+
and ordertypeid = 70
38+
and internalsettlementlinkid <> ''
39+
and salecompanyname = '成都供应链'
40+
),
41+
cc2 as (
42+
select
43+
distinct
44+
drr.*
45+
from dws_railway_revenue drr
46+
left join cc1 on drr.referenceno = cc1.referenceno and drr.platform = cc1.platform -- and drr.checkdate = cc1.checkdate
47+
where cc1.referenceno is not null
48+
--- group by referenceno, checkdate,platform, oplev2_name
49+
),
50+
cc as (
51+
select
52+
referenceno
53+
, checkdate
54+
, platform
55+
, oplev3_name
56+
, sum(rcamount) rcamount
57+
, sum(rcntaxamount) rcntaxamount
58+
, sum(pcamount) pcamount
59+
, sum(pcntaxamount) pcntaxamount
60+
from cc2
61+
group by referenceno, checkdate,platform, oplev3_name
62+
)
63+
select
64+
bb.platform
65+
, bb.oplev3_name
66+
, sum(bb.rcamount) rcamount
67+
, sum(bb.rcntaxamount) rcntaxamount
68+
, sum(bb.pcamount) pcamount
69+
, sum(bb.pcntaxamount) pcntaxamount
70+
, sum(bb.rcntaxamount)-sum(bb.pcntaxamount) profile
71+
, if(coalesce(sum(bb.rcntaxamount),0)=0,'', (sum(bb.rcntaxamount)-sum(bb.pcntaxamount))/sum(bb.rcntaxamount)) profile_rate
72+
, sum(aa.rop_fee) rop_fee
73+
, sum(cc.rcntaxamount)-sum(cc.pcntaxamount) product_profile
74+
from bb
75+
left join aa on bb.referenceno = aa.referenceno and bb.checkdate = aa.checkdate
76+
left join cc on bb.referenceno = cc.referenceno and bb.checkdate = cc.checkdate
77+
group by bb.platform, bb.oplev3_name
78+
order by bb.platform, bb.oplev3_name;

0 commit comments

Comments
 (0)