Mysql刷题笔记
CREATE TABLE T0112(
日期 DATETIME NULL,
代码 NVARCHAR(999) NULL,
金额 DECIMAL(18, 2) NULL,
金额差异值 DECIMAL(19, 2) NULL
);
INSERT INTO T0112 VALUES('2020-07-01','A',2,0);
INSERT INTO T0112 VALUES('2020-07-06','A',4,2);
INSERT INTO T0112 VALUES('2020-07-12','A',3,-1);
INSERT INTO T0112 VALUES('2020-07-13','A',4,1);
INSERT INTO T0112 VALUES('2020-07-14','A',5,1);
INSERT INTO T0112 VALUES('2020-07-13','B',2,0);
INSERT INTO T0112 VALUES('2020-07-19','B',4,2);
T0112
+
| 日期 | 代码 | 金额 | 金额差异值 |
+
| 2020-07-01 00:00:00 | A | 2.00 | 0.00 |
| 2020-07-06 00:00:00 | A | 4.00 | 2.00 |
| 2020-07-12 00:00:00 | A | 3.00 | -1.00 |
| 2020-07-13 00:00:00 | A | 4.00 | 1.00 |
| 2020-07-14 00:00:00 | A | 5.00 | 1.00 |
| 2020-07-13 00:00:00 | B | 2.00 | 0.00 |
| 2020-07-19 00:00:00 | B | 4.00 | 2.00 |
+
结果
+
| 日期 | 代码 | 金额 | 金额差异值 | 增长方向 | rn | 连续天数 |
+
| 2020-07-01 00:00:00 | A | 2.00 | 0.00 | 正 | 1 | 1 |
| 2020-07-06 00:00:00 | A | 4.00 | 2.00 | 正 | 2 | 2 |
| 2020-07-12 00:00:00 | A | 3.00 | -1.00 | 负 | 3 | 1 |
| 2020-07-13 00:00:00 | A | 4.00 | 1.00 | 正 | 4 | 1 |
| 2020-07-14 00:00:00 | A | 5.00 | 1.00 | 正 | 5 | 2 |
| 2020-07-13 00:00:00 | B | 2.00 | 0.00 | 正 | 1 | 1 |
| 2020-07-19 00:00:00 | B | 4.00 | 2.00 | 正 | 2 | 2 |
+
根据金额求出金额差异值
方法1:
1.为了方便 我们首先使用窗口函数 对于代码一样的按照时间排序 获得排名
2.代码相同且rn相差为1的构建连接 注意rn=1的情况 没有比他更小的 所以连接的是NULL 用IFNULL解决
CREATE VIEW tt0112 AS
(SELECT ROW_NUMBER() OVER(PARTITION BY 代码 ORDER BY 日期)rn,日期,代码,金额 FROM T0112)
+
| rn | 日期 | 代码 | 金额 |
+
| 1 | 2020-07-01 00:00:00 | A | 2.00 |
| 2 | 2020-07-06 00:00:00 | A | 4.00 |
| 3 | 2020-07-12 00:00:00 | A | 3.00 |
| 4 | 2020-07-13 00:00:00 | A | 4.00 |
| 5 | 2020-07-14 00:00:00 | A | 5.00 |
| 1 | 2020-07-13 00:00:00 | B | 2.00 |
| 2 | 2020-07-19 00:00:00 | B | 4.00 |
+
SELECT a.*,(a.金额 - IFNULL(b.金额,a.金额)) AS 金额差异新
FROM tt0112 a LEFT JOIN tt0112 b
ON a.代码 = b.代码 AND a.rn = b.rn+1
ORDER BY a.代码,a.日期;
+
| rn | 日期 | 代码 | 金额 | 金额差异新 |
+
| 1 | 2020-07-01 00:00:00 | A | 2.00 | 0.00 |
| 2 | 2020-07-06 00:00:00 | A | 4.00 | 2.00 |
| 3 | 2020-07-12 00:00:00 | A | 3.00 | -1.00 |
| 4 | 2020-07-13 00:00:00 | A | 4.00 | 1.00 |
| 5 | 2020-07-14 00:00:00 | A | 5.00 | 1.00 |
| 1 | 2020-07-13 00:00:00 | B | 2.00 | 0.00 |
| 2 | 2020-07-19 00:00:00 | B | 4.00 | 2.00 |
+
方法2:
1. 对应每个日期找到对应比他小的最近的日期 用order by
2. 找到后做差
SELECT 日期,代码,金额,IFNULL(金额 - 最近金额,0) AS 金额差异新
FROM
(
SELECT a.*,
(SELECT b.金额 FROM T0112 b WHERE a.日期 > b.日期 AND a.代码 =b.代码
ORDER BY b.日期 DESC LIMIT 1) as 最近金额
FROM T0112 a
)t
+
| 日期 | 代码 | 金额 | 金额差异新 |
+
| 2020-07-01 00:00:00 | A | 2.00 | 0.00 |
| 2020-07-06 00:00:00 | A | 4.00 | 2.00 |
| 2020-07-12 00:00:00 | A | 3.00 | -1.00 |
| 2020-07-13 00:00:00 | A | 4.00 | 1.00 |
| 2020-07-14 00:00:00 | A | 5.00 | 1.00 |
| 2020-07-13 00:00:00 | B | 2.00 | 0.00 |
| 2020-07-19 00:00:00 | B | 4.00 | 2.00 |
+
解决思路:
1. 根据金额差异值 创建视图 并按照代码分组 日期排序
2. 这点很重要:假设相同的代码所有金额都是增加的 那么日期排序顺序就是 连续的天数
3. 如果不是增加的 我们只需要找到上一个 最近的与此增长方向相反的对应rn 相减就是连续的天数
4. 相当于每次找到和本次增长方向相反的 为起点
CREATE VIEW v0112 AS(
SELECT *,
CASE WHEN 金额差异值 >=0 THEN '正' ELSE '负' END AS 增长方向,
ROW_NUMBER() OVER(PARTITION BY 代码 ORDER BY 日期) AS rn
FROM T0112
)
SELECT a.*,
a.rn - IFNULL((
SELECT b.rn FROM v0112 b WHERE b.代码 = a.代码 AND b.rn < a.rn
AND b.增长方向 <> a.增长方向
ORDER BY b.rn DESC LIMIT 1),0) AS 连续天数
FROM v0112 a;