简单的线性数据比较

## 效率-商户情况-蜜月期商户情况(中长尾)(金额算率)分地区
import datetime
import pymysql

# 风控生产库
db1 = pymysql.connect(
host="",
port=,
user="",
passwd="",
db="",
charset='utf8'
)

# 核心库
db2 = pymysql.connect(
host="",
port=,
user="",
passwd="",
db="",
charset='utf8'
)

# 资管库
db3 = pymysql.connect(
host="",
port=,
user="",
passwd="",
db="",
charset='utf8'
)

# BI库
db5 = pymysql.connect(
host="",
port=,
user="",
passwd="",
db="",
charset='utf8'
)

db5 = pymysql.connect(
    host="",
port=,
user="",
passwd="",
db="",
charset='utf8'
)

##业态
# cursor5 = db5.cursor()
# cursor5.execute("SELECT DISTINCT m.code FROM master_merchant_sub m LEFT JOIN (SELECT * FROM master_comm_code WHERE KEY_CODE='MERCHANT_TYPE') AS comm_code ON m.type=comm_code.CODE WHERE merchant_code IN ('M107000010', 'M107000043') and name='植发' ")
# areas = cursor5.fetchall()
#指定地区的商户
cursor4 = db4.cursor()
cursor4.execute("select DISTINCT company_id from Bi_Mid_Ylfq_qr_group where group_name ='西南地区'")
areas = cursor4.fetchall()
#蜜月期的所有商户
cursor4 = db4.cursor()
cursor4.execute("SELECT DISTINCT merchant_number_sub FROM Report_jt_merchant_number where jt='中长尾'")
honeymoon = cursor4.fetchall()
#某地区的蜜月期商户
list_honeymoon = []
list_honeymoon_areas = []
for i in range(len(honeymoon)):
list_honeymoon.append(honeymoon[i][0])
for i in range(len(areas)):
if areas[i][0] in list_honeymoon:
list_honeymoon_areas.append(areas[i][0])

end = datetime.date(2019, 6, 9)
list0=[]
list1 = []
list2 = []
list3 = []
for i in range(0, 7):
oneday = datetime.timedelta(days=i)
day = end + oneday
list0.append(str(day))

for i in range(1, 8):
oneday = datetime.timedelta(days=i)
day = end - oneday
list1.append(str(day))

# 风控申请金额
cursor1 = db1.cursor()
cursor1.execute("SELECT sum(amount) FROM credit_apply WHERE date_format(apply_time, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list0]) + "') AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in"+ "('" + "', '".join([i for i in list_honeymoon_areas]) + "')")
risk_apply_amount = cursor1.fetchone()[0]

cursor1.execute("SELECT distinct CONTRACT_NUMBER FROM credit_apply WHERE date_format(apply_time, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list0]) + "')AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in"+ "('" + "', '".join([i for i in list_honeymoon_areas]) + "')")
risk_contract_number = cursor1.fetchall()
print(risk_contract_number)

cursor3 = db3.cursor()
cursor3.execute(
"SELECT sum(apply_amount) FROM asset_apply WHERE NOT (approved_status = '-1'AND (INSTR(contract_number, '-') > 0 OR INSTR(contract_number, '_') > 0))"
"AND LEFT(contract_number, 32) in " + "('" + "', '".join([i[0] for i in risk_contract_number]) + "')")
data_asset_amount = cursor3.fetchone()[0]
print('进入资方的金额:',data_asset_amount)

cursor3.execute(
"SELECT sum(apply_amount) FROM asset_apply WHERE approved_status IN ('1','5','7','11','13','16','17','-1','-2') and NOT (approved_status = '-1'AND (INSTR(contract_number, '-') > 0 OR INSTR(contract_number, '_') > 0)) "
"AND LEFT(contract_number, 32) in " + "('" + "', '".join([i[0] for i in risk_contract_number]) + "')")
data_asset_approval_amount =cursor3.fetchone()[0]
print('资方通过的金额:',data_asset_approval_amount)

#出量商户数
cursor1.execute("SELECT count(distinct MERCHANT_NUMBER) FROM credit_apply WHERE date_format(apply_time, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list0]) + "') AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in"+ "('" + "', '".join([i for i in list_honeymoon_areas]) + "')")
merchant_apply_amount = cursor1.fetchone()[0]

cursor1.execute("SELECT distinct MERCHANT_NUMBER FROM credit_apply WHERE date_format(apply_time, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list0]) + "') AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in"+ "('" + "', '".join([i for i in list_honeymoon_areas]) + "')")
merchant_apply_amount_detail = cursor1.fetchall()
print(merchant_apply_amount_detail)
#通过金额
cursor2 = db2.cursor()
cursor2.execute("SELECT sum(APPROVAL_AMOUNT) FROM biz_contract WHERE`STATUS` IN (1, 2, 3)"
"AND NUMBER IN " + "('" + "', '".join([i[0] for i in risk_contract_number]) + "')")
approve_amount = cursor2.fetchone()[0]


#w-1的申请金额
cursor1.execute("SELECT sum(amount) FROM credit_apply WHERE date_format(CREATE_TIME, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list1]) + "') "+" AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in "+ "('" + "', '".join([j for j in list_honeymoon_areas]) + "')")
amount_last_week = cursor1.fetchone()[0]

for i in range(8, 15):
oneday = datetime.timedelta(days=i)
day = end - oneday
list2.append(str(day))

#w-2的申请金额
cursor1.execute("SELECT sum(amount) FROM credit_apply WHERE date_format(CREATE_TIME, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list2]) + "') "+" AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in "+ "('" + "', '".join([j for j in list_honeymoon_areas]) + "')")
amount_last_two_week = cursor1.fetchone()[0]

for i in range(15, 22):
oneday = datetime.timedelta(days=i)
day = end - oneday
list3.append(str(day))

#w-3的申请金额
cursor1.execute("SELECT sum(amount) FROM credit_apply WHERE date_format(CREATE_TIME, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list3]) + "') "+" AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in "+ "('" + "', '".join([j for j in list_honeymoon_areas]) + "')")
amount_last_three_week = cursor1.fetchone()[0]

print("商户总数量:%s,出量商户数:%s ,申请金额:%s 通过金额:%s,w-1申请金额:%s ,w-2申请金额:%s w-3申请金额:%s"%(len(list_honeymoon_areas),merchant_apply_amount,risk_apply_amount,approve_amount,amount_last_week,amount_last_two_week,amount_last_three_week))
上一篇:grid网格布局——色子布局


下一篇:自关联