学习源
- 简单教程
- 菜鸟教程
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);
基本使用
-
如果想计算总的平均访问数,可以使用下面的 SQL 语句
select avg(views) from lesson_views;
运行结果如下:
+------------+ | avg(views) | +------------+ | 139.2222 | +------------+
-
如果想要知道每门课程的平均访问数,则需要结合
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 | +---------------------+------------+
-
如果想要知道平均每天的访问数,则可以使用下面的 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 |