Python笔记 之 计算商品复购

说明

该算法通过获取用户订单详情（未拆分订单），获取商品后续购买订单及时间，计算商品复购时间间隔。

算法

文件名：CFGOracle.ini

;Oracle数据库配置信息  
  
[orcl]  
host = 192.168.0.10 
port = 1521  
sid = orcl  
user = test  
psword = 123456
1
2
3
4
5
6
7
8

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

商品复购算法

# -*- 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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84