def handle(self, **options): start_time='2010-12-01 00:00:00' end_time='2015-12-31 23:59:59' sites=[] if options.has_key('site') and options['site']!=None : sites.append(options['site']) else : # 获取该用户的所有关联site名称 station_infos=get_all_stations() for info in station_infos: sites.append(info[0]) user_source=1 if options.has_key('user_source') and options['user_source']!=None : user_source=int(options['user_source']) # 对每一个油站进行分析 for site in sites: # 建立数据会话 create_session = get_dash_session_maker(site) s = create_session() # 对每个station,获取所有的用户列表 users={} user_total_purchase_amounts={} # 记录每个用户的总金额 user_total_nonfuel_purchase_amounts={} # 记录每个用户的非油品总金额 user_total_fuel_amounts={} # 记录每个用户的加油量 user_promotion_times={} # 记录用户接受促销的次数 try: # 遍历该站点的所有交易数据,统计每个用户的消费总金额,非油品总金额和油品数量 sql = select([ Trans.site,Trans.cardnum,Trans.timestamp,Trans.trans_id, Trans.trans_type,Trans.barcode,Trans.price,Trans.quantity,Trans.pay, Trans.desc,Trans.pump_type ]).select_from(Trans.__table__).where( and_(Trans.cardnum>0,Trans.site==site,Trans.quantity>0, Trans.timestamp>=start_time,Trans.timestamp<=end_time) ).order_by('trans_id asc') trans_ret = s.execute(sql) transactions = trans_ret.fetchall() for trans in transactions: # 更新总金额和消费次数 if users.has_key(trans.cardnum) : users[trans.cardnum]+=1 user_total_purchase_amounts[trans.cardnum]+=trans.pay else : users[trans.cardnum]=1 user_total_purchase_amounts[trans.cardnum]=0 # 如果此次消费金额为0 if trans.pay==0: if user_promotion_times.has_key(trans.cardnum) : user_promotion_times[trans.cardnum]+=1 else : user_promotion_times[trans.cardnum]=1 # 更新非油品总金额和油品数量 if trans.trans_type==TransType.FUEL: if user_total_nonfuel_purchase_amounts.has_key(trans.cardnum) : user_total_nonfuel_purchase_amounts[trans.cardnum]+=trans.pay else : user_total_nonfuel_purchase_amounts[trans.cardnum]=trans.pay else : if user_total_fuel_amounts.has_key(trans.cardnum) : user_total_fuel_amounts[trans.cardnum]+=trans.quantity else : user_total_fuel_amounts[trans.cardnum]=trans.quantity except Exception, e : print 'exception:%s'%str(e) # 对用户根据金额总数排序 user_array=sorted(user_total_purchase_amounts.items(), key=lambda x:x[1], reverse=True) user_prominence={} nb_users=len(user_array) idx=0 # 根据用户总金额来计算用户的排名权重 for user in user_array: user_prominence[user[0]]=100-(idx*100+0.0001)/nb_users idx+=1 # 对每一个用户,查询其所有的交易记录,按照先后时间排序 idx=0 for user in users: # 获取该用户的所有交易记录 sql = select([ Trans.site,Trans.cardnum,Trans.timestamp,Trans.trans_id, Trans.trans_type,Trans.barcode,Trans.price,Trans.quantity,Trans.pay, Trans.desc, Trans.pump_type ]).select_from(Trans.__table__).where( and_(Trans.cardnum==user,Trans.site==site,Trans.quantity>0, Trans.timestamp>=start_time,Trans.timestamp<=end_time) ).order_by('trans_id asc') trans_ret = s.execute(sql) trans_array = trans_ret.fetchall() #初始化统计分析的结果 # 加油时间倾向 prefer_time #0 无; 1 早; 2 中; 3 晚 prefer_time_stat={} prefer_time_stat[1]=0 prefer_time_stat[2]=0 prefer_time_stat[3]=0 prefer_time_stat[4]=0 # 加满率 prefer_pump_type #0 无;1 加满;2 定额 prefer_pump_type_stat={} prefer_pump_type_stat[1]=0 prefer_pump_type_stat[2]=0 # 加油额 prefer_fuel_cost #0 无规律;1 加很多;2 加很少;3 一般 prefer_fuel_cost_stat={} prefer_fuel_cost_stat[1]=0 prefer_fuel_cost_stat[2]=0 prefer_fuel_cost_stat[3]=0 # 非油品销售量 prefer_nonfuel_cost #0 无规律;1 买很多;2 买很少;3 一般 prefer_nonfuel_cost_stat={} prefer_nonfuel_cost_stat[1]=0 prefer_nonfuel_cost_stat[2]=0 prefer_nonfuel_cost_stat[3]=0 #平均加油间隔, 平均间隔的天数,avg_charge_period user_charge_timestamps=[] # 对油站的影响程度 efficiency #0 无影响 1/3;1 一般 1/3-2/3;2 严重 2/3 efficiency_stat={} efficiency_stat[1]=0 efficiency_stat[2]=0 prev_trans_id=None non_fuel_amount=0 fuel_amount=0 nb_transactions=0; # 扫描该用户的所有记录 for trans in trans_array: # 一次新的交易的开始 if trans.trans_id!=prev_trans_id : # 用户有一次新的交易 nb_transactions+=1 # 追加加油日期 user_charge_timestamps.append(trans.timestamp) # 更新对油站的影响程度(加油时间是否在查询得到的该站当天高峰期内)统计 # TODO # 统计终结上一个交易的数据 if prev_trans_id!=None: # 油品消费额 if fuel_amount<100: prefer_fuel_cost_stat[1]+=1 elif fuel_amount<=300: prefer_fuel_cost_stat[2]+=1 elif fuel_amount>0: prefer_fuel_cost_stat[3]+=1 # 非油品销售额 if non_fuel_amount<100: prefer_nonfuel_cost_stat[1]+=1 elif fuel_amount<=300: prefer_nonfuel_cost_stat[2]+=1 elif non_fuel_amount>0: prefer_nonfuel_cost_stat[3]+=1 # 更新消费时间统计 if trans.timestamp.hour>=5 and trans.timestamp.hour<11: prefer_time_stat[1]+=1 elif trans.timestamp.hour>=11 and trans.timestamp.hour<17: prefer_time_stat[2]+=1 elif trans.timestamp.hour>=17 and trans.timestamp.hour<23: prefer_time_stat[3]+=1 else : prefer_time_stat[4]+=1 # 更新加满率统计 if trans.trans_type==TransType.FUEL and trans.pay!=0: if trans.pump_type==PumpType.FILLOUT: prefer_pump_type_stat[1]+=1 else : prefer_pump_type_stat[2]+=1 # 初始化加油额和非油品销售额 if trans.trans_type==TransType.FUEL: fuel_amount=trans.pay else : non_fuel_amount=trans.pay # 同在一次交易内的其它物品 else : # 更新加油额和非油品销售额 if trans.trans_type==TransType.FUEL: fuel_amount+=trans.pay else : non_fuel_amount+=trans.pay prev_trans_id=trans.trans_id # 根据统计得到消费时间倾向结果 prefer_time=0 for i in prefer_time_stat.keys(): if (prefer_time_stat[i]+0.0001)/nb_transactions>0.5: prefer_time=i break # 根据统计得到加满率结果 prefer_pump_type=0 for i in prefer_pump_type_stat.keys(): if (prefer_pump_type_stat[i]+0.0001)/nb_transactions>0.6: prefer_pump_type=i break # 根据统计得到加油额结果 prefer_fuel_cost=0 for i in prefer_fuel_cost_stat.keys(): if (prefer_fuel_cost_stat[i]+0.0001)/nb_transactions>0.6: prefer_fuel_cost=i break # 根据统计得到非油品购买行为结果 prefer_nonfuel_cost=0 for i in prefer_nonfuel_cost_stat.keys(): if (prefer_nonfuel_cost_stat[i]+0.0001)/nb_transactions>0.6: prefer_nonfuel_cost=i break # 根据统计得到平均加油间隔结果 avg_charge_period=0 if len(user_charge_timestamps)<=1: avg_charge_period=0 else : avg_char_period=(user_charge_timestamps[-1]-user_charge_timestamps[0]).total_seconds()/86400/nb_transactions # 根据统计得到对油站的效率影响结果 efficiency=0 for i in efficiency_stat.keys(): if (efficiency_stat[i]+0.0001)/nb_transactions>0.6: efficiency=i break # 用户最适合的促销模式 best_promotion_mode # TODO # 写回该用户的信息到model数据库 if user_total_fuel_amounts.has_key(user) : total_fuel_amount=user_total_fuel_amounts[user] else : total_fuel_amount=0 if user_total_nonfuel_purchase_amounts.has_key(user) : total_nonfuel_amount=user_total_nonfuel_purchase_amounts[user] else : total_nonfuel_amount=0 if user_total_purchase_amounts.has_key(user) : total_purchase_amount=user_total_purchase_amounts[user] else : total_purchase_amount=0 # 查询用户的现有画像结果 profile_sql=select_directly([CustomerProfile.cardnum]).where(and_(CustomerProfile.cardnum==user,CustomerProfile.user_source==user_source)).label('tao-gilbarco') rs=s.query(profile_sql) ret=rs.first() if ret[0]!=None: stmt = update(CustomerProfile).where(CustomerProfile.cardnum==user).\ values(prefer_time=prefer_time, prefer_pump_type=prefer_pump_type, prefer_fuel_cost=prefer_fuel_cost, prefer_nonfuel_cost=prefer_nonfuel_cost, avg_charge_period=avg_charge_period, efficiency=efficiency, prominence=user_prominence[user], total_fuel_amount=total_fuel_amount, total_nonfuel_purchase_amount=total_nonfuel_amount, total_purchase_amount=total_purchase_amount) s.execute(stmt) else: s.execute(CustomerProfile.__table__.insert(),[ {'site':site, 'cardnum':user, 'user_source':user_source, 'prefer_time':prefer_time, 'prefer_pump_type':prefer_pump_type, 'prefer_fuel_cost':prefer_fuel_cost, 'prefer_nonfuel_cost':prefer_nonfuel_cost, 'avg_charge_period':avg_charge_period, 'efficiency':efficiency, 'prominence':user_prominence[user], 'total_fuel_amount':total_fuel_amount, 'total_nonfuel_purchase_amount':total_nonfuel_amount, 'total_purchase_amount':total_purchase_amount}]) idx+=1 if idx%1000==0: s.commit() s.commit() s.close()
) # 构建从numid到中文名的映射 Fuels = FuelTypes.tuples() name_descs={} for fuel in Fuels: name_descs[fuel[0]]=fuel[1] # 从JL_CN_97到标号的映射开始列出三元组 Fuel_keys = FuelTypes.KEYS for name in Fuel_keys: numid=Fuel_keys[name] description=name_descs[numid] # 检查是否已经存在 sql=select_directly([FuelType.id]).where(or_(FuelType.name==name, FuelType.numid==str(numid), FuelType.description==description)).label('tao-gilbarco') rs=s.query(sql) ret=rs.first() if ret!=None and ret[0]!=None: continue # 创建不存在的 fuel_dic={ 'name':name, 'numid':str(numid), 'description':description } try : s.execute(FuelType.__table__.insert(), [fuel_dic])