用户统计

下边一大片没啥重要的也就这一句经常用到

SELECT DATE_FORMAT(NOW(), '%Y') - SUBSTRING( '152822198700000000',7,4) AS age

#1、关于回款

#（1）以回款日期进行查询（日期可改），查询每个注册来源渠道的来源名称、回款人数、回款金额、回款日期（排除代理人数据）


SELECT 
  um.`source` AS '注册来源',
  COUNT(DISTINCT lip.`userId`) AS '回款人数',
  SUM(
    lip.toBeCollectedPrincipal + lip.toBeCollectedInterest
  ) AS '回款金额',
  DATE_FORMAT(lip.phaseDate, "%Y-%m-%d") AS '回款日期' 
FROM
  loan_investor_phase lip 
  LEFT JOIN user_main um 
    ON um.`userId` = lip.`userId` 
WHERE um.`roles` = 1 
  AND DATE_FORMAT(lip.phaseDate, "%Y-%m-%d") >= '2017-09-08' 
  AND DATE_FORMAT(lip.phaseDate, "%Y-%m-%d") <= '2017-09-08' 
GROUP BY um.`source`,
  DATE_FORMAT(lip.phaseDate, "%Y-%m-%d") 
ORDER BY DATE_FORMAT(lip.phaseDate, "%Y-%m-%d")


#（2）以回款日期进行查询（日期可改），查询每个回款用户的姓名、手机号、回款金额、回款日期、注册时间、注册来源
SELECT 
  um.`realName`,
  um.`mobile`,
  SUM(
    lip.toBeCollectedPrincipal + lip.toBeCollectedInterest
  ) AS '回款金额',
  DATE_FORMAT(lip.phaseDate, "%Y-%m-%d") AS '回款日期',
  DATE_FORMAT(um.`registerTime`, "%Y-%m-%d") AS '注册时间',
  um.`source` AS '注册来源' 
FROM
  loan_investor_phase lip 
  LEFT JOIN user_main um 
    ON um.`userId` = lip.`userId` 
WHERE um.`roles` = 1 
  AND DATE_FORMAT(lip.phaseDate, "%Y-%m-%d") >= '2017-09-08' 
  AND DATE_FORMAT(lip.phaseDate, "%Y-%m-%d") <= '2017-09-08' 
GROUP BY lip.`userId`,
  DATE_FORMAT(lip.phaseDate, "%Y-%m-%d") 
ORDER BY DATE_FORMAT(lip.phaseDate, "%Y-%m-%d")




#2、关于提现
#（1）以提现日期进行查询（日期可改），每个注册来源渠道的来源名称、提现人数、提现金额、提现日期（排除代理人数据）
SELECT 
  um.`source` AS '注册来源',
  COUNT(DISTINCT cwr.`userId`) AS '提现人数',
  SUM(cwr.amount) AS '提现金额',
  DATE_FORMAT(cwr.applyTime, "%Y-%m-%d") AS '提现日期' 
FROM
  cash_withdraw_request cwr 
  LEFT JOIN user_main um 
    ON um.`userId` = cwr.userId 
WHERE cwr.pnrStatus = 1 
  AND um.`roles` = 1 
  AND DATE_FORMAT(cwr.applyTime, "%Y-%m-%d") >= '2017-09-01' 
  AND DATE_FORMAT(cwr.applyTime, "%Y-%m-%d") <= '2017-09-08' 
GROUP BY um.`source`,
  DATE_FORMAT(cwr.applyTime, "%Y-%m-%d") 
ORDER BY DATE_FORMAT(cwr.applyTime, "%Y-%m-%d")




#（2）以提现日期进行查询（日期可改），每个提现用户的姓名、手机号、提现金额、提现日期、实时在投金额、实时账户余额、最后投资时间、注册时间、注册来源（排除代理人数据）
SELECT 
  um.`realName`,
  um.`mobile`,
  IFNULL(SUM(cwr.amount), 0) AS '提现金额',
  DATE_FORMAT(cwr.applyTime, "%Y-%m-%d") AS '提现日期',
  IFNULL(zaitouTab.investAmountSum, 0) AS '实时在投金额',
  IFNULL(ua.cash, 0) AS '实时账户余额',
  DATE_FORMAT(
    lastTouziTab.lastInvestTime,
    "%Y-%m-%d"
  ) AS '最后投资时间',
  DATE_FORMAT(um.`registerTime`, "%Y-%m-%d") AS '注册时间',
  um.`source` AS '注册来源' 
FROM
  cash_withdraw_request cwr 
  LEFT JOIN user_main um 
    ON um.`userId` = cwr.userId 
  LEFT JOIN user_account ua 
    ON ua.userId = cwr.userId 
  LEFT JOIN 
    (SELECT 
      SUM(li.`investAmount`) AS investAmountSum,
      li.`investorUserId` 
    FROM
      loan_investor li 
    WHERE li.`pnrStatus` = 1 
      AND li.lastRepayDate > NOW() 
    GROUP BY li.`investorUserId`) AS zaitouTab 
    ON zaitouTab.investorUserId = cwr.userId 
  LEFT JOIN 
    (SELECT 
      MAX(li2.`investTime`) AS lastInvestTime,
      li2.`investorUserId` 
    FROM
      loan_investor li2 
    WHERE li2.`pnrStatus` = 1 
    GROUP BY li2.`investorUserId` 
    ORDER BY li2.id DESC) AS lastTouziTab 
    ON lastTouziTab.investorUserId = cwr.userId 
WHERE cwr.pnrStatus = 1 
  AND um.`roles` = 1 
  AND DATE_FORMAT(cwr.applyTime, "%Y-%m-%d") >= '2017-09-07' 
  AND DATE_FORMAT(cwr.applyTime, "%Y-%m-%d") <= '2017-09-07' 
GROUP BY um.userId,
  DATE_FORMAT(cwr.applyTime, "%Y-%m-%d") 
ORDER BY DATE_FORMAT(cwr.applyTime, "%Y-%m-%d")






#3、关于账户余额
#实时查询账户余额＞0的用户的姓名、手机号、账户余额、实时在投金额、最后投资时间、投资次数、注册时间、注册来源


SELECT 
  um.`realName`,
  um.`mobile`,
  ua.cash,
  IFNULL(zaitouTab.investAmountSum, 0) AS '实时在投金额',
  DATE_FORMAT(
    lastTouziTab.lastInvestTime,
    "%Y-%m-%d"
  ) AS '最后投资时间',
  IFNULL(investorCNTTab.investorCNT, 0) AS '投资次数',
  DATE_FORMAT(um.`registerTime`, "%Y-%m-%d") AS '注册时间',
  um.`source` AS '注册来源' 
FROM
  user_account ua 
  LEFT JOIN user_main um 
    ON um.`userId` = ua.userId 
  LEFT JOIN 
    (SELECT 
      SUM(li.`investAmount`) AS investAmountSum,
      li.`investorUserId` 
    FROM
      loan_investor li 
    WHERE li.`pnrStatus` = 1 
      AND li.lastRepayDate > NOW() 
    GROUP BY li.`investorUserId`) AS zaitouTab 
    ON zaitouTab.investorUserId = ua.userId 
  LEFT JOIN 
    (SELECT 
      MAX(li2.`investTime`) AS lastInvestTime,
      li2.`investorUserId` 
    FROM
      loan_investor li2 
    WHERE li2.`pnrStatus` = 1 
    GROUP BY li2.`investorUserId` 
    ORDER BY li2.id DESC) AS lastTouziTab 
    ON lastTouziTab.investorUserId = ua.userId 
  LEFT JOIN 
    (SELECT 
      COUNT(*) AS investorCNT,
      li3.`investorUserId` 
    FROM
      loan_investor li3 
    WHERE li3.`pnrStatus` = 1 
    GROUP BY li3.`investorUserId`) AS investorCNTTab 
    ON investorCNTTab.investorUserId = ua.userId 
WHERE ua.cash > 0 
  AND um.`roles` = 1 




#4、关于投资
#（1）某时间段每日交易明细【可参考附件03】
#以投资日期时间段进行查询（日期可改），查询每笔投资记录，包含：姓名、手机号、项目名称、项目期限、投资金额、投资日期、注册时间、注册来源
SELECT 
  um.realName "用户姓名",
  um.mobile AS '手机号', 
  li.investorNickname "投资项目名称",
  l.termCount "投资项目期限",
  li.investAmount "投资金额",
  DATE_FORMAT(li.investTime, "%Y-%m-%d") "投资日期" ,
  DATE_FORMAT(um.registerTime, "%Y-%m-%d") "注册时间",
  um.source AS '注册来源'
FROM
  loan_investor li
LEFT JOIN user_main um ON um.`userId` = li.investorUserId 
LEFT JOIN loan l ON l.loanId = li.loanId  
WHERE DATE_FORMAT(li.investTime, "%Y-%m-%d") >= '2017-08-01' 
  AND DATE_FORMAT(li.investTime, "%Y-%m-%d") <= '2017-08-01' 
  AND li.pnrStatus = 1 
  
  
  
#（2）某时间段注册用户转化数据【可参考附件02】
#某时间段注册的用户，截止人工查询时间点的投资情况，数据包含以下维度：
#姓名、手机号、年龄、注册来源、注册时间、开户时间、首笔投资时间、首笔投资金额
#投资总次数、总充值金额、投资总金额、总提现金额、可用余额、新手项目投资金额
#30天项目投资次数、30天项目投资金额、60天项目投资次数、60天项目投资金额
#90天项目投资次数、90天项目投资金额、180天项目投资次数、180天项目投资金额
#360天项目投资次数、360天项目投资金额  


SELECT 
  u.userId "用户ID",
  u.realName "姓名",
  u.mobile "手机",
  DATE_FORMAT(NOW(), '%Y') - SUBSTRING(u.idCardNo, 7, 4) AS '年龄',
  IFNULL(u.source, "") "注册来源",
  DATE_FORMAT(
    u.registerTime,
    "%Y-%m-%d %H:%i:%S"
  ) AS 注册时间,
  IFNULL(up.openTime, "") "开户时间",
  IFNULL(li.investTime, "") "首笔投资时间",
  IFNULL(li.investAmount, "") "首笔投资金额",
  COUNT(lii.id) "投资总次数",
  IFNULL(ur.ZCZ, 0) "总充值金额",
  IFNULL(l.ZTZ, 0) "投资总金额",
  IFNULL(us.ZTX, 0) "总提现金额",
  up.cash "可用余额",
  IFNULL(touzi.xinshoutouzi, 0) "新手项目投资金额",
  IFNULL(touzi.sanshitian, 0) "30天项目投资次数",
  IFNULL(touzi.sanshitiantouzi, 0) "30天项目投资金额",
  IFNULL(touzi.liushitian, 0) "60天项目投资次数",
  IFNULL(touzi.liushitiantouzi, 0) "60天项目投资金额",
  IFNULL(touzi.jiushitian, 0) "90天项目投资次数",
  IFNULL(touzi.jiushitiantouzi, 0) "90天项目投资金额",
  IFNULL(touzi.yibaibashitian, 0) "180天项目投资次数",
  IFNULL(touzi.yibaibashitiantouzi, 0) "180天项目投资金额",
  IFNULL(touzi.sanbailiushitian, 0) "360天项目投资次数",
  IFNULL(touzi.sanbailiushitiantouzi, 0) "360天项目投资金额" 
FROM
  user_main AS u 
  LEFT JOIN user_account up 
    ON up.userId = u.userId 
  LEFT JOIN 
    (SELECT 
      userId,
      SUM(amount) AS ZCZ 
    FROM
      recharge_log 
    WHERE flag = 1 
    GROUP BY userId) AS ur 
    ON u.userId = ur.userId 
  LEFT JOIN 
    (SELECT 
      userId,
      SUM(amount) AS ZTX 
    FROM
      cash_withdraw_request 
    WHERE pnrStatus = 1 
    GROUP BY userId) AS us 
    ON u.userId = us.userId 
  LEFT JOIN 
    (SELECT 
      investorUserId,
      SUM(investAmount) AS ZTZ 
    FROM
      loan_investor 
    WHERE pnrStatus = 1 
    GROUP BY investorUserId) AS l 
    ON u.userId = l.investorUserId 
  LEFT JOIN 
    (SELECT 
      * 
    FROM
      loan_investor i 
    WHERE i.pnrStatus = 1 
    GROUP BY i.investorUserId) AS li 
    ON u.userId = li.investorUserId 
  LEFT JOIN 
    (SELECT 
      * 
    FROM
      loan_investor li 
    WHERE li.pnrStatus = 1) AS lii 
    ON u.userId = lii.investorUserId 
  LEFT JOIN 
    (SELECT 
      SUM(
        CASE
          WHEN c.isRookiePrj = 1 
          THEN b.investAmount 
          ELSE 0 
        END
      ) AS xinshoutouzi,
      SUM(
        CASE
          WHEN termCount >= 1 
          AND termCount <= 30 
          AND c.isRookiePrj <> 1 
          THEN 1 
          ELSE 0 
        END
      ) AS sanshitian,
      SUM(
        CASE
          WHEN termCount >= 1 
          AND termCount <= 30 
          AND c.isRookiePrj <> 1 
          THEN b.investAmount 
          ELSE 0 
        END
      ) AS sanshitiantouzi,
      SUM(
        CASE
          WHEN termCount > 30 
          AND termCount <= 60 
          THEN 1 
          ELSE 0 
        END
      ) AS liushitian,
      SUM(
        CASE
          WHEN termCount > 30 
          AND termCount <= 60 
          THEN b.investAmount 
          ELSE 0 
        END
      ) AS liushitiantouzi,
      SUM(
        CASE
          WHEN termCount > 60 
          AND termCount <= 90 
          THEN 1 
          ELSE 0 
        END
      ) AS jiushitian,
      SUM(
        CASE
          WHEN termCount > 60 
          AND termCount <= 90 
          THEN b.investAmount 
          ELSE 0 
        END
      ) AS jiushitiantouzi,
      SUM(
        CASE
          WHEN termCount > 90 
          AND termCount <= 180 
          THEN 1 
          ELSE 0 
        END
      ) AS yibaibashitian,
      SUM(
        CASE
          WHEN termCount > 90 
          AND termCount <= 180 
          THEN b.investAmount 
          ELSE 0 
        END
      ) AS yibaibashitiantouzi,
      SUM(
        CASE
          WHEN termCount > 180 
          AND termCount <= 360 
          THEN 1 
          ELSE 0 
        END
      ) AS sanbailiushitian,
      SUM(
        CASE
          WHEN termCount > 180 
          AND termCount <= 360 
          THEN b.investAmount 
          ELSE 0 
        END
      ) AS sanbailiushitiantouzi,
      DATE_FORMAT(b.`investTime`, '%Y-%m-%d') AS investTime,
      b.investorUserId 
    FROM
      loan a,
      loan_investor b,
      loan_desc c 
    WHERE a.loanId = b.loanId 
      AND b.loanId = c.loanID 
      AND b.pnrStatus = 1 
    GROUP BY b.investorUserId) AS touzi 
    ON touzi.investorUserId = u.userId 
WHERE u.roles = 1 
  AND DATE_FORMAT(u.registerTime, "%Y-%m-%d") >= "2017-08-01" 
  AND DATE_FORMAT(u.registerTime, "%Y-%m-%d") <= "2017-08-31" 
GROUP BY u.userId 
ORDER BY u.userId DESC