能成功创建 但是构建任务报错 提示如下
Input slot(s) not in child's output: DORIS_DELETE_SIGN#462 in plan: LogicalFilter[2677] ( predicates=(DORIS_DELETE_SIGN#462 = 0) )
child output is: [DORIS_DELETE_SIGN#455]
plan tree:
LogicalFilter[2677] ( predicates=(DORIS_DELETE_SIGN#462 = 0) )
+--LogicalProject[2508] ( distinct=false, projects=[DORIS_DELETE_SIGN#455], excepts=[] )
+--LogicalOlapScan ( qualified=internal.csc.erp_cus_customer_group, indexName=erp_cus_customer_group, selectedIndexId=669196, preAgg=ON )
使用的语句如下
CREATE MATERIALIZED VIEW tb_customer BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 10 MINUTE
DISTRIBUTED BY RANDOM BUCKETS 12
PROPERTIES (
'replication_num' = '3'
) AS
WITH RankedRecords AS (
WITH contact AS (
SELECT
erp_cus_customer_group.id,
MAX(erp_cus_customer_contact.enterprise_id) AS enterprise_id,
MAX(erp_cus_customer_contact.shop_id) AS shop_id,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.NAME != '' THEN erp_cus_customer_contact.NAME ELSE NULL END, '/' ) AS customerName,
MAX( CASE WHEN erp_cus_customer_group_relation_contact.main = 1 THEN erp_cus_customer_contact.mobile ELSE NULL END ) AS mainPhone,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.mobile != '' THEN erp_cus_customer_contact.mobile ELSE NULL END, '/' ) AS phone,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.wechat != '' THEN erp_cus_customer_contact.wechat ELSE NULL END, '/' ) AS weChat,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.sex = TRUE THEN '男' WHEN erp_cus_customer_contact.sex = FALSE THEN '女' ELSE NULL END, '/' ) AS customerSex,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.address != '' THEN erp_cus_customer_contact.address ELSE NULL END, '/' ) AS address,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.remark != '' THEN erp_cus_customer_contact.remark ELSE NULL END, '/' ) AS remarks,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.due_date > 0 THEN from_unixtime( erp_cus_customer_contact.due_date / 1000, '%Y-%m-%d' ) ELSE NULL END, '/' ) AS expectedDateChildbirth,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.birthday_time > 0 THEN from_unixtime( erp_cus_customer_contact.birthday_time / 1000, '%Y-%m-%d' ) ELSE NULL END, '/' ) AS customerBirthday,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.birthday_str != '' THEN erp_cus_customer_contact.birthday_str ELSE NULL END, '/' ) AS customerBirthLunarCalendar,
GROUP_CONCAT( CASE WHEN erp_cus_customer_contact.birthday_lunar = TRUE THEN '1' WHEN erp_cus_customer_contact.birthday_lunar = FALSE THEN '0' ELSE NULL END, '/' ) AS customerBirthIsLunarCalendar,
GROUP_CONCAT( CASE WHEN tb_member.totalCardBalance != '' THEN tb_member.totalCardBalance ELSE '0.00' END, '/' ) AS memberBalance,
GROUP_CONCAT( CASE WHEN tb_member.memberPoints != '' THEN tb_member.memberPoints ELSE '0' END, '/' ) AS memberIntegral
FROM
erp_cus_customer_group
INNER JOIN erp_cus_customer_group_relation_contact ON erp_cus_customer_group_relation_contact.group_id = erp_cus_customer_group.id
INNER JOIN erp_cus_customer_contact ON erp_cus_customer_contact.id = erp_cus_customer_group_relation_contact.contact_id
INNER JOIN erp_bs_enterprise ON erp_bs_enterprise.id = erp_cus_customer_contact.enterprise_id
LEFT JOIN tb_member ON tb_member.memberMobile = erp_cus_customer_contact.mobile AND tb_member.companyId = erp_bs_enterprise.enterprise_no
GROUP BY
erp_cus_customer_group.id
),
baby AS (
SELECT
erp_cus_customer_group.id,
GROUP_CONCAT( CASE WHEN erp_cus_customer_baby.NAME != '' THEN erp_cus_customer_baby.NAME ELSE NULL END, '/' ) AS babyName,
GROUP_CONCAT( CASE WHEN erp_cus_customer_baby.sex = TRUE THEN '男' WHEN erp_cus_customer_baby.sex = FALSE THEN '女' ELSE NULL END, '/' ) AS babySex,
GROUP_CONCAT( CASE WHEN erp_cus_customer_baby.birthday_time > 0 THEN from_unixtime( erp_cus_customer_baby.birthday_time / 1000, '%Y-%m-%d' ) ELSE NULL END, '/' ) AS babyBirthDay,
GROUP_CONCAT(
CASE
WHEN erp_cus_customer_baby.birthday_lunar = 0 AND erp_cus_customer_baby.birthday_time > 0 THEN
DAYS_SINCE_TIMESTAMP_BIRTHDAY(erp_cus_customer_baby.birthday_time)
WHEN erp_cus_customer_baby.birthday_lunar = 1 AND erp_cus_customer_baby.birthday_str != '' THEN
LUNAR_DAYS_SINCE_BIRTH(erp_cus_customer_baby.birthday_str)
ELSE '0'
END, '/'
) AS daysSinceBirth,
GROUP_CONCAT( CASE WHEN erp_cus_customer_baby.birthday_str != '' THEN erp_cus_customer_baby.birthday_str ELSE NULL END, '/' ) AS babyBirthLunarCalendar,
GROUP_CONCAT( CASE WHEN erp_cus_customer_baby.birthday_lunar = TRUE THEN '1' WHEN erp_cus_customer_baby.birthday_lunar = FALSE THEN '0' ELSE NULL END, '/' ) AS babyBirthIsLunarCalendar
FROM
erp_cus_customer_group
LEFT JOIN erp_cus_customer_baby ON erp_cus_customer_baby.group_id = erp_cus_customer_group.id
GROUP BY
erp_cus_customer_group.id
),
crmx_contact AS (
SELECT
crmx_customer_contact.customerId,
GROUP_CONCAT( CASE WHEN crmx_customer_contact.NAME != '' THEN crmx_customer_contact.NAME ELSE NULL END, '/' ) AS contactName,
GROUP_CONCAT( CASE WHEN crmx_customer_contact.phone != '' THEN crmx_customer_contact.phone ELSE NULL END, '/') AS contactPhone,
GROUP_CONCAT( CASE WHEN crmx_customer_contact.sex != '' && crmx_customer_contact.sex != '未知' THEN crmx_customer_contact.sex ELSE NULL END, '/' ) AS contactSex,
GROUP_CONCAT( CASE WHEN crmx_customer_contact.wxNumber != '' THEN crmx_customer_contact.wxNumber ELSE NULL END, '/' ) AS contactWxNumber
FROM
crmx_customer_contact
GROUP BY
crmx_customer_contact.customerId
)
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY mainPhone ORDER BY createTime DESC) AS rn
FROM (
SELECT
MD5(CONCAT('1MIEChHw7vJsSPEa', erp_cus_customer_group.id)) AS customerId,
erp_cus_customer_group.id AS appDataId,
FROM_UNIXTIME( MAX( erp_cus_customer_group.create_time ) / 1000 ) AS createTime,
contact.customerName,
contact.mainPhone,
contact.phone,
contact.weChat,
contact.customerSex,
contact.address,
contact.expectedDateChildbirth,
contact.customerBirthday,
contact.customerBirthLunarCalendar,
contact.customerBirthIsLunarCalendar,
'1MIEChHw7vJsSPEa' AS appId,
MAX( erp_bs_enterprise.enterprise_no ) AS companyId,
MAX( erp_bs_shop.shop_no ) AS shopId,
MAX( erp_cus_customer_group.origin_str ) AS customerSource,
baby.babyName,
baby.babySex,
baby.babyBirthDay,
baby.daysSinceBirth,
baby.babyBirthLunarCalendar,
baby.babyBirthIsLunarCalendar,
MAX(erp_st_order.id) AS orderId,
MAX(erp_st_order.order_no) AS orderNo,
contact.memberBalance,
contact.memberIntegral,
IFNULL(MAX(mainOrderNum.orderNum), 0) AS orderNum,
IFNULL(MAX(subOrderNum.orderNum), 0) AS orderItemNum,
IFNULL(MAX(tb_completed_num.completedNum), 0) AS completedNum,
IFNULL(MAX(tb_photo_num.photoNum), 0) AS photosTakenNum
FROM
erp_cus_customer_group
INNER JOIN contact ON contact.id = erp_cus_customer_group.id
INNER JOIN erp_bs_enterprise ON erp_bs_enterprise.id = contact.enterprise_id
INNER JOIN erp_bs_shop ON erp_bs_shop.id = contact.shop_id
INNER JOIN erp_st_order ON erp_st_order.customer_group_id = erp_cus_customer_group.id
LEFT JOIN baby ON baby.id = erp_cus_customer_group.id
LEFT JOIN tb_order_num mainOrderNum ON mainOrderNum.phone = contact.mainPhone AND mainOrderNum.type = 1 AND mainOrderNum.companyId = erp_bs_enterprise.enterprise_no
LEFT JOIN tb_order_num subOrderNum ON subOrderNum.phone = contact.mainPhone AND subOrderNum.type = 2 AND subOrderNum.companyId = erp_bs_enterprise.enterprise_no
LEFT JOIN tb_completed_num ON tb_completed_num.phone = contact.mainPhone AND tb_completed_num.companyId = erp_bs_enterprise.enterprise_no
LEFT JOIN tb_photo_num ON tb_photo_num.phone = contact.mainPhone AND tb_photo_num.companyId = erp_bs_enterprise.enterprise_no
GROUP BY
erp_cus_customer_group.id,
contact.customerName,
contact.mainPhone,
contact.phone,
contact.weChat,
contact.customerSex,
contact.address,
contact.remarks,
contact.expectedDateChildbirth,
contact.customerBirthday,
contact.customerBirthLunarCalendar,
contact.customerBirthIsLunarCalendar,
contact.memberBalance,
contact.memberIntegral,
baby.babyName,
baby.babySex,
baby.babyBirthDay,
baby.babyBirthLunarCalendar,
baby.babyBirthIsLunarCalendar,
baby.daysSinceBirth
UNION ALL
SELECT
MD5(CONCAT('YvkfqGi3fh3pICMK', crmx_customer.customerId)) AS customerId,
crmx_customer.customerId AS appDataId,
crmx_customer.createTime AS createTime,
CONCAT(crmx_customer.NAME, CASE WHEN crmx_contact.contactName IS NOT NULL AND crmx_contact.contactName != '' THEN CONCAT('/', crmx_contact.contactName) ELSE '' END) AS customerName,
crmx_customer.phone AS mainPhone,
CONCAT(crmx_customer.phone, CASE WHEN crmx_contact.contactPhone IS NOT NULL AND crmx_contact.contactPhone != '' THEN CONCAT('/', crmx_contact.contactPhone) ELSE '' END) AS phone,
CONCAT(crmx_customer.wxNumber, CASE WHEN crmx_contact.contactWxNumber IS NOT NULL AND crmx_contact.contactWxNumber != '' THEN CONCAT('/', crmx_contact.contactWxNumber) ELSE '' END) AS weChat,
CONCAT(crmx_customer.sex, CASE WHEN crmx_contact.contactSex IS NOT NULL AND crmx_contact.contactSex != '' THEN CONCAT('/', crmx_contact.contactSex) ELSE '' END) AS customerSex,
crmx_customer.address AS address,
crmx_customer.dueDate AS expectedDateChildbirth,
crmx_customer.birthdayTime AS customerBirthday,
'' AS customerBirthLunarCalendar,
'0' AS customerBirthIsLunarCalendar,
'YvkfqGi3fh3pICMK' AS appId,
crmx_company.ccId AS companyId,
crmx_customer.store AS shopId,
CASE
WHEN company_center_channel.channelName IS NULL THEN company_center_source.sourceName
WHEN company_center_source.sourceName IS NULL THEN CONCAT(
CASE company_center_channel.type
WHEN 1 THEN '网络推广'
WHEN 2 THEN '客资深挖'
WHEN 3 THEN '内部转介绍渠道'
WHEN 4 THEN '外部转介绍渠道'
WHEN 5 THEN '爱婴渠道'
WHEN 6 THEN '门店渠道'
END
,'/'
,company_center_channel.channelName
)
ELSE CONCAT(
CASE company_center_channel.type
WHEN 1 THEN '网络推广'
WHEN 2 THEN '客资深挖'
WHEN 3 THEN '内部转介绍渠道'
WHEN 4 THEN '外部转介绍渠道'
WHEN 5 THEN '爱婴渠道'
WHEN 6 THEN '门店渠道'
END
,'/'
,company_center_channel.channelName
,'/'
,company_center_source.sourceName
)
END AS customerSource,
crmx_customer.babyName,
crmx_customer.babySex,
crmx_customer.babyBirthday AS babyBirthDay,
DATE_DIFF(crmx_customer.babyBirthday) AS daysSinceBirth,
'' AS babyBirthLunarCalendar,
'0' AS babyBirthIsLunarCalendar,
'' AS orderId,
crmx_customer.contractNumber AS orderNo,
IFNULL(tb_member.totalCardBalance, '0.00') AS memberBalance,
IFNULL(tb_member.memberPoints, '0') AS memberIntegral,
IFNULL(MAX(mainOrderNum.orderNum), 0) AS orderNum,
IFNULL(MAX(subOrderNum.orderNum), 0) AS orderItemNum,
IFNULL(MAX(tb_completed_num.completedNum), 0) AS completedNum,
IFNULL(MAX(tb_photo_num.photoNum), 0) AS photosTakenNum
FROM
crmx_customer
INNER JOIN crmx_company ON crmx_company.companyId = crmx_customer.companyId
LEFT JOIN crmx_contact ON crmx_contact.customerId = crmx_customer.customerId
LEFT JOIN company_center_source ON company_center_source.sourceId = crmx_customer.sid
LEFT JOIN company_center_channel ON company_center_channel.channelId = crmx_customer.cid
LEFT JOIN tb_member ON tb_member.memberMobile = crmx_customer.phone AND tb_member.companyId = crmx_company.ccId
LEFT JOIN tb_order_num mainOrderNum ON mainOrderNum.phone = crmx_customer.phone AND mainOrderNum.type = 1 AND mainOrderNum.companyId = crmx_company.ccId
LEFT JOIN tb_order_num subOrderNum ON subOrderNum.phone = crmx_customer.phone AND subOrderNum.type = 2 AND subOrderNum.companyId = crmx_company.ccId
LEFT JOIN tb_completed_num ON tb_completed_num.phone = crmx_customer.phone AND tb_completed_num.companyId = crmx_company.ccId
LEFT JOIN tb_photo_num ON tb_photo_num.phone = crmx_customer.phone AND tb_photo_num.companyId = crmx_company.ccId
GROUP BY
crmx_customer.customerId,
crmx_customer.customerId,
crmx_customer.createTime,
crmx_customer.NAME,
crmx_customer.phone,
crmx_customer.wxNumber,
crmx_customer.sex,
crmx_customer.address,
crmx_customer.dueDate,
crmx_customer.birthdayTime,
crmx_customer.babyName,
crmx_customer.babySex,
crmx_customer.babyBirthday,
crmx_customer.contractNumber,
crmx_customer.store,
crmx_contact.contactName,
crmx_contact.contactPhone,
crmx_contact.contactWxNumber,
crmx_contact.contactSex,
crmx_company.ccId,
company_center_channel.channelName,
company_center_channel.type,
company_center_source.sourceName,
tb_member.totalCardBalance,
tb_member.memberPoints
) AS combined_results
)
SELECT *
FROM RankedRecords
WHERE rn = 1;
