Lancome5_order

最新推荐文章于 2025-06-09 00:08:01 发布

hm11290219

最新推荐文章于 2025-06-09 00:08:01 发布

阅读量395

点赞数

CC 4.0 BY-SA版权

分类专栏： Python MySQL 文章标签： python

本文链接：https://blog.youkuaiyun.com/hm11290219/article/details/52278315

Python 同时被 2 个专栏收录

61 篇文章

订阅专栏

MySQL

7 篇文章

订阅专栏

本文介绍了一个使用Python和MySQL进行兰蔻品牌订单数据处理和分析的脚本。该脚本通过创建视图简化了数据查询过程，并执行了一系列统计查询来获取有关用户行为、消费习惯和产品偏好的关键指标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/usr/bin/env python
# -*- coding: utf-8 -*- 
import xlwt
import xlrd
import xlutils
from xlutils.copy import copy
import MySQLdb
import os
os.chdir("E:\work") 

def lancome15_addview():
    conn=MySQLdb.connect("localhost","root","111111","work")
    cursor=conn.cursor()
    print "do it"
    sql = "CREATE VIEW lancome15 (oid,otime,uid,gid,gname,gline,category,axe,ogn,price,amount,statstatus,class,age,gender,status,lastbuydate,firstbuydate)\
        AS SELECT lancome15_order_online.oid,lancome15_order_online.otime,lancome15_order_online.uid,lancome15_order_online.gid,lancome15_order_online.gname,\
        lancome15_order_online.gline,lancome15_order_online.category,lancome15_order_online.axe,lancome15_order_online.ogn,lancome15_order_online.price,\
        lancome15_order_online.amount,lancome15_order_online.statstatus,lancome15_order_online.class,lancome15_user_online.age,lancome15_user_online.gender,\
        lancome15_user_online.status,lancome15_user_online.lastbuydate,lancome15_user_online.firstbuydate FROM lancome15_order_online, lancome15_user_online \
        WHERE lancome15_order_online.uid=lancome15_user_online.uid" 
    cursor.execute(sql)


def lancome15_getresult():
    conn=MySQLdb.connect("localhost","root","111111","work")
    cursor=conn.cursor()
    print "do it"

    #sql1 = "SELECT COUNT(distinct uid) from lancome15 WHERE statstatus=1"
    #sql2 = "SELECT SUM(ogn*price) from lancome15 WHERE statstatus=1 and class=5"
    #sql3 = "SELECT COUNT(distinct uid) from lancome15 WHERE firstbuydate BETWEEN '2015-01-01' AND '2015-12-31' AND statstatus=1 and class=5"
    #sql4 = "SELECT COUNT(distinct uid) from lancome15 WHERE statstatus=1  AND firstbuydate BETWEEN '2015-01-01' AND '2015-12-31' HAVING(count(uid)>1) "
    #sql5 = "SELECT COUNT(distinct uid) from lancome15 WHERE gender='F' AND statstatus=1"
    #sql6 = "SELECT SUM(age)/COUNT(distinct uid) FROM lancome15_user_online WHERE age BETWEEN 1.0 AND 100.0 and class=5"
    #sql7 = "SELECT COUNT(age) from lancome15_user_online WHERE age BETWEEN 46 AND 56 and class=5" #36 AND 46, 26 AND 36, 16 AND 26
    #sql8 = "SELECT SUM(ogn*price)/COUNT(distinct uid) from lancome15 WHERE statstatus=1 and class=5"
    #sql9 = "SELECT COUNT(ogn*price) FROM lancome15 WHERE statstatus=1 AND ogn*price BETWEEN 0 AND 500 and class=5"
    #sql10 = "SELECT COUNT(ogn*price) FROM lancome15 WHERE statstatus=1 AND ogn*price BETWEEN 0 AND 500 AND status='sleep' and class=5"
    #sql11 = "SELECT COUNT(ogn*price) FROM lancome15 WHERE statstatus=1 AND ogn*price BETWEEN 0 AND 500 AND status='Active' and class=5"
    #sql12 = "SELECT COUNT(oid)/COUNT(distinct uid) FROM lancome15 where statstatus=1 and class=5"
    #sql13 = "SELECT COUNT(distinct uid) FROM lancome15 where class=5 HAVING(COUNT(uid)>1)"
    #sql14 = "SELECT SUM(ogn*price)/COUNT(oid) FROM lancome15 WHERE statstatus=1 and class=5"



    #sql21 = "SELECT COUNT(distinct uid) FROM lancome15 WHERE axe='SKIN CARE' AND statstatus=1" SKU=distinct gname
    #sql22 = "SELECT COUNT(distinct uid) FROM lancome15 WHERE axe='MAKE-UP' AND statstatus=1 and class=5"
    #sql23 = "SELECT COUNT(uid) FROM lancome15 WHERE axe='MAKE-UP' AND axe='SKIN CARE' AND statstatus=1" #ͬ
    #sql24 = "SELECT COUNT(distinct uid) FROM lancome15 WHERE axe='Perfume' AND statstatus=1 and class=5"
    #sql25 = #
    #sql = "SELECT COUNT(distinct uid) FROM lancome15 WHERE firstbuydate = otime AND statstatus=1 and axe='Perfume' and class=5" #26,27,28,29,30

    #sql31 = "SELECT COUNT(distinct uid) FROM lancome15 WHERE lastbuydate<'2015-06-30' and class=5"
    #sql32 = "SELECT (distinct uid) FROM lancome15 WHERE gname LIKE '%礼盒%'" #中文显示有问题
    #sql33 = "SELECT (distinct uid) FROM lancome15 WHERE gname LIKE '%菁纯%'" #中文显示有问题
    #sql34 = "SELECT (distinct uid) FROM lancome15 WHERE gline LIKE '%H%Z%'"
    #sql35 = "SELECT (distinct uid) FROM lancome15 WHERE gline LIKE '%B%E%X%'"

    L=[]
    sql = "SELECT distinct uid FROM lancome15 WHERE axe='SKIN CARE' AND statstatus=1  "
    cursor.execute(sql)
    numrows = int(cursor.rowcount)
    for j in range(numrows):
        g = cursor.fetchone()
        L.append(g[0])
    print len(L)


    l=[]
    sum = 0
    for i in range(len(L)):
        sql ="SELECT COUNT(distinct uid) from lancome15 where statstatus=1 and axe='MAKE-UP' and uid='%s' " %(L[i])
        cursor.execute(sql)
        g = cursor.fetchone()
        if g[0]!=0:
            l.append(g[0])

    print len(l)

    m=[]
    for i in range(len(l)):
        sql ="SELECT COUNT(distinct uid) from lancome15 where statstatus=1 and axe='MAKE-UP' and uid='%s' " %(l[i])
        cursor.execute(sql)
        g = cursor.fetchone()
        if g[0]!=0:
            m.append(g[0])

    print len(m)

if __name__ == "__main__":
    print "Begin"
    #lancome15_addview()
    lancome15_getresult()
    print "Over"