在 Group by 之内(前)实现 Order by 排序

发表于

发现问题

统计功能需求时，比如统计某 APP 在一小时内最后一笔交易的时间点，测试后发现数据实为该小时内的第一笔交易。

SQL 语句示例

1
2
3
4
5
6
7
SELECT appid, created_time
FROM order
WHERE paid = 1
      AND created_time >= '2016-01-26 17:00:00'
      AND created_time < '2016-01-26 18:00:00'
GROUP BY appid
ORDER BY created_time DESC;

执行后发现实际 ORDER BY created_time DESC 仅是在 GROUP BY 外排序，而没有满足需求在 GROUP BY 内排序。

解决问题

方法一、使用 MAX 函数

SQL 语句示例

1
2
3
4
5
6
SELECT appid, created_time, MAX(created_time) AS latest_created_time
FROM order
WHERE paid = 1
      AND created_time >= '2016-01-26 17:00:00'
      AND created_time < '2016-01-26 18:00:00'
GROUP BY appid;

其中 created_time 为该小时内第一笔交易，latest_created_time 为该小时内最后一笔交易

优点: 使用简单，逻辑清楚
缺点: 全表排，效率低，大数据量慎用

方法二、使用子查询

SQL 语句示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT appid, created_time, latest_created_time 
FROM (
  SELECT appid, created_time FROM order
    WHERE paid = 1
      AND created_time >= '2016-01-26 17:00:00'
      AND created_time < '2016-01-26 18:00:00'
    ORDER BY created_time ASC
    ) AS temp_table_a , (
    SELECT created_time AS latest_created_time FROM order
    WHERE paid = 1
      AND created_time >= '2016-01-26 17:00:00'
      AND created_time < '2016-01-26 18:00:00'
    ORDER BY created_time DESC
  ) AS temp_table_b
GROUP BY appid;

同上，其中 created_time 为该小时内第一笔交易，latest_created_time 为该小时内最后一笔交易

优点: 效率高，适用大数据量
缺点: 语句较复杂，需要脱离框架，手动拼接实现

总结

不必纠结了，数据量小于200万就使用第一种方法吧，反之使用第二种。