下边一大片没啥重要的也就这一句经常用到
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