版本是2.1.3
建表语句是这样的
drop table if exists dim.dim_pub_product_suit_pack;
CREATE TABLE if not exists dim.dim_pub_product_suit_pack (
`key_id` bigint not null auto_increment comment '自增主键',
`product_id` VARCHAR(36) NULL COMMENT ,
`suit_id` VARCHAR(36) NULL COMMENT ,
`package_id` VARCHAR(36) NULL COMMENT ,
`product_code` VARCHAR(40) NULL COMMENT ,
`product_name` VARCHAR(512) NULL COMMENT ,
`product_state` VARCHAR(10) NULL COMMENT ,
`suit_code` VARCHAR(40) NULL COMMENT ,
`suit_name` VARCHAR(512) NULL COMMENT ,
`suit_num` DECIMAL(18, 2) NULL COMMENT,
`pack_code` VARCHAR(40) NULL COMMENT ,
`pack_name` VARCHAR(512) NULL COMMENT ,
`emall_flag` char(1) NULL COMMENT ,
`pack_num` decimal(18,2) NULL COMMENT ,
`purchase_price` decimal(18,2) NULL COMMENT ,
`supplier_id` VARCHAR(40) NULL COMMENT ,
`supplier_code` VARCHAR(40) NULL COMMENT ,
`supplier_name` VARCHAR(512) NULL COMMENT ,
`data_version` bigint(20) null comment '数据版本',
`delete_flag` CHAR(1) NOT NULL DEFAULT "N" COMMENT '是否删除',
`date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`created_by` INT NOT NULL DEFAULT "0" COMMENT '创建人',
`date_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`updated_by` INT NOT NULL DEFAULT "0" COMMENT '修改人'
) ENGINE=OLAP
UNIQUE KEY(`key_id`)
COMMENT '电商-产品包套件关系表'
DISTRIBUTED BY HASH(`key_id`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
插入脚本
insert into dim.dim_pub_product_suit_pack
(
product_id
,suit_id
,package_id
,product_code
,product_name
,product_state
,suit_code
,suit_name
,suit_num
,pack_code
,pack_name
,emall_flag
,pack_num
)
with base_product_info as (
SELECT
t1.id as product_id,
t1.is_product_suite,
t1.is_product_pack,
t1.is_sale_product,
t1.code,
t1.description,
t1.pro_state,
t1.is_elec_businiess
FROM
ods.ods_ec_base_product_info t1
left join (
select
tt1.product_code
from
ods.ods_ec_conf_goods_product tt1
where dr=0
group by tt1.product_code
) as t2 on t1.code=t2.product_code
where t1.status=1
and t1.dr=0
and (t1.is_elec_businiess=1 OR t2.product_code is not null)
)
SELECT
v.product_id as product_id,
v.suit_id as suit_id,
v.pack_id as pack_id,
v.product_code as product_code,
v.product_name as product_name,
v.product_state as product_state,
v.suit_code as suit_code,
v.suit_name as suit_name,
v.suit_num as suit_num,
v.pack_code as pack_code,
v.pack_name as pack_name,
if(is_elec_businiess=1,'Y','N') as emall_flag,
v.pack_num as pack_num
FROM (
select
b.product_id product_id,
b.code product_code,
b.description product_name,
b.pro_state product_state,
d.product_id suit_id,
d.code suit_code,
d.description suit_name,
c.child_num suit_num,
g.product_id pack_id,
g.code pack_code,
g.description pack_name,
g.is_elec_businiess,
f.child_num * c.child_num pack_num
from ods.ods_ec_base_product_bom a
left join base_product_info b on b.product_id = a.product_info_id
left join ods.ods_mp_base_product_bom_sub c on c.product_bom_id = a.id
left join base_product_info d on d.product_id = c.product_info_id
left join ods.ods_ec_base_product_bom e on e.product_info_id = c.product_info_id
left join ods.ods_mp_base_product_bom_sub f on f.product_bom_id = e.id and e.id is not null
left join base_product_info g on g.product_id = f.product_info_id
where a.bom_type = '1'
and a.is_valid = '1'
and e.bom_type = '2'
and e.is_valid = '1'
and b.is_product_suite = '0'
and b.is_product_pack = '0'
and b.is_sale_product = '1'
and d.is_product_pack = '0'
and c.dr = '0'
and f.dr = '0'
and a.dr = '0'
) v
;
执行结果不稳定，查询的表的数据没有变化 都是在凌晨的时候更新的
单独执行select的查询结果，比较稳定。直接导出明细也是跟查询数量对得上。insert的时候会有问题
修改了一下表 改成了明细模型
drop table if exists dim.dim_pub_product_suit_pack;
CREATE TABLE if not exists dim.dim_pub_product_suit_pack (
`product_id` VARCHAR(36) NULL ,
`suit_id` VARCHAR(36) NULL ,
`package_id` VARCHAR(36) NULL ,
`product_code` VARCHAR(40) NULL ,
`product_name` VARCHAR(512) NULL ,
`product_state` VARCHAR(10) NULL ,
`suit_code` VARCHAR(40) NULL ,
`suit_name` VARCHAR(512) NULL ,
`suit_num` DECIMAL(18, 2) NULL ,
`pack_code` VARCHAR(40) NULL ,
`pack_name` VARCHAR(512) NULL ,
`emall_flag` char(1) NULL ,
`pack_num` decimal(18,2) NULL ,
`purchase_price` decimal(18,2) NULL ,
`supplier_id` VARCHAR(40) NULL ,
`supplier_code` VARCHAR(40) NULL ,
`supplier_name` VARCHAR(512) NULL ,
`data_version` bigint(20) null comment '数据版本'
)
COMMENT '电商-产品包套件关系表'
DISTRIBUTED BY HASH(`product_id`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
insert into dim.dim_pub_product_suit_pack
(
product_id
,suit_id
,package_id
,product_code
,product_name
,product_state
,suit_code
,suit_name
,suit_num
,pack_code
,pack_name
,emall_flag
,pack_num
)
with base_product_info as (
SELECT
t1.id as product_id,
t1.is_product_suite,
t1.is_product_pack,
t1.is_sale_product,
t1.code,
t1.description,
t1.pro_state,
t1.is_elec_businiess
FROM
ods.ods_ec_base_product_info t1
left join (
select
tt1.product_code
from
ods.ods_ec_conf_goods_product tt1
where dr=0
group by tt1.product_code
) as t2 on t1.code=t2.product_code
where t1.status=1
and t1.dr=0
and (t1.is_elec_businiess=1 OR t2.product_code is not null)
)
SELECT
v.product_id as product_id,
v.suit_id as suit_id,
v.pack_id as pack_id,
v.product_code as product_code,
v.product_name as product_name,
v.product_state as product_state,
v.suit_code as suit_code,
v.suit_name as suit_name,
v.suit_num as suit_num,
v.pack_code as pack_code,
v.pack_name as pack_name,
if(is_elec_businiess=1,'Y','N') as emall_flag,
v.pack_num as pack_num
FROM (
select
b.product_id product_id,
b.code product_code,
b.description product_name,
b.pro_state product_state,
d.product_id suit_id,
d.code suit_code,
d.description suit_name,
c.child_num suit_num,
g.product_id pack_id,
g.code pack_code,
g.description pack_name,
g.is_elec_businiess,
f.child_num * c.child_num pack_num
from ods.ods_ec_base_product_bom a
left join base_product_info b on b.product_id = a.product_info_id
left join ods.ods_mp_base_product_bom_sub c on c.product_bom_id = a.id
left join base_product_info d on d.product_id = c.product_info_id
left join ods.ods_ec_base_product_bom e on e.product_info_id = c.product_info_id
left join ods.ods_mp_base_product_bom_sub f on f.product_bom_id = e.id and e.id is not null
left join base_product_info g on g.product_id = f.product_info_id
where a.bom_type = '1'
and a.is_valid = '1'
and e.bom_type = '2'
and e.is_valid = '1'
and b.is_product_suite = '0'
and b.is_product_pack = '0'
and b.is_sale_product = '1'
and d.is_product_pack = '0'
and c.dr = '0'
and f.dr = '0'
and a.dr = '0'
) v
;
执行插入的结果依旧不稳定
直接运行select 查询总数
更奇怪的是 插入的数据有null值
但是实际执行查询的时候 这个null值字段是有值的
