MySQL子查询、WITH AS、LAG查询统计数据实战

2023-12-23 17:52
文章标签 实战 mysql 查询 database 统计数据 lag

本文主要是介绍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());// 有新用户数才insertif (check) {for (OsDayCountVO item : resp.getData()) {OsDayCount po = BeanConvertUtils.convert(item, OsDayCount.class);osDayCountMapper.insert(po);// 前一天 osStatCount = 前一天 statCount + 前两天 osStatCountString 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 entryosDayCountMapper.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_countWHERE stat_date &lt;= #{time}AND os = #{os};
</select>

Service层通过简简单单一个for循环来执行 2 ∗ N 2*N 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 &lt;= #{time};

注意到一定要使用LIMIT 1来限制只返回一条数据，否则报错：Subquery returns more than 1 row。max或min函数只会返回一条数据，所以不用冗余追加limit 1限制。

组合之后，写出如下SQL：

SELECTdates.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_dateUNION ALL SELECT '2023-12-17'UNION ALL SELECT '2023-12-18') AS dates
ORDER BYdates.stat_date;

达到效果。

那如何进一步区分os枚举类型信息呢？当然也是join。不过不是使用left join，left join需要使用on条件关联一下。这里使用cross join。

最终的SQL如下：

SELECTdates.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_dateUNION 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 BYdates.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">SELECTdates.stat_date AS statDate,oss.os,(SELECT os_stat_count FROM os_day_count WHERE stat_date &lt;= dates.stat_date AND os = oss.os ORDER BY stat_dateDESC limit 1) AS statCountFROM(SELECT #{timeList[0]} AS stat_date<foreach close="" collection="timeList" item="item" open="" separator="">UNION SELECT#{item}</foreach>) AS datesCROSS JOIN (SELECT DISTINCT os FROM os_day_count) AS ossORDER BY dates.stat_date;
</select>

MySQL 8

借助于MySQL 8提供的WITH AS及LAG函数，可写出如下SQL：

WITH dates AS (SELECT '2023-12-16' AS stat_dateUNION ALL SELECT '2023-12-17'UNION ALL SELECT '2023-12-18'
),
cte AS (SELECTdates.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_countFROMdatesLEFT JOINos_day_count ON dates.stat_date = os_day_count.stat_date
)
SELECTstat_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
FROMcte
ORDER BYstat_date;

如果想要进一步增加OS信息，写出如下SQL：

TODO：cross join os后有重复的数据

最后

在写SQL的过程中，还是相当耗费一些心力的，各种Stackoverflow浏览帖子，各种Google搜索，没有找到解决方案。也体验过CSDN推出的C知道，呵呵。OpenAI的Chat GPT也体验过，虽然比C知道强，但是也没有拿到满意的答案。

最后在CSDN问答里发布帖子MySQL查询不存在的日期数据。不过1~2分钟，就拿到满意的答案。不得不说，GitHub与OpenAI强强联合推出的GitHub Copilot真™强大啊！！

这篇关于MySQL子查询、WITH AS、LAG查询统计数据实战的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

原文地址:
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.chinasem.cn/article/528893。

相关文章

MySQL 表的内外连接案例详解

MySQL 表的内外连接案例详解

《MySQL表的内外连接案例详解》本文给大家介绍MySQL表的内外连接,结合实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录表的内外连接（重点）内连接外连接表的内外连接（重点）内连接内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我
阅读更多...
Java Spring 中的监听器Listener详解与实战教程

Java Spring 中的监听器Listener详解与实战教程

《JavaSpring中的监听器Listener详解与实战教程》Spring提供了多种监听器机制,可以用于监听应用生命周期、会话生命周期和请求处理过程中的事件,：本文主要介绍JavaSprin... 目录一、监听器的作用1.1 应用生命周期管理1.2 会话管理1.3 请求处理监控二、创建监听器2.1 Ser
阅读更多...
MySQL的ALTER TABLE命令的使用解读

MySQL的ALTER TABLE命令的使用解读

《MySQL的ALTERTABLE命令的使用解读》：本文主要介绍MySQL的ALTERTABLE命令的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、查看所建表的编China编程码格式2、修改表的编码格式3、修改列队数据类型4、添加列5、修改列的位置5.1、把列
阅读更多...
Mybatis嵌套子查询动态SQL编写实践

Mybatis嵌套子查询动态SQL编写实践

《Mybatis嵌套子查询动态SQL编写实践》：本文主要介绍Mybatis嵌套子查询动态SQL编写方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、实体类1、主类2、子类二、Mapper三、XML四、详解总结前言MyBATis的xml文件编写动态SQL
阅读更多...
解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

《解决mysql插入数据锁等待超时报错:Lockwaittimeoutexceeded;tryrestartingtransaction》：本文主要介绍解决mysql插入数据锁等待超时报... 目录报错信息解决办法1、数据库中执行如下sql2、再到 INNODB_TRX 事务表中查看总结报错信息Lock
阅读更多...
Apache 高级配置实战之从连接保持到日志分析的完整指南

Apache 高级配置实战之从连接保持到日志分析的完整指南

《Apache高级配置实战之从连接保持到日志分析的完整指南》本文带你从连接保持优化开始,一路走到访问控制和日志管理,最后用AWStats来分析网站数据,对Apache配置日志分析相关知识感兴趣的朋友... 目录Apache 高级配置实战：从连接保持到日志分析的完整指南前言 一、Apache 连接保持 - 性
阅读更多...
MySQL启动报错：InnoDB表空间丢失问题及解决方法

MySQL启动报错：InnoDB表空间丢失问题及解决方法

《MySQL启动报错：InnoDB表空间丢失问题及解决方法》在启动MySQL时,遇到了InnoDB:Tablespace5975wasnotfound,该错误表明MySQL在启动过程中无法找到指定的s... 目录mysql 启动报错：InnoDB 表空间丢失问题及解决方法错误分析解决方案1. 启用 inno
阅读更多...
MySQL 安装配置超完整教程

MySQL 安装配置超完整教程

《MySQL安装配置超完整教程》MySQL是一款广泛使用的开源关系型数据库管理系统（RDBMS）,由瑞典MySQLAB公司开发,目前属于Oracle公司旗下产品,：本文主要介绍MySQL安装配置... 目录一、mysql 简介二、下载 MySQL三、安装 MySQL四、配置环境变量五、配置 MySQL5.1
阅读更多...
MySQL 添加索引5种方式示例详解(实用sql代码)

MySQL 添加索引5种方式示例详解(实用sql代码)

《MySQL添加索引5种方式示例详解(实用sql代码)》在MySQL数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中,下面给大家分享MySQL添加索引5种方式示例详解(实用sql代码),... 在mysql数据库中添加索引可以帮助提高查询性能，尤其是在数据量大的表中。索引可以在创建表时定义，也可
阅读更多...
Mybatis Plus JSqlParser解析sql语句及JSqlParser安装步骤

Mybatis Plus JSqlParser解析sql语句及JSqlParser安装步骤

《MybatisPlusJSqlParser解析sql语句及JSqlParser安装步骤》JSqlParser是一个用于解析SQL语句的Java库,它可以将SQL语句解析为一个Java对象树,允许... 目录【一】jsqlParser 是什么【二】JSqlParser 的安装步骤【三】使用场景【1】sql语
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2