1、场景
业务背景：有一张公众号粉丝关注数总表，按照每天汇总关注总数，解读如下：截止到2022-01-01公众号关注总数为：45087
image.png
业务需求：需要按照月统计各个公众号关注总数。
实现分析：从存储数据来看，只需要按照公众号区分取每个月最后一条数据就是月份总数。
2、实现方式
设计思路：
① 先统计数据库中有多少个月，并取出每个月最后一天，借助Last_day()函数
②使用第一步中的结果集作为参数进行数据筛选。
- 取出数据中的每个月份最后一天
select
distinct last_day(date_format(infc.ref_date,'%Y-%m-%d'))as max_date
from member.wechat_fans_cumulates infc
group by infc.app_id,date_format(infc.ref_date,'%Y-%m')
执行结果：
image.png
- 业务实现sql 数据
select
fc.app_id,
date_format(fc.ref_date,'%Y年%m月') as ref_date,
fc.cumulate_user
from member.wechat_fans_cumulates fc
inner join (select
distinct last_day(date_format(infc.ref_date,'%Y-%m-%d'))as max_date
from member.wechat_fans_cumulates infc
group by infc.app_id,date_format(infc.ref_date,'%Y-%m'))ib on ib.max_date = fc.`ref_date`
group by fc.app_id,date_format(fc.ref_date,'%Y年%m月');
-
执行结果
image.png
该实现方式，由于当前所在月还未到最后一天，存在无法获取当前月数据情况。经过考虑，使用max取每一个月中最大的一天，最终优化sql如下：
select
fc.app_id,
date_format(fc.ref_date, '%Y年%m月') as ref_date,
fc.cumulate_user
from
member.wechat_fans_cumulates fc
inner join (
select app_id,left(ref_date,7),max(ref_date) as max_date
from member.wechat_fans_cumulates
group by app_id,left(ref_date,7)
)ib on fc.app_id = ib.app_id and fc.ref_date = ib.max_date;
3、注意事项
1、这里使用inner join 而非where 子查询，涉及到效率执行问题。
