# 循环统计截至每日历史累计值（Python）

### SQL准备（计算截至某个日期的数据指标）

• 将日期设定为
• date <= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),’%%Y-%%m-%%d’)
• 即可通过循环修改 %s 实现日期的变更，达到循环统计每一天历史累计值的效果
SELECT date
,sum(if(op_type = '行为B',coin_amount,0)) as loan_XXX
,sum(if(op_type = '行为B',coin_amount,if(op_type ='行为C',-coin_amount,if(op_type = '行为A',-coin_amount,0)))) as cdp_debt_XXX
FROM(
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d') as date
,'行为B' as op_type
,loan_XXX as coin_amount
FROM 表1
WHERE date <= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d')

UNION ALL
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d') as date
,'行为A' as op_type
,(lq_pay_XXX-fine_XXX) as coin_amount
FROM 表2
WHERE date <= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d')

UNION ALL
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d') as date
,'行为C' as op_type
,repay_XXX as coin_amount
FROM 表3
WHERE date <= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d')
)r
GROUP BY date


### 将SQL嵌入Python代码的循环模式当中

# -*- coding: utf-8 -*-
"""
Created on Fri Jul 31 14:16:16 2020

@author: Songyou Han
"""

# SQL 查询语句模板
sql_model = '''
SELECT date
,sum(if(op_type = '行为B',coin_amount,0)) as loan_XXX
,sum(if(op_type = '行为B',coin_amount,if(op_type ='行为C',-coin_amount,if(op_type = '行为A',-coin_amount,0)))) as cdp_debt_XXX
FROM(
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d') as date
,'行为B' as op_type
,loan_XXX as coin_amount
FROM 表1
WHERE date <= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d')

UNION ALL
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d') as date
,'行为A' as op_type
,(lq_pay_XXX-fine_XXX) as coin_amount
FROM 表2
WHERE date <= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d')

UNION ALL
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d') as date
,'行为C' as op_type
,repay_XXX as coin_amount
FROM 表3
WHERE date <= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL %s DAY),'%%Y-%%m-%%d')
)r
GROUP BY date
'''

import pymysql

# 打开数据库连接
source_db = pymysql.connect("10.0.0.40", "wayki-rw", "wayiC@r19w", "bicoredata", charset='utf8' )
# destination_db = db = pymysql.connect("localhost", "root", "root", "experiment", charset='utf8' )

# 使用cursor()方法获取操作游标
src_cursor = source_db.cursor()
# des_cursor = destination_db.cursor()

for interval in range(284):
interval_list = []
interval_list.append(str(interval))
interval_list = interval_list*6
interval_list = tuple(interval_list)
try:
#保持数据库连接
source_db.ping(reconnect=True)
# 执行SQL语句
src_cursor.execute(sql_model,interval_list)
# 获取所有记录列表
results = src_cursor.fetchall()
for row in results:
date = row[0]
loan_cumulative = format(int(row[1])/100000000, '.1f')
loan_current = format(int(row[2])/100000000, '.1f')
# 打印结果
print (date,loan_cumulative,loan_current)
except:
print ("Error: unable to fecth data")

# 关闭数据库连接
source_db.close()

01-20 2055
06-07 6万+

02-05 3668
07-03 2550
05-19 948
08-31 970
08-18 1610
10-21 9819
08-22 2069