CREATE DEFINER=XXXX@% PROCEDURE PRO_product_pass_rate(IN start_date date, #统计开始日期
IN end_date date, #统计结束日期
IN product_id VARCHAR(3000), #产品ID,多个用“,”分隔
IN show_type VARCHAR(2), #显示模式 1:按时间段只显示1笔通过率 2:跨月时间段按月显示通过率
OUT result_status VARCHAR(30), #返回处理状态
OUT result_info VARCHAR(100) #返回处理信息
)
BEGIN
DECLARE v_cnt1 INT;
DECLARE v_cnt2 INT;
DECLARE pid_list VARCHAR(3000);
SET result_status = 'S';
SET result_info = 'OK';
IF start_date IS NULL OR end_date IS NULL THEN
SET result_status = 'F';
SET result_info = '统计日期不能为空!';
ELSEIF product_id IS NULL THEN
SET result_status = 'F';
SET result_info = '产品ID不能为空!';
END IF;
SET pid_list = CONCAT(',',product_id);
SELECT COUNT(1)
INTO v_cnt2
FROM wechat_finance_db.chsell_product p
WHERE p.id IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(pid_list, ',', t.id + 1 ), ',',- 1 ) as 'pid_list'
FROM sj_sequnce_list_t t
WHERE t.id < (length(pid_list) - length(REPLACE(pid_list, ',', '' )) + 1)
);
IF v_cnt2 = 0 THEN
SET result_status = 'F';
SET result_info = '所输的产品ID无效!';
END IF;
IF result_status = 'S' THEN
IF IFNULL(show_type,'1') = '1' THEN
#按时间段只显示1笔通过率
SELECT v.product_id,
v.product_name,
v.pass_qty,
v.total_qty,
ROUND(v.pass_qty/v.total_qty*100,2) as 'pass_rate'
FROM (SELECT o.product_id,
o.product_name,
COUNT(DISTINCT CASE
WHEN o.product_id = '33a9bc0a35234b9d96206aca5ed78fdd' AND (o.money > 0 OR o.credit_line >0) AND o.busi_status IN ('BSD_24','BSD_34','BSD_17','BSD_15','BSD_22') THEN
o.company_name
WHEN o.product_id = 'fd69947e5f8a4c6fb484b143ab4de73c' AND (o.money > 0 OR o.credit_line >0) THEN
o.guest_id
WHEN o.product_id != '33a9bc0a35234b9d96206aca5ed78fdd' AND (o.money > 0 OR o.credit_line >0) THEN
o.company_name
END) as 'pass_qty',
COUNT(DISTINCT CASE
WHEN o.product_id = 'a898f6f804954eceb2bea3ceb709732c' AND (v.busi_state IS NOT NULL OR o.busi_status IS NULL) THEN
o.company_name
WHEN o.product_id = 'fd69947e5f8a4c6fb484b143ab4de73c' AND v.busi_state IS NOT NULL THEN
o.guest_id
WHEN o.product_id != 'a898f6f804954eceb2bea3ceb709732c' AND v.busi_state IS NOT NULL THEN
o.company_name
END) as 'total_qty'
FROM sj_ksr_order_info o
LEFT JOIN sj_pass_busi_state_view v ON o.busi_status = v.busi_state
WHERE o.product_id IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(pid_list, ',', t.id + 1 ), ',',- 1 ) as 'pid_list'
FROM sj_sequnce_list_t t
WHERE t.id < (length(pid_list) - length(REPLACE(pid_list, ',', '' )) + 1))
AND DATE_FORMAT(o.busi_time,'%Y-%m-%d') BETWEEN DATE_FORMAT(start_date,'%Y-%m-%d') AND DATE_FORMAT(end_date,'%Y-%m-%d')
GROUP BY o.product_id,o.product_name
) v
ORDER BY v.product_id;
ELSE
#跨月时间段按月显示通过率
SELECT v.product_id,
v.product_name,
v.month,
v.pass_qty,
v.total_qty,
ROUND(v.pass_qty/v.total_qty*100,2) as 'pass_rate'
FROM (SELECT o.product_id,
o.product_name,
DATE_FORMAT(o.busi_time,'%Y-%m') as 'month',
COUNT(DISTINCT CASE
WHEN o.product_id = '33a9bc0a35234b9d96206aca5ed78fdd' AND (o.money > 0 OR o.credit_line >0) AND o.busi_status IN ('BSD_24','BSD_34','BSD_17','BSD_15','BSD_22') THEN
o.company_name
WHEN o.product_id = 'fd69947e5f8a4c6fb484b143ab4de73c' AND (o.money > 0 OR o.credit_line >0) THEN
o.guest_id
WHEN o.product_id != '33a9bc0a35234b9d96206aca5ed78fdd' AND (o.money > 0 OR o.credit_line >0) THEN
o.company_name
END) as 'pass_qty',
COUNT(DISTINCT CASE
WHEN o.product_id = 'a898f6f804954eceb2bea3ceb709732c' AND (v.busi_state IS NOT NULL OR o.busi_status IS NULL) THEN
o.company_name
WHEN o.product_id = 'fd69947e5f8a4c6fb484b143ab4de73c' AND v.busi_state IS NOT NULL THEN
o.guest_id
WHEN o.product_id != 'a898f6f804954eceb2bea3ceb709732c' AND v.busi_state IS NOT NULL THEN
o.company_name
END) as 'total_qty'
FROM sj_ksr_order_info o
LEFT JOIN sj_pass_busi_state_view v ON o.busi_status = v.busi_state
WHERE o.product_id IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(pid_list, ',', t.id + 1 ), ',',- 1 ) as 'pid_list'
FROM sj_sequnce_list_t t
WHERE t.id < (length(pid_list) - length(REPLACE(pid_list, ',', '' )) + 1))
AND DATE_FORMAT(o.busi_time,'%Y-%m-%d') BETWEEN DATE_FORMAT(start_date,'%Y-%m-%d') AND DATE_FORMAT(end_date,'%Y-%m-%d')
GROUP BY o.product_id,o.product_name,DATE_FORMAT(o.busi_time,'%Y-%m')
) v
ORDER BY v.product_id,v.month;
END IF;
END IF;
END
