说明
该算法通过获取用户订单详情(未拆分订单),获取商品后续购买订单及时间,计算商品复购时间间隔。
算法
文件名:CFGOracle.ini
;Oracle数据库配置信息
[orcl]
host = 192.168.0.10
port = 1521
sid = orcl
user = test
psword = 123456
configparser辅助类
# -*- codeing = utf-8 -*-
# @File : CCLParameter.py
# @Author : 一起种梧桐吧
# @Version : Python 3.10.0
# @Software : PyCharm
# @Time : 2022-08-25 14:23
# @Purpose : 读取ini配置文件
from configparser import ConfigParser as config
class Parameter():
"""读取mysql数据库配置文件'./CFGMysql.ini'"""
def __init__(self, section, filename, coding='utf-8'):
"""初始化参数"""
self.section = section
self.filename = filename
self.coding = coding
self.config = config()
self.dict = {}
self.getOption()
def getOption(self):
"""遍历option获取配置文件"""
try:
self.config.read(self.filename, self.coding)
for option in self.config.options(self.section):
self.dict[option] = self.config.get(self.section, option)
except Exception as err:
return str(err)
else:
return '成功读取"{}"文件"{}"配置'.format(self.filename, self.section)
if __name__ == "__main__":
p = Parameter('ouser', './CFGMysql.ini')
print(p.dict)
oracle辅助类
# -*- codeing = utf-8 -*-
# @File : CCLOracle.py
# @Author : 一起种梧桐吧
# @Version : Python 3.10.0
# @Software : PyCharm
# @Time : 2022-08-25 14:57
# @Purpose : 连接指定Oracle数据库并管理指针
import cx_Oracle as oracle
from CCLParameter import Parameter
defautfile = './Cfg_oracle.ini'
class OracleHelper():
"""获取oracle数据库游标并管理指针"""
def __init__(self, section, filename='./CFGOracle.ini', coding='utf-8'):
"""初始化并读取配置文件"""
self.section = section
self.config = Parameter(section, filename, coding)
self.conn = None
self.curs = None
self.getCursor()
def getCursor(self):
"""尝试获取游标"""
try:
dns = oracle.makedsn(self.config.dict['host'],
int(self.config.dict['port']),
self.config.dict['sid'])
self.conn = oracle.connect(self.config.dict['user'],
self.config.dict['psword'],
dns)
self.curs = self.conn.cursor()
except Exception as err:
return str(err)
else:
return '成功连接到Oracle数据库%s' % self.section
def release(self):
"""释放游标"""
if self.curs:
self.curs.close()
return '成功释放Oracle数据%s指针' % self.section
if self.conn:
self.conn.close()
return '成功释放Oracle数据%s连接' % self.section
if __name__ == '__main__':
orcl = OracleHelper('slave01')
orcl.release()
商品复购算法
# -*- codeing = utf-8 -*-
# @File : CCLAlgorithmYSWSecondPurchase.py
# @Author : 一起种梧桐吧
# @Version : Python 3.10.0
# @Software : PyCharm
# @Time : 2022-09-13 17:31
# @Purpose : 计算商品复购时间及复购率
from CCLDecorator import decorator
from CCLOracle import OracleHelper
class Algorithm():
""" 计算云书网线上商品关联关系 """
def __init__(self):
self.defaultsize = 200000
self.tasks = []
def getTasks(self, section='master02',
sql='''Select frmuser, frmtable, sqlread, touser, totable, sqlinsert
From ctrl_sqlcommands Where pjectname = 'hnxhhy' And sqltype = 'algorithm' And notes = 'CommoditySecondPurchase' And flag = 1'''):
"""根据section读取CFGOracle.ini配置文件,读取待执行任务"""
client = None
try:
client = OracleHelper(section)
self.tasks = client.curs.execute(sql).fetchall()
except Exception as err:
return str(err)
else:
return '获取任务{}条'.format(len(self.tasks))
finally:
if client: client.release()
def statistic(self, item):
"""
分析订单数据获取商品之间同单数量
:param item: :return:
"""
frmuser, frmtable, sqlread, touser, totable, sqlinsert = range(6)
oracle_read = OracleHelper(item[frmuser])
oracle_load = OracleHelper(item[touser])
oracle_read.curs.execute(item[sqlread].read())
soitem = {}
memberid, productid, ordercode, ordertime = range(4)
for recode in oracle_read.curs.fetchall():
_key = '&'.join(recode[memberid:ordercode])
if _key in soitem.keys():
soitem[_key].append([recode[ordercode], recode[ordertime]])
else:
soitem[_key] = [[recode[ordercode], recode[ordertime]]]
secondpurchase = []
for key, val in soitem.items():
# 判断订单商品数量,如果商品数量小于1继续下一循环
if len(val) <= 1:
continue
for _val in val:
_ = key.split('&')
_.extend(_val)
secondpurchase.append(_)
rowcount = len(secondpurchase)
while len(secondpurchase) >= self.defaultsize:
slice, secondpurchase = secondpurchase[:self.defaultsize], secondpurchase[self.defaultsize:]
oracle_load.curs.executemany(item[sqlinsert].read(), slice)
oracle_load.conn.commit()
else:
if secondpurchase:
oracle_load.curs.executemany(item[sqlinsert].read(), secondpurchase)
oracle_load.conn.commit()
if oracle_read: oracle_read.release()
if oracle_load: oracle_load.release()
return "成功从{}.{}传输{}条数据到{}.{}".format(item[frmuser], item[frmtable], rowcount, item[touser], item[totable])
def run(self):
self.getTasks()
if not self.tasks:
return '没有待执行任务'
for item in self.tasks:
self.statistic(item)
return '{}条任务执行完成'.format(len(self.tasks))
if __name__ == '__main__':
worker = Algorithm()
worker.run()