MySQL04: 查询语句

搞数据分析肯定是要学习下数据库的;

本文数据下载可以后台联系入群

核心7个关键词SELECT、FROM、 WHERE、GROUP BY、HAVING、ORDER BY、LIMIT

SELECT <select_list>
FROM <table_list>
[WHERE <where_condition>]
[GROUP BY <group_by_list>]
[HAVING <having_condition>]
[ORDER BY <order_by_list> ASC|DESC]
[LIMIT <limit_number>]

SELECT、FROM组合

查询命令中必须使用的组合，select相当于tidyverse中的select选择列，from是选择的表格的名字

-- 选择指定的数据库
use s50;
-- 查看所有列的数据
select * from sec_buildings;
-- 查看链家二手房的小区名称、户型、面积、单价和总价;
select fname,ftype,size,price_unit,tot_amt from sec_buildings;
-- 查看链家二手房的平均单价和平均总价
select avg(price_unit),avg(tot_amt) from sec_buildings;

WHERE

类似tidyverse中的filter按照条件过滤行

运算符类型	符号	含义
算术运算符	+-*/	加减乘除
比较运算符	=	等于
<=	小于或等于
>=	大于或等于
!=或<>	不等于
!>	不大于
!<	不小于
逻辑运算符	and	且
or	或
not	非
区间运算符	in	包含在内
between...and...	两者之间
通配符	%	匹配任何内容的多个占位符
-	匹配任何内容的单个占位符

逻辑运算符的优先级问题 逻辑运算符优先级:not > and > or
括号可以改变优先级

select * from sec_buildings
where built_date = "1996年建" -- 1996年建的
and region in ("浦东","静安","闵行")
and tot_amt > 390 -- 房价在390万以上
and size between 50 and 100 -- 面积在50-100平米
and (direction="朝南" or direction="朝南北") -- 阳台朝南或者朝南北
and fname like "%恒大%"; -- 小区名称中包含“恒大字样

GROUP BY

5种常用聚合函数，类似tidyverse的group_by()+summarise()

聚合函数	功能	说明
count	用于统计记录数	函数内传递星号*,即表示统计数据表的记录数；如果函数内传递某个字段名称，即表示统计该字段的记录数（非空数据的个数）；如果需要去重计数，可以在字段或者*前面加上关键字distinct
sum	用于数值型数据的求和	需要注意的是变量求和之后，仍然具有业务含义
avg	用于数值型数据的均值计算	同上
min	用于计算数值型数据的最小值	常用于日期时间型变量的最小值，也就是业务中的首次交易
max	用于计算数值型数据的最大值	常用于日期时间型变量的最大值，也就是业务中的最后一次交易

count、distinct函数练习

select count(*) from sec_buildings; --多少行
select count(region) from sec_buildings; --多少行
select distinct region from sec_buildings; --不重复的区域
select count(distinct region) from sec_buildings; --多少个区域

group by 应用实例 查询各个行政区域下每年二手房的数量、总的可居住面积、平均总价格、最大总价格和最小单价

select region,built_date,
  count(*) as counts,
  sum(size) as tot_size,
  avg(tot_amt) as avg_amt,
  max(tot_amt) as max_amt,
  min(price_unit) as min_price
from sec_buildings
group by region,built_date
limit 5;

按照区和建成年份分组，比如第4行为浦东区1992年建成的小区中，总共有30套房子，总面积1778.41，平均每套房子价格372.8667万，最贵的房子为600万，平均房价36011元/平米

+--------+------------+--------+----------+----------+---------+-----------+
| region | built_date | counts | tot_size | avg_amt  | max_amt | min_price |
+--------+------------+--------+----------+----------+---------+-----------+
| 浦东   | 1992年建   |     30 |  1778.41 | 372.8667 |     600 |     36011 |
| 浦东   | 2002年建   |     85 |  9918.56 | 777.5529 |    1800 |     35087 |
| 浦东   | 1988年建   |     24 |  1237.65 | 356.2083 |     490 |     47886 |
| 浦东   | 1997年建   |    144 | 10664.67 | 416.2847 |     890 |     32136 |
| 浦东   | 1987年建   |     21 |  1119.64 | 315.7143 |     470 |     32483 |
+--------+------------+--------+----------+----------+---------+-----------+
5 rows in set (0.05 sec)

HAVING

对统计结果的筛选，类似于filter 按照地区、户型、楼层和阳台朝向分组统计静安区和浦东区二手房的平均单价和总数量,并筛选出平均单价 超过100000元的记录

select region,ftype,floor,direction,
  avg(price_unit) as avg_price,
  count(*) as counts
from sec_buildings
where region in ("浦东","静安")
group by region,ftype,floor,direction
having avg_price >100000;

用于查询结果的排序，排序过程中可以按照某个或者某些字段进行升序或者降序的排列。默认是按照升 序方式排序，如果需要降序排列的话，需要在字段后面跟上一个关键词DESC。
按面积降序、总价升序的方式查询出所有2室1厅的二手房信息

select * from sec_buildings
where ftype = "2室1厅"
order by size desc,tot_amt;

LIMIT

LIMIT n:表示返回查询结果的前n行
LIMIT m,n:表示从第m+1行开始，连续返回n行的数据
查询出浦东新区2013年建的二手房,并且总价排名在6-10

select * from sec_buildings
where region="浦东" and built_date = "2013年建"
order by tot_amt desc
limit 5,5;

CASE WHEN

离散数值与实际含义的映射 类似tidyverse中的mutate+case_when，根据对列中的值重新赋值到新的列

select *,
  case
    when Is_Discount = 1 then "享受折扣" else "无折扣"
  end Discount_New,
  case
    when Pay_Type in (1,3,5,7,10) then "微信支付"
    when Pay_Type in (6,9,12) then "银行卡支付" else "支付宝支付"
  end  Pay_Type_New
from goods_orders
limit 5;

将长形统计表转换成宽形统计表，统计2018年每个月微信支付、银行卡支付、支付宝支付的总金额分别为多少

select month(Order_Date) as iMonth,
  sum(case when Pay_Type in (1,3,5,7,10) then Pay_Amt end) as 'Wechat',
  sum(case when Pay_Type in (6,9,12) then Pay_Amt end) as 'Bank_Card',
  sum(case when Pay_Type not in (1,3,5,7,10,6,9,12) then Pay_Amt end) as 'Ali_Pay'
from goods_orders
where year(Order_Date) = 2018
group by month(Order_Date)
order by month(Order_Date);

将宽形统计表转换成长形统计表，统计2018年每个月微信支付、银行卡支付、支付宝支付的总金额分别为多少

select 
  month(Order_Date) as iMonth,
  case
    when Pay_Type in (1,3,5,7,10) then '微信支付'
    when Pay_Type in (6,9,12) then '银行卡支付'
    else '支付宝支付'
  end as iType,
  sum(Pay_Amt) as Amt
from goods_orders
where year(Order_Date) = 2018
group by iMonth,iType
order by month(Order_Date);

嵌套查询

1. 含比较运算符的嵌套查询

# 1.查询年龄超过所有学员平均年龄的学员信息
SELECT * FROM stu_info
WHERE age >= (SELECT AVG(age) FROM stu_info); # 需要注意的是Where后面不能使用聚合函数
# 2.查询年龄不低于所属系平均年龄的学员信息
SELECT * FROM stu_info AS s1
WHERE age>= ( SELECT avg(age) FROM stu_info AS s2
              WHERE s1.department = s2.department);

含ANY或ALL关键词的嵌套查询 相当于求出最大值ANY(SELECT DISTINCT age FROM stu_info WHERE department = '管理系') AND department != '管理系'

# 1.查询非管理系中比管理系任意一个学员年龄小的学员信息 
SELECT * FROM stu_info
WHERE age < ANY(SELECT DISTINCT age FROM stu_info
WHERE department = '管理系') AND department != '管理系';

含IN关键词的嵌套查询

# 1.查询数学系和计算机系的学员信息
SELECT * FROM stu_info
WHERE department IN('数学系','计算机系');
# 2.查询与张勇、刘伟同一个系的学员信息
SELECT * FROM stu_info
WHERE department IN (SELECT department FROM stu_info WHERE iname IN('张勇','刘 伟'));
# 3.查询MySQL成绩大于85分的学员信息
SELECT * FROM stu_info
WHERE id IN (SELECT id FROM stu_score WHERE MySQL > 85);

含EXISTS关键词的嵌套查询

# 查询MySQL成绩大于85分的学员信息
SELECT * FROM stu_info
WHERE EXISTS(SELECT * FROM stu_score WHERE stu_score.id = stu_info.id AND MySQL > 85);

NOT IN和NOT EXISTS 查询数学系和计算机系之外的学员信息

# 方法一
SELECT * FROM stu_info
WHERE department NOT IN('数学系','计算机系');
#方法二
SELECT * FROM stu_info
WHERE NOT EXISTS(SELECT * FROM stu_score WHERE department IN('数学系','计算机系') and stu_score.id = stu_info.id);

开窗函数

开窗函数是SQL中一种高级功能，它允许你在一组相关的行（一个“窗口”）上执行计算，而不仅仅是单行。这组行是由PARTITION BY, ORDER BY等子句定义的，使得你能够在每个分组或排序的集合上进行聚合操作，同时保留表中的每一行记录。开窗函数对于解决诸如排名、分组统计、移动平均、累计和等问题非常有用。 总结就是，之前的聚合函数只能按照一个分组信息统计，但是开窗函数可以按照多个分组同时统计，相当于多分组聚合。

一些常见的开窗函数

1. ROW_NUMBER() ：为结果集中的每一行分配一个唯一的、连续的整数。
2. RANK() 和 DENSE_RANK() ：根据排序列的值为行分配排名，前者在有相同值时会跳过排名（如两个第二名后是第四名），后者则不会跳过（连续的排名）。
3. NTILE(n) ：将行分为n个相等的组（桶）。
4. LEAD(column, [offset], [default]) 和 LAG(column, [offset], [default]) ：分别获取当前行之后或之前指定位置的行的列值。
5. FIRST_VALUE(column) OVER ... 和 LAST_VALUE(column) OVER ... ：获取窗口内第一行或最后一行的指定列值。
6. SUM(column) OVER ...、AVG(column) OVER ...、COUNT(column) OVER ... 等聚合函数：在窗口内进行聚合计算。
7. PERCENT_RANK() 和 CUME_DIST() ：分别为每行提供其在窗口内的百分位排名和累积分布。

使用开窗函数的基本语法结构如下：

function_name(column) OVER (
    [PARTITION BY column1, column2,...] 
    [ORDER BY column3, column4,... 
        {ASC | DESC}]
    [ROWS BETWEEN start AND end]
)

• PARTITION BY：定义了窗口的分区，每个分区独立计算函数结果。
• ORDER BY ：在每个窗口内对行进行排序。
• ROWS BETWEEN ... ：定义了窗口的范围，可以是CURRENT ROW、UNBOUNDED PRECEDING、UNBOUNDED FOLLOWING或者它们的组合，来指定窗口的开始和结束位置。

实例

创建表格

create table user_login( 
  user_id varchar(100),
  login_time datetime);

插入值

insert into user_login values
(1,'2020-11-25 13:21:12'),
(1,'2020-11-24 13:15:22'),
(1,'2020-11-24 10:30:15'),
(1,'2020-11-24 09:18:27'),
(1,'2020-11-23 07:43:54'),
(1,'2020-11-10 09:48:36'),
(1,'2020-11-09 03:30:22'),
(1,'2020-11-01 15:28:29'),
(1,'2020-10-31 09:37:45'),
(2,'2020-11-25 13:54:40'),
(2,'2020-11-24 13:22:32'),
(2,'2020-11-23 10:55:52'),
(2,'2020-11-22 06:30:09'),
(2,'2020-11-21 08:33:15'),
(2,'2020-11-20 05:38:18'),
(2,'2020-11-19 09:21:42'),
(2,'2020-11-02 00:19:38'),
(2,'2020-11-01 09:03:11'),
(2,'2020-10-31 07:44:55'),
(2,'2020-10-30 08:56:33'),
(2,'2020-10-29 09:30:28');

查看每位用户连续登录的情况

select 
  user_id,
  min(login_date) start_date,
  max(login_date) end_date,
  count(login_date) days
from(
  select *,date_sub(login_date, interval irank day) idate
  from(
      select *,rank() over(partition by user_id order by login_date) irank
      from(
        select distinct user_id, date(login_time) login_date
        from user_login) as a) as b) as c
group by user_id,idate;

计算每个用户最大连续登录天数

select user_id,max(days) 
from(
    select 
      user_id,
      min(login_date) start_date,
      max(login_date) end_date,
      count(login_date) days
    from(
      select *,date_sub(login_date, interval irank day) idate
      from(
          select *,rank() over(partition by user_id order by login_date) irank
          from(
            select distinct user_id, date(login_time) login_date
            from user_login) as a) as b) as c
    group by user_id,idate) as d
group by user_id;

查看在某个时间段里连续登录天数超过N天的用户
查看10/29-11/25在这段时间内连续登录天数≥5天的用户

select distinct user_id
from (select *,datediff(idate5,login_date)+1 as days
      from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5
            from user_login_date) as a)as b
where days = 5;

本文由 mdnice 多平台发布