Skip to content

Commit 4ae5fa4

Browse files
committed
add TB case (apache#29)
1 parent ed1824b commit 4ae5fa4

9 files changed

Lines changed: 1695 additions & 0 deletions

File tree

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+
-- !q01 --
3+
0
4+
Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
CREATE TABLE `dim_date_tag_real`
2+
(
3+
`calday` varchar(45) NULL COMMENT "查看日期",
4+
`date_tag` varchar(45) NULL COMMENT "日期标签",
5+
`date_sort` varchar(45) NULL COMMENT "日期趋势字段",
6+
`date_range` varchar(90) NULL COMMENT "日期标签时间范围",
7+
`date_range_tq` varchar(90) NULL COMMENT "日期标签同期时间范围",
8+
`date_s` datetime NULL COMMENT "日期时点开始范围",
9+
`date_e` datetime NULL COMMENT "日期时点开始范围",
10+
`date_s_tq` datetime NULL COMMENT "同期日期时点开始范围",
11+
`date_e_tq` datetime NULL COMMENT "同期日期时点开始范围",
12+
`date_recall_s` datetime NULL COMMENT "回溯日期开始范围",
13+
`date_recall_e` datetime NULL COMMENT "回溯日期结束范围",
14+
`date_recall_s_tq` datetime NULL COMMENT "同期回溯日期开始范围",
15+
`date_recall_e_tq` datetime NULL COMMENT "同期回溯日期结束范围"
16+
) ENGINE=OLAP
17+
DUPLICATE KEY(`calday`)
18+
COMMENT "日期标签表"
19+
DISTRIBUTED BY HASH(`calday`) BUCKETS 1
20+
PROPERTIES (
21+
"replication_allocation" = "tag.location.default: 1",
22+
"in_memory" = "true",
23+
"storage_format" = "V2"
24+
);
Lines changed: 110 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,110 @@
1+
CREATE TABLE `dim_skc_info`
2+
(
3+
`skc_code` varchar(150) NULL COMMENT "SKC编码",
4+
`skc_desc` varchar(150) NULL COMMENT "skc描述",
5+
`item_code` varchar(150) NULL COMMENT "款号",
6+
`color_name` varchar(300) NULL COMMENT "颜色",
7+
`color_code` varchar(60) NULL COMMENT "颜色编码",
8+
`brand_id` varchar(600) NULL COMMENT "品牌id",
9+
`brand_code` varchar(600) NULL COMMENT "品牌编码",
10+
`brand_name` varchar(600) NULL COMMENT "品牌名称",
11+
`product_year` varchar(30) NULL COMMENT "产品年份",
12+
`season` varchar(30) NULL COMMENT "产品季节",
13+
`product_season` varchar(30) NULL COMMENT "产品季",
14+
`category_id` varchar(60) NULL COMMENT "商品大类id",
15+
`category_code` varchar(60) NULL COMMENT "商品大类编码",
16+
`category_name` varchar(60) NULL COMMENT "商品大类",
17+
`m_name` varchar(600) NULL COMMENT "商品中类",
18+
`c_name` varchar(600) NULL COMMENT "商品类型",
19+
`product_line` varchar(600) NULL COMMENT "产品线",
20+
`product_line_name` varchar(600) NULL COMMENT "产品线名称",
21+
`category_m_name` varchar(60) NULL COMMENT "品类名称",
22+
`series` varchar(600) NULL COMMENT "系列",
23+
`series_name` varchar(600) NULL COMMENT "系列名称",
24+
`theme` varchar(600) NULL COMMENT "主题",
25+
`theme_name` varchar(600) NULL COMMENT "主题名称",
26+
`style_code` varchar(600) NULL COMMENT "款式编码",
27+
`style_name` varchar(600) NULL COMMENT "商品名称",
28+
`style_name_set` varchar(600) NULL COMMENT "品名归集",
29+
`sale_category_name` varchar(300) NULL COMMENT "品类名称冗余字段",
30+
`sale_middle_type` varchar(600) NULL COMMENT "销售中类",
31+
`developcode` varchar(150) NULL COMMENT "开发款号",
32+
`jpg` varchar(200) NULL COMMENT "图片网址",
33+
`tag_price` decimal(8, 2) NULL COMMENT "吊牌价",
34+
`tag_price_usd` decimal(8, 2) NULL COMMENT "吊牌价(美元)",
35+
`tag_price_hkd` decimal(8, 2) NULL COMMENT "吊牌价(港币)",
36+
`tag_price_twd` decimal(8, 2) NULL COMMENT "吊牌价(新台币)",
37+
`price_region` varchar(60) NULL COMMENT "价格区间段",
38+
`dingdanchengbenjia` varchar(45) NULL COMMENT "订单成本价",
39+
`shangshiboduan_nan` varchar(45) NULL COMMENT "上市日期(南)",
40+
`shangshiboduan_bei` varchar(45) NULL COMMENT "上市日期(北)",
41+
`kaifayanse` varchar(100) NULL COMMENT "开发颜色",
42+
`colortypename` varchar(300) NULL COMMENT "颜色类别名称",
43+
`sex` varchar(600) NULL COMMENT "性别",
44+
`ul_garments_name` varchar(600) NULL COMMENT "服装名称",
45+
`style_version_name` varchar(600) NULL COMMENT "款式版本名称",
46+
`needle_weaving_name` varchar(600) NULL COMMENT "织针名称",
47+
`clothing_length` varchar(90) NULL COMMENT "衣长",
48+
`thickness` varchar(90) NULL COMMENT "厚度",
49+
`kuanshimiaoshu` varchar(6000) NULL COMMENT "款式描述",
50+
`material_science` varchar(765) NULL COMMENT "材料",
51+
`caizhi` varchar(765) NULL COMMENT "材质",
52+
`mianliaofengge` varchar(45) NULL COMMENT "面料风格",
53+
`knitwear_weight` varchar(45) NULL COMMENT "服装重量",
54+
`weight` varchar(6000) NULL COMMENT "重量",
55+
`is_pop` varchar(45) NULL COMMENT "是否POP款",
56+
`banshi` varchar(45) NULL COMMENT "版式",
57+
`pro_level` varchar(600) NULL COMMENT "推广级别",
58+
`dinghuokejidiaopaishiyong` varchar(45) NULL COMMENT "",
59+
`shejishi` varchar(45) NULL COMMENT "设计师",
60+
`gushibao` varchar(6000) NULL COMMENT "故事包",
61+
`gushibaoxilie` varchar(45) NULL COMMENT "故事包系列",
62+
`gushibaojibie` varchar(45) NULL COMMENT "故事包级别",
63+
`en_name` varchar(6000) NULL COMMENT "英文名",
64+
`classify` varchar(300) NULL COMMENT "年龄分段(成人,儿童)",
65+
`abroadcolor` varchar(6000) NULL COMMENT "外部颜色",
66+
`XSQY` varchar(300) NULL COMMENT "销售区域",
67+
`prod_level` varchar(300) NULL COMMENT "产品层级",
68+
`launch_month_c` varchar(300) NULL COMMENT "上市月份(中)",
69+
`ProjectType` varchar(45) NULL COMMENT "延续类型",
70+
`is_sale` varchar(300) NULL COMMENT "是否非卖品",
71+
`agegroup` varchar(33) NULL COMMENT "年龄段",
72+
`agegroup_desc` varchar(30) NULL COMMENT "年龄段描述",
73+
`status` int(11) NULL COMMENT "状态",
74+
`updatetime` date NULL COMMENT "修改时间",
75+
`short_and_long_clothes` varchar(600) NULL COMMENT "长短衣",
76+
`cloth_category` varchar(150) NULL COMMENT "布种分类",
77+
`story_pack_warm` varchar(3000) NULL COMMENT "故事包温带",
78+
`story_pack_hot` varchar(6000) NULL COMMENT "故事包热带",
79+
`story_pack_cold` varchar(6000) NULL COMMENT "故事包寒带",
80+
`story_pack_nuan` varchar(6000) NULL COMMENT "故事包暖带",
81+
`story_pack` varchar(6000) NULL COMMENT "故事包",
82+
`style_channel` varchar(300) NULL COMMENT "渠道",
83+
`listing_date_country` varchar(6000) NULL COMMENT "全国上市日期",
84+
`listing_date` date NULL COMMENT "商品季上市日期",
85+
`zixilie` varchar(6000) NULL COMMENT "子系列",
86+
`is_changqing` varchar(300) NULL COMMENT "是否常青",
87+
`internal_style_code` varchar(150) NULL COMMENT "内部码",
88+
`itemchannel` varchar(100) NULL COMMENT "商品渠道",
89+
`itemclass` varchar(100) NULL COMMENT "商品类型",
90+
`MaterialClass` varchar(6000) NULL COMMENT "面料分类",
91+
`is_nosale` varchar(10) NULL COMMENT "是否非卖品",
92+
`ShoeHeight` varchar(10) NULL COMMENT "帮高",
93+
`local_name` varchar(30) NULL COMMENT "中文名",
94+
`color_cscheme` varchar(100) NULL COMMENT "色系",
95+
`color_colorInclass` varchar(100) NULL COMMENT "颜色中类",
96+
`thick_and_thin_clothes` varchar(100) NULL COMMENT "厚薄装",
97+
`channel` varchar(100) NULL COMMENT "渠道",
98+
`is_brand_drive` varchar(100) NULL COMMENT "品牌驱动",
99+
`is_business_drive` varchar(100) NULL COMMENT "生意驱动",
100+
`product_season_tq` varchar(30) NULL COMMENT "用于统计同期产品季使用",
101+
`ifcn_name` varchar(300) NULL COMMENT "是否中国开发"
102+
) ENGINE=OLAP
103+
UNIQUE KEY(`skc_code`)
104+
COMMENT "skc主数据"
105+
DISTRIBUTED BY HASH(`skc_code`) BUCKETS 1
106+
PROPERTIES (
107+
"replication_allocation" = "tag.location.default: 1",
108+
"in_memory" = "false",
109+
"storage_format" = "V2"
110+
);
Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,83 @@
1+
CREATE TABLE `dwb_ret_sales_ds`
2+
(
3+
`calyear` smallint(6) NOT NULL COMMENT "销售年",
4+
`calmonth` int(11) NOT NULL COMMENT "销售月",
5+
`calday` date NOT NULL COMMENT "销售日期",
6+
`report_date` datetime NOT NULL COMMENT "销售时间",
7+
`calyear1` smallint(6) NULL COMMENT "真实仓库出库年",
8+
`calmonth1` int(11) NULL COMMENT "真实仓库出库月",
9+
`calday1` date NULL COMMENT "真实仓库出库日期",
10+
`shipping_time_ck` datetime NULL COMMENT "真实仓库出库时间",
11+
`shipping_time_tzph` datetime NULL COMMENT "通知配货时间",
12+
`order_status` tinyint(4) NULL DEFAULT "0" COMMENT "订单状态:0-未确认;1-已确认; ",
13+
`deal_code` varchar(500) NULL COMMENT "订单交易号",
14+
`ticket_id` varchar(20) NULL COMMENT "小票id",
15+
`qudao_leixing` varchar(30) NULL COMMENT "渠道类型",
16+
`region_name` varchar(360) NULL DEFAULT "" COMMENT "收货城市",
17+
`province_name` varchar(100) NULL DEFAULT "" COMMENT "收货省份",
18+
`sku` varchar(130) NULL COMMENT "商品-款色码",
19+
`skc` varchar(80) NULL COMMENT "商品-款色",
20+
`source_type` int(11) NULL COMMENT "来源单据类型",
21+
`ticket_no` int(11) NULL COMMENT "单据编号",
22+
`shop_id` int(11) NULL COMMENT "门店id",
23+
`shop_code` varchar(60) NULL COMMENT "门店编码",
24+
`item_code` varchar(60) NULL COMMENT "商品编码",
25+
`color_code` varchar(20) NULL COMMENT "颜色编码",
26+
`size_code` varchar(50) NULL COMMENT "商品尺码",
27+
`saleman_id` int(11) NULL COMMENT "营业员id",
28+
`user_id` int(11) NULL COMMENT "(user_id of users)会员ID",
29+
`user_name` varchar(500) NULL COMMENT "会员昵称",
30+
`warehouse_id` int(11) NULL COMMENT "仓库id",
31+
`series` varchar(600) NULL COMMENT "系列",
32+
`brand_id` varchar(96) NULL COMMENT "品牌id",
33+
`brand_code` varchar(96) NULL COMMENT "品牌编码",
34+
`is_new_item` varchar(15) NULL COMMENT "新旧品",
35+
`is_hh` tinyint(4) NULL COMMENT "换货单{0:否;1:是}",
36+
`is_copy` tinyint(4) NULL COMMENT "是否是复制订单",
37+
`is_combine_new` tinyint(4) NULL COMMENT "是否为合并产生的新订单",
38+
`is_split_new` tinyint(4) NULL COMMENT "是否为拆分产生的新订单",
39+
`shop_name` varchar(300) NULL COMMENT "门店名称",
40+
`qd_id` int(11) NULL COMMENT "渠道id",
41+
`qd` varchar(255) NULL COMMENT "渠道(平台)",
42+
`bg` varchar(255) NULL COMMENT "业务组别",
43+
`area` varchar(765) NULL COMMENT "BI大区",
44+
`khmc` varchar(300) NULL DEFAULT "" COMMENT "客户名称",
45+
`warehouse_name` varchar(100) NULL COMMENT "仓库名称",
46+
`distributor_price` decimal(12, 2) NULL COMMENT "分销价",
47+
`receiver_district` varchar(360) NULL DEFAULT "" COMMENT "收货地区",
48+
`ckxz` bigint(20) NULL DEFAULT "0" COMMENT "仓库性质",
49+
`pt_type` bigint(20) NULL DEFAULT "0" COMMENT "仓库类型(1电商仓 2分公司仓 3门店仓)",
50+
`size_name` varchar(150) NULL COMMENT "尺码名称",
51+
`is_yugou` int(11) NULL COMMENT "预售订单 0-否 1-是",
52+
`pay_status` tinyint(4) NULL DEFAULT "0" COMMENT "付款状态[0:未付款;1:付款中(部分付款);2:已付款]",
53+
`share_discount_fee` decimal(10, 2) NULL DEFAULT "0.00" COMMENT "平摊优惠金额",
54+
`share_shipping_fee` decimal(10, 2) NULL DEFAULT "0.00" COMMENT "平均物流成本",
55+
`bi_store` varchar(765) NULL COMMENT "BI店铺",
56+
`khjc` varchar(150) NULL DEFAULT "" COMMENT "客户简称",
57+
`qdmc` varchar(300) NULL DEFAULT "" COMMENT "渠道名称",
58+
`cost` decimal(19, 5) SUM NULL DEFAULT "0.0000" COMMENT "成本",
59+
`source_type_name` varchar(600) REPLACE NULL COMMENT "来源单据名称",
60+
`series_name` varchar(600) REPLACE NULL COMMENT "系列名称",
61+
`brand_name` varchar(96) REPLACE NULL COMMENT "品牌名称",
62+
`label_price` decimal(18, 6) REPLACE NOT NULL COMMENT "吊牌价",
63+
`djs` bitmap BITMAP_UNION NULL COMMENT "单据数",
64+
`quantity_total` decimal(18, 2) SUM NULL COMMENT "销售数量(退货前)",
65+
`label_money_total` decimal(18, 6) SUM NULL COMMENT "销售吊牌额(退货前)",
66+
`receivable_money_total` decimal(18, 6) SUM NULL COMMENT "销售金额(退货前)",
67+
`quantity_return` decimal(18, 2) SUM NULL COMMENT "退货数量",
68+
`label_money_return` decimal(18, 6) SUM NULL COMMENT "退货吊牌额",
69+
`receivable_money_return` decimal(18, 6) SUM NULL COMMENT "退货金额",
70+
`quantity` decimal(18, 2) SUM NULL COMMENT "销售数量",
71+
`label_money` decimal(18, 6) SUM NULL COMMENT "销售吊牌",
72+
`receivable_money` decimal(18, 6) SUM NULL COMMENT "销售金额",
73+
INDEX ticket_id (`ticket_id`) USING BITMAP COMMENT '小票id'
74+
) ENGINE=OLAP
75+
AGGREGATE KEY(`calyear`, `calmonth`, `calday`, `report_date`, `calyear1`, `calmonth1`, `calday1`, `shipping_time_ck`, `shipping_time_tzph`, `order_status`, `deal_code`, `ticket_id`, `qudao_leixing`, `region_name`, `province_name`, `sku`, `skc`, `source_type`, `ticket_no`, `shop_id`, `shop_code`, `item_code`, `color_code`, `size_code`, `saleman_id`, `user_id`, `user_name`, `warehouse_id`, `series`, `brand_id`, `brand_code`, `is_new_item`, `is_hh`, `is_copy`, `is_combine_new`, `is_split_new`, `shop_name`, `qd_id`, `qd`, `bg`, `area`, `khmc`, `warehouse_name`, `distributor_price`, `receiver_district`, `ckxz`, `pt_type`, `size_name`, `is_yugou`, `pay_status`, `share_discount_fee`, `share_shipping_fee`, `bi_store`, `khjc`, `qdmc`)
76+
COMMENT "电商流水明细表"
77+
DISTRIBUTED BY HASH(`ticket_id`) BUCKETS 10
78+
PROPERTIES (
79+
"replication_allocation" = "tag.location.default: 1",
80+
"bloom_filter_columns" = "ticket_id",
81+
"in_memory" = "true",
82+
"storage_format" = "V2"
83+
);

0 commit comments

Comments
 (0)