SQL学习之avg() 函数

学习源

• 简单教程
  • https://www.twle.cn/l/yufei/sql/sql-basic-func-avg.html
• 菜鸟教程
  • https://www.runoob.com/sql/sql-function.html

SQL avg() 函数

SQL avg() 函数返回数值列的平均值

SELECT AVG(column_name) FROM table_name;

示例数据

CREATE DATABASE IF NOT EXISTS hardy_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;

USE hardy_db;

CREATE TABLE IF NOT EXISTS lesson_views (
    uniq bigint(20) primary key NOT NULL default '0' ,
    lesson_name varchar(32) NOT NULL default '',
    lesson_id int(11) NOT NULL default '0',
    date_at  int(11) NOT NULL default '0',
    views int(11) NOT NULL default '0'
);

INSERT INTO lesson_views(uniq, lesson_name, lesson_id, date_at, views) VALUES
(20170511000001,'Python 基础教程',1,20170511,320),
(20170511000002,'Scala 基础教程', 2,20170511,22),
(20170511000003,'Ruby 基础教程', 3, 20170511,49),
(20170512000001,'Python 基础教程',1,20170512,220),
(20170512000002,'Scala 基础教程',2,20170512,12),
(20170512000003,'Ruby 基础教程',3,20170512,63),
(20170513000001,'Python 基础教程',1,20170513,441),
(20170513000002,'Scala 基础教程',2,20170513,39),
(20170513000003,'Ruby 基础教程',3,20170513,87);

基本使用

1. 如果想计算总的平均访问数，可以使用下面的 SQL 语句

   select avg(views) from lesson_views;
   

   运行结果如下：

   +------------+
   | avg(views) |
   +------------+
   |   139.2222 |
   +------------+
   

2. 如果想要知道每门课程的平均访问数，则需要结合 group by 语句

   select lesson_name, avg(views) from lesson_views group by lesson_name;
   

   运行结果如下：

   +---------------------+------------+
   | lesson_name        | avg(views) |
   +---------------------+------------+
   | Python 基础教程     |   327.0000 |
   | Ruby 基础教程       |    66.3333 |
   | Scala 基础教程      |    24.3333 |
   +---------------------+------------+
   

3. 如果想要知道平均每天的访问数，则可以使用下面的 SQL 语句

   select date_at, avg(views) from lesson_views group by date_at;
   

   运行结果如下：

   +----------+------------+
   | date_at  | avg(views) |
   +----------+------------+
   | 20170511 |   130.3333 |
   | 20170512 |    98.3333 |
   | 20170513 |   189.0000 |