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;