doris版本2.1.6,异步物化视图 不支持窗口函数吗?

Viewed 19

能成功创建 但是构建任务报错 提示如下

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;
0 Answers