SQL学习之聚合统计

一、聚合函数

1.COUNT（）函数

例子：计算女性用户有多少。条件：

1.COUNT用户数量并取别名为count_number
2.数据来源为account_2c表
3.筛选条件gender等于女

1
2
3
SELECT count(*) AS count_number
FROM account_2c
WHERE gender = "女";

上例中count(*)会统计表中行的数目，包括列中是空值以及重复的行

  • COUNT(column)对特定列column中的不为NULL的行进行计数，忽略NULL值。
  • 若需要去掉重复行，可以使用DISTINCT关键字，得到特定列column中去掉重复行的行数，即COUNT(DISTINCT column)

2.MAX()函数

1
2
3
SELECT MAX(total_price) AS max_price
FROM orders
WHERE order_time BETWEEN '2020-11-01 00:00:00' AND '2020-11-15 00:00:00';

代码的作用:

查找orders (订单表) 中最高的订单总价 (total_price)。

第一行，使用关键字SELECT选择查找使用MAX(total_price)函数返回total_price列中的最大值，指定名称为max_price。

第二行，使用关键字FROM指定要查找的表格。

第三行，使用WHERE条件查询限定时间范围为2020-11-01 00:00:00到2020-11-15 00:00:00

特别注意：

  • MAX( )通常用来找出最大的数值或者日期值。但在MySQL中，也可以用于文本列获得按字母排序的最高值。

  • MAX( )函数忽略列值为NULL的行。

3.MIN（）函数

1
2
3
SELECT MIN(order_time) AS min_time
FROM orders
WHERE order_time BETWEEN '2020-11-01 00:00:00' AND '2020-11-15 00:00:00';

代码的作用:

查找orders (订单表) 中下单最快的时间 (order_time)。

第一行，使用MIN( )函数计算order_time列中的最小日期值，指定名称为min_time。
并且使用关键字SELECT选择查找min_time。

第二行，使用关键字FROM指定要查找的表格。

第三行，使用关键字WHERE指定查询的时间范围为2020-11-01 00:00:00到2020-11-15 00:00:00

特别注意:

  • 同样的MIN( )与MAX( )类似，可用来找出最小的数值或者日期值。在MySQL中也可以用于文本列获得按字母排序的最低值。

  • MIN( )函数也忽略列值为NULL的行。

4.SUM()函数

ROI（Return On Investment）： 是指通过投资而应返回的价值，即企业从一项投资活动中得到的经济回报。

一般计算公式为：投资回报率（ROI）=产出（销售收入）/ 投入（成本）

通常来说：
ROI > 1 : 表示收益大于投入的费用，可以继续投入；

ROI = 1 ：表示投入的效果和收益持平，一般情况下可以继续投入；

ROI < 1 : 除非有特殊用途，例如烧钱扩大知名度，一般情况下会停止投入。

1
2
3
SELECT SUM(total_price) / 50000 AS ROI
FROM orders
WHERE order_time BETWEEN '2020-11-01 00:00:00' AND '2020-11-15 00:00:00';

代码的作用

查找orders (订单表) 中的订单价格，并计算促销期间的 ROI。

第一行，使用SUM(total_price)函数计算所有订单的价格之和除以成本50000取别名为ROI。

第二行，查找对应表格。

第三行，筛选订单日期在2020-11-01 00:00:00到2020-11-15日 00:00:00的订单。

5.AVG()函数

1
2
3
SELECT AVG(total_price) AS avg_price 
FROM orders
WHERE order_time BETWEEN '2020-11-01 00:00:00' AND '2020-11-15 00:00:00';

代码的作用:

查找11月1号到11月15号的平均订单价格(avg_price)。

第一行，使用AVG( )函数计算total_price列订单的平均价格，取别名为avg_price。

第二行，指定要查找的表格。

第三行，筛选订单时间。

例题：

1.用sql语句来计算商品表格(commodity)中的平均价格(avg_price)，总价格(sum_price)，最低价格(min_price)和最高价格(max_price)。

1
2
SELECT AVG(price) AS avg_price,SUM(price) AS sum_price,MIN(price) AS min_price,MAX(price) AS max_price
FROM commodity;

2.编写SQL代码，从面包记录表 (bread) 中统计出法棍的种类个数,将统计数值重命名为法棍面包种类数。

1
2
3
SELECT COUNT(*) AS 法棍面包种类数
FROM bread
WHERE name LIKE '%法棍%';

3.写一个SQL语句从老师名单表 (teacher) 找一找这个学校有多少个张老师，并重命名为number。

1
2
3
SELECT COUNT(*) AS number
FROM teacher
WHERE teacher_name LIKE '张%';

二、时间函数与字符串函数

1.NOW（）、TIMESTAMPDIFF（）函数

1
2
SELECT name, TIMESTAMPDIFF(YEAR, birthday, NOW()) AS age
FROM account_2c;

代码的作用:

TIMESTAMPDIFF()函数的作用是计算时间差。

第一行代码使用SELECT关键字选择姓名name字段，并使用TIMESTAMPDIFF()函数计算当前时间与生日birthday字段的时间差并取别名age。

第二行代码使用FROM关键字提取account_2c客户表中的信息。

其中：

  • TIMESTAMPDIFF()的作用是返回日期之间的整数差。可以有以下形式：

    • SECOND - 秒钟
    • MINUTE - 分钟
    • HOUR - 小时
    • DAY - 天
    • WEEK - 星期
    • MONTH - 月
    • QUARTER - 季度
    • YEAR - 年

  • birthday,该位置的参数计算差值中较远的日期

  • NOW（）的作用是提取当前时间，该位置的参数计算差值中较近的日期

2.CONCAT（）函数

1
2
3
SELECT CONCAT(province,city,district,detailed_addr) AS 详细地址,recipient,contact_number
FROM address
WHERE account_id = 1004000054;

代码的作用

第一行代码通过SELECT关键字选择查找recipient,contact_number字段。并用CONCAT关键字拼接province,city,district,detailed_addr四个字段取别名为详细地址。

第二行代码通过FROM关键字选择address表。

第三行代码通过WHERE关键字设置查询条件为account_id = 1004000054

3.SUBSTR()函数

1
2
3
SELECT COUNT(*)
FROM invoice
WHERE SUBSTR(express_number,1,2) = 'FT';

代码的作用:

这段代码可以从invoice表中查找到运单前两个字母为FT的订单数量。

第一行代码使用COUNT函数计算总数量。

第二行代码使用FROM关键字从invoice表中查找。

第三行代码使用SUBSTR关键字提取express_number字段中从第1个开始的前2个字符，判定条件为前两个字符等于FT。

SUBSTR(express_number,1,2) 详解：

  • 语法：substr(string,start,length)
  • string - 指定的要截取的字符串
  • start - 必需，规定在字符串的何处开始。
  • length - 指定要截取的字符串长度。

例子：查找金通公司（订单以JT开头）最近20笔的订单信息。

1
2
3
4
5
SELECT *
FROM invoice
WHERE SUBSTR(express_number,1,2) = 'JT'
ORDER BY delivery_time DESC
LIMIT 20;

例题1：帮助公司找出客户的姓氏（此处以名字的第一个字为准），性别，和年龄。并将姓氏取名为”lastname”，年龄取名为“age”。

1
2
SELECT SUBSTR(name,1,1) AS lastname,gender,TIMESTAMPDIFF(YEAR,birthday,NOW()) AS age
FROM account_2c;

例题2：写一个SQL语句查询该面包店每样面包名字(name)以及它对应的保质期，并列出保质期大于2天的面包名称。保质期是过期日期(expiration_date)和生产日期(production_date)之间的时间间隔。输出的列为：name(面包名字),保质期

1
2
3
SELECT name,TIMESTAMPDIFF(DAY,production_date,expiration_date) AS 保质期
FROM bread_shop
WHERE TIMESTAMPDIFF(DAY,production_date,expiration_date) > 2;

例题3：在签证办理时，直接分别填写了姓 (last_name) 和名 (first_name)在表格 (visa_application) 中。编写SQL语句帮助范范将签证申请(visa_application)表的员工的first_name和last_name拼接起来作为全名(name)，中间以一个空格区分

1
2
SELECT CONCAT(first_name,' ',last_name) AS name
FROM visa_application;

例题4：制作用户生命周期。计算公式如下：(这里假设当前日期为2021年6月1日）
R = 2021-06-01 - 最后一次购买日期
L = 最后一次购买日期 - 第一次购买日期

写一个SQL代码计算用户萌萌对应的R和L值～注意，在此题中我们计算的两个时间之间相差的月数。

输出的列为：customer_name(客户姓名), R, L

order_id order_time customer_name quantity sale cost
1 2018-02-25 萌萌 2 2362 2720
2 2018-05-01 懵懵 1 3713 2187
3 2018-01-31 萌萌 4 3993 1714
4 2019-02-22 萌萌 4 2426 2446
5 2018-01-29 懵懵 1 2950 2554
6 2019-04-22 明明 2 974 798
7 2021-02-04 明明 1 3502 1437
8 2019-04-02 敏敏 2 1373 1961
9 2019-04-14 懵懵 2 2223 1336
10 2021-02-21 明明 5 2856 2752 
1
2
3
SELECT customer_name,TIMESTAMPDIFF(month,MAX(order_time),'2021-06-01') AS R,TIMESTAMPDIFF(month,MIN(order_time),MAX(order_time)) AS L
FROM market_data
WHERE customer_name = "萌萌";

三、分组

1.GROUP BY

1

代码释义：

将orders按照account_id进行分组，计算出每组用户的总消费金额并命名为M。

第1行，指定了需要查找的数据；

第2行，指定了查找的表orders；

第3行，指定了按照account_id进行分组。

注意：

  • 在MYSQL数据库中，对于一个单元格中有多个数据的列，不能直接用SELECT取出其中的所有元素。

  • 因此在使用GROUP BY时只能用select选择使用了聚合函数的列和GROUP BY列。不能选择非聚合函数列和非GROUP BY列。

  • 选择非聚合函数列和非GROUP BY列得到的数据是不准确的。

例题：查找每位用户的account_id和对应的消费频率F。

1
2
3
4
SELECT account_id,COUNT(*) AS F
FROM orders
WHERE total_price is not NULL
GROUP BY account_id;

例题：找出各个分类中各个品牌的价格最高的商品的价格取别名为最高价格，并找出对应商品的分类id和品牌id。

1
2
3
SELECT category_id,brand_id,MAX(sku_price) AS 最高价格
FROM commodity
GROUP BY category_id,brand_id;

2.HAVING

对于分组后的数据进行过滤

1
2
3
4
SELECT account_id,SUM(total_price) AS M
FROM orders
GROUP BY account_id
HAVING M>1000;

上述代码找出M值超过1000元的客户。

第1行，指定了需要查找的数据；

第2行，指定了查找的表orders；

第3行，指定了按照account_id进行分组；

第4行，设置了筛选条件M>1000。

2.1HAVING与WHERE的区别

HAVING的作用和WHERE类似。HAVING支持所有WHERE操作符。唯一的差别是WHERE过滤行，HAVING过滤组。

注意：HAVING必须在GROUP BY之后使用，表示对分组后的结果进行筛选。

SQL子句的执行顺序：

例题：找出在某商城购买5次以上的客户，并按购买次数（别名为F）从多到少排列，以及account_id正序排列。需要筛掉订单总价为空值的情况

1
2
3
4
5
6
SELECT account_id,COUNT(*) AS F
FROM orders
WHERE total_price is not NULL
GROUP BY account_id
HAVING F > 5
ORDER BY F DESC,account_id;

例题：往年课程表格(course)中筛选出课程，按照人数由多到少排序，以及班级名称升序排列。输出的列为：class(课程), count_number(人数)

d student class
1 小马 English
2 小绿 English
3 小红 Cooking
4 小兰 History
5 小河 History
6 小绿 Cooking
7 小君 Math
8 小风 Biology
9 小兰 English
10 小红 English 
1
2
3
4
SELECT class,count(*) AS count_number
FROM course
GROUP BY class
ORDER BY count_number DESC,class;

例题：从玩家登陆服务器的活动(activity)表格中筛选出每一个玩家第一次登陆的日期，来分析游戏的宣发效果，按照玩家(player_id)分组，完成上述任务。输出的列为： player_id(玩家), first_login(第一次登陆日期)

player_id first_login
1001 2020-03-01
1002 2020-06-20
1003 2020-05-18
1004 2020-03-02 
1
2
3
SELECT player_id,MIN(event_date) AS first_login
FROM activity
GROUP BY player_id;

例题：编写一个SQL查询，从travel表中提取出景区数量排名前十的省份～结果按照total_number降序排列和province升序排列。输出的列为：province(省份), total_number(总景点数量)

id scenic_spot_name province tourist_num_2019_w tourist_num_2020_w
1 布达拉宫 西藏 29 29
2 稻城亚丁 四川 18 18
3 故宫 北京 23 23
4 张家界 湖南 19 19
5 九寨沟 四川 14 14
6 丽江古城 云南 27 27
7 雅鲁藏布江大峡谷 西藏 28 28
8 乐山大佛 四川 23 23
9 万里长城 北京 12 12
10 宏村 安徽 24 24 
1
2
3
4
5
SELECT province,count(*) AS total_number
FROM travel
GROUP BY province
ORDER BY total_number DESC,province
LIMIT 10;

例题：假设今天是2021年7月6日，写一个SQL查询语句从 (user_sign) 表中找出正常打卡上下班的员工对应的昨天打卡时间。输出的列为：user_id(员工id), startTime(上班打卡时间), endTime(下班打卡时间)

id user_id sign_date sign_time ip_address
1 1000239 2021-07-05 08:32:55 192.165.16.122
2 1000008 2021-07-05 10:36:32 192.168.16.127
3 1000008 2021-07-05 18:42:35 192.165.16.122
4 1000239 2021-07-05 21:38:05 192.175.14.125
6 1000169 2021-07-04 08:32:55 192.165.16.122
7 1000169 2021-07-05 10:15:43 192.165.16.122 
1
2
3
4
5
SELECT user_id,MIN(sign_time) AS startTime,MAX(sign_time) AS endTime
FROM user_sign
WHERE sign_date = '2021-07-05'
GROUP BY user_id
HAVING startTime < endTime;

例题：写SQL查询语句帮助他从邮件列表(e_mails)找到这些重复的邮件

输出的内容为： e_mail

id e_mail
1 b@c.com
2 c@d.com
3 c@d.com
4 d@e.com
5 a@b.com
6 d@e.com
7 c@d.com
8 b@c.com 
1
2
3
4
SELECT e_mail
FROM e_mails
GROUP BY e_mail
HAVING COUNT(*) > 1;

四、互联网数据分析实战

用户行为 (user_behavior) 表中记录所有用户的行为数据。其中包含六个字段：

  • 编号id
  • 用户编号 (user_id)
  • 商品编号 (item_id)
  • 商品种类编号 (category_id)
  • 行为类型 (behavior_type)，又包含四种类型：
    • pv 浏览访问商品页面
    • buy 商品购买
    • cart 将商品加入购物车
    • fav 收藏商品
  • 日期 (date)。

4.1主要数据指标

计算数据指标是流量分析的第一步。流量分析中有多种数据指标用于衡量电商活动效果，以及流量多少。最常使用的2个指标是：

日PV：每日页面访问量。

具体解释：电商网站有多个页面，包括不同商品的详情页、商品分类页等等。用户每打开其中一个页面则记录一次。我们把记录的访问量，page view，称作PV。PV越多，浏览网页的次数就越多。对于网站来说，PV就像电视收视率。

日PV，即每日页面访问量。通常可用于追踪流量情况，观察活动前、活动中、活动后的变化情况，评估活动效果。一般来说，活动期间或当天访问量会大幅提高，活动后有一定程度的落回，可以认为是一个成功的活动。

除了促销活动等，对于日PV的监控追踪还可以用于衡量修改方案效果等。例如，通过调整页面布局、改进功能等。每一次变动后，对日PV数据进行跟踪观察，可以分析改进效果。

日UV：每日访问、浏览这个页面的人数 (访客数）

具体解释：Unique View，简称为UV。也就是通过互联网访问、浏览这个页面的人数。

UV和我们常说的IP不同。比如，A和B都通过自己不同的账号在同一台电脑上访问了网站，那么IP数为1，UV数为2。因为使用了同一个电脑，所以IP为1，但他们都有各自的账号，所以UV为2。

日UV则是每日访问的人数。在同一天内，A的第一次访问会记录，而第二次访问则不会计数。日UV提供了一定时间内不同用户数量的统计指标。

4.2 流量分析过程

  • 通过观察流量规律，辅助确定活动的安排，以及调整方案。比如说，在一天之中，用户访问集中在上午10点到12点和下午3点到7点这些时间段。假设现在想要举办每天两小时的消费满200减50并赠送礼品的活动。根据上述规律，活动开始时间最好是选在上午11、12点钟。这样才能在一定的时间内被大部分用户所知道。

  • 通过纵向或横向对比，发掘流量特征以及异常。比如，可以通过分析不同渠道或用户属性下的流量变化情况，可以总结具体的渠道、用户属性的访问规律。

  • 根据对流量分析的解释，我们可以发现流量分析的一个重要作用，就是分析出流量的规律，对活动效果、业务调整具有重大影响。

4.3 日PV的计算

用户行为表 (user_behavior) 中包含了从2020年12月5日～2021年12月13日的行为记录。用户有四种行为类型：pv, buy, cart, fav。为了计算日PV，小鹿需要筛选行为类型为’pv’的记录，再根据日期分组排序，并统计每天对应的记录总数量。

  • 第一步：初步筛选。筛选用户行为类型为’pv’的记录的方法，便是使用前面讲到的WHERE语句。

  • 第二步:按照日期分组并排列。筛选后发现，每一天会有多条用户访问的记录。为了计算每天对应的用户访问记录总数，需要先将所有记录按照对应的日期分组并排序，方便查看分析数据。使用GROUP BY和ORDER BY语句就能轻松完成

  • 第三步：统计数量。使用聚合函数COUNT( )计算筛选分组后每天对应的记录数。通常在工作中，GROUP BY语句和聚合函数的联合使用是基础重要的操作。

    代码实现：

    1
    2
    3
    4
    5
    SELECT date,COUNT(*) AS day_PV
    FROM user_behavior
    WHERE behavior_type = 'pv'
    GROUP BY date
    ORDER BY date;

4.4 日UV的计算

统计用户行为表中每天对应的独立访问用户人数，即用户行为是pv，并按照日期从远到近排列。因为每个用户当天若多次访问只记录一次，所以对用户需要去重处理。

代码实现：

1
2
3
4
5
SELECT date,COUNT(DISTINCT user_id) AS day_UV
FROM user_behavior
WHERE behavior_type = 'pv'
GROUP BY date
ORDER BY date;

4.5 复购分析

在日常工作中，与引流（拉新）同样重要的便是维护老客户。商家获得新用户的成本是维护老用户的5～10倍，由此可见关注老用户的复购的重要性。

复购率是指最近一段时间购买次数，可以分为用户复购率和订单复购率。

用户复购率 = 单位时间内：购买两次及以上的用户数/有购买行为的总用户数

订单复购率 = 单位时间内：第二次及以上购买的订单个数/总订单数

复购率用于说明用户的忠诚度，也说明了商品或者服务的用户粘性。

复购分析应用：在日常工作中，常常用横向维度（商品、用户、渠道）对比分析。

将复购率细化，帮助精准定位问题位置。

举例说明：复购率可以分为微信端、手机qq端、APP以及PC端复购率，分别表示来自不同渠道的用户再次购买的机率。通过对比，微信端用户复购率最高，便可以得知微信渠道的运营推广对于提高复购率更有效。

复购用户数计算：需要显示的内容：复购用户的user_id以及购买次数。即筛选用户行为表中购买（用户行为为buy）记录条数大于或等于2的用户。

代码实现：

1
2
3
4
5
SELECT user_id,COUNT(*) AS 购买次数
FROM user_behavior
WHERE behavior_type = 'buy'
GROUP BY user_id
HAVING 购买次数 >= 2;

用户复购率 = 复购用户总人数 / 购买用户总人数。

因此要完成复购分析，除了计算复购用户总人数的需求外，第二步便是计算用户总人数。

代码实现：

1
2
3
SELECT COUNT(DISTINCT user_id) AS 购买用户总数
FROM user_behavior
WHERE behavior_type = 'buy';

没有复购用户数计算：需要显示的内容：没有复购用户的user_id。即筛选用户行为表中购买（用户行为为buy）记录条数等于1的用户。

代码实现：

1
2
3
4
5
SELECT user_id
FROM user_behavior
WHERE behavior_type = 'buy'
GROUP BY user_id
HAVING COUNT(*) = 1;

例题：首先是收集仅在2021年春季才售出的产品，即只在区间2021-01-01至2021-03-31（包含1月1日和3月31日）之间发售的产品。

order_id product_id buyer_id sale_date quantity
1001 2001 3001 2021-01-21 16
1002 2002 3002 2021-02-18 4
1003 2002 3003 2021-06-07 8
1004 2003 3004 2021-05-15 17
1005 2004 3005 2021-03-18 16

代码实现：

1
2
3
4
SELECT product_id,MAX(sale_date)
FROM orders
GROUP BY product_id
HAVING MIN(sale_date) >= '2021-01-01' AND MAX(sale_date) <= '2021-03-31';

例题：从往年旅游景点数据表格中筛选出2019和2020两年的五一平均接待游客人数小于40w的省份, 且五一平均接待游客人数升序排列后的第8名到10名。输出的列为：province(省份),avg_number(平均接客量)

id scenic_spot_name province tourist_num_2019_w tourist_num_2020_w
1 布达拉宫 西藏 29 29
2 稻城亚丁 四川 18 18
3 故宫 北京 23 23
4 张家界 湖南 19 19
5 九寨沟 四川 14 14
6 丽江古城 云南 27 27
7 雅鲁藏布江大峡谷 西藏 28 28
8 乐山大佛 四川 23 23
9 万里长城 北京 12 12
10 宏村 安徽 24 24

代码实现：

1
2
3
4
5
SELECT province,SUM((tourist_num_2019_w + tourist_num_2020_w)/2) AS avg_number
FROM travel
GROUP BY province
ORDER BY avg_number
LIMIT 7,3;