mysql - MYSQL 中的交叉检测
问题描述
如果有一个包含三列的 MySQL 表,其中一列是日期,另外两列是数量 1 和数量 2。我试图找到由数量 1 与日期绘制的曲线与数量 2 与日期相交的所有日期?问题是有时数据可能不包含发生交叉的点,在这种情况下,我应该在交叉后很快找到日期有什么方法可以在 MySQL 中做到这一点?
解决方案
首先,了解两条曲线交叉的标记是,在给定日期,两个条件之一为真:
- 第一条曲线大于第二条曲线,并且在前一天,第二条曲线大于第一条曲线,或
- 上面的反面是真的
鉴于您可以访问 MySQL 8+,我们可以LAG
在这里尝试使用分析功能:
WITH yourTable AS (
SELECT '2018-01-01' AS date, 1 AS quantity1, 5 AS quantity2 UNION ALL
SELECT '2018-01-02', 3, 4 UNION ALL
SELECT '2018-01-03', 4, 3 UNION ALL
SELECT '2018-01-04', 2, 5 UNION ALL
SELECT '2018-01-05', 4, 7 UNION ALL
SELECT '2018-01-06', 9, 8
),
cte AS (
SELECT
date, quantity1, quantity2,
LAG(quantity1) OVER (ORDER BY date) AS q1lag,
LAG(quantity2) OVER (ORDER BY date) AS q2lag
FROM yourTable
)
SELECT
date
FROM cte
WHERE
(quantity1 > quantity2 AND q1lag < q2lag) OR
(quantity2 > quantity1 AND q2lag < q1lag);
这是示例时间序列数据的图表:
应该清楚的是,在结果集中的三个日期中的每一天,与前一个日期相比,两个时间序列相互交叉。
演示
请注意,这将产生两个图表交叉的一对日期中的较大值。一般来说,交叉会发生在两个日期之间。
编辑:
如果您的 MySQL 版本早于 8+,那么我们将不得不使用另一种方式来查找LAG
. 一种选择是使用相关子查询:
SELECT date
FROM
(
SELECT
date, quantity1, quantity2,
(SELECT quantity1 FROM yourTable t2
WHERE t2.date < t1.date ORDER BY t2.date DESC LIMIT 1) AS q1lag,
(SELECT quantity2 FROM yourTable t2
WHERE t2.date < t1.date ORDER BY t2.date DESC LIMIT 1) AS q2lag
FROM yourTable t1
) t
WHERE
(quantity1 > quantity2 AND q1lag < q2lag) OR
(quantity2 > quantity1 AND q2lag < q1lag);