1.数据来源
charge_record表数据
提取代码如下:
# coding=utf-8 import pymysql # 原数据库链接 db1 = pymysql.connect( host='***', port=3306, user='***', passwd='***', db='***', charset='utf8') cursor1 = db1.cursor() # 定义查询语句 len1 = cursor1.execute('SELECT uid,amount,update_time FROM charge_record WHERE buss_type=0 AND charge_status=2 AND charge_prod_id IS NOT NULL') # 迁移数据库链接 db2 = pymysql.connect( host='127.0.0.1', port=3306, user='root', passwd='123456', db='test', charset='utf8') cursor2 = db2.cursor() # 批量插入语句 sql = 'INSERT INTO charge_record(uid,amount,update_time) VALUE(%s, %s,%s)' # 导入全部数据 data2 = cursor1.fetchall() cursor2.executemany(sql, data2) # 提交到数据库 db2.commit() # 关闭数据库连接 db1.close() db2.close()
2.RFM值计算(基于MySQL)
-- 计算RFM的值
-- R值的计算
-- R值(近度)= (当前时间-最后一次消费时间)
SELECT uid,max(update_time) 最后一次消费时间,datediff("2020-05-15",max(update_time)) R值 FROM charge_record GROUP BY uid;
-- F值的计算
-- F值(频度)=(客户购买的频次)
SELECT uid,count(distinct(update_time)) F值 FROM charge_record GROUP BY uid;
-- M值的计算
-- M值(额度) = (一段时间的总额或平均额)
SELECT uid,count(distinct(update_time)) F值,sum(amount) 总额,round(sum(amount)/count(distinct(update_time)),2) M值 FROM charge_record GROUP BY uid;
-- 汇总计算
SELECT uid,datediff("2020-05-15",max(update_time)) R值,
count(distinct(update_time)) F值,round(sum(amount)/count(distinct(update_time)),2) M值
FROM charge_record GROUP BY uid;