循环统计截至每日历史累计值(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
	FROM1
	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
	FROM2
	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
	FROM3
	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()
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页