首页 > 数据库 >MySQL子查询、WITH AS、LAG查询统计数据实战

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

时间:2024-08-21 17:27:11浏览次数:14  
标签:count stat 12 LAG 查询 MySQL date os SELECT

需求

给出一个比较常见的统计类业务需求:统计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 &lt;= #{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 &lt;= #{time};

注意到一定要使用LIMIT 1来限制只返回一条数据,否则报错:Subquery returns more than 1 rowmaxmin函数只会返回一条数据,所以不用冗余追加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 joinleft 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]}foreachcollection有重复第一个元素,一开始想要改造collection标签元素,没搞定。

咱不就是想去重嘛。去重的话,使用UNION替换UNION ALL

其他就是foreach的几个元素的处理:opencloseseparator,都置为空即可。

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 &lt;= 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真™强大啊!!

标签:count,stat,12,LAG,查询,MySQL,date,os,SELECT
From: https://www.cnblogs.com/johnny-wong/p/18372156

相关文章

  • 更快更强,SLS 推出高性能 SPL 日志查询模式
    作者:无哲引言随着数字化进程的持续深化,可观测性一直是近年来非常火热的话题,在可观测的三大支柱Log/Trace/Metric中,日志(Log)数据一般是最为常见的,企业迈向可观测性的第一步,也往往始于日志数据的采集上云。日志完成收集后,最直接的需求就是从海量日志数据中检索分析出有价值的信息......
  • 数据库运维实操优质文章分享(含Oracle、MySQL等) | 2024年7月刊
    本文为大家整理了墨天轮数据社区2024年7月发布的优质技术文章/文档,主题涵盖Oracle、MySQL、PostgreSQL等主流数据库系统以及国产数据库的深度教程和实用指南。从基础的安装配置到复杂的故障排查,再到性能优化的高级技巧,每篇文章都是由领域专家精心撰写。此外,还有常用脚本和操作注意......
  • 【mysql】纯干货--mysql连接与启动
      本站以分享各种运维经验和运维所需要的技能为主《python零基础入门》:python零基础入门学习《python运维脚本》: python运维脚本实践《shell》:shell学习《terraform》持续更新中:terraform_Aws学习零基础入门到最佳实战《k8》从问题中去学习k8s《docker学习》暂未更新......
  • MYSQL忘记root密码
    需求背景最近发现用户服务器的mysql是别人安装的,但是没有告诉它任何关于mysql的信息。所以我这里写一篇文章记录一下。以下教程是centos7.9的语法,其余操作系统参照操作步骤,方便有个方向。教程开始第一种直接重新安装//这个方法适合不需要保留以前的任何数据//直接卸载......
  • MySQL 通过set global设置变量的注意点
    今天有人问,为什么他修改动态变量log_output的时候,部分可以设置成功,部分设置失败,具体现象如下:root@localhost(none)>showvariableslike'log_output';+---------------+-------+|Variable_name|Value|+---------------+-------+|log_output|TABLE|+--------......
  • 使用Mybatis 的PageHelper插件实现分页查询功能
    Mybatis提供了一个分页插件PageHelper,它会帮助我们自动完成分页和获取总数量的操作,可以方便实现分页查询。第一步,在pom.xml文件引入PageHelper依赖。<dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper-spring-boot-s......
  • mysql 语句的执行顺序
    MySQL语句的执行顺序如下:FROM 子句:确定数据来源的表或表达式。ON 子句:应用 JOIN 条件,如果有的话。JOIN 子句:如果有的话,根据 JOIN 类型(如 INNERJOIN, LEFTJOIN 等)连接表。WHERE 子句:过滤记录,只留下满足条件的记录。GROUPBY 子句:根据指定的......
  • MySQL 禁止使用 HASH 索引
    在MySQL中,虽然InnoDB存储引擎默认使用的是BTREE索引,但它也支持一种特殊的自适应哈希索引(adaptivehashindex,AHI),这是InnoDB自动管理和使用的,用户无法直接控制。然而,对于内存存储引擎MEMORY(之前称为HEAP),MySQL确实允许用户显式地创建HASH索引。如果你想要在MEMORY表上使用HASH索......
  • 自定义 MySQL Shell 提示符
    MySQLShell中的提示符的样式和格式可以根据使用者的需求进行定制。我们可以配置提示符以显示有关数据库连接和使用的模式的不同或简化信息。本文将展示如何添加视觉提示,让您知道何时连接到生产数据库。设置步骤既然您正在阅读关于自定义MySQLShell的内容,您应该已经安装了它......
  • MySQL数据库命令行操作
    MySQL数据库命令行操作运行输入cmd有的要以管理员身份进入登录数据库mysql-h主机名-u用户名-p或者mysql-uroot-p-h:该命令用于指定客户端所要登录的MySQL主机名,登录当前机器该参数可以省略;-u:所要登录的用户名;-p:告诉服务器将会使用一个密码来......