mysql创建模型_MySQL建立RFM模型

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;