MySQL：使用子查询计算本地化的最小值和最大值

Question

MySQL：使用子查询计算本地化的最小值和最大值

3

我正在基于MySQL中过去五次观测值的相对值构建标准化数据集。为此，我需要知道过去五个数据点的最大值和最小值。我正在编写一个子查询，其工作原理如下：我需要获取表中前五个出现次数的最小值和最大值。虽然我的表目前按照DESC顺序排列，但我无法依赖它保持这种结构，因为特定MY_ID的RECENT_DATE的后续更新将更改最近的5项，并更新MY_VALUE。以下是我正在处理的基本表的部分内容。

    MY_ID | RECENT_DATE | MY_VALUE
    392, 2013-10-06 12:00:00, 18332
    146, 2013-09-06 12:00:00, 5623
    72, 2013-09-02 12:00:00, 23242
    643, 2013-09-01 12:00:00, 15242
    492, 2013-08-06 12:00:00, 15332
    346, 2013-07-26 12:00:00, 17332
    172, 2013-07-22 12:00:00, 14001
    123, 2013-07-22 12:00:00, 13918
    243, 2013-07-11 12:00:00, 23229

最后我需要它看起来像这样。

    MY_ID | RECENT_DATE | MY_VALUE | MAX_VALUE | MIN_VALUE
    392, 2013-10-06 12:00:00, 18332, 23242, 5623
    146, 2013-09-06 12:00:00, 5623, 23242, 5623
    72, 2013-09-02 12:00:00, 23242, 23242, 14001
    643, 2013-09-01 12:00:00, 15242, 17332, 13918
    492, 2013-08-06 12:00:00, 15332, 23229, 13918
    346, 2013-07-26 12:00:00, 17332, 23229, 13918
    172, 2013-07-22 12:00:00, 14001, 23229, 13918
    123, 2013-07-22 12:00:00, 13918, 23229, 13918
    243, 2013-07-11 12:00:00, 23229, 23229, 23229

这里是一个快速示例表格：

CREATE TABLE IF NOT EXISTS dbo.baseTable
(
    MY_ID BIGINT(20) UNSIGNED, 
    RECENT_DATE DATETIME, 
    MY_VALUE BIGINT(20), 
);

INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (392, '2013-10-06 12:00:00', 18332);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (146, '2013-09-06 12:00:00', 5623);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (72, '2013-09-02 12:00:00', 23242);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (643, '2013-09-01 12:00:00', 15242);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (492, '2013-08-06 12:00:00', 15332);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (346, '2013-07-26 12:00:00', 17332);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (172, '2013-07-22 12:00:00', 14001);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (123, '2013-07-22 12:00:00', 13918);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (243, '2013-07-11 12:00:00', 23229);

现在是我失败的查询尝试A：

SELECT DISTINCT t1.MY_ID,
        t1.RECENT_DATE,
        t1.MY_VALUE, MAX(t2.MY_VALUE) AS MAX_MY_VALUE, MIN(t2.MY_VALUE) AS MIN_MY_VALUE
FROM dbo.baseTable t1
    INNER JOIN (
        SELECT t.MY_ID, t1.RECENT_DATE, t.MY_VALUE
            FROM dbo.baseTable t
            WHERE t.RECENT_DATE <= t1.RECENT_DATE
            LIMIT 5) AS t2 ON t1.MY_ID = t2.MY_ID
/* WHERE OMITTED */
ORDER BY t1.RECENT_DATE DESC
;

- Ben

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，

- fthiella · Accepted Answer

这个查询应该返回你需要的结果：

SELECT
  ta.MY_ID, ta.MY_ID, ta.RECENT_DATE, ta.MY_VALUE,
  MAX(tb.MY_VALUE),
  MIN(tb.MY_VALUE)
FROM (
  SELECT
    t1.MY_ID,
    t1.RECENT_DATE,
    t1.MY_VALUE,
    SUBSTRING_INDEX(GROUP_CONCAT(t2.MY_ID ORDER BY t2.RECENT_DATE DESC), ',', 5) as Last_5
  FROM
    baseTable t1 INNER JOIN baseTable t2
    ON t1.recent_date >= t2.recent_date
  GROUP BY
    t1.my_id
) ta INNER JOIN baseTable tb ON FIND_IN_SET(tb.MY_ID, Last_5)
GROUP BY
  ta.MY_ID
ORDER BY
  RECENT_DATE DESC

这不是标准的SQL语句，使用了一些MySQL技巧。请查看这里的示例。