github-fill

查询中使用聚合函数

更新时间：2024-11-28 23:00:00

编辑

本文通过具体示例介绍如何在查询中使用聚合函数。

聚合函数

聚合函数用于扫描一组记录，然后返回单行记录。这组记录可以是一个表或者视图、或者一个子查询的结果。

在分组查询中，聚合函数通常跟 GROUP BY 子句一起使用，按照一个或多个列的值分组，然后每组返回单行记录。

示例

创建示例表 toys_order，并录入订单数据。

/**/
obclient> CREATE TABLE toys_order (
  order_id     INTEGER,
  toy_id       INTEGER,
  toy_dealer   VARCHAR(20),
  toy_price    DECIMAL(10, 2),
  toy_amount   INTEGER  
);
Query OK, 0 rows affected

obclient> INSERT INTO toys_order VALUES ( 1001,1, 'A',53.12,100 ),( 1002,2, 'A',24.8,190 ),( 1003,3, 'A',19.9,330)
      ,( 1004,1, 'B',53.12,50 ),( 1005,2, 'B',24.8,200 ),( 1006,3, 'B',19.9,350 ),( 1007,2, 'A',24.8,100 );
7 rows in set

在查询中使用聚合函数

使用 count()、sum()、avg()、round()、min()、max() 统计玩具订单的销售额。

obclient> SELECT toy_id
    , count(*)                         order_count
    , sum(toy_amount)                  sum_amount
    , round(avg(toy_amount),2)         avg_amount
    , min(toy_amount)                  min_amount
    ,max(toy_amount)                   max_amount
    FROM toys_order GROUP BY toy_id  ORDER BY toy_id;
+--------+-------------+------------+------------+------------+------------+
| toy_id | order_count | sum_amount | avg_amount | min_amount | max_amount |
+--------+-------------+------------+------------+------------+------------+
|      1 |           2 |        150 |      75.00 |         50 |        100 |
|      2 |           3 |        490 |     163.33 |        100 |        200 |
|      3 |           2 |        680 |     340.00 |        330 |        350 |
+--------+-------------+------------+------------+------------+------------+
3 rows in set

在 OceanBase 数据库 MySQL 模式下，聚合函数跟 GROUP BY 子句一起使用时，对 select_list 中的列没有特殊要求。如果要求 select_list 里的列跟 GROUP BY 子句中的列保持一致，需要设置 MySQL 命令行下的 sql_mode 为 ONLY_FULL_GROUP_BY。示例如下：

obclient> SHOW VARIABLES LIKE '%sql_mode%';
+---------------+-------------------------------------------------------+
| Variable_name | Value                                                 |
+---------------+-------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
+---------------+-------------------------------------------------------+
1 row in set 

obclient> SET SESSION sql_mode='STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY';                                                                                                                                    Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected

obclient> SELECT toy_id,order_id
    , count(*)                         order_count
    , sum(toy_amount)                  sum_amount
    , round(avg(toy_amount),2)         avg_amount
    , min(toy_amount)                  min_amount
    , max(toy_amount)                  max_amount
     FROM toys_order GROUP BY toy_id  ORDER BY toy_id ;

ERROR 1055 (42000): 'test.toys_order.order_id' is not in GROUP BY

有关 OceanBase 数据库当前版本所支持的聚合函数详情，请参见 《SQL 语法（MySQL 模式）》 章节。