首次分配 沟通时间

#!/usr/bin/env python
# coding: utf-8

# In[2]:


import pandas as pd
import pymysql


# In[3]:


conn = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com",port=3306,user="yanyanglong_read",passwd="jZYhtZNwD6kWbCiJlNBI",db="hfjydb",charset="utf8")
cur = conn.cursor()


# In[13]:


sql0 = '''
SELECT
        s.student_intention_id,
        s.student_no
FROM
        view_student s
WHERE
        s.student_no in ('48680659','66609222','72419322','21110544','31354555','78153543','95876509','13401468','71601162','49725580','15475515','11774047','28980679','31541958','60193371','23358278','85532787','17224302','10442607','82136230','43145452','69101253','58437266','85693980','29963455','84566236','13800821','90758805','17330574','12017749','71422266','35978203','97242127','21937794','41823044','98826421','32616061','31184154','69433410','39666802','30742031','65200288','12869671','38515573','71109990','48743084','65099524','66919901','93091158','30396138','99979593','19475361','40868812','87452506','80672885','88880759','54839785','93017345','26942409','49840941','81642577','81233435','56367198','14339781','36837767','45035029','42863207','74645200','85604704','15081436','60132509','36617245','52831196','81006971','43692419','97355986','35115162','27551794','74144619','44892913','99940218','32337290','17401297','57655303','33950469','59233294','41572954','33205053','54865137','80929433','88363737','57443346','79953474','67619543','10461724','99354009','95899045','33247394','80475425','84048073','48896545','55467590','62964558','63148309','61894820','59952509','31635545','63008794','94681310','59135114','26203074','31279694','90511221','28398004','39596231','51755530','23642777','54487321','85082259','21958897','85618437','41474989','68410785','64839669','62350677','14048712','88889726','89913312','55618473','40770614','51620418','79071127','10984441','89131885','33000199')
GROUP BY
        s.student_no 
'''

df0 = pd.read_sql(sql0, conn)
df0.head()


# In[16]:


sql1 = '''
SELECT
        intention_id as student_intention_id,
        min(date) 首次分配时间
FROM
        bidata.charlie_new_keys
WHERE
        intention_id in {0}
GROUP BY
        intention_id
'''.format(tuple(df0['student_intention_id']))

df1 = pd.read_sql(sql1, conn)
df1.head()


# In[19]:


sql2 = '''
SELECT
        student_intention_id,
        min(end_time) 第一次沟通记录时间
FROM
        view_communication_record
WHERE
        student_intention_id in {0}
        and role_code in ('ZJ-ZY','ZJ-ZZ','XS-JL','XS-ZY','XS-ZZ')
GROUP BY
        student_intention_id
'''.format(tuple(df0['student_intention_id']))

df2 = pd.read_sql(sql2, conn)
df2.head()


# In[21]:


df_sub = pd.merge(df0,df1,how='left',on=['student_intention_id'])
df_sub = pd.merge(df_sub,df2,how='left',on=['student_intention_id'])

df_sub


# In[23]:


df_sub.to_excel(r'e:\Desktop\葫芦娃需求.xlsx',index=False,encoding='GB18030')

文档更新时间: 2019-08-19 11:45   作者:admin