MySQL：按标准差偏离值分组获取首次出现的完整行

嗨，我来帮你解决这个问题～你当前的SQL写法存在一个关键问题：用group by round(...)时，其他非聚合列（比如Timestamp、TestNum）会被MySQL随机返回一行，这根本没法保证拿到的是首次出现的那条数据。而且在开启ONLY_FULL_GROUP_BY的标准模式下，这种写法直接会报错，不符合SQL规范。

针对你的需求，我提供两种实现方案，你可以根据自己的MySQL版本选择：

方法一：使用窗口函数（MySQL 8.0及以上推荐） #

窗口函数ROW_NUMBER()可以轻松帮我们给每个StDevsAway分组内的行按Timestamp排序编号，然后只取编号为1的行（也就是首次出现的那条）：

WITH calc_stdevs AS (
    SELECT 
        a.Name,
        a.ID,
        a.Timestamp,
        a.TestNum,
        ROUND((a.Grade - b.Avg) / b.StDev, 2) AS StDevsAway
    FROM MyTable1 a
    JOIN MyTable2 b ON a.ID = b.ID
)
SELECT Name, ID, Timestamp, TestNum, StDevsAway
FROM calc_stdevs
WHERE ROW_NUMBER() OVER (PARTITION BY StDevsAway ORDER BY Timestamp) = 1
ORDER BY Timestamp;

简单解释：

• 先用CTE（公共表表达式）calc_stdevs计算出所有行的StDevsAway值；
• 再用ROW_NUMBER() OVER (PARTITION BY StDevsAway ORDER BY Timestamp)给每个相同的StDevsAway分组里的行按时间排序，第一行的编号为1；
• 最后筛选出编号为1的行，再按时间排序就得到了你要的结果。

方法二：兼容MySQL 5.x版本（无窗口函数） #

如果你的MySQL版本低于8.0，不支持窗口函数，可以用子查询先找到每个StDevsAway对应的最早Timestamp，再关联回去拿到完整数据：

SELECT 
    a.Name,
    a.ID,
    a.Timestamp,
    a.TestNum,
    ROUND((a.Grade - b.Avg) / b.StDev, 2) AS StDevsAway
FROM MyTable1 a
JOIN MyTable2 b ON a.ID = b.ID
JOIN (
    SELECT 
        ROUND((Grade - Avg) / StDev, 2) AS StDevsAway,
        MIN(Timestamp) AS first_timestamp
    FROM MyTable1
    JOIN MyTable2 ON MyTable1.ID = MyTable2.ID
    GROUP BY StDevsAway
) c ON ROUND((a.Grade - b.Avg) / b.StDev, 2) = c.StDevsAway 
    AND a.Timestamp = c.first_timestamp
ORDER BY a.Timestamp;

简单解释：

• 子查询c先计算每个StDevsAway对应的最早出现时间first_timestamp；
• 把原表的计算结果和子查询c关联，只保留时间等于首次时间的行；
• 最后按时间排序得到目标结果。

两种方法都能输出你预期的结果：

Name | ID | Timestamp | TestNum | StDevsAway
Alex | 1101 | 2020-10-01 12:00:00 | 1 | -1.23
Alex | 1101 | 2020-10-02 13:00:00 | 2 | 0
Alex | 1101 | 2020-10-03 8:00:00 | 3 | 1.23

另外要注意：因为是用ROUND()后的数值分组，要确保精度一致，避免因为浮点精度问题导致分组错误哦～

内容的提问来源于stack exchange，提问作者Cody Troyer