数据分析学习笔记part_1

数据分析

Lesson 1 : SQL初探

SQL和移动平均值

SQL简介

实体关系图(ERD) 是查看数据库中数据的常用方式。下面是我们将用于 Parch & Posey 数据库的 ERD。包括：1. 表的名称 2. 每个表中的列 3. 表配合工作的方式。如下图所示，可以将每个表视为一个单独的电子表格。然后将每个电子表格中的列放在表名下面。例如，region 表有两列: id 和 name，而 web_events 表有四列。

SQL的优点，使用传统关系数据库与 SQL 交互有一些主要优点。最明显的 5 个优点是：
1. SQL 很容易理解。
2. 传统的数据库允许我们直接访问数据。
3. 传统的数据库可使我们审核和复制数据。
4. SQL 是一个可一次分析多个表的很好工具。5. 相对于 Google Analytics 等仪表板工具，SQL 可使我们分析更复杂的问题。
为什么企业喜欢使用数据库:
1. 只有输入了需要输入的数据，以及只有某些用户能够将数据输入数据库，才能保证数据的完整性。
2. 可以快速访问数据 - SQL 可使我们从数据库中快速获取结果。可以优化代码，快速获取结果。
3. 可以很容易共享数据 - 多个人可以访问存储在数据库中的数据，所有访问数据库的用户获得的数据都是一样。
SQL 数据库存储数据方式
1. CREATE TABLE 是一个在数据库中创建新表的语句。
2. DROP TABLE 是删除数据库中表的语句。
3. SELECT 读取并显示数据。我们将这称为查询。
常用的两种查询语句
1. SELECT用于提醒查询要返回哪些列。
2. FROM用于提醒查询在哪个表中查询。注意，这个表中需要有列。
```
SELECT *
FROM orders;
```
注意：每个查询至少有一个 SELECT 和 FROM 语句。 SELECT 语句用于放置要显示数据的列。FROM 语句用于放置要从中提取数据的表。
练习：尝试编写自己的查询，以便为 orders 表中的所有订单选择 id、account_id 和 occurred_at 列。
```
SELECT id, account_id, occurred_at
FROM orders;
```

规定查询格式
1. 大写，我们大写了 SELECT 和 FROM，而将表和列名称小写。这是一个常见的格式惯例。大写命令（SELECT、FROM），小写查询中的其他内容是常见做法。这使得查询更容易读取，这在编写更复杂的查询时更为重要。
2. 表和变量名中不需要空格，通常在列名中使用下划线，避免使用空格。在 SQL 中使用空格有点麻烦。在 Postgres 中，如果列或表名称中有空格，就需要使用双引号括住这些列/表名称（例如：FROM "Table Name"，而不是 FROM table_name）。在其他环境中，可能会使用方括号（例如：FROM [Table Name]）。
3. 在查询中使用空格，QL 查询忽略空格，因此可以根据需要在代码之间添加尽可能多的空格和空行，并且查询结果是相同的。
```
SELECT account_id FROM orders
#等价于
SELECT account_id
FROM orders；
```

SQL不区分大小写

SELECT account_id
FROM orders;
#和这个相同
DELECT account_id
FROM orders
#也和这个相同
SELECT AcCoUnt_id
FROM oRdErS

但是如果没有区分大小写键入正确的字符，编程语言会变得不易阅读。

分号

根据 SQL 环境，查询结尾可能需要一个执行的分号。这个"要求"在其他环境中比较灵活。我们认为在每个语句的末尾添加一个分号是最好的做法，如果环境能够一次显示多个结果，那么这样做还可以一次运行多个命令。最好的做法：
```
SELECT account_id
FROM orders;
```

LIMIT

LIMIT 命令始终是查询的最后一部分。下面的例子仅显示订单表的前 10 行和所有列：
```
SELECT *
FROM orders
LIMIT 10；
```
练习
1. 编写一个查询，将响应限制在前 15 行，和包括 web_events 表中的 occurred_at、account_id 和 channel 字段。
```
SELECT occurred_at, account_id
FROM web_events
LIMIT 15;
```
ORDER BY
语句可使我们按任意行排序表。ORDER BY 语句始终在 SELECT 和 FROM 语句之后，但位于 LIMIT 语句之前。学习其他命令时，这些语句的顺序将更为重要。如果使用 LIMIT 语句，它将始终显示在最后。（提示记住可以在 ORDER BY 语句中的列之后添加 DESC，然后按降序排序，因为默认是按升序排序的。"）

练习
1. 编写查询，返回 orders 表的前 10 个订单。包含 id、occurred_at 和 total_amt_usd。
```
SELECT id, occurred_at, total_amt_usd
FROM orders
ORDER BY occurred_at
LIMIT 10;
```
2. 编写一个查询，基于 total_amt_usd 返回前 5 个最高的订单 (orders 表)。包括 id、account_id 和 total_amt_usd。
```
SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC 
LIMIT 5;
```
3. 编写一个查询，基于 total 返回前 20 个最低订单 (orders 表)。包括 id、account_id和 total。
```
SELECT id, account_id, total
FROM orders
ORDER BY total
LIMIT 20;
```

我们可以基于必须满足的条件，使用 WHERE 语句来为表格创建子集。WHERE 语句中使用的常用符号包括：

>（大于）
<（小于）
>=（大于或等于）
<=（小于或等于）
=（等于）
!=（不等于）

练习
1. 从订单表提取出大于或等于 1000 的 gloss_amt_usd 美元数额的前五行数据（包含所有列）。
```
SELECT *
FROM orders
WHERE gloss_amt_usd >= 1000
LIMIT 5;
```
2. 从订单表提取出小于 500 的 total_amt_usd美元数额的前十行数据（包含所有列）。
```
SELECT *
FROM orders
WHERE total_amt_usd < 500
LIMIT 10;
```

WHERE 与非数字数据一起使用
- WHERE 语句也可以与非数字数据一起使用。在这节课中，我们可以使用 = 和 ！= 运算符。还需要确保在文本数据中使用引号（单引号或双引号都可以 - 如果原始文本中有引号，就一定要注意）。
- 通常将 WHERE 与非数字数据字段一起使用时，我们会使用 LIKE、NOT 或 IN 运算符。
练习
1. 过滤账户（accounts ）表格，从该表格中筛选出 Exxon Mobil 的 name、website 和 primary point of contact (primary_poc)。
```
SELECT name, website, primary_poc
FROM accounts
WHERE name = 'Exxon Mobil';
```
算数运算符
派生列：我们将现有的列组合，生成的新列称为派生列。

常见运算包括：
1. *（乘法）
2. +（加法）
3. -（减法）
4. /（除法）
练习
1. 使用 订单 (orders)表：
  
  创建一个用 standard_amt_usd 除以 standard_qty 的列，查找每个订单中标准纸的单价。将结果限制到前 10 个订单，并包含 id和 account_id 字段。
```
SELECT id, account_id, standard_amt_usd/standard_qty AS unit_price
FROM orders
LIMIT 10;
```
2. 编写一个查询，查找每个订单海报纸的收入百分比。只需使用以 _usd 结尾的列。（在这个查询中试一下不使用总列）。包含 id 和 account_id 字段。
```
SELECT id, account_id,
poster_amt_usd/(standard_amt_usd + gloss_amt_usd + poster_amt_usd) AS post_per
FROM orders;
```
逻辑运算符
逻辑运算符包括:
1. LIKE 可用于进行类似于使用 WHERE 和 = 的运算，但是这用于可能不知道自己想准确查找哪些内容的情况。
2. IN 用于执行类似于使用 WHERE 和 = 的运算，但用于多个条件的情况。
3. NOT 这与 IN 和 LIKE 一起使用，用于选择 NOT LIKE 或 NOT IN 某个条件的所有行。
4. AND & BETWEEN 可用于组合所有组合条件必须为真的运算。
5. OR 可用于组合至少一个组合条件必须为真的运算。
LIKE 运算符对于处理文本非常有用。我们将在 WHERE 子句中使用 LIKE。 LIKE 运算符经常与 ％ 一起使用。 ％ 告诉我们，我们可能会想要任何数量的字符，产生一组特定的字符或者遵循一组特定的字符。记住，需要用单引号或双引号将传达给 LIKE 运算符的文本括住，因为这个字符串中的大小写字母不一样。搜索 'T' 与搜索 't' 不同。

练习

使用 accounts (客户) 表查找
1. 所有以 'C' 开头公司名。
```
SELECT name
FROM accounts
WHERE name LIKE 'C%';
```
2. 名称中包含字符串 'one' 的所有公司名。
```
SELECT name
FROM accounts
WHERE name LIKE '%one%';
```
3. 所有以 's' 结尾的公司名。
```
SELECT name
FROM accounts
WHERE name LIKE '%s';
```
IN 运算符对于数字和文本列都很有用。这个运算符可使我们使用 =，但对于特定列的多个项目。可以检查我们要提取数据的一个、两个或多个列值，但这些都在同一个查询中。在后面的概念中，我们将介绍 OR 运算符，也可以使用这个运算符执行这些任务，但使用 In运算符编写的查询更清楚一些。

练习
1. 使用客户表查找 Walmart、Target 和 Nordstrom 的name (客户名称), primary_poc (主要零售店), and sales_rep_id (销售代表 id)。
```
SELECT name, primary_poc, sales_rep_id
FROM accounts
WHERE name IN ('Walmart', 'Target', 'Nordstrom');
```
2. 使用 web_events 表查找有关通过 organic 或 adwords 联系的所有个人信息。
```
SELECT *
FROM web_events
WHERE channel IN ('organic', 'adwords');
```
NOT 运算符是一个非常有用的运算符，用于与之前介绍的两个运算符 IN 和 LIKE 一起运算。通过指定 NOT LIKE 或 NOT IN，我们可以查找到所有不符合特定条件的行。

练习
1. 使用客户表查找除 Walmart、Target 和 Nordstrom 之外的所有商店的客户名称、主要零售店和销售代表 id。
```
SELECT name, primary_poc, sales_rep_id
FROM accounts
WHERE name NOT IN ('Walmart', 'Target', 'Nordstrom');
```
2. 使用 web_events 表查找除通过任何方法联系的个人的所有信息，除了使用 organic 或 adwords 方法。
```
SELECT *
FROM web_events
WHERE channel NOT IN ('organic', 'adwords');
```
3. 使用客户表查找所有不以 'C' 开头的公司名。
```
SELECT name
FROM accounts
WHERE name NOT LIKE 'C%';
```
4. 所有名称中不包含字符串 'one' 的公司名。
```
SELECT name
FROM accounts
WHERE name NOT LIKE '%one%';
```
5. 所有不以 's' 结尾的公司名。
```
SELECT name
FROM accounts
WHERE name NOT LIKE '%s';
```
有时使用 BETWEEN 比使用 AND 使语句更清楚一些。
AND 运算符用于 WHERE 语句中，用于一次考虑多个逻辑子句。使用 AND 连接一个新的语句时，需要指定感兴趣的列。可以同时连接尽可能多的考虑语句。这个运算符可用于我们迄今为止所学习到的所有运算，包括算术运算符（+、*、-、/），可以使用 AND运算符将 LIKE、IN 和 NOT 逻辑连接到一起。
```
WHERE column >= 6 AND column <= 10
#运用between
WHERE column BETWEEN 6 AND 10
```
练习
1. 编写一个查询，返回所有订单，其中 standard_qty 超过 1000，poster_qty 是 0，gloss_qty 也是 0。
```
SELECT *
FROM orders
WHERE standard_qty > 1000 AND poster_qty = 0 AND gloss_qty = 0;
```
2. 使用客户表查找所有不以 'C' 开始但以 's' 结尾的公司名。
```
SELECT name
FROM accounts
WHERE name NOT LIKE 'C%' AND name LIKE '%s';
```
3. 使用 web_events 表查找通过 organic 或 adwords 联系，并在 2016 年的任何时间开通帐户的个人全部信息，并按照从最新到最旧的顺序排列。
```
SELECT *
FROM web_events
WHERE channel IN ('organic', 'adwords') AND occurred_at BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY occurred_at DESC;
```
与 AND 运算符类似，OR 运算符可以组合多个语句。使用 OR 连接新的语句时，需要指定感兴趣的列。可以同时连接尽可能多的考虑语句。这个运算符可用于我们迄今为止所学习到的所有运算，包括算术运算符（+、*、-、/），可以使用 OR 运算符将 LIKE、IN、NOT、AND 和 BETWEEN逻辑连接到一起。

将多个运算组合到一起时，可能经常需要使用括号来确保我们要执行的逻辑能得到正确执行。

练习
1. 查找订单 (orders) id 的列表，其中 gloss_qty 或 poster_qty 大于 4000。只在结果表中包含 id 字段。
```
SELECT id
FROM orders
WHERE gloss_qty > 4000 OR poster_qty > 4000;
```
2. 编写一个查询，返回订单 (orders) 的列表，其中标准数量 (standard_qty)为零，光泽度 (gloss_qty) 或海报数量 (poster_qty)超过 1000。
```
SELECT *
FROM orders
WHERE standard_qty = 0 AND (gloss_qty > 1000 OR poster_qty > 1000);
```
3. 查找以 'C' 或 'W' 开头的所有公司名 (company names)，主要联系人 (primary contact) 包含 'ana' 或 'Ana'，但不包含 'eana'。
```
SELECT *
FROM accounts
WHERE (name LIKE 'C%' OR name LIKE 'W%') 
           AND ((primary_poc LIKE '%ana%' OR primary_poc LIKE '%Ana%') 
           AND primary_poc NOT LIKE '%eana%');
```
总结

语句	使用方法	其他详细信息
SELECT	SELECT Col1, Col2, ...	提供需要的列
FROM	FROM Table	提供列所在的表格
LIMIT	LIMIT 10	限制返回的行数
ORDER BY	ORDER BY Col	根据列命令表格。与 DESC 一起使用。
WHERE	WHERE Col > 5	用于过滤结果的一个条件语句
LIKE	WHERE Col LIKE '%me%'	仅提取出列文本中具有 'me' 的行
IN	WHERE Col IN ('Y', 'N')	仅过滤行对应的列为 'Y' 或 'N'
NOT	WHERE Col NOT IN ('Y', "N')	NOT 经常与 LIKE 和 IN 一起使用。
AND	WHERE Col1 > 5 AND Col2 < 3	过滤两个或多个条件必须为真的行
OR	WHERE Col1 > 5 OR Col2 < 3	过滤一个条件必须为真的行
BETWEEN	WHERE Col BETWEEN 3 AND 5	一般情况下，语法比使用 AND 简单一些

SELECT col1, col2
FROM table1
WHERE col3  > 5 AND col4 LIKE '%os%'
ORDER BY col5
LIMIT 10;

移动平均值
移动平均值用于将数据线性化，以便更容易观察长期趋势，也不会因日常波动而错乱。例如，假设想可视化服装零售店的销售趋势。从每天的数据开始，而图表看起来太不稳定，因为更多的人在周末购物，所以销售额会在周末飙升。
可以按周计算销售额，但这可能不能显示想要的一些详细信息。
使用移动平均值可使每日波动平滑一些，也可以观察长期趋势。

Project : 探索未来气候发展趋势
注：学习笔记是在优达学城学习时，参考部分课程内容总结而成。
Project : 探索未来气候发展趋势-项目心得：
项目描述:用SQL编写查询，提取城市数据，并导出至csv文件，利用Excle对城市气候变化趋势进行可视化，最终围绕三个问题进行分析。
项目心得：通过本项目，了解了全球变暖下的气候发展背景，熟悉SQL基本查询语句，初步了解数据分析基本流程，在实际问题与数据分析相联系方面有了新的认识，对于数据分析更加感兴趣。

posted on 2018-10-21 22:37 python小白i 阅读(...) 评论(...) 编辑收藏

秒客网

数据分析学习笔记part_1

数据分析学习笔记part_1

数据分析

Lesson 1 : SQL初探

SQL和移动平均值

SQL简介

LIMIT

ORDER BY

WHERE 与非数字数据一起使用

算数运算符

逻辑运算符

总结

移动平均值

Project : 探索未来气候发展趋势

相关文章