强化 SQL 学习+案例分析-05

发表于 | 更新于 | 评论数：

SQL这种东西十分钟入门，半小时精通。

一个数据分析师和我说的话，现在是精通的机会。

在了解了基本的 SQL 语言后，现在重新看回更加注重 表格联结这方面的内容。

学习的主要途径是 SQL 教程

其中MySQL，从入门到熟练也受益良多。

在亚马逊买了一本电子版的《SQL必知必会》，可以作为后期查询学习的重要工具。

—–1023更新

mysql 安装和调用环境

不仅如此，目前还需要更多的了解下 mysql 的基本原理，所以在 mac 安装了基本的 sql 环境
安装过程比较简单，不在详细说明

通过终端可以调用 mysql

1
2
$ /usr/local/mysql/bin/mysql -u root -p
Enter password:

或者通过 调用 pgsql

1
$ /Library/PostgreSQL/10/scripts/runpsql.sh;

当然，我们可以利用更直观的 GUI 作为统计分析的工具，注重掌握工具而非掌握 SQL 语言

电脑上安装了 sequel pro 和 querious 两款，在新的系统上sequel 比较容易奔溃，尝试好几天才在电脑上导入了数据。

注意

sequel 导入数据的时候需要新建表名，需要注意编码标准，软件不是很直观，需要熟悉。

如果希望有更大的进步可以在终端命令行用语句解决，但是不是目前阶段的学习重点。

SQL 查询语句基础

以 运营大湿兄 的案例作为案例

select

当我们涉及到非常复杂的与或逻辑判断，应该怎么办？比如即满足条件AB，又要满足条件C，或者是满足条件DE。此时需要用括号明确逻辑判断的优先级。

1
2
3
select * from DataAnalyst
where (city = '上海' and positionName = '数据分析师')
   or (city = '北京' and positionName = '数据产品经理')

count函数

count 可以迅速计算出个数，比如

1
2
select city,count(1) from DataAnalyst
group by city

如果有重复的名字，则需要去重

1
2
select city,count(distinct positionId) from DataAnalyst
group by city

distinct 是去重函数，distinct positionId 会只计算唯一的positionId个数。日常工作中，活跃用户数、文章UV，都是用distinct 计算获得，这是唯一标示符ID的重要作用。

除了count，还有max，min，sum，avg等函数，也叫做聚合函数。用法和Excel没什么区别。

逻辑判断

SQL也有if函数，和Excel的用法一摸一样，通过它我们能进行复杂的运算。比如我想统计各个城市中有多少数据分析职位，其中，电商领域的职位有多少，在其中的占比？

industryField是公司的行业领域，虽然我们能用where like 计算出有几个电商的数据分析师，但是占比的计算会比较麻烦，此时可以用if。

1
select if(industryField like '%电子商务%',1,0) from DataAnalyst

比较复杂的查询函数

如果要模糊查找，能用like

* from DataAnalyst
1
where companyId between 10000 and 20000

嵌套子查询

1
2
select * from(select city,count(distinct positionId) as counts from DataAnalyst) as t1
where counts >=500

我们将第一次查询获得的城市职位数的结果，看作一张新的表，利用as 将它命名为t1( table1 的简写)，将职位数命名为一个新的字段counts。然后外面再套一层select 过滤出counts >=500。

这种查询方式就叫嵌套子查询，使用场景比较广泛，where 后面也能跟子查询。

数据清洗函数

比较难，需要补充，后续专文记录，更需要不断更新训练结构

1023更新————————-

表格嵌套

join 函数是 SQL 里面 比较高级的部分，需要重点掌握。

数据库由多张表组成，表与表之间可以实现关联。表与表之间的关联需要通过表中间唯一的数值 ，即主键。

在上图中，

Students.addressId = Address.id

Students.id = Scores.studentId

Scores.courseId = Courses.id

换成 SQL 语言，我们则需要 join 将 多个表格关联起来。

1
2
select * from Students
join Address on Students.addressId = Address.id

这里需要注意的是，因为字段可能重名，所以一旦使用了Join，字段前应该加上表名，如Students.addressId和Address.id ，这种用法是为了字段的唯一性，否则遇到重名，系统不知道使用哪个字段，就会报错。

Join语法有很多不同的变形，Left Join，Outer Join等，新人很容易混淆。这个我们可以用数学中的交集和并集掌握。

真实的数据查询场景中，Join会用到很多，业务复杂用五六个Join也是常态，如果算上各类逻辑处理，SQL代码行数可以破百。这时候，考验的就是熟练度了。

SQL只要多加训练，并不是一门很难掌握的语言。除了技巧，还要看你对业务表的熟悉程度，一般公司发展大了，百来张表很正常，各类业务逻辑各种Join，各字段的含义，这是同样要花费时间的苦功夫。

1024更新————————-

1.内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现
2.外连接: 包括 （1）左外连接(左边的表不加限制) （2）右外连接(右边的表不加限制) （3）全外连接(左右两表都不加限制)
3.自连接(连接发生在一张基表内)

内连接多表查询的标准写法

对于 mysql，我们可以采用多表查询的简单写法，例如

1
2
3
4
5
select
ID,CITY.NAME,CITY.POPULATIONG,LIFEEXPECTANCY
from CITY,COUNTRY
WHERE
ID<10 AND city.COUNTRYcode=COUNTRY.code

不过为了更好的迁移性和适用性，我们最好选用标准语句

1
2
3
4
5
select
ID,CITY.NAME,CITY.POPULATIONG,LIFEEXPECTANCY
from CITY inner join COUNTRY
on
ID<10 AND city.COUNTRYcode=COUNTRY.code

笛卡尔积和 cross jion

CROSS JOIN又称为笛卡尔乘积，实际上是把两个表乘起来。

外连接查询

左连接查询

1
2
3
4
5
6
select
ID,CITY.NAME,CITY.POPULATIONG,LIFEEXPECTANCY
from CITY left Outer join COUNTRY
on
ID<10 AND city.COUNTRYcode=COUNTRY.code
limit 10

右连接查询

1
2
3
4
5
6
select
ID,CITY.NAME,CITY.POPULATIONG,LIFEEXPECTANCY
from CITY right Outer join COUNTRY
on
ID<10 AND city.COUNTRYcode=COUNTRY.code
limit 10

两者的区别在于以哪张表格为“基准表”：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。

子查询

择时学习更新

孰能生巧

SQL 本身并不困难，但是需要熟练掌握。

https://leetcode.com/ 是一个优秀的练习网站，需要时常登录练习，而非要用再来查询。

记录整理下， 在工作中见到的负责语句，常看常新。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    id,
    sum(play_ac) as play,
    sum(like_ac) as like,
    sum(comment_ac) as comment,
    sum(share_ac) as share
FROM
    aweme.d_aweme_item_stats
WHERE
    date BETWEEN '20180915' AND '20180916'
    and create_time>'2018-09-15'
group by id
ORDER BY rand() LIMIT 100000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
SELECT
    a.uid,
    b.company_name,
    b.industry_2,
    d.nickname,
    c.industry_name,
    c.small_industry_name,
    e.new_item,
    e.vv,
    e.like,
    e.comment,
    e.share,
    f.fans_all,(f.fans_all -g.fans_all) as fans_new
FROM
    (
        SELECT
            pgc_basis_auth_id,
            uid
        FROM
            dm_crm.ods_crm_auth_pgc_basis_auth_relation
        WHERE
            date = '${date}'
            AND channel = 2
            AND status = 1
    ) a
    inner JOIN (
        SELECT
            id,
            company_name,
            industry_2
        FROM
            dm_crm.ods_crm_auth_pgc_basis_authentication
        WHERE
            date = '${date}'
    ) b on a.pgc_basis_auth_id = b.id
    LEFT JOIN (
        SELECT
            customer_name,
            business_type,
            industry_name,
            small_industry_name
        FROM
            ad_dim.dim_crm_customer
        WHERE
            p_date = '${date}'
        group by
            customer_name,
            business_type,
            industry_name,
            small_industry_name
    ) c on b.company_name = c.customer_name
    left join (
        SELECT
            user_id,
            sum(if(create_time > '${DATE}', 1, 0)) as new_item,
            sum(play_count) as vv,
            sum(like_count) as like,
            sum(comment_count) as comment,
            sum(share_count) as share
        FROM
            aweme.d_aweme_item_stats
        WHERE
            date between '${date-29}'
            and '${date}'
        group by
            user_id
    ) e on e.user_id = a.uid
    left JOIN (
        SELECT
            to_user_id,
            count(from_user_id) as fans_all
        FROM
            aweme.stg_user_relation_info_all
        WHERE
            date = '${date}'
            AND type = 1
            AND business_id = 32
        group by
            to_user_id
    ) f on a.uid = f.to_user_id
    left JOIN (
        SELECT
            to_user_id,
            count(from_user_id) as fans_all
        FROM
            aweme.stg_user_relation_info_all
        WHERE
            date = '${date-29}'
            AND type = 1
            AND business_id = 32
        group by
            to_user_id
    ) g on a.uid = g.to_user_id
    LEFT JOIN (
        SELECT
            user_id,
            nickname
        FROM
            aweme.ods_user_profile_info
        WHERE
            date = '${date}'
    ) d on d.user_id = a.uid
WHERE
    c.business_type = 1