SET @start_date=CURDATE()-1;
SET @end_date=CURDATE();
SELECT A.注冊(cè)渠道,A.注冊(cè)人數(shù),B.綁卡人數(shù),C.`投資人數(shù)`, C.`投資人數(shù)`/A.注冊(cè)人數(shù) AS 投資轉(zhuǎn)化率 FROM
(SELECT remark AS 注冊(cè)渠道,COUNT(*) AS 注冊(cè)人數(shù) FROM b2015_gblc_user
WHERE INTIME BETWEEN @start_date AND @end_date
GROUP BY remark) A
LEFT JOIN (SELECT remark AS 注冊(cè)渠道,COUNT(*) AS 綁卡人數(shù) FROM b2015_gblc_user
WHERE INTIME BETWEEN @start_date AND @end_date
AND RZSTATUS=2
GROUP BY remark) B
ON A.`注冊(cè)渠道`=B.`注冊(cè)渠道`
LEFT JOIN (SELECT remark AS 注冊(cè)渠道,COUNT(*) AS 投資人數(shù) FROM
(SELECT D.phone,D.INTIME,D.REMARK,MIN(E.intime) AS TZTIME,E.SUM AS First_investment,SUM(E.SUM) AS SUM_investment,
CASE WHEN SUM>=100 THEN 3/*狀態(tài)認(rèn)證3表示已投資狀態(tài) */
ELSE 4/*狀態(tài)認(rèn)證4表示未投資狀態(tài)，也等于注冊(cè)人數(shù)減去投資人數(shù) */
END AS RZSTATUS2
FROM (SELECT * FROM b2015_gblc_user WHERE INTIME BETWEEN @start_date AND @end_date) AS D
LEFT JOIN mairuorder AS E/*連接買入表，加入狀態(tài)認(rèn)證3表示已投資狀態(tài) */
ON D.PHONE=E.uphone
GROUP BY PHONE/*按phone分組目的是找出一個(gè)用戶的一條最早買入記錄*/
ORDER BY INTIME) F
WHERE RZSTATUS2=3
GROUP BY remark) C
ON A.`注冊(cè)渠道`=C.`注冊(cè)渠道`
