MySQL子查询、WITH AS、LAG查询统计数据实战
需求
给出一个比较常见的统计类业务需求:统计App(包括iOS和Android两大类)每日新注册用户数、以及累计注册用户数。
数据库采用MySQL,根据上面的需求,不难设计表如下:
create table os_day_count(
stat_date varchar(10) not null comment '统计日期',
os varchar(7) not null comment '操作系统类型',
stat_count int not null comment '用户数',
os_stat_count int null comment 'os类型累计用户数',
primary key (stat_date, os)
) comment '每日App新装机统计表';
由于面对的是一个日活量非常小的App,经常出现每日新增用户数为0的情况。
insert
数据落库逻辑如下:
public void appOsStatisticFromUser(String time) {
// 远程Feign接口获取新用户数
Response<List<OsDayCountVO>> resp = remoteUserService.appOsStats(time);
boolean check = resp != null && resp.getCode() == 0 && CollectionUtils.isNotEmpty(resp.getData());
// 有新用户数才insert
if (check) {
for (OsDayCountVO item : resp.getData()) {
OsDayCount po = BeanConvertUtils.convert(item, OsDayCount.class);
osDayCountMapper.insert(po);
// 前一天 osStatCount = 前一天 statCount + 前两天 osStatCount
String twoDayAgo = DateUtils.addDay(DateUtils.parse(item.getStatDate(), DateUtils.DATE_SMALL_STR), DateUtils.DATE_SMALL_STR, -1);
Integer count = osDayCountMapper.osMax(twoDayAgo, item.getOs());
po.setOsStatCount(count + item.getStatCount());
// 此处update逻辑一定要注意where条件限制否则报错:SQLIntegrityConstraintViolationException Duplicate entry
osDayCountMapper.update(po, new LambdaUpdateWrapper<OsDayCount>().eq(OsDayCount::getStatDate, item.getStatDate()).eq(OsDayCount::getOs, item.getOs()));
}
}
}
问题
上面的业务逻辑没有问题,运行之后,数据库如下:
表里的数据不是连续的!!没有某个stat_date日期的数据则表示该天没有新增用户,os_stat_count表示的是累计用户数。
现在想要查询【连续】日期的用户数,即实现
// 没有2023-12-18数据,则取2023-12-17;没有2023-12-17数据,则取2023-12-16;以此类推
select stat_date, os_stat_count from os_day_count where stat_date in ('2023-12-16','2023-12-17','2023-12-18');
最后返回的数据应该有3行,分别是2023-12-16、2023-12-17、2023-12-18,而且因为2023-12-17和2023-12-18没有新增用户。故而查询出来的三行数据结果是一模一样的。
实现方案
全量冗余存储
想要查询某个连续时间段,如最近一个月的累计用户数。很简单,修改insert
逻辑即可,每天都落数据,哪怕和前一天数据一模一样。这样查询时直接使用上面的SQL即可实现功能。
但是这样会在数据库里全量存储很多冗余数据。不建议。
应用层实现
保持insert
逻辑不变,那就需要在select
处花点心思,也很简单。
数据库PO实体类定义如下:
@Data
@TableName(value = "os_day_count")
public class OsDayCount {
@TableId(value = "stat_date", type = IdType.NONE)
private String statDate;
private String os;
private Integer statCount;
private Integer osStatCount;
public OsDayCount(String statDate, String os, Integer statCount) {
this.statDate = statDate;
this.os = os;
this.statCount = statCount;
}
}
枚举类定义:
@Getter
@AllArgsConstructor
public enum OsEnum {
IOS("iOS", "iOS"),
ANDROID("Android", "Android"),
ALL("ALL", "ALL");
private final String desc;
private final String name;
public static String getNameByDesc(String desc) {
for (OsEnum osEnum : OsEnum.values()) {
if (osEnum.desc.equals(desc)) {
return osEnum.name;
}
}
return null;
}
}
Mapper接口类定义查询方法:
Integer osMax(@Param("time") String time, @Param("os") String os);
对应的MyBatis mapper.xml
文件:
<select id="osMax" resultType="java.lang.Integer">
SELECT ifnull(max(os_stat_count), 0)
FROM os_day_count
WHERE stat_date <= #{time}
AND os = #{os};
</select>
Service层通过简简单单一个for
循环来执行$2*N$次SQL查询实现,其中2表示枚举类定义的类型个数,N表示查询日期跨度。
List<OsDayCount> osList = Lists.newArrayListWithExpectedSize(dto.getTimeList().size() * 2);
for (String item : dto.getTimeList()) {
osList.add(new OsDayCount(item, OsEnum.ANDROID.getDesc(), osDayCountMapper.osMax(item, OsEnum.ANDROID.getDesc())));
osList.add(new OsDayCount(item, OsEnum.IOS.getDesc(), osDayCountMapper.osMax(item, OsEnum.IOS.getDesc())));
}
不管是查询日期跨度增加,还是换一种场景,枚举类型个数增长。上面这种方式都是极不可取的。
SQL
上面这种for循环肯定不可取,因此有必要替换成一个SQL来实现查询取数逻辑。提到MySQL实现,一般都会有MySQL 8和非MySQL 8两种情况。
非MySQL 8
相当多的公司,哪怕他们的业务并不是金融或保险或交易相关等,也不会(不敢)考虑选择(或升级迁移)使用MySQL 8。哪怕MySQL 8于2018年4月份发布,距今已经五年多。原因无外乎慎重起见、因循守旧等。
事实上,这几年工作中,鄙人也仅在一家公司的一个产品中,在生产中用过MySQL 8。
不难分析出来,stat_date是一个非常关键的字段,由于数据库里并没有存储2023-12-17,2023-12-18两天的数据。
因此非常有必要做一个子查询:
SELECT '2023-12-16' AS stat_date
UNION ALL SELECT '2023-12-17'
UNION ALL SELECT '2023-12-18' AS dates
此子查询返回期望的多行日期数据。然后关联另一个子查询:
SELECT os_stat_count FROM os_day_count WHERE stat_date <= dates.stat_date ORDER BY stat_date DESC LIMIT 1;
事实上,这个子查询和上面的应用层实现方案里的查询逻辑一样:
SELECT ifnull(max(os_stat_count), 0) FROM os_day_count WHERE stat_date <= #{time};
注意到一定要使用LIMIT 1
来限制只返回一条数据,否则报错:Subquery returns more than 1 row
。max
或min
函数只会返回一条数据,所以不用冗余追加limit 1
限制。
组合之后,写出如下SQL:
SELECT
dates.stat_date,
(SELECT os_stat_count FROM os_day_count WHERE stat_date <= dates.stat_date ORDER BY stat_date DESC LIMIT 1) AS os_stat_count
FROM
(SELECT '2023-12-16' AS stat_date
UNION ALL SELECT '2023-12-17'
UNION ALL SELECT '2023-12-18') AS dates
ORDER BY
dates.stat_date;
达到效果。
那如何进一步区分os枚举类型信息呢?当然也是join
。不过不是使用left join
,left join
需要使用on
条件关联一下。这里使用cross join
。
最终的SQL如下:
SELECT
dates.stat_date,
oss.os,
(SELECT os_stat_count FROM os_day_count WHERE stat_date <= dates.stat_date and os = oss.os ORDER BY stat_date DESC limit 1) AS os_stat_count
FROM
(
SELECT '2023-12-16' AS stat_date
UNION ALL SELECT '2023-12-17'
UNION ALL SELECT '2023-12-18'
) AS dates
cross join (select distinct os from os_day_count) AS oss
ORDER BY
dates.stat_date;
SQL没有问题,实现期望效果。那如何把SQL转写为MyBatis Mapper.xml
文件支持的语法呢?
最关键的部分,还是子查询得到的dates
数据。总不可能一一列出来吧,如果要查询最近半年的数据呢?
MyBatis提供的标签符合此场景的貌似只有foreach
。经过尝试,MyBatis果然支持以Index方式取集合元素,即:#{timeList[0]}
。#{timeList[0]}
和foreach
的collection
有重复第一个元素,一开始想要改造collection
标签元素,没搞定。
咱不就是想去重嘛。去重的话,使用UNION
替换UNION ALL
。
其他就是foreach
的几个元素的处理:open
,close
,separator
,都置为空即可。
Anyway,日期子查询转写成MyBatis语法最终如下:
SELECT #{timeList[0]} AS stat_date
<foreach close="" collection="timeList" item="item" open="" separator="">
UNION SELECT
#{item}
</foreach>
最终版MyBatis mapper.xml
文件如下:
<select id="osSum" resultType="com.aaaaa.collect.data.dao.entity.OsDayCount">
SELECT
dates.stat_date AS statDate,
oss.os,
(SELECT os_stat_count FROM os_day_count WHERE stat_date <= dates.stat_date AND os = oss.os ORDER BY stat_date
DESC limit 1) AS statCount
FROM
(SELECT #{timeList[0]} AS stat_date
<foreach close="" collection="timeList" item="item" open="" separator="">
UNION SELECT
#{item}
</foreach>
) AS dates
CROSS JOIN (SELECT DISTINCT os FROM os_day_count) AS oss
ORDER BY dates.stat_date;
</select>
MySQL 8
借助于MySQL 8提供的WITH AS及LAG函数,可写出如下SQL:
WITH dates AS (
SELECT '2023-12-16' AS stat_date
UNION ALL SELECT '2023-12-17'
UNION ALL SELECT '2023-12-18'
),
cte AS (
SELECT
dates.stat_date,
IFNULL(os_day_count.os_stat_count, LAG(os_day_count.os_stat_count) OVER (ORDER BY dates.stat_date)) AS os_stat_count
FROM
dates
LEFT JOIN
os_day_count ON dates.stat_date = os_day_count.stat_date
)
SELECT
stat_date,
IFNULL(os_stat_count, (SELECT os_stat_count FROM cte WHERE os_stat_count IS NOT NULL ORDER BY stat_date DESC LIMIT 1)) AS os_stat_count
FROM
cte
ORDER BY
stat_date;
如果想要进一步增加OS信息,写出如下SQL:
TODO:cross join os后有重复的数据
最后
在写SQL的过程中,还是相当耗费一些心力的,各种Stackoverflow浏览帖子,各种Google搜索,没有找到解决方案。也体验过CSDN推出的C知道,呵呵。OpenAI的Chat GPT也体验过,虽然比C知道强,但是也没有拿到满意的答案。
最后在CSDN问答里发布帖子MySQL查询不存在的日期数据。不过1~2分钟,就拿到满意的答案。不得不说,GitHub与OpenAI强强联合推出的GitHub Copilot真™强大啊!!