MySQL with 语句的使用
插入
需求:给年龄小于18岁的用户加上学生角色,实现SQL如下
1 | insert into s_user_role (user_id,role_id) |
更新
需求:给管理员这个角色的用户年龄加1
1 | with a as (select u.user_id,u.username,u.age from s_user u,s_user_role sur ,s_role sr where |
需求:给年龄小于18岁的用户加上学生角色,实现SQL如下
1 | insert into s_user_role (user_id,role_id) |
需求:给管理员这个角色的用户年龄加1
1 | with a as (select u.user_id,u.username,u.age from s_user u,s_user_role sur ,s_role sr where |
在数据统计时,有时候会出现某天没有数据的情况,比如某天某类产品销量为0,这样就没有真实订单。我们统计的时候就可能出现下面的数据:
1 | dt | val |
我们看到在上面的数据里,5月7日和5月9日的数据是缺失的。业务上往往要求,我们要把缺失的补0.也就是需要得到下面的结果:
1 | dt | val |
把5月7日和5月9日的数据补上,那么怎么实现呢?先看完整的SQL:
1 | with a as (select '2023-05-06' as dt, 12 as val union select '2023-05-10',110 union select '2023-05-08',80), |
看到上面的SQL 是不是有一种要放弃的感觉。太长了,确实太长了。我们来解析一下 都是干嘛的。我们这里用的是MySQL with 的语法,这个语法的好处就是可以把SQL写的贼长。然后还能年的懂。
先看:
1 | with a as (select '2023-05-06' as dt, 12 as val union select '2023-05-10',110 union select '2023-05-08',80) |
这一段就是我们造三行数据,实际业务中应该用真实的业务表数据替换。接下来
1 | b as (select max(dt) max_dt,min(dt) min_dt from a), |
这个就是找到业务里最大的日期,和最小的日期。以方便后面的补全。然后就是特别长的 z
1 | z as (select DATE(DATE_SUB(CURDATE(),INTERVAL rn day)) as dt from |
这一段就是 获取最近1000天的日期。 这里用三个 select 0 一直 union 到9的表,三个表每个表有10个数字。101010 就等于1000. 然后通过 ROW_NUMBER() over() 获得行号,也就是1 至 1000 。再通过 DATE(DATE_SUB(CURDATE(),INTERVAL rn day)) as dt 转成日期。因为我们不需要这么的日期,所以需要限定一下日期的数据,也就是
1 | t as (select z.* from z,b where dt>=b.min_dt and dt <= b.max_dt ) |
到了这里 就只会返回 最小日期和最大日期之间的日期了。然后 做一下表关联和排序。也就是
1 | select t.dt,ifnull(a.val,0) as val from t left join a on t.dt = a.dt order by t.dt; |
这样我们就得到了想要的,日期补全后的数据了。
看到这里,有的朋友要说了,这个SQL也太长了,能不能精简一点。我们看上面的 1 到 1000 这一步,确实太长了。我们可以用视图来优化一下。我们先创建一个视图:
1 | create view vn as SELECT ROW_NUMBER() over() as rn |
这样一个 1 到 1000 的视图就创建好了。前面的SQL 也可以简化为:
1 | with a as (select '2023-05-06' as dt, 12 as val union select '2023-05-10',110 union select '2023-05-08',80), |
对于数据:
1 | dt | val |
我们可以通过下面的SQL来对期进行补全:
1 | with a as (select '2023-04' as dt, 12 as val union select '2023-06',110 union select '2023-07',80), |
运行上面的SQL,得到:
1 | dt | val |
对于 月份的处理,我们用到了 date_format(DATE_SUB(CURDATE(),INTERVAL rn month),’%Y-%m’) 来把月份变成 2023-05 这样的一个格式。
这个方法,各位觉得怎么样?欢迎留言讨论
最近在查一个SQL Server 事务引起的表锁问题,折腾好久才发现要开启 快照读取模式。
1 | -- 查询数据库是否开启了 快照读取模式 |
直接看SQL
1 | select DATE(DATE_SUB(CURDATE(),INTERVAL rn day)) as date from |
返回结果
1 | 2023-06-26 |
我们在业务中常常需要统计这个月销售量多少,同比增加多少,环比增加多少。这篇博文我们就看看如何利用窗口函数实现同比及环比的计算。
用到的关键字包括:lag, window
首先我们得有MySQL 8.0及以上版本, 然后我们准备一张统计表。
1 | CREATE TABLE `my_stat` ( |
表里的内容如下:
month | profit |
---|---|
2022-05 | 10.00 |
2022-06 | 12.00 |
2022-07 | 6.00 |
2022-08 | 30.00 |
2022-09 | 20.00 |
2022-10 | 8.00 |
2022-11 | 2.00 |
2022-12 | 4.00 |
2023-01 | 14.00 |
2023-02 | 10.00 |
2023-03 | 8.00 |
2023-04 | 9.00 |
2023-05 | 7.00 |
2023-06 | 10.00 |
2023-07 | 15.00 |
数据 插入的SQL
1 | INSERT INTO `my_stat` (`month`, `profit`) |
先看最终SQL及输出结果
1 | select `month`,profit, |
输出结果:
1 | month|profit|上月|环比|环比比例 |
lag 函数的完整定义如下:
1 | LAG(expr [, N[, default]]) [null_treatment] over_clause |
参数一 expr(表达式)是必须的,这里可以是字段名,如上面的示例SQL,也可以是其它运算表达式。
参数二 N 是可选的,必须是大于等于0的整数。默认1,1表示上一行,0表示当前行,12表示前12行,后面计算同比时会用到
参数三 是默认值,比如第一行的 上一行是不存在的,这个时候 返回什么值就可以通过参数三来控制,默认是 NULL
null_treatment 的定义是处理NULL的策略,但是因为MySQL只实现了 RESPECT NULLS 并且作为默认值,就感觉它只是提醒我们 计算结果需要考虑NULL
over_clause 是必须的,它描述窗口的定义, 如上面的SQL示例最后一行:
1 | WINDOW w AS (ORDER BY `month`); |
这里定义了一个 窗口(window), 基于 month这个字段升序。
上一节的 over_clause 定义如下:
1 | over_clause: |
我们看到,可以 over (窗口定义) 或者 over 窗口名称。
1 | lag(profit) OVER w AS 上月 |
这就是一个 over 窗口名称的示例。
接下来我们看 window_spec 的定义:
1 | window_spec: |
还是先看最终SQL
1 | select `month`,profit, |
返回结果
month | profit | 上月 | 环比 | 环比比例 | 上年同月 | 同比 | 同比比例 |
---|---|---|---|---|---|---|---|
2022-05 | 10.00 | NULL | NULL | NULL | NULL | NULL | NULL |
2022-06 | 12.00 | 10.00 | 2.00 | 20.000000 | NULL | NULL | NULL |
2022-07 | 6.00 | 12.00 | -6.00 | -50.000000 | NULL | NULL | NULL |
2022-08 | 30.00 | 6.00 | 24.00 | 400.000000 | NULL | NULL | NULL |
2022-09 | 20.00 | 30.00 | -10.00 | -33.333333 | NULL | NULL | NULL |
2022-10 | 8.00 | 20.00 | -12.00 | -60.000000 | NULL | NULL | NULL |
2022-11 | 2.00 | 8.00 | -6.00 | -75.000000 | NULL | NULL | NULL |
2022-12 | 4.00 | 2.00 | 2.00 | 100.000000 | NULL | NULL | NULL |
2023-01 | 14.00 | 4.00 | 10.00 | 250.000000 | NULL | NULL | NULL |
2023-02 | 10.00 | 14.00 | -4.00 | -28.571429 | NULL | NULL | NULL |
2023-03 | 8.00 | 10.00 | -2.00 | -20.000000 | NULL | NULL | NULL |
2023-04 | 9.00 | 8.00 | 1.00 | 12.500000 | NULL | NULL | NULL |
2023-05 | 7.00 | 9.00 | -2.00 | -22.222222 | 10.00 | -3.00 | -30.000000 |
2023-06 | 10.00 | 7.00 | 3.00 | 42.857143 | 12.00 | -2.00 | -16.666667 |
2023-07 | 15.00 | 10.00 | 5.00 | 50.000000 | 6.00 | 9.00 | 150.000000 |
同比与环比的差别其实不大,只是 lag(profit)变成了 lag(profit,12)。 lag(profit,12)表示取前12行的数据。
需要注意的是计算同比的时候,我们需要保证数据是连续的,不然数据会有偏差,因为这里的lag(profit,12)取的是前12行的数据,如果月份数据有缺失就可以取错数据。比如:如果少了一个月,那么前12行取的可能就是去年上个月的数据了。
我们先准备一下数据,先创建一张表 my_stat_food
1 | CREATE TABLE `my_stat_food` ( |
然后插入数据
1 | INSERT INTO `my_stat_food` (`month`, `type`, `profit`) |
定义
1 | partition_clause: |
然后我们试试pratition的效果
1 | select *,lag(`profit`) over (partition by `type`) as lp from my_stat_food |
输出:
month | type | profit | lp |
---|---|---|---|
2023-05 | 水果 | 2.00 | NULL |
2023-06 | 水果 | 5.00 | 2.00 |
2023-07 | 水果 | 8.00 | 5.00 |
2023-05 | 肉类 | 3.00 | NULL |
2023-06 | 肉类 | 6.00 | 3.00 |
2023-07 | 肉类 | 9.00 | 6.00 |
2023-05 | 蔬菜 | 1.00 | NULL |
2023-06 | 蔬菜 | 4.00 | 1.00 |
2023-07 | 蔬菜 | 7.00 | 4.00 |
我们的数据是按月分排序的,但我们看到通过 partition进行分区后,就会分区来返回内容了。这样我们就可以通过分区来统计不同品类的环比。
在go语言的标准库里,可以通过time来处理日期和时间。我们需要引用标准库
1 | import "time" |
1 | now := time.Now() |
输出
1 | 2023-04-13 13:10:17.8577739 +0800 CST m=+0.004403301 |
time.Now 返回的结构是 time.Time
1 | type Time struct { |
按go的约定,这个结构里的数据都是私有变量,对于我们使用来说没有价值。
特别注意:go的格式化字符串不是常见的 yy-MM-dd HH:mm:ss,而是2006-01-02 15:04:05 Go的成立日期。
1 | fmt.Println(now.Format("2006-01-02 15:04:05")) |
输出
1 | 2023-04-13 14:21:21 |
格式化字符说明:
字符 | 说明 |
---|---|
1 | 月份 |
01 | 月份,保证两位数字,1月会输出01 |
2 | 日期 |
02 | 日期,保证两位数字,2日会输出02 |
3 | 小时,12小时制 |
03 | 小时,保证两位数字,3时会输出03 |
15 | 小时,24小时制,保证两位数字,3时会输出03 |
4 | 分钟 |
04 | 分钟,保证两位数字,4分会输出04 |
5 | 秒 |
05 | 秒,保证两位数字,5秒会输出05 |
06 | 年,输出最后两位 |
2006 | 年,输出4位 |
.000 | 毫秒 |
可以快速记忆: 1月2日3时4分5秒6年
通过time.Parse来把字符串转为 Time 时间对象
1 | t, err := time.Parse("2006-01-02 15:04:05", "2023-04-14 07:03:04") |
输出: 2023
如果出错了,会在返回的err 里有说明。比如:
1 | t, err := time.Parse("2006-01-02 15:04:05", "04-14 07:03:04") |
就会输出
1 | parsing time "04-14 07:03:04" as "2006-01-02 15:04:05": cannot parse "04-14 07:03:04" as "2006" |
1 | t, err := time.Parse("2006-01-02 15:04:05", "04-14 07:03:04") |
输出
1 | 年 2023 |
1 | package main |
1 | package main |
Jackson是一个解析json的java库。springboot默认引用了这个json库。Jackson基于Apache-2.0 license开源,在github上有超过3k的star。 Jackson 在 mvnrepository.com 网站上的json分类里排名第一。如此优秀的开源库,值得我们去学习它的使用方法。
spring-boot-starter-web里已经通过 spring-boot-starter-josn 依赖了 jackson-databind包。 对于springboot的web项目,我们可以直接使用Jackson。 如果没有使用springboot可以在pom.xml中通过以下方式引入jar包。
1 | <dependency> |
先定义一个java类 Student.java
1 | import lombok.Data; |
然后通过以下代码完成json到java对象的转换
1 | import com.fasterxml.jackson.core.JsonProcessingException; |
关键方法:ObjectMapper.writeValueAsString, 代码示例:
1 | Student student = new Student(); |
1 | String str = "{\"name\":\"tom\",\"age\":16}"; |
借助 TypeReference
1 | String str = "[{\"name\":\"tom\",\"age\":16},{\"name\":\"lucy\",\"age\":15}]"; |
1 | String str = "[{\"name\":\"tom\",\"age\":16},{\"name\":\"lucy\",\"age\":15}]"; |
借助 TypeReference
1 | String str = "{\"name\":\"tom\",\"age\":16}"; |