连续天数的数据:Oracle 与 MySQL 查询技巧

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 连续天数的数据:Oracle 与 MySQL 查询技巧

问题:最近有开发需求,查询最近一个月连续天数>=2的数据项,我这里数据库日期存储的是yyyymmdd,字符串 (可自行根据自身情况修改)

思路:如果可以找到连续日期的开始与结束时间,那么就可以 where 日期 between 开始日期 and 结束日期即可

关键代码(Mysql方式):

1. SELECT
2.  STAT_DATE, -- 数据库日期字段,我这里是yyyymmdd形式的字符串存储
3.  DATE_ADD( b.STAT_DATE, INTERVAL - ( @rownum := @rownum + 1 ) DAY ) ROWNUM -- 序列
4. FROM
5.  ( SELECT @rownum := 0 ) a,
6.  YCYP_TG_ABN_ANALYSE b 
7.  WHERE -- 筛选条件,筛选最近一个月的数据(2022-07-01是前端传的参数)
8.  b.TG_ID = '4110100001'
9.  AND b.STAT_DATE BETWEEN DATE_FORMAT( DATE_ADD( '2022-07-01', INTERVAL - 30 DAY ), '%Y%m%d' ) 
10.   AND DATE_FORMAT( str_to_date( '2022-07-01', '%Y-%m-%d' ), '%Y%m%d' ) 
11. ORDER BY
12.   b.STAT_DATE ASC

结果如下:

我们发现,所有最近一个月的日期都会被查询出来,并且有个ROWNUM 标记字段,有没有发现,如果是连续日期的话,ROWNUM值是一样的。

所以我们紧接着根据ROWNUM分组,找出分组后个数>=2的,然后求每个分组的最小日期,降序(由于有多个连续天数的话,我们取最近的)

1. SELECT
2.  MIN( t1.STAT_DATE ) AS stat_date,
3.  MAX( t1.STAT_DATE ) AS end_time 
4. FROM
5.  (
6.  SELECT
7.    STAT_DATE,-- 数据库日期字段,我这里是yyyymmdd形式的字符串存储
8.    DATE_ADD( b.STAT_DATE, INTERVAL - ( @rownum := @rownum + 1 ) DAY ) ROWNUM -- 序列
9. 
10.   FROM
11.     ( SELECT @rownum := 0 ) a,
12.     YCYP_TG_ABN_ANALYSE b 
13.     WHERE-- 筛选条件,筛选最近一个月的数据(2022-07-01是前端传的参数)
14.     b.TG_ID = '4110100001'
15.     AND b.STAT_DATE BETWEEN DATE_FORMAT( DATE_ADD( '2022-07-01', INTERVAL - 30 DAY ), '%Y%m%d' ) 
16.     AND DATE_FORMAT( str_to_date( '2022-07-01', '%Y-%m-%d' ), '%Y%m%d' ) 
17.   ORDER BY
18.     b.STAT_DATE ASC
19.   ) t1 
20. GROUP BY
21.   t1.ROWNUM 
22. HAVING
23.   count(*) >= 2
24. ORDER BY
25.   stat_date DESC
26. LIMIT 1

如图:我们就得到了连续日期的开始和结束日期,排序过后,我们需要加个limit 1,因为只取最近的连续日期。

所以 limit 1 之后,最终得到结果

20220629 ---2022-0701

那我们只需要在表中查询时间在这个范围内的即可。

oracle代码(Oracle方式):

1. SELECT
2.  start_date,
3.  end_date 
4. FROM
5.  (
6.  SELECT
7.    MIN( STAT_DATE ) AS start_date,
8.    MAX( STAT_DATE ) AS end_date 
9.  FROM
10.     (
11.     SELECT
12.       STAT_DATE,
13.       TO_DATE( STAT_DATE, 'YYYY-MM-DD' ) - ROW_NUMBER ( ) OVER ( PARTITION BY TG_ID ORDER BY STAT_DATE ) AS grp 
14.     FROM
15.       YCYP_TG_ABN_ANALYSE 
16.     WHERE
17.       TG_ID = '4110100001'
18.       AND STAT_DATE BETWEEN TO_CHAR( TO_DATE( '2022-07-01', 'YYYY-MM-DD' ) - 30, 'YYYYMMDD' ) 
19.       AND REPLACE ( '2022-07-01', '-', '' ) 
20.     ) 
21.   GROUP BY
22.     grp 
23.   HAVING
24.     COUNT( * ) >= 2
25.   ORDER BY
26.     start_date DESC
27.   ) 
28. WHERE
29.   ROWNUM = 1

结果:

注意

这里的tg_id是分组的列,可以理解为如果tg_id不一样,就算日期连续,但是不是一类,也不会给分配一样的行号。(自行根据自己所需业务填写)

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
4
分享
相关文章
MySQL 和 Oracle 的区别?
本文对比了Oracle和MySQL数据库的多个方面。Oracle适用于大型数据库,支持高并发和大访问量,市场占有率为40%,安装占用空间较大,约3G;而MySQL适合中小型应用,是开源免费的,安装仅需152M。两者在主键生成、字符串处理、SQL语句、事务处理等方面存在差异。Oracle功能更为强大,尤其在企业级应用中表现突出,而MySQL则以简单易用见长。
MySQL 和 Oracle 的区别?
SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
通过详细的步骤和示例代码,您可以在 SpringBoot 项目中成功集成 Flink CDC,并实时追踪 MySQL 数据库的变动。
139 43
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
28 9
MySQL进阶突击系列(09)数据磁盘存储模型 | 一行数据怎么存?
文中详细介绍了MySQL数据库中一行数据在磁盘上的存储机制,包括表空间、段、区、页和行的具体结构,以及如何设计和优化行数据存储以提高性能。
Mysql、Oracle审计日志的开启
通过上述步骤,可以在 MySQL 和 Oracle 数据库中启用和配置审计日志。这些日志对于监控数据库操作、提高安全性和满足合规性要求非常重要。确保正确配置审计参数和策略,定期查看和分析审计日志,有助于及时发现并处理潜在的安全问题。
58 11
【YashanDB知识库】oracle dblink varchar类型查询报错记录
在使用Oracle DBLink查询VARCHAR类型数据时,可能会遇到多种报错。通过了解常见错误原因,采取合适的解决方法,可以有效避免和处理这些错误。希望本文提供的分析和示例能帮助你在实际工作中更好地处理DBLink查询问题。
60 10
【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
问题分类】功能使用 【关键字】char,char(1) 【问题描述】MySQL 迁移至崖山环境,字段类型源端和目标端都为 char(2),但应用存储的数据为'0'、'1',此时崖山查询该表字段时会自动补充空格 【问题原因分析】mysql 有 sql_mode 控制,检查是否启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式。如果启用了这个模式,MySQL 才会保留 CHAR 类型字段的尾随空格,默认没有启动。 #查看sql_mode mysql> SHOW VARIABLES LIKE 'sql_mode'; 【解决/规避方法】与应用确认存储的数据,正确定义数据
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
326 0

推荐镜像

更多