【MySQL】日期格式为 YYYY-MM 无法直接使用 DATE_SUB 函数的解决方案(特殊处理 或 PERIOD_DIFF 函数)
力扣题
1、题目地址
1843. 可疑银行账户
2、模拟表
表:Accounts
| Column Name | Type |
|---|---|
| account_id | int |
| max_income | int |
- account_id 是这张表具有唯一值的列。
- 每行包含一个银行账户每月最大收入的信息。
表:Transactions
| Column Name | Type |
|---|---|
| transaction_id | int |
| account_id | int |
| type | ENUM |
| amount | int |
| day | datetime |
- transaction_id 是这张表具有唯一值的列。
- 每行包含一条转账信息。
- type 是枚举类型(包含’Creditor’,‘Debtor’),其中 ‘Creditor’ 表示用户向其账户存入资金,‘Debtor’ 表示用户从其账户取出资金。
- amount 是交易过程中的存入/取出的金额。
3、要求
如果一个账户在 连续两个及以上 月份的 总收入 超过最大收入(max_income),那么认为这个账户 可疑。 账户当月 总收入 是当月存入资金总数(即 transactions 表中 type 字段的 ‘Creditor’)。
编写一个解决方案,报告所有的 可疑 账户。
以 任意顺序 返回结果表
返回结果格式如下示例所示。
示例 1:
输入:
Accounts 表:
| account_id | max_income |
|---|---|
| 3 | 21000 |
| 4 | 10400 |
Transactions 表:
| transaction_id | account_id | type | amount | day |
|---|---|---|---|---|
| 2 | 3 | Creditor | 107100 | 2021-06-02 11:38:14 |
| 4 | 4 | Creditor | 10400 | 2021-06-20 12:39:18 |
| 11 | 4 | Debtor | 58800 | 2021-07-23 12:41:55 |
| 1 | 4 | Creditor | 49300 | 2021-05-03 16:11:04 |
| 15 | 3 | Debtor | 75500 | 2021-05-23 14:40:20 |
| 10 | 3 | Creditor | 102100 | 2021-06-15 10:37:16 |
| 14 | 4 | Creditor | 56300 | 2021-07-21 12:12:25 |
| 19 | 4 | Debtor | 101100 | 2021-05-09 15:21:49 |
| 8 | 3 | Creditor | 64900 | 2021-07-26 15:09:56 |
| 7 | 3 | Creditor | 90900 | 2021-06-14 11:23:07 |
输出:
| account_id |
|---|
| 3 |
解释:
对于账户 3:
- 在 2021年6月,用户收入为 107100 + 102100 + 90900 = 300100。
- 在 2021年7月,用户收入为 64900。
可见收入连续两月超过21000的最大收入,因此账户3列入结果表中。
对于账户 4:
- 在 2021年5月,用户收入为 49300。
- 在 2021年6月,用户收入为 10400。
- 在 2021年7月,用户收入为 56300。
可见收入在5月与7月超过了最大收入,但6月没有。因为账户没有没有连续两月超过最大收入,账户4不列入结果表中。
4、代码编写
思路
1、Transactions 中根据 account_id 和 day里面的年月进行分组,加上查询条件 type=‘Creditor’(存入的钱),就可以查询到不同账户在不同月份存钱的总量
SELECT account_id, DATE_FORMAT(day, '%Y-%m') AS `month`, SUM(amount) AS sAmount
FROM Transactions
WHERE `type` = 'Creditor'
GROUP BY account_id, `month`
| account_id | month | sAmount |
| ---------- | ------- | ------- |
| 3 | 2021-06 | 300100 |
| 4 | 2021-06 | 10400 |
| 4 | 2021-05 | 49300 |
| 4 | 2021-07 | 56300 |
| 3 | 2021-07 | 64900 |
2、要求里面是要连续两个月或以上总收入超过最大收入,那我们按最少两个月就行,将上面查询出来的连接自己,account_id 相同,月份(month)差距是一个月,满足总收入(sAmount)超过最大收入(max_income)就行
第一版写法(日期 YYYY-MM 格式特殊处理)
WITH tmp AS(SELECT account_id, DATE_FORMAT(day, '%Y-%m') AS `month`, SUM(amount) AS sAmountFROM TransactionsWHERE `type` = 'Creditor'GROUP BY account_id, `month`
)
SELECT DISTINCT one.account_id
FROM tmp AS one, tmp AS two
WHERE one.account_id = two.account_id
AND CONCAT(one.month, '01') = DATE_SUB(CONCAT(two.month, '-01'), INTERVAL 1 MONTH)
AND one.sAmount > (SELECT max_income FROM Accounts WHERE account_id = one.account_id)
AND two.sAmount > (SELECT max_income FROM Accounts WHERE account_id = two.account_id)
说一下我为什么这样用:
首先我测试 select date_sub('2023-02', interval 1 month),期望结果是 2023-01,但是结果是 Null,
其次我再测试 select date_sub('2023-02-01', interval 1 month),这次正常输出,结果是 2023-01-01
所以就有了个想法,拼接后面的 -01,使用 concat 函数进行拼接即可,即 select date_sub(concat('2023-02','-01'), interval 1 month)
第二版写法(日期格式用 YYYYMM 使用 PERIOD_DIFF 函数)
PERIOD_DIFF
1、PERIOD_DIFF() 函数返回两日期之间的差异,结果以月份计算。
2、period1 和 period2 应采用相同的格式(格式为YYMM或YYYYMM)
3、语法:PERIOD_DIFF(period1, period2)
测试:
select period_diff('202302', '202301'),结果 1
select period_diff('2302', '2301'),结果 1
select period_diff('202301', '202302'),结果 -1
select period_diff('2301', '2302'),结果 -1
参考:MySQL PERIOD_DIFF() 函数
WITH tmp AS(SELECT account_id, DATE_FORMAT(day, '%Y%m') AS `month`, SUM(amount) AS sAmountFROM TransactionsWHERE `type` = 'Creditor'GROUP BY account_id, `month`
)
SELECT DISTINCT one.account_id
FROM tmp AS one, tmp AS two
WHERE one.account_id = two.account_id
AND PERIOD_DIFF(two.month, one.month) = 1
AND one.sAmount > (SELECT max_income FROM Accounts WHERE account_id = one.account_id)
AND two.sAmount > (SELECT max_income FROM Accounts WHERE account_id = two.account_id)
注意:日期转换 %Y 和 %m 对应 YYYY 和 MM(mm是分钟,MM才是月份,注意不要弄错)
date_format( ) 转换格式:
| 格式 | 描述 |
|---|---|
| %M | 月名 |
| %m | 月,数值(00-12) |
| %Y | 年,4 位 |
| %y | 年,2 位 |
相关文章:
【MySQL】日期格式为 YYYY-MM 无法直接使用 DATE_SUB 函数的解决方案(特殊处理 或 PERIOD_DIFF 函数)
力扣题 1、题目地址 1843. 可疑银行账户 2、模拟表 表:Accounts Column NameTypeaccount_idintmax_incomeint account_id 是这张表具有唯一值的列。每行包含一个银行账户每月最大收入的信息。 表:Transactions Column NameTypetransaction_idint…...
Redis的key淘汰方式和内存不足淘汰方式
Redis的key过期淘汰方式 Redis key过期策略 定期删除惰性删除 Redis如何淘汰过期的key 定期删除 隔一段时间,就随机抽取一些设置了过期时间的key,检查其是否过期,如果过期就删除定期删除可能会导致很多过期key到了时间并没有被删除掉&#x…...
java使用redis
1、pom.xml文件里面增加如下依赖: <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-redis</artifactId> </dependency> 2、yml文件增加如下配置: redis:host: loc…...
MySQL技能树
MySQL作为一款广泛使用的关系型数据库管理系统,提供了丰富多样的SQL语句以支持数据的创建、查询、更新和删除等操作。以下是一份MySQL语句操作大全的概览,涵盖从数据库管理到复杂查询的常用命令: ### 一、数据库管理(DDL - 数据定…...
redis获取过期时间
03,redisTemplate_redistemplate 获取剩余时间-CSDN博客 11.返回当前key所对应的剩余过期时间 redisTemplate.getExpire(key);1 12.返回剩余过期时间并且指定时间单位 redisTemplate.getExpire(key, unit);...
ERROR in Plugin “react“ was conflicted .... 天坑留念-turborepo、eslint plugin
前两天项目代码拉下来,装完依赖启动的时候直接报错: [eslint] Plugin "react" was conflicted between ".eslintrc.js eslint-config-custom eslint-config-alloy/react" and "BaseConfig D:\pan\erp\test\business-servic…...
MergeTwoSortedLists 【合并有序链表】
有种归并排序的感觉 链表好久不用有些生疏了,思想思路是对的,但是代码写出来有问题,。 写完说点感受: 当时在学校学习链表的时候,就了解到链表分为“有头节点”和“无头节点”的链表,所以这里好像就不练&am…...
基于多反应堆的高并发服务器【C/C++/Reactor】(中)HttpRequest模块 解析http请求协议
一、HTTP响应报文格式 HTTP/1.1 200 OK Bdpagetype: 1 Bdqid: 0xf3c9743300024ee4 Cache-Control: private Connection: keep-alive Content-Encoding: gzip Content-Type: text/html;charsetutf-8 Date: Fri, 26 Feb 2021 08:44:35 GMT Expires: Fri, 26 Feb 2021 08:44:35 GM…...
muduo网络库剖析——网络地址InetAddress类
muduo网络库剖析——网络地址InetAddress类 前情从muduo到my_muduo 概要socketaddr_in介绍成员用法 网络地址转换函数 框架与细节成员函数使用方法 源码结尾 前情 从muduo到my_muduo 作为一个宏大的、功能健全的muduo库,考虑的肯定是众多情况是否可以高效满足&…...
什么是本地IP?服务器本地IP有哪些优势?
本地IP是指直接在互联网上分配给服务器或设备的IP地址,而不是通过NAT(网络地址转换)或 代理等中间设备进行转发。让我们关注本地IP的优势。 1.直接访问:原始IP允许无中间设备转发或代理直接访问服务器或设备。这减少了网络延迟&a…...
Open CASCADE学习|参数化球面的奇异性
参数曲面的奇异性是一个相对复杂的概念,它涉及到参数曲面的几何特性和参数化过程中的一些特殊情况。参数曲面通常用于描述三维空间中的复杂形状,通过参数方程将二维参数域映射到三维空间中。然而,在某些情况下,参数曲面可能会表现…...
基础知识篇(一)Acticity生命周期
Activity 类是 Android 应用的关键组件,而 activity 的启动和组合方式是平台应用模型的基本组成部分。与使用 main() 方法启动应用的编程范式不同,Android 系统会通过调用与其生命周期特定阶段对应的特定回调方法,在 Activity 实例中启动代码…...
Java内存结构
前文: 《Java8之类的加载》 《Java8之类加载机制class源码分析》 写在开头:本文为学习后的总结,可能有不到位的地方,错误的地方,欢迎各位指正。 JVM 在执行 Java 程序的过程中会把它所管理的内存划分为若干个不同的数…...
Java--ListUtil工具类,实现将一个大列表,拆分成指定长度的子列表
文章目录 前言实现代码执行结果 前言 在项目中有时会出现列表很大,无法一次性批量操作,我们需要将列表分成指定大小的几个子列表,一份一份进行操作,本文提供这样的工具类实现这个需求。 实现代码 以下为ListUtil工具类代码实现…...
SpringSecurity 密码加密登录
SpringSecurity 密码加密登录 1.前端所需文件2.后端所用工具类3.登录代码4.灵魂一问 1.前端所需文件 import JSEncrypt from jsencrypt/bin/jsencrypt.min// 密钥对生成 http://web.chacuo.net/netrsakeypairconst publicKey MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAKoR8mX0rGKLqz…...
大模型实战作业03
大模型实战作业03 注: 因为微调数据较少,没有显示出个人助手的名字...
【AI视野·今日Sound 声学论文速览 第四十四期】Tue, 9 Jan 2024
AI视野今日CS.Sound 声学论文速览 Tue, 9 Jan 2024 Totally 27 papers 👉上期速览✈更多精彩请移步主页 Daily Sound Papers DJCM: A Deep Joint Cascade Model for Singing Voice Separation and Vocal Pitch Estimation Authors Haojie Wei, Xueke Cao, Wenbo Xu…...
Windows Copilot 更新及使用教程
Windows Copilot 是一款由微软开发的人工智能辅助工具,可以通过学习用户的操作习惯和上下文,自动完成代码编写和其他重复性任务。它可以帮助开发人员提高工作效率,减少重复劳动。 以下是关于Windows Copilot的更新和使用教程的一些信息&…...
【Android开发】不同Activity之间的数据回传实例(一)摘桃子游戏
一、功能介绍 该项目实现的功能主要有: 在首页显示一个按钮点击该按钮跳转到桃园页面在桃园页面,点击桃子会弹窗显示摘到几个桃子,同时被点击桃子消失,总桃子数1点击退出桃园会返回首页,首页桃子数会根据点击的桃子数…...
增量式PID和脉冲轴组合控制阀门开度(算法介绍)
这篇博客我们以S7-1200PLC平台来举例,介绍我们的PID闭环控制器如何控制脉冲轴实现阀门角度控制。SMART PLC PID控制器控制伺服驱动器实现关节角度控制详细内容请参考下面文章: https://rxxw-control.blog.csdn.net/article/details/129658364https://rxxw-control.blog.csdn…...
Chapter03-Authentication vulnerabilities
文章目录 1. 身份验证简介1.1 What is authentication1.2 difference between authentication and authorization1.3 身份验证机制失效的原因1.4 身份验证机制失效的影响 2. 基于登录功能的漏洞2.1 密码爆破2.2 用户名枚举2.3 有缺陷的暴力破解防护2.3.1 如果用户登录尝试失败次…...
多模态2025:技术路线“神仙打架”,视频生成冲上云霄
文|魏琳华 编|王一粟 一场大会,聚集了中国多模态大模型的“半壁江山”。 智源大会2025为期两天的论坛中,汇集了学界、创业公司和大厂等三方的热门选手,关于多模态的集中讨论达到了前所未有的热度。其中,…...
突破不可导策略的训练难题:零阶优化与强化学习的深度嵌合
强化学习(Reinforcement Learning, RL)是工业领域智能控制的重要方法。它的基本原理是将最优控制问题建模为马尔可夫决策过程,然后使用强化学习的Actor-Critic机制(中文译作“知行互动”机制),逐步迭代求解…...
UDP(Echoserver)
网络命令 Ping 命令 检测网络是否连通 使用方法: ping -c 次数 网址ping -c 3 www.baidu.comnetstat 命令 netstat 是一个用来查看网络状态的重要工具. 语法:netstat [选项] 功能:查看网络状态 常用选项: n 拒绝显示别名&#…...
376. Wiggle Subsequence
376. Wiggle Subsequence 代码 class Solution { public:int wiggleMaxLength(vector<int>& nums) {int n nums.size();int res 1;int prediff 0;int curdiff 0;for(int i 0;i < n-1;i){curdiff nums[i1] - nums[i];if( (prediff > 0 && curdif…...
鸿蒙中用HarmonyOS SDK应用服务 HarmonyOS5开发一个医院查看报告小程序
一、开发环境准备 工具安装: 下载安装DevEco Studio 4.0(支持HarmonyOS 5)配置HarmonyOS SDK 5.0确保Node.js版本≥14 项目初始化: ohpm init harmony/hospital-report-app 二、核心功能模块实现 1. 报告列表…...
WEB3全栈开发——面试专业技能点P2智能合约开发(Solidity)
一、Solidity合约开发 下面是 Solidity 合约开发 的概念、代码示例及讲解,适合用作学习或写简历项目背景说明。 🧠 一、概念简介:Solidity 合约开发 Solidity 是一种专门为 以太坊(Ethereum)平台编写智能合约的高级编…...
工业自动化时代的精准装配革新:迁移科技3D视觉系统如何重塑机器人定位装配
AI3D视觉的工业赋能者 迁移科技成立于2017年,作为行业领先的3D工业相机及视觉系统供应商,累计完成数亿元融资。其核心技术覆盖硬件设计、算法优化及软件集成,通过稳定、易用、高回报的AI3D视觉系统,为汽车、新能源、金属制造等行…...
IoT/HCIP实验-3/LiteOS操作系统内核实验(任务、内存、信号量、CMSIS..)
文章目录 概述HelloWorld 工程C/C配置编译器主配置Makefile脚本烧录器主配置运行结果程序调用栈 任务管理实验实验结果osal 系统适配层osal_task_create 其他实验实验源码内存管理实验互斥锁实验信号量实验 CMISIS接口实验还是得JlINKCMSIS 简介LiteOS->CMSIS任务间消息交互…...
如何在最短时间内提升打ctf(web)的水平?
刚刚刷完2遍 bugku 的 web 题,前来答题。 每个人对刷题理解是不同,有的人是看了writeup就等于刷了,有的人是收藏了writeup就等于刷了,有的人是跟着writeup做了一遍就等于刷了,还有的人是独立思考做了一遍就等于刷了。…...
