基于DB2的字符串处理：构建客户最优推荐服务引擎

算力阿峰

2026-6-30

客户偏好服务推荐SQL优化需求

原表结构及数据

Customer ID	PREFERRED_SERVICE
aaaa	P2P_TRANSFER_ONNET
bbbb	P2P_TRANSFER_OFFNET,BILL_PAY
cccc	P2P_TRANSFER_OFFNET
dddd	MERCHANT_PAY_ONNET
eeee	MERCHANT_PAY_ONNET,P2P_TRANSFER_ONNET
ffff	BILL_PAY
gggg	P2P_TRANSFER_OFFNET,BILL_PAY
hhhh	MERCHANT_PAY_ONNET,P2P_TRANSFER_ONNET, GOVT_PAY

编写的SQL语句

select 
distinct customer_id,
CASE
   WHEN  UPPER(PREFERRED_SERVICE) LIKE '%P2P_TRANSFER_ONNET%' THEN 'BILL_PAY/MERCHANT_PAY_ONNET'
   WHEN  UPPER(PREFERRED_SERVICE) LIKE '%MERCHANT_PAY_ONNET%' THEN 'P2P_TRANSFER_ONNET/BILL_PAY'
   WHEN  UPPER(PREFERRED_SERVICE) LIKE '%MERCHANT_PAY_ONNET,P2P_TRANSFER_ONNET%' THEN 'PAY BILL'
   ELSE 'PAY BILL/P2P AM/MERCHPAY_AM'
END NEXT_BEST_OFFER
from Table_name_a

期望输出结果

Customer ID	PREFERRED_SERVICE	NEXT_BEST_OFFER
aaaa	P2P_TRANSFER_ONNET	BILL_PAY/MERCHANT_PAY_ONNET
bbbb	P2P_TRANSFER_OFFNET,BILL_PAY	P2P_TRANSFER_ONNET/MERCHANT_PAY_ONNET
cccc	P2P_TRANSFER_OFFNET	P2P_TRANSFER_ONNET/MERCHANT_PAY_ONNET/BILL_PAY
dddd	MERCHANT_PAY_ONNET	P2P_TRANSFER_ONNET/BILL_PAY
eeee	MERCHANT_PAY_ONNET,P2P_TRANSFER_ONNET	BILL_PAY
ffff	BILL_PAY	P2P_TRANSFER_ONNET/MERCHANT_PAY_ONNET
gggg	P2P_TRANSFER_OFFNET,BILL_PAY	P2P_TRANSFER_ONNET/MERCHANT_PAY_ONNET
hhhh	MERCHANT_PAY_ONNET,P2P_TRANSFER_ONNET, GOVT_PAY	BILL_PAY

推荐规则说明

规则1：当PREFERRED_SERVICE仅包含P2P_TRANSFER_ONNET时，推送BILL_PAY/MERCHANT_PAY_ONNET
规则2：当PREFERRED_SERVICE仅包含MERCHANT_PAY_ONNET时，推送P2P_TRANSFER_ONNET/BILL_PAY
规则3：当PREFERRED_SERVICE同时包含MERCHANT_PAY_ONNET和P2P_TRANSFER_ONNET（顺序不限）时，推送BILL_PAY
规则4：当PREFERRED_SERVICE包含P2P_TRANSFER_ONNET以及任何非MERCHANT_PAY_ONNET的服务时，推送MERCHANT_PAY_ONNET/BILL_PAY
规则5：当PREFERRED_SERVICE包含MERCHANT_PAY_ONNET以及任何非P2P_TRANSFER_ONNET的服务时，推送P2P_TRANSFER_ONNET/BILL_PAY