两年内每月月底需要的退货客户数量

Question

select count(distinct t.user)
from  (
select l.user_id as 'user', l.code as 'loan', ifnull(max(DATEDIFF(ifnull(pr.repaidAt,now()),pr.payAt)),0) as 'days'
from Account a
join Loan l on l.account_id = a.id
join User u on l.user_id = u.id
left join PaymentRequest pr on pr.referredInstance_uuid = l.uuid and pr.requestType = 'cre' and pr.requestState in ('pel','pad')
where l.productType = 'per' and l.repaidAt <= '2021-12-31 23:59:59' and l.user_id not in (select user_id from Loan where repaidAt >= '2021-12-31 23:59:59' or repaidAt is null and user_id=l.user_id and createdAt <= '2021-12-31 23:59:59')
group by l.code) t
where t.days <= 90;

需要将查询中的日期更改为语法以获得如下结果

2021.12.31 1794 
2021.11.30 1805 
2021.10.31 1781 
2021.09.30 1761 
2021.08.31 1746 
2021.07.31 1732 
2021.06.30 1686 
2021.05.31 1659 
2021.04.30 1616 
2021.03.31 1591 
2021.02.28 1560 
2021.01.31 1533 
2020.12.31 1503 
2020.11.30 1461 
2020.10.31 1411 
2020.09.30 1397 
2020.08.31 1344 
2020.07.31 1310 
2020.06.30 1256 
2020.05.31 1255 
2020.04.30 1226 
2020.03.31 1204 
2020.02.28 1151 
2020.01.31 1091

我有一个查询，以获得一个月的退货客户数量。需要改变它，以获得过去两年的数据，每个月。有人能帮我吗？

Answer 1

若要更改日期格式，应使用格式化函数

例如：

SELECT DATE_FORMAT("2017-06-15", "%Y.%m.%d");

会导致2017.06.15

就你的情况而言，应该是这样：

select date_format(your_date_column,"%Y.%m.%d"),count(distinct t.user)
from  (
select l.user_id as 'user', l.code as 'loan', ifnull(max(DATEDIFF(ifnull(pr.repaidAt,now()),pr.payAt)),0) as 'days'
from Account a
join Loan l on l.account_id = a.id
join User u on l.user_id = u.id
left join PaymentRequest pr on pr.referredInstance_uuid = l.uuid and pr.requestType = 'cre' and pr.requestState in ('pel','pad')
where l.productType = 'per' and l.repaidAt <= '2021-12-31 23:59:59' and l.user_id not in (select user_id from Loan where repaidAt >= '2021-12-31 23:59:59' or repaidAt is null and user_id=l.user_id and createdAt <= '2021-12-31 23:59:59')
group by l.code) t
where t.days <= 90;

Answer 2

您可以在主select子句中这样做：

select concat(date(repaidAt), ' ', count(distinct t.user));

但是要支持这一点，您需要在子查询中使用select repaidAt，并且可能需要使用聚合函数，比如max，或者根据实际需求将其添加到group by中。