python 求顾客相邻两次购买周期-CSDN博客

本文链接：https://blog.csdn.net/wutanghua/article/details/113707006

import smtplib
import pymysql
import pymysql
import pandas as pd
from email.mime.text import MIMEText
from email.utils import formataddr


def data_out_msql(DB, msql):
    conn = pymysql.connect(host=DB['host'], port=DB['port'], user=DB['user'], passwd=DB['password'], db=DB['dbname'],
                           charset='utf8',cursorclass=pymysql.cursors.DictCursor)
    # 创建游标
    cursor = conn.cursor()
    # 执行sql语句
    cursor.execute(msql)
    y = cursor.fetchall()
    y = pd.DataFrame(y)
    cursor.close()
    conn.close()
    return y
if __name__ == '__main__':
    DB= {'host':'192.168.50.81',
    'port':3306,
    'user':'lijianhang',
    'password':'amoxr3tz',
    'dbname':'gd_dw'}


    mysql = """
       select distinct Ifnull(SPLITORIGORDER,id) id,receivmobile 手机号,orderdate 下单时间
from dim_oms_order
where DATE_format(orderdate,'%Y-%m-%d')>= '2020-01-01'  and receivmobile is not null and
 DATE_format(orderdate,'%Y-%m-%d')<= '2020-12-31' 
 and orderamount>0
and ORDERSTATUS not in (7,8)
and ordershopid in (6,7,8,9,10,14,17,30,31,38,43,48,49,50,64,65,79,80,81,83,86,98,107,151,153,156,176,187,189,190,191,192,193,194,196,197,198,199,200,201,202,203,204,225,226,268,269,270,272,413,473,488,489,518,523,525,528,529,538,548,549,623,633,634,635)
group by id 
order by receivmobile,orderdate 
        """

    writer = pd.ExcelWriter('时间差.xlsx')
    a = data_out_msql(DB, mysql)
    for i in range(a.shape[0] - 1):
        if a.loc[i, "手机号"] == a.loc[i + 1, "手机号"]:
            tim = (a.loc[i + 1, "下单时间"] - a.loc[i, "下单时间"])
            a.loc[i,'tim']=((a.loc[i + 1, "下单时间"] - a.loc[i, "下单时间"]).total_seconds()/3600/24)
    print(a.dropna(axis=0,how="any",inplace=True,subset=['tim']))
    print(a)