休假回来 更博-MySQL以月为单位的客户综合情况表_20161008

十一休假老家事比较多 未来得及更新 今起依旧更博-

生成一个以用户ID为单位，各月下单天次，各月买了几个产品，各月订单额

SELECT 城市,用户ID,SUM(IF(年月=201607,天次,NULL)) AS 7月天次,SUM(IF(年月=201608,天次,NULL)) AS 8月天次,SUM(IF(年月=201609,天次,NULL)) AS 9月天次
FROM (
    SELECT city AS 城市,username AS 用户ID,DATE_FORMAT(order_date,"%Y%m") AS 年月,COUNT(order_date) AS 天次
    FROM `test_a03order` AS a
    GROUP BY city,username,DATE_FORMAT(order_date,"%y%m") 
) AS b
GROUP BY  城市,用户ID

SELECT 城市,用户ID,SUM(IF(年月=201607,1,NULL)) AS 7月产品数,SUM(IF(年月=201608,1,NULL)) AS 8月产品数,SUM(IF(年月=201609,1,NULL)) AS 9月产品数
FROM (
    SELECT city AS 城市,username AS 用户ID,DATE_FORMAT(order_date,"%Y%m") AS 年月,productID AS 产品ID
    FROM `test_a03order` AS a
    GROUP BY city,username,DATE_FORMAT(order_date,"%y%m"),productID
) AS b
GROUP BY  城市,用户ID

SELECT city AS 城市,username AS 用户ID,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201607,pay_money,NULL)) AS 7月金额,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201608,pay_money,NULL)) AS 8月金额,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) AS 9月金额
FROM `test_a03order` AS a
GROUP BY  city,username

这样每个用户ID在各月的数据指标都已经写出来了，通过left join 把这几个指标连接起来 

SELECT a.城市,a.用户ID,a.7月金额,b.7月天次,c.7月产品数,a.8月金额,b.8月天次,c.8月产品数,a.9月金额,b.9月天次,c.9月产品数
FROM (
    SELECT city AS 城市,username AS 用户ID,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201607,pay_money,NULL)) AS 7月金额,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201608,pay_money,NULL)) AS 8月金额,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) AS 9月金额
    FROM `test_a03order` AS a1
    GROUP BY  city,username
) AS a
LEFT JOIN (
    SELECT 城市,用户ID,SUM(IF(年月=201607,天次,NULL)) AS 7月天次,SUM(IF(年月=201608,天次,NULL)) AS 8月天次,SUM(IF(年月=201609,天次,NULL)) AS 9月天次
    FROM (
        SELECT city AS 城市,username AS 用户ID,DATE_FORMAT(order_date,"%Y%m") AS 年月,COUNT(order_date) AS 天次
        FROM `test_a03order` AS b1
        GROUP BY city,username,DATE_FORMAT(order_date,"%y%m") 
    ) AS b2
    GROUP BY  城市,用户ID
) AS b ON a.城市=b.城市 AND a.用户ID=b.用户ID
LEFT JOIN (
    SELECT 城市,用户ID,SUM(IF(年月=201607,1,NULL)) AS 7月产品数,SUM(IF(年月=201608,1,NULL)) AS 8月产品数,SUM(IF(年月=201609,1,NULL)) AS 9月产品数
    FROM (
        SELECT city AS 城市,username AS 用户ID,DATE_FORMAT(order_date,"%Y%m") AS 年月,productID AS 产品ID
        FROM `test_a03order` AS c1
        GROUP BY city,username,DATE_FORMAT(order_date,"%y%m"),productID
    ) AS c2
    GROUP BY  城市,用户ID
) AS c ON a.城市=c.城市 AND a.用户ID=c.用户ID