场景:区分未下单/下过订单的用户
首先,我们根据关键的时间点(关键行为)对用户进行区分,并将分析的表存进新表
####区分新老户#######
drop table if exists new_old_distingush;
create table new_old_distingush
SELECT
user_id,nid,apply_time,firstend_oldstart_time,if(TIMESTAMPDIFF(second,firstend_oldstart_time,apply_time)>0,'老户','新户') user_type
FROM
(
SELECT
a.user_id,a.nid,FROM_UNIXTIME( a.add_time / 1000 , '%Y-%m-%d %H:%i:%s' ) apply_time,
w.*
from a.`test1` a
left join
(
SELECT
a.user_id user_id0,from_unixtime( a.add_time / 1000, '%Y-%m-%d %H:%i:%s' ) first_start_time,
if(first_loan_time is null,'2050-01-01 00:00:00',first_loan_time) firstend_oldstart_time
FROM
a.`test2` a
left join
(
SELECT
a.user_id ,a.nid,from_unixtime(min(loan_time)/1000, '%Y-%m-%d %H:%i:%s' ) first_loan_time
from a.test1 a
GROUP BY a.user_id
)b on a.user_id=b.user_id
)w on a.user_id=w.user_id0
)x
;
当然我们也可以利用存储函数对用户进行分类;
存储过程/存储函数在处理比较复杂的业务时比较实用。
比如说,一个复杂的数据操作。如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话,就只有一次,从响应时间上来说有优势。也就是说存储过程可以给我们带来运行效率提高的好处。
ps:
关于存储函数和存储过程可以实现相同的目标,本质上区别不大。
首先我们看一下它的定义:指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
区别:存储函数:可以通过return 语句返回函数值;存储过程:不能
利用存储函数很容易实现这一功能:
CREATE DEFINER=`wifi`@`%` FUNCTION `find_user_type_bytime`(p_user_id BIGINT(20),p_time datetime) RETURNS varchar(20) CHARSET utf8
BEGIN
DECLARE X INT;
SELECT COUNT(1) FROM a.test3 WHERE user_id = p_user_id and lend_date <= p_time INTO X;
RETURN X;
END
对用户进行更加细致地分类,可得:
CREATE DEFINER=`root`@`10.0.0.155` FUNCTION `find_user_type`(p_id_card VARCHAR(20),p_mobile VARCHAR(20),p_merchant_id SMALLINT UNSIGNED) RETURNS VARCHAR(20) CHARSET utf8
BEGIN
DECLARE X,i_user_id,b_borrow_count,b_finance_borrow_count,b_account_status INT;
DECLARE b_nid VARCHAR(32);
SELECT COUNT(8) FROM contract WHERE mobile = p_mobile AND id_card = p_id_card AND ENABLE = 1 INTO X;
SELECT id FROM user WHERE username = p_mobile AND merchant_id = p_merchant_id INTO i_user_id;
SELECT COUNT(8) FROM borrow WHERE user_id = i_user_id INTO b_borrow_count;
SELECT COUNT(8) FROM borrow1 WHERE user_id = i_user_id INTO b_finance_borrow_count;
SELECT nid FROM borrow1 WHERE user_id = i_user_id INTO b_nid;
SELECT account_status FROM yhb_loan_contract WHERE nid = b_nid INTO b_account_status;
IF(X >= 1) THEN SET X = 4; -- 老户
ELSEIF b_borrow_count=1 THEN SET X = 1; -- 新户
ELSEIF b_finance_borrow_count=0 THEN SET X = 2; -- 次新户
ELSEIF b_account_status != '' && b_account_status=3 THEN SET X = 3; -- 老户
ELSE SET X = 0; -- 无匹配
END IF;
RETURN X;
END$$
DELIMITER ;