用户信息统计
日统计
#日统计作者： 李俊
SELECT A.日期,A.新增注册人数,Z.投资人数,C.新增投资人数,D.投资金额,E.提现金额 FROM
(SELECT DATE_FORMAT(INTIME,'%Y-%m-%d') AS 日期,COUNT(*) AS 新增注册人数 FROM b2015_gblc_user
GROUP BY 日期) A /*计算新增注册人数*/
LEFT JOIN
(SELECT DATE_FORMAT(intime,'%Y-%m-%d') AS 日期,COUNT(DISTINCT uphone) AS 投资人数
FROM `mairuorder`
GROUP BY 日期
) Z
ON A.日期=Z.日期
LEFT JOIN
(SELECT DATE_FORMAT(TZTIME,'%Y-%m-%d') AS 日期,COUNT(PHONE) AS 新增投资人数
FROM
(SELECT uphone AS PHONE,MIN(intime) AS TZTIME FROM mairuorder
GROUP BY uphone
ORDER BY intime ASC) B /*用户第一次投资时间*/
GROUP BY 日期) C /*计算新增投资人数*/
ON A.日期=C.日期
LEFT JOIN
(SELECT DATE_FORMAT(intime,'%Y-%m-%d') AS 日期 ,SUM(SUM) AS 投资金额
FROM mairuorder
GROUP BY 日期) D /*计算用户投资金额*/
ON A.日期=D.日期
LEFT JOIN
(SELECT DATE_FORMAT(INTIME,'%Y-%m-%d') AS 日期,SUM(sum) AS 提现金额 FROM b2015_gblc_cashrecord
WHERE type=1 /*type=1是用户提现，type=2是红包提现*/
GROUP BY 日期) E /*计算用户提现金额*/
ON A.日期=E.日期;
周统计
#周统计作者： 李俊
SELECT A.周数,A.新增注册人数,Z.投资人数,C.新增投资人数,D.投资金额,E.提现金额 FROM
(SELECT DATE_FORMAT(INTIME,'%Y-%u周') AS 周数,COUNT(*) AS 新增注册人数 FROM b2015_gblc_user
GROUP BY 周数) A /*计算新增注册人数*/
LEFT JOIN
(SELECT DATE_FORMAT(intime,'%Y-%u周') AS 周数,COUNT(DISTINCT uphone) AS 投资人数
FROM `mairuorder`
GROUP BY 周数
) Z
ON A.周数=Z.周数
LEFT JOIN
(SELECT DATE_FORMAT(TZTIME,'%Y-%u周') AS 周数,COUNT(PHONE) AS 新增投资人数
FROM
(SELECT uphone AS PHONE,MIN(intime) AS TZTIME FROM mairuorder
GROUP BY uphone
ORDER BY intime ASC) B /*用户第一次投资时间*/
GROUP BY 周数) C /*计算新增投资人数*/
ON A.周数=C.周数
LEFT JOIN
(SELECT DATE_FORMAT(intime,'%Y-%u周') AS 周数 ,SUM(SUM) AS 投资金额
FROM mairuorder
GROUP BY 周数) D /*计算用户投资金额*/
ON A.周数=D.周数
LEFT JOIN
(SELECT DATE_FORMAT(INTIME,'%Y-%u周') AS 周数,SUM(sum) AS 提现金额 FROM b2015_gblc_cashrecord
WHERE type=1 /*type=1是用户提现，type=2是红包提现*/
GROUP BY 周数) E /*计算用户提现金额*/
ON A.周数=E.周数;
月统计
SELECT A.月份,A.新增注册人数,Z.投资人数,C.新增投资人数,D.投资金额,E.提现金额 FROM
(SELECT DATE_FORMAT(INTIME,'%Y-%m月') AS 月份,COUNT(*) AS 新增注册人数 FROM b2015_gblc_user
GROUP BY 月份) A /*计算新增注册人数*/
LEFT JOIN
(SELECT DATE_FORMAT(intime,'%Y-%m月') AS 月份,COUNT(DISTINCT uphone) AS 投资人数
FROM `mairuorder`
GROUP BY 月份
) Z
ON A.月份=Z.月份
LEFT JOIN
(SELECT DATE_FORMAT(TZTIME,'%Y-%m月') AS 月份,COUNT(PHONE) AS 新增投资人数
FROM
(SELECT uphone AS PHONE,MIN(intime) AS TZTIME FROM mairuorder
GROUP BY uphone
ORDER BY intime ASC) B /*用户第一次投资时间*/
GROUP BY 月份) C /*计算新增投资人数*/
ON A.月份=C.月份
LEFT JOIN
(SELECT DATE_FORMAT(intime,'%Y-%m月') AS 月份 ,SUM(SUM) AS 投资金额
FROM mairuorder
GROUP BY 月份) D /*计算用户投资金额*/
ON A.月份=D.月份
LEFT JOIN
(SELECT DATE_FORMAT(INTIME,'%Y-%m月') AS 月份,SUM(sum) AS 提现金额 FROM b2015_gblc_cashrecord
WHERE type=1 /*type=1是用户提现，type=2是红包提现*/
GROUP BY 月份) E /*计算用户提现金额*/
ON A.月份=E.月份;
季统计
SELECT A.季度,A.新增注册人数,Z.投资人数,C.新增投资人数,D.投资金额,E.提现金额 FROM
(SELECT CONCAT(YEAR(intime),'-',QUARTER(intime),'季度') AS 季度,COUNT(*) AS 新增注册人数 FROM b2015_gblc_user
GROUP BY 季度) A /*计算新增注册人数*/
LEFT JOIN
(SELECT CONCAT(YEAR(intime),'-',QUARTER(intime),'季度') AS 季度,COUNT(DISTINCT uphone) AS 投资人数
FROM `mairuorder`
GROUP BY 季度
) Z
ON A.季度=Z.季度
LEFT JOIN
(SELECT CONCAT(YEAR(TZtime),'-',QUARTER(TZTIME),'季度') AS 季度,COUNT(PHONE) AS 新增投资人数
FROM
(SELECT uphone AS PHONE,MIN(intime) AS TZTIME FROM mairuorder
GROUP BY uphone
ORDER BY intime ASC) B /*用户第一次投资时间*/
GROUP BY 季度) C /*计算新增投资人数*/
ON A.季度=C.季度
LEFT JOIN
(SELECT CONCAT(YEAR(intime),'-',QUARTER(intime),'季度') AS 季度 ,SUM(SUM) AS 投资金额
FROM mairuorder
GROUP BY 季度) D /*计算用户投资金额*/
ON A.季度=D.季度
LEFT JOIN
(SELECT CONCAT(YEAR(intime),'-',QUARTER(intime),'季度') AS 季度,SUM(sum) AS 提现金额 FROM b2015_gblc_cashrecord
WHERE type=1 /*type=1是用户提现，type=2是红包提现*/
GROUP BY 季度) E /*计算用户提现金额*/
ON A.季度=E.季度;
年统计
SELECT A.年份,A.新增注册人数,Z.投资人数,C.新增投资人数,D.投资金额,E.提现金额 FROM
(SELECT DATE_FORMAT(INTIME,'%Y') AS 年份,COUNT(*) AS 新增注册人数 FROM b2015_gblc_user
GROUP BY 年份) A /*计算新增注册人数*/
LEFT JOIN
(SELECT DATE_FORMAT(intime,'%Y') AS 年份,COUNT(DISTINCT uphone) AS 投资人数
FROM `mairuorder`
GROUP BY 年份
) Z
ON A.年份=Z.年份
LEFT JOIN
(SELECT DATE_FORMAT(TZTIME,'%Y') AS 年份,COUNT(PHONE) AS 新增投资人数
FROM
(SELECT uphone AS PHONE,MIN(intime) AS TZTIME FROM mairuorder
GROUP BY uphone
ORDER BY intime ASC) B /*用户第一次投资时间*/
GROUP BY 年份) C /*计算新增投资人数*/
ON A.年份=C.年份
LEFT JOIN
(SELECT DATE_FORMAT(intime,'%Y') AS 年份 ,SUM(SUM) AS 投资金额
FROM mairuorder
GROUP BY 年份) D /*计算用户投资金额*/
ON A.年份=D.年份
LEFT JOIN
(SELECT DATE_FORMAT(INTIME,'%Y') AS 年份,SUM(sum) AS 提现金额 FROM b2015_gblc_cashrecord
WHERE type=1 /*type=1是用户提现，type=2是红包提现*/
GROUP BY 年份) E /*计算用户提现金额*/
ON A.年份=E.年份;
