用于数据科学的 SQL
SQL 是数据科学中最需要的技能之一。让我们使用 BigQuery 来了解它如何用于数据处理和机器学习。
(Source: https://wallpapercave.com/database-wallpapers)
介绍
SQL(结构化查询语言)是一种用于在关系数据库中查询和管理数据的编程语言。关系数据库由二维表的集合构成(例如数据集、Excel 电子表格)。然后，这些表中的每一个都由固定数量的列和任何可能数量的行组成。
举个例子，让我们考虑一下汽车制造商。每个汽车制造商可能有一个由许多不同的表组成的数据库(例如，每个不同的汽车模型都有一个表)。在这些表中的每一个中，将存储关于不同国家的每种汽车型号销售的不同指标。
与 Python 和 R 一起，SQL 现在被认为是数据科学中最需要的技能之一(图 1)。如今如此需要 SQL 的一些原因是:
- 每天大约产生 2.5 万亿字节的数据。为了存储如此大量的数据，利用数据库是绝对必要的。
- 公司现在越来越重视数据的价值。例如，数据可以用于:分析和解决业务问题，预测市场趋势，了解客户需求。
使用 SQL 的主要优势之一是，当对数据执行操作时，可以直接访问数据(无需事先复制)。这可以大大加快工作流的执行速度。
Figure 1: Most Requested Data Science Skills, June 2019 [1]
存在许多不同的 SQL 数据库，如:SQLite、MySQL、Postgres、Oracle 和 Microsoft SQL Server。
在本文中，我将向您介绍如何使用 Google big query ka ggle integration 免费开始使用 SQL。从数据科学的角度来看，SQL 既可以用于预处理，也可以用于机器学习。本教程中使用的所有代码都将使用 Python 运行。
如 BigQuery 文档中所述:
BigQuery 是一个企业数据仓库，通过使用 Google 基础设施的处理能力实现超快速的 SQL 查询来解决问题。只需将您的数据转移到 BigQuery 中，让我们来处理困难的工作。
— BigQuery 文档[2]
MySQL 入门
当使用 Kaggle 内核(嵌入在 Kaggle 系统中的 Jupyter 笔记本的在线版本)时，可以选择启用 Google BigQuery(图 2)。事实上，Kaggle 为每个用户每月提供高达 5TB 的免费 BigQuery 服务(如果你用完了每月的限额，你必须等到下个月)。
为了使用 BigQuery ML，我们首先需要在我们的 Google 服务上创建一个免费的 Google 云平台账户和一个项目实例。你可以在这里找到关于如何在几分钟内开始的指南。
Figure 2: Enabling BigQuery on Kaggle Kernels
一旦在谷歌账户平台上创建了一个 BigQuery 项目，我们就会得到一个项目 ID。我们现在可以将 Kaggle 内核与 BigQuery 连接起来，只需运行下面几行代码。
在这个演示中，我将使用 OpenAQ 数据集(图 3)。该数据集包含有关世界各地空气质量数据的信息。
Figure 3: OpenAQ Dataset
数据预处理
我现在将向您展示一些基本的 SQL 查询，它们可用于预处理我们的数据。
让我们先来看看一个国家有多少个不同的城市进行了空气质量测量。我们只需在 SQL 中选择 Country 列，并在 location 列中计算所有不同的位置。最后，我们将结果按国家分组，并按降序排列。
前十个结果如图 4 所示。
Figure 4: Number of measure stations in each country
之后，我们可以尝试检查该值的一些统计特征，并在小时列中取平均值，仅将 g/m 作为单位。通过这种方式，我们可以快速检查是否有任何异常。
“数值”栏代表污染物的最新测量值，而“小时平均值”栏代表该值的平均小时数。
Figure 5: Value and Averaged Over In Hours columns statistical summary
最后，为了总结我们的简要分析，我们可以计算每个不同国家臭氧气体的平均值，并使用 Matplotlib 创建一个条形图来总结我们的结果(图 6)。
Figure 6: Average value of Ozone in each different country
机器学习
此外，谷歌云还提供了另一项名为 BigQuery ML 的服务，专门用于直接使用 SQL 查询来执行机器学习任务。
BigQuery ML 使用户能够使用标准的 SQL 查询在 BigQuery 中创建和执行机器学习模型。BigQuery ML 通过让 SQL 从业者使用现有的 SQL 工具和技能来构建模型，使机器学习民主化。BigQuery ML 通过消除移动数据的需要提高了开发速度。
— BigQuery ML 文档[3]
使用 BigQuery ML 可以带来几个好处，例如:我们不必在本地内存中读取我们的数据，我们不需要使用多种编程语言，我们的模型可以在训练后直接提供。
BigQuery ML 支持的一些机器学习模型有:
- 线性回归。
- 逻辑回归。
- k-表示集群。
- 预训练张量流模型的导入。
首先，我们需要导入所有需要的依赖项。在这种情况下，我还决定将 BigQuery magic command 集成到我们的笔记本中，以使我们的代码更具可读性。
我们现在可以创建我们的机器学习模型。对于这个例子，我决定使用逻辑回归(只对前 800 个样本进行回归，以减少内存消耗)，根据纬度、经度和污染程度来预测国家名称。
一旦训练了我们的模型，我们就可以使用下面的命令来查看训练总结(图 7)。
Figure 7: Logistic Regression Training Summary
最后，我们可以使用 BigQuery ML 来评估模型性能的准确性。EVALUETE 函数(图 8)。
Figure 8: BigQuery ML model evaluation
结论
这是一个关于如何开始使用 SQL 解决数据科学问题的简单介绍，如果你有兴趣了解更多关于 SQL 的知识，我强烈建议你遵循 Kaggle 介绍 SQL 和 SQLBolt 免费课程。相反，如果你正在寻找更实际的例子，这些可以在这个我的 GitHub 库中找到。
希望您喜欢这篇文章，感谢您的阅读！
联系人
如果你想了解我最新的文章和项目，请在 Medium 上关注我，并订阅我的邮件列表。以下是我的一些联系人详细信息:
文献学
[1]如何成为更有市场的数据科学家，KDnuggets。访问网址:https://www . kdnugges . com/2019/08/market able-data-scientist . html
[2]什么是 BigQuery？谷歌云。访问地点:【https://cloud.google.com/bigquery/what-is-bigquery
[3]big query ML 简介。谷歌云。访问地点:【https://cloud.google.com/bigquery-ml/docs/bigqueryml-intro
面向数据科学家的 Python 中的 SQL 介绍
在 Python 环境中使用 SQL 的数据科学家指南。
本文为数据科学家提供了基本 SQL 语句的概述，并解释了如何在 Python 中实例化 SQL 引擎并用于从数据库中查询数据。
作为一名使用 Python 的数据科学家，您经常需要从本地服务器或云(例如 AWS cloud)上托管的关系数据库中获取数据。有许多方法可以解决这个问题。例如，您可以在 Oracle 中查询您的数据，将文件保存为.csv文件，然后在 Python 中导入它。然而，最有效的方法是在 Python 中直接使用 SQL。SQL 和 Pandas 的结合将为您在 Python 中查询、处理和使用项目数据提供许多选择。
重要的事情先来！什么是 SQL？
SQL(也称为结构化查询语言)是一种编程语言，用于管理或查询存储在关系数据库管理系统(RDBMS)中的数据。SQL 一直是处理结构化数据的主要语言，其中数据库中的实体(例如表或表实体)是相关的(这就是为什么这些数据库被称为关系数据库)。处理这类数据还有其他选择，但 SQL 是业内最流行、最广泛使用的语言。
“SQL”怎么发音？
SQL 于 20 世纪 70 年代初由 IBM 开发，最初名为“ SEQUEL(结构化英语查询语言)”。后来，由于商标问题，该名称被改为 SQL(结构化查询语言)。然而，发音“see-qu-El”(/ˈsiːkwəl/)保留了下来，这也是大多数从业者所采用的发音。
如果你想得到这份工作，当你去面试的时候，确保你的发音是“see-qu-el ”!]
关系数据库看起来像什么？
Amazon Web Services 为关系数据库提供了最佳定义:
关系数据库是数据项之间具有预定义关系的集合。这些条目被组织成一组包含列和行的表。表用于保存关于要在数据库中表示的对象的信息。表中的每一列保存某种数据，一个字段存储属性的实际值。表中的行表示一个对象或实体的相关值的集合。一个表中的每一行都可以用一个被称为主键的唯一标识符来标记，多个表中的行可以使用外键进行关联**。可以用许多不同的方式访问这些数据，而无需重新组织数据库表本身。**
数据库可以有非常复杂的设计，有许多表，每个表有许多实体(列)和许多行。当不知道表之间的关系时，查询数据将非常困难，甚至是不可能的。 ERD (实体关系图)用于可视化这些关系，还显示每个表中的实体及其数据类型。您的数据库管理员应该能够为您提供数据库的 ERD。
A Sample ERD — https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/
SQL 在 Python 中是如何使用的？
在 Python 中使用 SQL 有很多种方法。为此，已经开发了多个可以利用的库。SQLite 和 MySQL 就是这些库的例子。
在本文中，我们将结合[sqlalchemy](https://www.sqlalchemy.org/) 库使用 Python Pandas。
创建 SQL 引擎
我们开始吧！我们需要先安装然后导入库。我们将使用这个库中的create_engine特性。
!pip install sqlalchemyimport pandas as pd
from sqlalchemy import create_engine
一旦库被导入，我们需要使用这个命令创建一个 SQL 引擎，它创建一个新的 class.Engine 实例。
engine = create_engine(*args, **kwargs)
第一个参数通常是以 URL 形式表示数据库方言和连接参数的字符串，可以写成:
dialect[+driver]://user:password@host/dbname[?key=value..]
其中dialect是数据库名称，如mysql、oracle、postgresql等。，以及driver 一个 DBAPI 的名字，如psycopg2、pyodbc、cx_oracle等。更多细节可以在 https://www.sqlalchemy.org/的找到。
使用 SQL 语句的数据查询
现在您已经连接到数据库，您可以提交数据查询。要使用sqlalchemy，您需要将您的 SQL 语句包装在一个容器中，将其发送到数据库，获取响应，然后将响应放入panda数据帧中。每个查询中必须存在的两个主要子句是SELECT和FROM。
SELECT允许你从一个表中选择列的子集(或全部)，FROM指定从哪个表中提取列。
例如，下面的代码片段将从table_1返回所有实体(列),将响应保存到 dataframe 中，并显示标题。
sql = """
SELECT *
FROM table_1
"""df = pd.read_sql_query(sql, engine)
df.head()
相反，您也可以使用以下代码从表中提取特定的列:
SELECT entity_1, entity_2, entity_3
FROM table_1
如果您正在处理多个表(在实际项目中肯定会这样)，您可能需要指定哪个表中的哪个实体，因为感兴趣的实体可能来自数据库中的不同表。我们将讨论如何查询多个表中的多个实体，但是这个例子是针对一个表中的实体的。在这种情况下，您可以在 SQL 语句中使用namespacing:
SELECT table_1.entity_1, table_1.entity_2
FROM table_1
出于简化或可读性的目的，还可以为每个实体名或表名指定别名:
SELECT t.entity_1 AS name, t.entity_2 AS id
FROM table_1 AS t
如果您想从一列中获得不同的行，您可以发送以下 SQL 语句:
SELECT DISTINCT entity_1
FROM table_1
如果您想按特定列(或多列)对数据进行排序，您可以使用ORDER BY并指定您想要的ASC(升序)或DESC(降序)顺序。请记住，如果您在ORDER BY中使用多列，SQL 排序数据的顺序将是从左到右。
SELECT entity_1, entity_2, entity_3
FROM table_1
ORDER BY entity_1 DESC, entity_3 ASC
有时您正在处理一个非常大的数据集，但是您可能只需要从数据库中检索一个有限的数据集。如果出现这种情况，可以使用LIMIT:
SELECT *
FROM table_1
LIMIT 10
如果你想在查询中包含一个条件，你可以使用WHERE.或者使用布尔条件，或者使用wildcards作为字符串实体。例如:
SELECT *
FROM table_1
WHERE entity_1 > 5
或者WHERE entity_1 BETWEEN 5 and 10
或者WHERE entity_1 > 5 AND entity_1 < 10.
通配符(或称通配符)是用来替换或表示单词中一个或多个字符的符号。大家比较熟悉的是 [*](https://www.computerhope.com/jargon/w/wildcard.htm) 用于 [zero or many characters](https://www.computerhope.com/jargon/w/wildcard.htm) 或者 [?](https://www.computerhope.com/jargon/w/wildcard.htm) 用于 [one character](https://www.computerhope.com/jargon/w/wildcard.htm).在 SQL 中使用LIKE语句查询字符串实体时，我们可以在 SQL 中有效地使用通配符。%和*的区别在于%也占下划线，而*没有。在 Python 中，你应该使用%%而不是一个%。以下语句返回entity_3以M.开始的所有行
SELECT *
FROM table_1
WHERE entity_3 LIKE "M%%"
ILIKE使该查询不区分字符大小写，并且NOT LIKE返回实体是而不是通配符的所有行。
要处理空值，您可以使用:
SELECT *
FROM table_1
WHERE entity_1 IS NULL
或者WHERE entity_1 IS NOT NULL.
通常，您需要聚合数据、对数据进行分组，并对聚合的数据应用条件。这些汇总报表包括COUNT, AVG, MIN, MAX, and SUM.例如:
SELECT SUM(entity_1) AS sum, entity_2
FROM table_1
使用聚合时，应该使用HAVING而不是WHERE,，比如:
SELECT SUM(entity_1) AS sum, entity_2
FROM table_1
HAVING entity_2 BETWEEN 5 and 10
要按特定实体对数据进行分组，您可以使用GROUP BY:
SELECT entity_1, SUM(entity_2) AS sum
FROM table_1
GROUP BY entity_1
HAVING entity_3 BETWEEN 5 and 10
连接表格
当查询多个表中的数据时，需要连接这些表。在 SQL 中有多种连接表的方法。下图说明了这些连接。您可能会更频繁地使用内部联接，但是理解每种类型的联接的作用很重要。
Different Types of Table Joins in SQL — https://www.dofactory.com/sql/join
只有当两个表之间有一个公共实体时，才能连接表，您需要使用ON来定义这种关系。
SELECT t_1.entity_1, t_2.entity_2
FROM table_1 AS t_1
INNER JOIN table_2 AS t_2 ON t_2.key = t_1.key
这些语句涵盖了 Python 中 SQL 的基础知识。您可以根据您正在处理的数据库、您需要的数据类型来组合这些语句。你可以使用更多的语句。https://www.w3schools.com/sql/对 SQL 语句提供了更全面的概述。
Nick Minaie，博士*(*LinkedIn 简介 )是一位高级顾问和富有远见的数据科学家，代表了领导技能、世界级数据科学专业知识、商业敏锐度和领导组织变革能力的独特组合。他的使命是推进人工智能(AI)和机器学习在行业中的实践。
SQL 连接
一次一个联接地将数据汇集在一起
感谢周润发巧妙的字幕！
Photo by Marc Sendra Martorell on Unsplash
我在这个博客中使用的代码可以在我的 GitHub 上找到。
SQL 有很多东西要学，尤其是对我来说。我花了最长时间理解的事情之一是JOIN陈述。一开始这很吓人，但是经过阅读和大量的练习，我掌握了窍门，我希望在这篇博客结束时，你也会有同样的感觉！
在这篇博客中，我们将讨论四种不同类型的连接:
- 内部联接(或联接)
- 左连接(或左外连接)
- 右连接(或右外连接)
- 完全连接
安装
对于这些例子，我们将使用好的 ol’ Chinook 数据库，它也用于 SQL 基础博客和我的大多数其他 SQL 相关博客。
让我们设置数据库文件、库和函数:
The chinook database for visualization purposes
数据库已经加载，现在我们准备好处理连接了！
内部联接/联接
The INNER JOIN keyword
****INNER JOIN关键字选择两个表中满足条件的所有行。该关键字创建一个新的集合，该集合组合了两个表中公共字段值相同的所有行。
让我们用一个例子来看看这是如何工作的:
**SELECT a.name, a.composer, b.name
FROM tracks AS a
INNER JOIN genres AS b
ON a.genreid = b.genreid
WHERE b.name LIKE 'b%'
LIMIT 10;**
在将tracks表设置为变量名a并将genres表设置为变量名b、后，这两个表通过一个公共键链接起来，这个公共键就是两个表中都存在的genreid键。然后，它从tracks表中找到歌曲名称和作曲家，然后从genres表中找到相应的流派名称。
然后，它将结果限制为歌曲名称以字母“b”开头的歌曲，并进一步将结果限制为前 10 个。最终输出将返回 10 首蓝调风格的歌曲，包括歌曲名和作曲家名。
现在我们可以更好地解释INNER JOIN关键字中发生的事情，让我们继续讨论LEFT JOIN关键字！
左连接/左外连接
The LEFT JOIN keyword
LEFT JOIN关键字 **返回连接左侧表的所有行和连接右侧表的匹配行。**如果左侧的行与右侧的行不匹配，空值将填充右侧的空间。**
**SELECT a.trackid, a.name, a.composer, b.invoicelineid, b.invoiceid
FROM tracks AS a
LEFT JOIN invoice_items AS b
ON a.trackid = b.trackid;**
在上面的例子中，查询将tracks表设置为变量名a，将invoice_items表设置为变量名b。它通过公共键trackid将它们链接起来，并返回 tracks 表中的 trackid、name 和 composer，以及 invoice_items 表中的 invoice line ID 和 invoice ID。
在查看数据时，您可能会想:“空值去了哪里？”。如果你没有，我们将向你展示如何找到其中的一些！
**SELECT a.trackid, a.name, a.composer, b.invoicelineid, b.invoiceid
FROM tracks AS a
LEFT JOIN invoice_items AS b
ON a.trackid = b.trackid
**WHERE b.invoiceid IS NULL;****
IS NULL子句对于查找列中的任何空值非常有用。将这一行添加到原始示例中，我们发现许多行在原始的invoice_items表中没有值，而是被替换为空值。
右连接/右外部连接
The RIGHT JOIN keyword
**正如你所料，RIGHT JOIN与LEFT JOIN相似。该连接返回连接右侧的表的所有行以及连接左侧的表的匹配行。并且，对于左侧现在有匹配行的任何行，这些值被替换为空值。
在大多数查询中，RIGHT JOIN看起来像这样:
***SELECT a.trackid, a.name, a.composer, b.invoicelineid, b.invoiceid
FROM tracks AS a
**RIGHT JOIN** invoice_items AS b
ON a.trackid = b.trackid;***
请注意，该设置看起来几乎与LEFT JOIN示例中的示例一模一样，只是现在显示为RIGHT JOIN。
现在，对于那些使用不支持 RIGHT JOIN 的 SQL 程序的人来说， RIGHT JOIN 关键字本质上是一个翻转的 LEFT JOIN。以下是该变通办法的效果:
***SELECT b.trackid, b.name, b.composer, a.invoicelineid, a.invoiceid
FROM invoice_items AS a
**LEFT JOIN** tracks AS b
ON a.trackid = b.trackid;***
它对RIGHT JOIN关键字进行了细微的调整，但是它将输出与RIGHT JOIN示例相同的内容。
该查询的输出将显示实际订购歌曲的发票。任何发票中未订购的任何歌曲都不会显示在该查询中。
现在我们已经有了这些例子，让我们继续进行FULL JOIN！
完全连接/完全外部连接
The FULL JOIN keyword
FULL JOIN关键字通过组合 LEFT JOIN 和 RIGHT JOIN的结果来创建结果。对于任何不匹配的行，结果将包含空值。
该关键字很少使用，但可用于查找两个表之间的重复行、缺失行或相似行。
如果您的 SQL 程序支持，您的查询看起来会非常简单:
***SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
**FULL JOIN** table2
ON table1.matching_column = table2.matching_column;***
知道FULL JOIN本质上是一个LEFT JOIN和一个RIGHT JOIN的组合是很有用的。假设我们的 SQL 程序既不支持FULL JOIN也不支持RIGHT JOIN，我们的查询应该是这样的:
***SELECT *
FROM invoice_items AS a
**LEFT JOIN** tracks AS b
ON a.trackid = b.trackid
**UNION**
SELECT *
FROM tracks AS a
**LEFT JOIN** invoice_items AS b
ON a.trackid =b.trackid
**WHERE a.trackid IS NULL;*****
在这个例子中，有三件主要的事情在进行:
- 第一部
LEFT JOIN - 第二个
LEFT JOIN(伪RIGHT JOIN) - 将两者合并在一起的
UNION
IS NULL行确保重复的行不会被输入到结果集中，这对获得可读的结果非常有用。
结果将输出 2240 行数据，包括发票数据和单首歌曲数据。
结论
唷！有很多问题，我很感谢你在这里逗留了这么久！
总的来说，下面是每个 JOIN 关键字的作用:
- 内部连接:从两个表中选择具有匹配公共键的所有行。
- 左连接:使用左侧表格中的所有行，并从右侧表格中寻找匹配的行。
- 右连接:使用右侧表格中的所有行，并从左侧表格中找到匹配的行。
- 完全连接:合并两个表中的所有行。
希望这有助于了解如何用你的数据库创建连接查询，我希望你会在下一篇文章中 加入 我！
SQL 连接
用 pandas 数据框架展示不同类型的 SQL 连接
如果你以前从未使用过 SQL，它可能会有点吓人。您会听到人们谈论表之间的复杂连接，但不知道他们在谈论什么。在过去的几年里，我已经教了很多人如何编写 SQL 查询，我想我可以在互联网上与大众分享一些知识。
首先，什么是 join？
在关系数据库中，如果表共享一个键，它们可以相互连接。主键是一个不能在一个表中重复的值。这意味着一个值在主键列中只能出现一次。该键可以作为外键存在于另一个创建关系的表中。外键在一个表中可以有重复的实例。
ERD example for relationships below
表关系
表之间的关系类型有一对一、一对多和多对多。
一对一类似于拥有一张顾客表和一张 SSN 表。一个客户只能有一个 SSN，一个 SSN 只能分配给一个人。Customer 表上的主键是 CustomerId，它是 SSN 表上的外键。
一对多就像有一个客户表和一个订单表。一个客户可以有多个订单，但是这些订单只能与一个客户相关联。在本例中，主键和外键是相同的。
多对多看起来就像有一个订单表和一个产品表。一个订单可以包含许多产品，一个产品可以包含在许多订单中。这些关系通常会有另一个表，您需要使用该表将主 Order 表和 Product 表连接在一起。您将在上面的图表中看到，该表名为 Product_Order。ProductId 和 OrderId 都是 Product_Order 表上的外键，但它们是各自表中的主键。
连接的类型
有 4 种类型的联接:内联接、左联接、右联接和全外联接。我将使用上面客户和订单表的一对多示例，通过一个非常简单的 pandas 数据框架向您展示这些内容，但是我还将展示 SQL 语法。
Using python pandas to create dataframes
Customer table
Order table
提醒一下，CustomerId 是 Customer 表的主键，也是 Order 表上的外键。OrderId 是订单表上的主键。
内部连接
内部联接将返回两个表中都存在所联接字段的值的行。
Image from dofactory
在 SQL 中，您可以使用 JOIN，因为它默认为内部连接。或者可以包含内部联接。
在 SQL 中，这可以写成:
SELECT *
FROM Customer
JOIN Order ON Customer.CustomerId = Order.CustomerId;
在 Pandas 中，你可以使用合并功能。
pd.merge(Customer, Order, how='inner', on='CustomerId')
Order 表中存在的唯一 CustomerId 值是[1，2，4]，因此在内部连接的结果中，CustomerId [3，5]不返回。
左连接
左连接将返回第一个表(在本例中为 Customer)中的所有行，并且只填充第二个表中存在键值的字段。它将在第二个表中不存在的地方返回 NULLs(或者 python 中的 NaNs)。
Image from dofactory
在 SQL 中，这将被写成:
SELECT *
FROM Customer
LEFT JOIN Order ON Customer.CustomerId = Order.CustomerId;
对于熊猫来说:
pd.merge(Customer, Order, how='left', on='CustomerId')
通过这个连接，我们可以看到所有的 customerid[1，2，3，4，5]，但是这些 customerid 有重复，因为它们有多个订单。CustomerId [3，5]没有任何订单，因此 OrderId 和 OrderDate 返回 NaN。
右连接
右连接将返回写入的第二个表中的所有行，在这个实例中排序，并且只填充存在键值的第一个表的字段。它将在第一个表中不存在的地方返回 NULLs(或者 python 中的 NaNs)。
Image from dofactory
在 SQL 中，这将被写成:
SELECT *
FROM Customer
RIGHT JOIN Order ON Customer.CustomerId = Order.CustomerId;
对于熊猫来说:
pd.merge(Customer, Order, how='right', on='CustomerId')
通过这个连接，我们可以看到 Order 表中的所有订单，但是由于 CustomerId 6 在 Customer 表中不存在，所以我们得到了 Name、Address 和 Phone 的 NaN 值。
完全外部连接
您还会听到这种称为外部连接(没有完整连接)的方法，因为您不必在编写的 SQL 中包含完整连接。
这将返回所有行，无论您要联接的字段中的值是否存在于两个表中。如果该值在另一个表中不存在，则该表的字段将返回空值。
Image from dofactory
在 SQL 中，这将被写成:
SELECT *
FROM Customer
OUTER JOIN Order ON a.col1 = b.col1;
对于熊猫来说:
pd.merge(Customer, Order, how='outer', on='CustomerId')
该联接的结果显示所有 CustomerId 和所有 OrderId。其中 CustomerId 为[3，5]，订单字段为 NaN，OrderId 为 6，客户字段为 NaN。
结论
在 SQL 和 Python 中，使用 pandas dataframes 用一个表中的一些数据补充另一个表中的数据，连接确实非常有用。在返回 NaN 或 NULL 值时必须小心，尤其是在向可能不允许 NULL 值的新表中插入数据时。理解要连接的表之间的关系也很重要，这样就不会返回不期望的结果。
我希望这些信息是有帮助的，我很乐意连接并回答您可能有关于 SQL 查询的任何问题！
注意:我在我写的 SQL 例子中使用了显式连接，但是有些人认为隐式连接也可以。根据我的经验，使用显式连接总是更安全，因为您确切地知道结果会是什么。隐式连接并不总是如你所愿。
SQL 还是 NoSQL？
NoSQL 数据库能做什么而关系数据库不能
NoSQL 数据库在现代数据体系结构中越来越受欢迎。它已经成为一种以特定格式存储数据的强大方法，这种格式可以快速处理大量数据。市场上已经有许多 NoSQL 数据库，而新的数据库仍在不断涌现。最流行的分类包括 4 种类型:宽列、文档、键值对和图形。在众多 NoSQL 数据库中，下面列出了几个受欢迎的数据库:
- 宽柱状:Cassandra、HBase、AWS DynamoDB
- 文档:Couchbase，MongoDB，Azure Cosmos DB，AWS DynamoDB
- 图形:Neo4J，Azure Cosmos DB，TigerGraph，AWS Neptune
- 键值对:AWS 迪纳摩，Redis，甲骨文 NoSQL
很明显，一个 NoSQL 数据库可以属于一个以上的类别。面对如此多的选择，这也给数据架构师和分析从业者带来了挑战，他们甚至在进行概念验证(POC)或最小可行产品(MVP)之前，就应该选择哪一个以及做出正确决策需要考虑哪些因素。我将这篇文章致力于深入 NoSQL 数据库的一般描述，解释 NoSQL 数据库如何实现关系数据库无法实现的性能。我希望这篇文章能够帮助读者做出正确的决定，首先决定您是否应该使用 NoSQL，如果是，哪些应该是考虑和 POC/MVP 测试的首选。
关系数据库和规范化
在评论 NoSQL 之前，我们应该对 SQL 和关系数据库有一个很好的理解。关系数据库是英国计算机科学家 Edgar F. Codd 在 1970 年发明的。他的发明导致了 RDBMS(关系数据库管理系统)数据库在随后三十年的成功，包括 IBM DB2、Oracle、Sybase、SQL Server、MySQL 和 PostReg SQL。大约在同一时间，SQL(结构化查询语言)开始活跃起来，成为查询和维护关系数据库的通用语言。
关系理论引入了规范化的概念，旨在最小化数据冗余和解决数据插入/更新/删除异常，如 Codd 在他的原始论文“数据库关系模型的进一步规范化”中所示:
- 将关系集合从不期望的插入、更新和删除依赖关系中释放出来。
- 随着新的数据类型的引入，减少对关系集合进行重组的需要，从而增加应用程序的寿命。
- 使关系模型向用户提供更多信息。
- 使关系的收集与查询统计无关，其中这些统计易于随着时间的推移而改变。
换句话说，目标是使用相关的“数据集合”通过准确的插入、更新和删除来有效地存储信息。为了实现这些目标，Codd 定义了三种规范化，这是每个关系数据库都应该满足的原则(尽管后来发布了更多的规范化形式，但它们在现实世界中并不常见)。简而言之，在设计关系数据库时，这三种范式会导致以下关键方面:
**第一范式:**数据库应该由表组成；每个表格都有行和列；每行应该有相同数量的列。
- 每一列都应该是相同的类型，并且不能有两列同名。
- 表中的每一行都应该是唯一的，由一列或多列强制执行，称为“主键”。给定主键，行的顺序无关紧要。
- 列和行相交处的每个单元格应该由一个值组成。
**第二和第三范式:**表中的所有非键列都应该依赖且仅依赖于主键:
- 如果有多个构成主键的列，则非键列应该依赖于所有这些列，而不仅仅是主键的一部分(第二范式)
- 非键列不应依赖于不在主键中的任何列(第三范式)
为了实现上述规则，数据被存储在独立逻辑实体的多个表中，其关系由引用另一个表中的主键的“外键”来实施。关系模型导致以最少的数据冗余存储数据的最有效的方式，并使其在表级别非常可扩展，只要明确定义关系并严格遵循三种范式。这些关系还使得一条信息的插入/更新/删除仅发生在一个地方，这消除了潜在的数据不一致问题，同时利用这些关系来维护数据的完整性和准确性，而没有任何信息丢失。最后，SQL 语言成为一种强大的数据库语言，可以从关系数据库中检索任何信息。
NoSQL 数据库:与 3 种范式相对
当 Elgar 发明关系模型时，他可能没有想到一个关系数据库可以分成多少个表，以及当人们试图连接大量的表时会面临什么样的性能问题。一般来说，关系数据库有两个最大的缺点，今天的 NoSQL 数据库试图克服:
刚性增加列和属性时，典型表现在两个方面:
- 对于非结构化或半结构化数据，通常需要动态添加属性，虽然只适用于特定的行，但不是每一行。此外，值可以是不同的类型，很难作为原子值来维护。
- 将表和列分解成关系的需要引入了很长的开发周期，包括数据库逻辑和物理设计、复杂查询的开发、测试和生产部署。
性能缓慢当需要连接多个表时，尤其是当一个或多个表存储大量数据时。例如:
- 当需要连接大型表时，缓慢的性能通常出现在读取中。
- 当需要一次更新多个相关表时，大容量数据加载会带来性能瓶颈。
因此，NoSQL 数据库被设计成在数据量大时提供添加信息的灵活性和快速性能，同时牺牲三种范式并容忍数据冗余和插入/更新/删除异常的风险。出于同样的原因，NoSQL 数据库最适合不适合关系数据库设计的半结构化或非结构化数据，以及需要很少约束来增强其准确性的数据场景。此外，大多数 NoSQL 数据库都是为快速读取性能而设计的，代价是降低了频繁更新、插入和删除的速度。
在市场上，有许多 NoSQL 数据库和不同的分类方式，如键值对、列类型、文档和图表。如果深入研究，大多数 NoSQL 数据库使用两种技术来绕过关系数据库:基于键值对的数据存储和通过水平伸缩实现的反规范化。
1。基于键值对的数据存储
克服关系表僵化的最常见技术是创建键值对，其中的值可以是任何数据类型，比如数组或文档。显然，这种结构完全抛弃了关系范式。JSON 格式本质上是一组以分层方式组织的键值对，这非常适合面向对象编程(OOP ),并且被大多数 API 使用。大多数 NoSQL 数据库使用键值对或 JSON 格式:
- 键值对:Cassandra、HBase、Redis、Amazon DynamoDB
- JSON:文档 NoSQL 数据库，如 Mango 和 CouchBase 图形 NoSQL 数据库，如 Neo4j 和 Azure Cosmo。
键值对为动态添加任何信息提供了极大的灵活性，无需考虑不同行或表之间的关系完整性。此外，键名和值存储在一起，这使得数据检索更有效。索引键后，可以快速检索值。当可能有许多列或字段时，使用键-值对，每个列或字段都被频繁添加，但只用于一小部分数据。该场景通常适用于从不同应用程序和系统收集的文档、调查数据、产品属性或日志数据。
这种结构的缺点很明显:
- 难以加强不同属性之间的关系
- 难以识别数据冗余
- 需要更多的存储空间，因为需要存储每个值的键名。
- 特定更新和删除的成本很高。
- 不适合大量扫描许多数据元素
2。通过水平缩放启用反规格化
提高 NoSQL 数据库性能的一种常见方法是“非规范化”表，即在一个表中存储所有可能的列或字段，这样在检索信息时就不需要连接多个表。在底层，现代 NoSQL 数据库以分布式方式将数据存储在不同服务器上的数据块或分区中，以实现水平扩展。与 Todd 的关系数据库目标完全相反，反规范化提高了数据库读取性能，同时带来了数据冗余和更新/插入/删除异常。在将数据加载到 NoSQL 数据库之前，应将其“连接”或反规范化。因此，它通常需要 ETL 过程来准备数据。对非规范化表的插入、更新和删除可能开销很大，并且还会导致数据不一致，因为无论列位于何处，都需要在多个位置更新该列。
此外，为了能够从“非规范化”的结构中快速检索，索引键列并以特定的顺序和本地化方式存储数据是所使用的基本技术，但是，这会使写入和加载花费更长的时间。数据结构的“非规范化”本质使得它通常只适合于一种具有一致检索模式的用例。对于不同的用例，应该构建不同的非规范化结构，这会导致数据重复，需要更多的数据存储。换句话说，这种类型的结构适合具有下面列出的三个特征的用例。
- 存储同质数据，通常用于一个应用程序
- 处理无限的大容量
- 一致的数据检索模式
对每个 NoSQL 数据库的合适用例的详细描述超出了本文的范围。然而，随着对 SQL 和 NoSQL 之间的基本差异的深入理解，我们应该能够在明确特定用例及其需求之后提出正确的问题并选择正确的问题。
结论
对于一个组织来说，关系数据库和 NoSQL 数据库都是需要的，这取决于不同的用例及需求。基于上述基本原理，您甚至可以在开始进行任何 POC、MVP 或实验之前就决定使用哪个数据库。一般来说，当数据量很大并且可以容忍数据冗余时，NoSQL 数据库适合某些类型的应用程序和选择性用例。另一方面，关系数据库。更加通用—它可以处理任何结构化数据，并保证插入/更新/删除的性能和数据质量。一些关系数据库可以有效地处理大数据，如亚马逊红移和雪花，通过利用大规模并行处理(MPP)，同时仍然支持 SQL。最终设计一个性能卓越的大数据系统是一项具有挑战性的任务。它需要完整的数据流，从数据源到数据的最终应用，并考虑大数据架构中的数据库和数据处理，以实现最佳性能(参见我的另外两篇文章:数据处理和数据访问中的大数据架构和大数据性能的设计原则)。
SQL Server 索引分析和优化
熟悉 SQL Server 数据库中索引分析和优化的关键方面
经常需要优化索引和统计数据，以便优化器更快地搜索必要的数据和更好地构建查询执行计划。
以下工作领域可以理解为索引优化:
- 缺失索引的创建
- 重叠索引的删除
- 删除未使用的索引
- 更改现有索引，以适应随时间变化的操作条件(键列的结构、包含列的结构以及索引本身的属性)
- 在随时间变化的操作条件下，删除那些维护成本明显大于其优化收益的指标
- 索引重组和重建。
SQL Server 索引分析
借助以下查询，可以分析数据库中的索引碎片级别:
将返回以下列:
- 数据库 —数据库名称
- 模式 —模式对象(表，视图)
- tb —索引所在的表/视图
- idx —索引 ID
- 数据库标识 —数据库标识
- 索引名称 —索引名称
- 指标类型 desc —指标类型描述
- object_id —索引所在的对象 id(表、视图)
- frag_num —分配单元最后一级的片段数
- frag —所有页面使用的可用磁盘空间的平均百分比
- frag_page —在分配单元 IN_ROW_DATA 的最后一级的一个片段中的平均页数
- 页 —索引页或数据的总数
这显示了大小不小于 1 个区(8 页)且碎片超过 10%的索引的碎片级别。它只适用于带有聚集索引的表，并且只考虑根索引。该查询使用两种系统视图:
- sys . DM _ db _ index _ physical _ stats—返回 SQL Server 中指定表或视图的数据和索引的大小和碎片信息。
- sys . indexes—包含表对象(如表、视图或表值函数)的每个索引或堆的一行。
接下来，让我们考虑何时推荐索引优化以及如何执行索引优化。
SQL Server 索引优化
在上一个查询中，有两个指标特别重要:
- frag —索引碎片的级别，以百分比表示
- 页面-索引总页数的大小
有不同的方法来解释索引碎片的级别和索引优化方法。本文将考虑其中之一。
在以下情况下，需要优化索引:
- 其大小超过 8 页，碎片化程度超过 30%。
- 它的大小超过 64 页，碎片程度超过 25%。
- 它的大小超过 1 000 页，碎片程度超过 20%。
- 它的大小超过 10 000 页，碎片程度超过 15%。
- 它的大小超过 100，00 0 页，碎片程度超过 10%。
有两种方法可用于索引优化:
1。 指标重组
索引重组需要最少的系统资源。在重组过程中，表和视图中的聚集索引和非聚集索引的叶级通过叶级页的物理重组进行碎片整理。因此，它们按照叶节点的逻辑顺序排列(从左到右)。此外，重组会压缩索引页。它们的压缩是根据填充因子的当前值执行的。
您可以借助以下命令执行索引重组:
ALTER INDEX < index_name> ON <schema>.<table> REORGANIZE;
2。索引重建
重建会删除旧索引并创建新索引。这消除了碎片，通过将页压缩到指定或现有的填充因子来恢复磁盘空间，对连续页中的索引行进行重新排序，并更新新的索引统计信息。
您可以借助以下命令执行索引重建:
ALTER INDEX < index_name> ON <schema>.<table> REBUILD;
如果您的 MS SQL Server 版本支持，索引重建可以在线完成:
ALTER INDEX <index_name> ON <schema>.<table> REBUILD WITH(ONLINE=ON);
更多关于改变索引命令的信息可以在这里找到。
有许多免费和收费的索引优化工具。例如，Sergey syrovacthenko正在开发一个相当强大且免费的工具用于优化索引。
该工具的优点如下:
- 获取碎片索引的优化算法
- 能够在一个流程中同时服务多个数据库
- 基于所选设置为索引自动选择操作
- 支持全局搜索和高级过滤，以实现更好的分析
- 许多关于索引的设置和有用信息
- 索引维护脚本的自动生成
- 支持堆和列索引维护
- 能够启用索引压缩和统计数据更新，而不是重建
- 支持 SQL Server 2008 和更高版本的所有版本，以及 Azure SQL 数据库。
关于该工具的详细讨论可以在这里找到。
统计分析和优化
让我们考虑一种确定过时统计数据的方法:
在给出的方法中，过时统计数据由以下指标确定:
- 如果数据发生了重大变化。
- 如果统计数据很长时间没有更新。
- 如果对象大小小于指定的最大值或未指定该最大值。
- 如果区段中的行数小于指定的最大值或未指定此最大值。
查询中使用了以下系统视图:
- sys . DM _ db _ partition _ stats—返回当前数据库所有部分的页数和行数信息。
- sys.objects —数据库对象。
- sys.stats —表、索引和索引视图的统计信息。
- 系统索引 —索引。
- sys . DM _ db _ stats _ properties—从当前 SQL Server 数据库返回指定数据库对象(表或索引视图)的统计属性。
- 【sys . stats _ columns】—sys . stats 统计数据中的每一列对应一行。
- sys.columns —所有带列对象的列。
- —数据类型。
使用以下命令可以进一步优化统计信息:
IF (EXISTS(SELECT TOP(1) 1 FROM sys.stats AS s WHERE s.[object_id]=<object_id> AND s.[stats_id]=<stats_id>))UPDATE STATISTICS <SchemaName>.<ObjectName> (<StatName>) WITH FULLSCAN;
关于更新统计命令的更多信息可在这里找到。
db forge Studio for SQL Server 中的索引分析和优化示例
在db forge Studio for SQL Server中，可以分析和优化索引碎片的级别。产品 dbForge 索引管理器也有这个功能。
在这个例子中，我们将考虑为 MS SQL Server DBMS 服务的 SRV 数据库。
这个 SRV 数据库是出于任何目的免费分发的。打开 studio 后，单击“管理”选项卡上的“管理索引碎片”按钮:
“Manage Index Fragmentation…” selection on the “Administration” tab.
在打开的窗口中，选择服务器并单击“选项”来配置设置:
Server selection and settings configuration.
在出现的选项窗口中，设定所需的参数:
Indexes optimization options.
通过点击左下角的按钮“保存命令行…”可以将设置结果保存为 bat 文件。你可以通过点击右下角的按钮“恢复默认值”来重置为默认设置。
接下来，单击“确定”。
现在选择您需要的数据库:
Database selection.
之后，分析就开始了。您也可以通过点击“重新分析”按钮来更新分析。
分析完成后，选择所需的优化索引:
Selection of indexes for optimization.
请注意，可以通过单击“导出到 CSV …”按钮将分析结果下载为 CSV 文件。
此外，可以通过多种方式进行优化:
- 在新的 studio 窗口中生成脚本(在“脚本更改”菜单中选择“到新的 SQL 窗口”)。
- 将脚本生成到剪贴板(在“脚本更改”菜单中选择“到剪贴板”)。
- 直接运行优化(单击“修复”按钮)。
让我们选择第三种方法—单击“修复”按钮。
优化过程完成后，您需要再次单击“重新分析”按钮:
Analysis after indexes optimization.
结论
对指标碎片化程度和随后优化的统计数据过时程度进行了分析。从上面的例子中还可以清楚地看到， dbForge 索引管理器工具允许您快速分析索引碎片的级别，并生成数据库索引优化脚本。
SQL 子查询
在查询中查询以获得更深入的见解
我在这篇博客中使用的代码可以在我的 GitHub 中找到。
每当我学习一些关于 SQL 的新知识时，我开始发现有很多我不知道的应用程序。在学习了查询的基本语法和一些基本工具之后，我开始学习 SQL 中的子查询。
当你执行多个步骤时，子查询(又名内部查询或嵌套查询)是非常有用的工具。这感觉像是盗梦空间，因为你在查询。
子查询可以在查询中的几个地方使用，所以今天我们将讨论在最常见的地方使用它们:SELECT、FROM和WHERE子句。
安装
对于这些例子，我们将使用 Chinook 数据库，它也在 SQL 基础博客中使用。
让我们设置数据库文件、库和函数:
The chinook database for visualization purposes
数据库已经加载，现在我们可以开始处理子查询了！
使用 FROM 语句的子查询
让我们从一个示例查询开始，并分析查询内部的情况。
SELECT thisisaname.*
FROM (
SELECT *
FROM genres
WHERE name LIKE 'r%'
) AS thisisaname
WHERE thisisaname.genreid > 6;
首先，数据库运行内部查询，或者括号内的部分:
SELECT *
FROM genres
WHERE name LIKE 'r%';
该查询进入genres表并提取名称以“r”开头的所有行。
您可能会注意到这个子查询可以独立存在，这一点非常重要:您的子查询必须独立运行。一旦运行，输出充当外部查询的底层表。
此外，子查询几乎总是应该有名字，这些名字加在括号后面。在本节中，我们将子查询命名为thisisaname。
子查询/内部查询运行后，外部查询采取行动:
SELECT thisisaname.*
FROM (**subquery**) thisisaname
WHERE thisisaname.genreid > 6;
这个查询现在看起来就像一个常规的基本查询！使用新的thisisaname表，外部查询现在搜索genreid大于 6 的所有数据。最终查询将输出两行:雷鬼和 R & B/Soul。
**补充说明:**在SELECT和WHERE语句中，由于这只是我们正在处理的一个表，我们不一定需要将thisisaname的名字添加到查询中，它仍然会输出相同的内容。使用它们仍然是一种很好的形式，当在多个表之间交互时使用JOIN语句会更有意义。
现在我们对WHERE语句中的子查询有了更好的理解，让我们看看SELECT语句中发生了什么！
使用 SELECT 语句的子查询
现在让我们看看当我们在SELECT语句中放置嵌套查询时我们能做什么。下面是一个可能的例子:
SELECT composer, COUNT(*) AS IndividualCount,
COUNT(*)*1.0/(SELECT COUNT(*)
FROM tracks) AS CountToTotalRatio
FROM tracks
WHERE composer <> 'none'
GROUP BY composer
HAVING IndividualCount > 30;
类似于上面的例子，我们将从内部查询开始，然后向外移动。
SELECT COUNT(*)
FROM tracks;
该子查询将对 tracks 数据集中的所有行进行完整计数，总共 3503 行。
SELECT composer, COUNT(*) AS IndividualCount,
COUNT(*)*1.0/(**subquery**) AS CountToTotalRatio
FROM tracks
WHERE composer <> 'none'
GROUP BY composer
HAVING IndividualCount > 30;
外部查询中的COUNT函数与子查询中的函数不同。外部查询中的COUNT使用GROUP BY子句计算每个作曲家的频率。它还只收集计数超过 30 的行，并忽略任何缺少编写器名称的行。
最终的输出将给出四行，包括作曲者姓名、每个作曲者的总数以及每个作曲者的频率(与 3503 的总表数相比)。
希望我解释得很好，有意义。最后，让我们看看WHERE语句中的子查询！
使用 WHERE 语句的子查询
像上一个例子一样，让我们从完整的查询开始:
SELECT *
FROM employees
WHERE hiredate IN (SELECT hiredate
FROM employees
ORDER BY hiredate
LIMIT 5
);
和往常一样，我们从内部查询开始:
SELECT hiredate
FROM employees
ORDER BY hiredate
LIMIT 5;
该查询按升序(默认情况下)对所有雇佣日期进行排序，并选取最早的 5 个雇佣日期。现在，让我们回到最初的查询！
SELECT *
FROM employees
WHERE hiredate IN (subquery);
外部查询现在搜索 employees 表中的所有行，并且只返回雇用日期在子查询中找到的 5 个雇用日期之内的行。这返回 6 个员工，公司里最早的一些！
结论
我真的很感激你坚持到最后，我希望你能从中找到一些有用的东西。请继续关注即将发布的另一篇 SQL 博文！
SQL 带有负索引的子字符串
在这篇文章中，我讨论了 SQL 中一个非常重要的字符串相关操作——SUBSTR 和负索引的应用。
SUBSTR 用于从给定位置提取给定字符串的特定部分。我已经使用 SUBSTR 的 SQLite 实现来说明负索引的使用。
我已经使用 movielens 数据库编写了 SUBSTR 的各种查询。这个数据库有四个表— 链接，电影，收视率，标签。我们对电影表感兴趣。该表的前 20 行如下
Table 1: movies table
现在我们将详细介绍 SUBSTR 操作符及其参数。SUBSTR 接受三个参数，如表 1 所示。最后一个参数— Z ，是可选的。
Figure 2: SQLite — SUBTSR
Y 和 Z 参数都可以取正负指标值。此外， Z 是可选的，因此我们有以下六种可能性
╔══════════╦══════════╗
║ Y ║ Z ║
╠══════════╬══════════╣
║ Positive ║ - ║
║ Negative ║ - ║
║ Positive ║ Positive ║
║ Positive ║ Negative ║
║ Negative ║ Positive ║
║ Negative ║ Negative ║
╚══════════╩══════════╝
现在，我们将为上述六种可能性中的每一种编写查询。在此之前，我们应该知道 SUBSTR 中的负索引是如何工作的。如图 3 所示，正索引从字符串中最左边的数字 1 开始，负索引从最右边的数字-1 开始。
Figure 3: SUBTSR — Positive and Negative Indexing
下表包含如何评估六种组合 Y 和 Z 的详细信息。
Table 2: SUBTSR — Combination of Y and Z with examples
现在，我们将执行一些查询，在这些查询中，我们可以使用来获得所需的结果。
查询 1:编写一个查询，从电影标题中删除电影发行年份，只显示标题。
SELECT TRIM(SUBSTR(TRIM(title), -6, (SELECT 6-MAX(LENGTH(title))
FROM movies)))
FROM movies
在查询 1 中，我们为 X，Y，Z1 设置了以下值。 X = TRIM(title)
我们已经修剪了电影表格的标题列，以消除电影标题两端有空格的任何可能性。2。 Y = -6
为什么-6？因为我们对所有的电影片名都有以下结构——<片名> <空间> ( <四位数电影上映年份> ) 。我们有最后 6 个字符作为‘(‘, ‘<digit>’, ‘<digit>’, ‘<digit>’, ‘<digit>’, ‘)’。
3.由于标题的长度不同，我们必须确保每个标题都完整地出现在结果中。我们已经处理了具有最大标题长度的情况，它将适用于其他情况。我们可以将此操作分解为以下步骤—
首先是，计算所有电影标题的长度— LENGTH(title)。它产生一个包含每个电影标题长度的表格。
第二个，计算最大长度— MAX(LENGTH(title))。这导致标题的最大长度为 158。
第三个，6-MAX(LENGTH(title))结果为 6–158 =-152。
因此我们有 Z as -152。这里，Y 和 Z 都有负值。从第-6 个字符开始读取，即**‘(’，)，然后读取-152 的绝对值，即 152，即 ‘(’ 之前的字符，不包括 ‘(’ 字符。由于 152 是电影表中标题的最大长度，它将提取所有电影的标题。以为例，《吉米·好莱坞(1994)》**就会产生‘**Jimmy Hollywood** ’和《修整后》‘**Jimmy Hollywood**’。
查询 2:编写一个查询，从电影标题中删除标题，只显示年份。
同样，我们可以从标题列中提取电影上映年份。
SELECT TRIM(SUBSTR(TRIM(title), -5, 4))
FROM movies
在此查询中，提取电影发行年份从电影发行年份的第一个数字-5 字符开始，然后提取其后的 4 个字符，包括-5 字符。
现在我们可以利用负索引的力量来执行一些特定的操作。例如，我们可以根据电影发行年份的查询 2 的结果进行分组，并可以获得每年发行的电影数量。
查询 3:写一个查询，找出电影《碟中谍》的上映年份。
SELECT TRIM(SUBSTR(TRIM(title), -5, 4))
FROM movies
WHERE title LIKE "Mission: Impossible%"
最后，我想强调的一点是，可以使用正则表达式以更加优雅和高效的方式执行上述操作，但在本文中，我尝试展示了 SUBSTR 中负索引的应用。
SQL 与 no SQL:ETL 应用的两种方法
Photo by Franck V. on Unsplash
在本文中，我将探讨 SQL 和 noSQL ETL 管道的区别。
本文将关注传输和加载技术->一旦数据被加载到应用程序中，那么会发生什么。
提取部分很简单。它涉及到读取文件和一些基本的数据争论。通过比较提取后数据集是如何划分的，您可以看到数据库的选择如何影响应用程序架构。您还可以确定选择特定数据库的优势和劣势。
我将首先讨论 SQL 方法。SQL 方法更加灵活。由于数据被划分为不同的单元，如果表可以以合理的方式连接起来，就更容易开发灵活的查询。SQL 方法的伸缩性不太好，因为设置水平 SQL 集群的效率不高。
noSQL 方法提供了显著的可伸缩性优势。查询速度更快，因为它们不涉及连接多个表。此外，根据 noSQL，数据库数据可以跨水平集群复制。尽管 noSQL 数据库需要一些先验知识或假设，但是当您对数据建模以适应特定查询时，您的应用程序将如何被使用。
一旦比较完成，我将讨论如何一起使用 SQL 和 noSQL 数据库。
SQL 方法
SQL 项目的目标是将 json 文件中的数据加载到下面的模型中。
图式:
数据集:
Extract of file regarding artists and songs:{
"num_songs": 1,
"artist_id": "ARD7TVE1187B99BFB1",
"artist_latitude": null,
"artist_longitude": null,
"artist_location": "California - LA",
"artist_name": "Casual",
"song_id": "SOMZWCG12A8C13C480",
"title": "I Didn't Mean To",
"duration": 218.93179,
"year": 0
}Example of file regarding songplays by application users:{
"artist": "N.E.R.D. FEATURING MALICE",
"auth": "Logged In",
"firstName": "Jayden",
"gender": "M",
"itemInSession": 0,
"lastName": "Fox",
"length": 288.9922,
"level": "free",
"location": "New Orleans-Metairie, LA",
"method": "PUT",
"page": "NextSong",
"registration": 1541033612796,
"sessionId": 184,
"song": "Am I High (Feat. Malice)",
"status": 200,
"ts": 1541121934796,
"userAgent": "\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"",
"userId": "101"
}
方法:
第一步是确定如何从数据中填充表。下面是如何选择歌曲和日志文件中的数据并对其进行重复数据消除以满足要求的高级概述。
同样值得注意的是，数据中存在明显的依赖关系，这要求应用程序以某种方式构建。
艺人和歌曲之间是一对多的关系(一个艺人可以有多首歌)。
因此，首先创建艺术家表，然后通过将特定歌曲的艺术家 id 加入特定艺术家的艺术家 id 来填充歌曲表。
query['artist_insert'] = 'insert into d_artist (artist_id, artist_name, artist_location, artist_longitude, artist_latitude) values (%s, %s, %s, %s, %s) on conflict (artist_id, artist_name) do nothing'query['song_insert'] = 'insert into d_song (song_id, song_name, year, length, artist_key) values (%s, %s, %s, %s, (select artist_key from d_artist where artist_name=%s)) on conflict (song_name, song_id) do nothing'
对于日志数据集，选择关于用户的数据，并将其直接插入到数据库中。
推断时间戳表的所有数据更加复杂。时间戳表需要将时间戳整数转换成一系列字符串、布尔值和整数，以提供每首歌曲播放时间的可读记录。
这包括:
- 迭代时间戳列表
- 将每个时间戳转换成可以映射到 d_timestamp 模式的列表
- 确定歌曲播放是否发生在周末
- 将列表转换为元组，以便可以写入数据库
def unpack_timestamp(*row*): new_row = list(
datetime.fromtimestamp(int(row[0] // 1000)).timetuple()[0: 7]
) new_row[-1] = new_row[-1] > 5 new_row.extend(row) *return* tuple(new_row)
一旦填充了时间戳表，就创建了提供上下文并丰富基于事务的歌曲播放表的所有维度表。
填充 song-play 表需要根据我前面定义的模式对表进行一些相对简单的连接。下面的查询用于迭代歌曲播放数据集，并从维度表中获取适当的主键，以便可以在进一步的查询中查找数据。
query['songplay_insert'] = 'insert into f_songplay (start_time, user_key, level, song_key, artist_key, session_id) values (%s, (select user_key from d_app_user where first_name = %s and last_name = %s), %s, (select song_key from d_song where song_name = %s and artist_key = (select artist_key from d_artist where artist_name = %s)),(select artist_key from d_artist where artist_name= %s),%s)'
该方法的优势:
这种方法创建了一个灵活的数据模型。由于数据被分成小的组成单元，查询数据相对简单。识别哪个男性用户在特定周末听了最多的歌曲的查询需要:
- 选择该时间段内的所有数据
- 按男性用户分组数据
- 按名字和姓氏对数据分组并选择最大计数
通过理解表之间的关系，对 SQL 有中级理解的人也可以实现类似复杂性的其他查询。因此，我们有一个可以回答各种业务问题的数据仓库。新的问题不一定需要创建新的表格或修改代码库。
哪里会分崩离析:
Photo by Jen Theodore on Unsplash
PostgreSQL 等关系数据库的伸缩性不如 noSQL。这是因为关系数据库不能容易地分布在服务器或容器集群中。
这是因为在高流量系统中，连接表和通过网络传送数据包会导致性能问题。复制也很难。例如，如果有一个涉及查询几个表的 update 语句，则必须在复制数据的每个节点上完成该事务。
如果数据集增加到 100GB+(甚至 20GB)，我创建的 ETL 可能会崩溃。
我可以做一些事情来提高性能。将 CSV 文件直接复制到数据库中并并行运行不相关的任务会提高应用程序的性能，尤其是当数据量开始增加时。然而，当查询单个服务器数据库时，应用程序将遇到瓶颈。
正是因为这些原因，一旦应用达到一定规模，往往首选 noSQL 数据库。
no SQL 方法
noSQL 方法包括将包含以下各列的 csv 文件加载到基于以下模式的三个表中。Cassandra 被用作 noSQL 数据库。
CSV 文件布局:
CSV Columns:artist,
auth,
firstName,
gender,
itemInSession,
lastName,
length,
level,
location,
method,
page,
registration,
sessionId,
song,
status,
ts,
userIdExample CSV Row:Stephen Lynch,
Logged In,
Jayden,
M,
0,
Bell,
182.85669,
free,
"Dallas-Fort Worth-Arlington, TX",
PUT,
NextSong,
1.54099E+12,829,
Jim Henson's Dead,
200,
1.54354E+12,
91
模式:
'user_and_song_model'
song = columns.Text(*primary_key*=True
firstName = columns.Text()
lastName = columns.Text()'user_and_session_model'
userId = columns.Integer(*primary_key*=True)
sessionId = columns.Integer(*primary_key*=True, *clustering_order*='DESC')
artist = columns.Text()
firstName = columns.Text()
gender = columns.Text()
itemInSession = columns.Integer()
lastName = columns.Text()
length = columns.Float()
level = columns.Text()
location = columns.Text()
song = columns.Text()__table_name__ = 'event_data_model'
sessionId = columns.Integer(*primary_key*=True)
itemInSession = columns.Integer(*primary_key*=True)
artist = columns.Text()
firstName = columns.Text()
gender = columns.Text()
lastName = columns.Text()
length = columns.Float()
level = columns.Text()
location = columns.Text()
song = columns.Text()
userId = columns.Integer()
第一个模型/表格记录了应用程序用户播放的歌曲。第二个和第三个表更详细，包括艺术家、sessionId 和其他有关用户活动的数据。
在 noSQL 方法中，表没有连接在一起。每个表代表一个特定的查询，或者需要解决的业务问题。
由于数据不需要连接，所以管道更简单。一旦数据被聚合和清理，每个表所需的数据集的列被选择并以可以直接读入数据库的格式写入磁盘。
aggregate_csv_path = os.getcwd() + '/event_datafile_new.csv'event_columns = ['sessionId', 'itemInSession', 'artist', 'firstName', 'gender', 'lastName', 'length', 'level', 'location', 'song', 'userId']event_frame[event_columns].to_csv(
*path_or_buf*=aggregate_csv_path, *index*=False
)subprocess.run(cqlsh_load_csv(), *shell*=True)def cqlsh_load_csv():
*return* (*return* ('cqlsh -e "COPY sparkifydb.event_data_model FROM ' + "'{}/event_datafile_new.csv' ".format(os.getcwd()) + 'WITH HEADER=TRUE"')
no SQL 方法的优势:
Photo by drmakete lab on Unsplash
noSQL 数据库比 SQL 数据库的伸缩性更好。由于查询不遍历多个表，因此它们可以更快地执行，因为它们不涉及跨磁盘的多次查找。
noSQL 数据库也允许水平伸缩。在 Cassandra 数据库中，建立了一个集群，并且在集群中对表进行复制。根据事务类型(读/写)，选择不同的节点。让一些节点负责读取而让其他节点负责写入会提高性能，并且有一种机制可以在成功的写入/更新/删除之后更新读取节点，以确保整个数据库的最终一致性。
更简单的查询和复制使 noSQL Cassandra 数据库能够处理比 PSQL 数据库多得多的流量。苹果、网飞、易贝和其他价值数十亿美元的公司使用 Cassandra 来管理万亿字节甚至千兆字节的数据，这一事实就说明了这一点！
no SQL 方法不灵活
noSQL 方法在可伸缩性方面提供了巨大的好处。但是在 noSQL 数据库上执行灵活的查询是很困难的，因为表不能被连接。比方说，每次会话播放的歌曲查询不提供商业价值。我们想知道早上最流行的歌曲。
使用 PSQL 数据库，我们可以通过连接时间戳表和歌曲播放表来识别最流行的早间歌曲。noSQL 模式目前还不支持这个需求。当使用 noSQL 数据库时，很有可能需要开发表和应用程序代码来回答那些可以由结构良好的关系数据库来回答的问题。
结论:
在本文中，我展示了如何围绕 SQL & noSQL 数据库构建应用程序和数据库软件。如果灵活性和执行复杂查询的需求是优先考虑的，那么 SQL 数据库更合适。
noSQL 数据库更适合处理大量数据。如果像网飞一样，您需要在不停机的情况下为数亿客户提供服务，那么 noSQL 数据库是更好的选择。
还值得注意的是，数据库可以一起使用。noSQL 数据库可用于为客户提供服务，并确保他们以最小的延迟访问内容。对于包括了解如何使用应用程序的分析任务，可以使用 SQL 数据库。在这里，成功和失败不是由 5 秒的页面加载来定义的，构建灵活而复杂的查询是非常重要的。
SQL 管道可以在这里查看。
noSQL 管道 可以在这里查看。
SSD:使用多盒进行物体检测的单次检测器
在这篇短文中，我们将了解什么是固态硬盘、它的架构以及它如何被训练和用于物体检测
单发探测器
像 YOLO 这样的单镜头探测器只需一次拍摄，就可以使用多框探测图像中的多个物体。
它在速度上明显快于高精度的目标检测算法。在 VOC2007 上快速比较不同对象检测模型的速度和准确性
- SDD300 : 59 FPS，贴图 74.3%
- **SSD 500:**22 帧/秒，平均映射为 76.9%
- 更快的 R-CNN : 7 FPS，mAP 73.2%
- YOLO : 45 FPS，贴图 63.4%
使用相对低分辨率图像的 SSD 的高速度和准确性归因于以下原因
- 消除了像 RCNN 中使用的包围盒建议
- 包括用于预测物体类别和边界框位置偏移的递减卷积滤波器。
SSD 中的高检测精度是通过使用具有不同大小和纵横比的多个盒子或过滤器来进行对象检测而实现的。它还将这些过滤器应用于网络后期的多个要素地图。这有助于在多个尺度上进行检测。
单触发探测器的体系结构
固态硬盘有一个基本的 VGG-16 网络，后跟多个 conv 层
基本神经网络:提取特征
SDD 的 VGG-16 基础网络是用于高质量图像分类的标准 CNN 架构，但是没有最终分类层。VGG-16 用于特征提取。
附加 Conv 层:检测物体
对于基本的 VGG 网络，我们增加了额外的卷积层用于检测。基本网络末端的卷积层大小逐渐减小。这有助于在多个尺度上检测物体。用于检测的卷积模型对于每个特征层是不同的。
对图像中不同对象的包围盒和置信度的预测不是由一个而是由代表多个尺度的不同大小的多个特征图来完成
逐渐减少的卷积层会减小特征图的大小并增加深度。深层覆盖更大的感受野，并构建更抽象的表达。这有助于探测更大的物体。初始 conv 层覆盖较小的感受野，有助于检测图像中存在的较小物体。
SSD 的培训
SSD 的输入是一个输入图像，图像中的每个对象都有地面真实边界框，如下所示
VGG-16 是执行特征提取的基础网络。Conv 图层在不同比例的多个要素地图中的每个位置评估不同纵横比的盒子，如下所示。
多盒子就像快速 R-CNN 的主播。我们有多个不同大小的默认框，如下图所示。SSD 用 8732 盒。这有助于找到与包含对象的地面真实边界框重叠最多的默认框。
匹配策略
在训练期间，默认框在纵横比、位置和比例上与地面真实框相匹配。我们选择与地面真实边界框具有最高重叠的框。预测框和地面实况之间的 IoU(交集/并集)应大于 0.5。我们最终选择了与真实情况有最大重叠的预测框。
在上图中，我们匹配了两个默认的框。一个带着猫，一个带着狗。它们被视为正边界框，其余的被视为负边界框。
每个预测包括
- 具有形状偏移的边界框。∈cx，∈cy， h 和 w *，*代表从默认框中心的偏移量及其高度和宽度
- 所有对象类别或所有类的置信度。类 0 被保留以指示对象的不存在
SSD 中使用的损失函数是多盒损失，它由两项组成:置信度损失和定位损失。
数据扩充:
数据扩充技术用于处理对象大小和形状的变化，使用剪切、放大、缩小、翻转、裁剪等。数据扩充的应用使得该模型对于各种输入对象尺寸和形状更加稳健。这有助于提高模型的准确性。
每个训练图像由以下选项之一随机采样
- 使用整个原始输入图像
- 对对象的面片进行采样，使与对象的最小重叠为 0.1、0.3、0.5、0.7 或 0.9
- 随机抽取补丁样本
使用 SSD 进行推断
SSD 在不同的输出层上使用不同比例、形状和纵横比的默认框。
它使用 8732 个盒子来更好地覆盖位置、比例和长宽比。大多数预测将不包含任何对象。SSD 会丢弃置信度得分低于 0.01 的预测。然后，我们应用每类 0.45 的非最大抑制(NMS)重叠，并保留每幅图像的前 200 个检测。
固态硬盘需要记住的要点
- 而 Yolo 具有固定的网格单元纵横比。固态硬盘使用不同的宽高比和多个盒子，以获得更高的精确度
- 固态硬盘在基本 VGG-16 的末端有额外的 conv 层，用于物体检测。卷积层具有不同尺度的多种特征，因此能够更好地检测多尺度下的目标
参考资料:
https://github.com/amdegroot/ssd.pytorch#demos
堆叠图例过滤器、双轴密度标记图和双轴散点图
加快数据可视化
它是数据化的！— Tableau 剧本
Photo by Campaign Creators on Unsplash
嘿#数据摇滚明星们！在这篇专栏的文章中，我将使用 Tableau、dashboarding 最佳实践和一些方便的技巧/窍门来涵盖各种数据可视化概念。在我的 Tableau 公共配置文件中会发布一个示例仪表板，您可以随意使用。所有的概念都会融入其中，方便你参考和学习。在本期 Tableau Playbook 系列中，我们将参考以下仪表盘。
It’s Datafied! Tableau Playbook Series
我们将一步一步地介绍仪表板每个组件的开发，以及其中使用了哪些概念。我希望你能像我写这篇博客一样兴奋地学习。那么，事不宜迟，让我们开始吧！
组件 1——兼作图例的过滤器组。
步骤 1: 将“区域”维度拖放到色卡上。你会看到如下所示的 4 个小方块。(颜色图例中的颜色可能不同)。
步骤 2: 双击 columns shelf 创建一个自定义度量，并键入“min(10)”作为度量(不带引号)。这将方形标记变成一个堆栈，如下所示。这样做是为了使用常数值作为调整堆栈大小的度量。您还可以为相同的创建自定义计算，以便更有条理。
步骤 3: 通过从底部拖动来调整堆叠的高度，宽度可以从尺寸卡开始增加。通过右键单击轴标题并取消选择“显示标题”来隐藏轴标题，如下所示。通过“格式”选项删除不必要的格式行，以保持数据-油墨比率。
**第四步:**将区域维度拖放到标签卡上瞧！我们已经完成了过滤器+图例堆栈的构建！很简单，不是吗？！我们可以通过过滤器动作在我们的仪表板中使用它作为过滤器。
组件 2——带区域和密度标记的双轴地图。
**第一步:**双击状态维度。Tableau 会自动将生成的纬度和经度度量药丸放置到行和列架上，因为 state dimension 被分配了地理角色。默认情况下，国家与州一起放在标志卡上，因为它是层次结构的一部分。您可以通过本文末尾提供的链接了解 Tableau 中的层次结构和地理角色。
**第 2 步:**从标记卡下拉菜单中更改标记为地图，因为我们必须创建一个彩色地图。
**第三步:**将区域尺寸拖放到色卡上，这里我们已经准备好了基于底层区域的颜色图，在其上绘制密度标记。您可能已经注意到，Tableau 会自动为这些区域分配与您在为上一个组件中的区域准备滤镜堆栈时选择的颜色相同的颜色。这就是 Tableau 如何帮助我们在整个仪表板中保持统一的配色方案，并被视为仪表板构建的最佳实践之一，即在所有仪表板中为相同的尺寸/度量使用统一的配色方案。
**第四步:**点击横排货架上的 latitude 药丸，按住 control 键并向右拖动药丸，创建一个重复的 latitude 药丸。很漂亮不是吗？这就是你如何为一个尺寸或尺寸动态创建一个复制药丸，并将其放到其他卡片/架子上。这就是地图现在的样子，即分成两个副本。您还会注意到，现在我们将标记卡分为三个部分，一个名为“ALL ”,通过它我们可以一次对两个地图进行更改，如果我们想单独进行更改，则为两个地图分别创建一个部分。
**第 5 步:**单击三个中最下面的纬度标记卡架部分，单击 State pill 前面的“+”号，向下钻取到层次结构中的城市级别。接下来，将利润率指标拖放到颜色卡上，并选择合适的配色方案。在这种情况下，我们有负和正值的利润率，所以我选择了一个发散的配色方案。这也是最佳实践之一，即在测量值为负值和正值的情况下，选择发散的配色方案。现在，从标记卡下拉菜单中选择密度。我们现在有了我们想要的两张地图，我们将在下一步使用双轴选项将一张叠加在另一张的上面。
**第 6 步:**单击我们创建的 rows shelf 上的第二个 latitude pill，然后选择双轴。
步骤 7: 要从地图上移除周围区域，点击地图工具栏，点击地图图层，在背景部分设置冲洗选项为 100%。这将删除地图的不必要的背景，使它看起来整洁。这有助于删除不必要的轴、标签、背景和标记，以保持数据-油墨比率。
在那里！我们基于密度标记的双轴地图！让我们继续保持#DataRockstar 的氛围，因为接下来还有另一个有趣的图表要开发。
组件 3 —双轴散点图
向散点图添加双轴基本上会使散点图更漂亮，并为您添加额外的颜色编码功能，即圆形轮廓。下面我们来探究一下截图，以便更好的理解。
您可能已经注意到，每个圆圈代表一个有两种不同颜色的城市，即一个是代表该城市所属地区的圆圈轮廓，另一个是代表该城市利润率是负还是正的内圈颜色。因此，使用这种方法，我们一目了然，而不是通常的散点图，这看起来很整洁！让我们来看看创建这个的步骤:
步骤 1: 将“利润”和“销售”度量拖放到“行/列”架上，然后将“城市”维度拖放到“详细信息”卡上，如下图所示，并增加“大小标记”卡上圆形标记的大小:
**第二步:**正如我们在双轴地图中所做的，按住 control 键，单击销售药丸并将其拖放到右侧，以创建一个重复的销售药丸。这将使双轴与 3 标记卡一起出现，一个代表散点图，一个代表每个单独的图表，如下所示。
**第三步:**转到中间的 SUM(Sales)标记卡菜单，将利润率拖放到色卡上，将颜色不透明度降低到 40%，从下拉菜单中选择圆形标记。
**第四步:**第二步，转到底部的标记卡菜单，将区域维度拖放到色卡，将城市拖放到标签卡。
**第 5 步:**正如我们在双轴图中所做的，单击第二个销售药丸，选择双轴以合并两个散点图。接下来，单击上方的销售轴并选择同步轴，然后隐藏标题。同步轴也是一个最佳实践，以避免双轴图表中的任何歧义，因此，请确保您永远不会忘记这一点。
嘣！您已经完成了双轴散点图！很有趣，不是吗？你现在已经学会了一些有趣的技巧。
总结
- 开发的组件——兼作图例的过滤器堆栈、带有密度标记的双轴地图和双轴散点图。
- 整合了最佳实践—在整个仪表板中保持统一的配色方案，删除不必要的标签、轴、背景等，以在创建双轴图表时保持数据-油墨比率和轴的同步。
- 分享的提示和技巧——按住 control 键并拖动药丸来复制它，并利用散点图中的双轴来表示额外的维度/度量。
在接下来的帖子中，我将提到同一个仪表板，并分享如何利用自定义计算使过滤器在仪表板中弹出，在仪表板上显示警告消息以显示哪些过滤器是活动的，并通过浮动容器在仪表板中获得布局。敬请关注。
进一步阅读的相关链接:
数据-油墨比是爱德华·塔夫特提出的一个概念，他的工作为…做出了重大贡献
infovis-wiki.net](https://infovis-wiki.net/wiki/Data-Ink_Ratio) [## 创建层次结构
当您连接到数据源时，Tableau 会自动将日期字段分成层次结构，这样您就可以很容易地打破…
help.tableau.com](https://help.tableau.com/current/pro/desktop/en-us/qs_hierarchies.htm) [## 调色板和效果
所有标记都有默认颜色，即使标记卡上没有颜色字段。对大多数标记来说，蓝色是…
help.tableau.com](https://help.tableau.com/current/pro/desktop/en-us/viewparts_marks_markproperties_color.htm) [## 为数据可视化选择颜色时需要考虑什么
数据可视化可以定义为用形状来表示数字——不管这些形状看起来像什么…
academy.datawrapper.de](https://academy.datawrapper.de/article/140-what-to-consider-when-choosing-colors-for-data-visualization) [## 过滤动作
筛选操作在工作表之间发送信息。通常，过滤器动作从选定的标记发送信息…
help.tableau.com](https://help.tableau.com/current/pro/desktop/en-us/actions_filter.htm) [## 表格中地理字段的格式-表格
本文描述了如何为 Tableau 中的字段分配地理角色，以便您可以使用它来创建地图视图。一个…
help.tableau.com](https://help.tableau.com/current/pro/desktop/en-us/maps_geographicroles.htm)
从我的 Tableau 公开个人资料链接到仪表板:
[## Tableau 公共
随意分享和玩耍
public.tableau.com](https://public.tableau.com/profile/pavneet.singh#!/vizhome/ProfitSalesAnalysisacrossCitiesinUSA/ProfitSalesAnalysisDashboard)
堆叠分类器以提高预测性能
利用多分类器的智慧提高性能
Photo by Erwan Hesry on Unsplash
**目的:**这篇文章的目的是为读者提供必要的工具来实现称为堆叠的集成学习技术。
**材料和方法:**使用 Scikit-learn ，我们为一个分类任务生成一个类 Madelon 数据集。然后分别训练支持向量分类器(SVC)、Nu-支持向量分类器(NuSVC)、多层感知器(MLP)和随机森林分类器。每个分类器的性能将使用受试者操作曲线下面积(AUC)来衡量。最后，我们使用 StackingCVClassifier 对象堆叠这些分类器的预测，并比较结果。
硬件:我们在配备英特尔酷睿 i7–8700 处理器(12 个 CPU，3.70 Ghz)和英伟达 GeForce RTX 2080 的工作站上训练和评估我们的模型。
**注意:**如果您是从零开始，我建议您按照这篇文章安装所有必要的库。最后，假设读者熟悉 Python、 Pandas 、Scikit-learn 和 ensemble 方法。这篇文章的全部内容可以在我的 GitHub 上找到。欢迎你来叉它。
符号:粗体文本将代表一个列表、字典、元组、一个 Pandas DataFram e 对象，或者将引用一个图形或脚本。This notation will be used to represent parameters in an object or command lines to run in the terminal.
什么是堆叠？
最简单的堆叠形式可以描述为集成学习技术，其中多个分类器(称为一级分类器)的预测被用作训练元分类器的新特征。元分类器可以是您选择的任何分类器。图 1 显示了如何训练三个不同的分类器。它们的预测被叠加，并被用作训练元分类器的特征，元分类器做出最终预测。
Figure 1 — Schematic of a stacking classifier framework. Here, three classifiers are used in the stack and are individually trained. Then, their predictions get stacked and are used to train the meta-classifier.
为了防止信息从目标(您试图预测的事物)泄漏到训练中，在堆叠分类器时应遵循以下规则:
- 一级预测应该来自没有用于训练一级分类器的训练数据的子集。
一个简单的方法是将你的训练分成两半。使用训练数据的前半部分来训练一级分类器。然后使用训练好的一级分类器对训练数据的后半部分进行预测。然后，这些预测应该用于训练元分类器。
一种更可靠的方法是使用 k 重交叉验证来生成一级预测。这里，训练数据被分成 k 倍。然后，前 k-1 个折叠用于训练一级分类器。然后，验证折叠用于生成一级预测的子集。对每个独特的组重复该过程。图 2 说明了这个过程。
Figure 2 — Schematic of k-fold cross validation used with a Stacking classifier framework.
热心的读者可能会想，为什么不堆叠另一层分类器呢？可以，但这会增加堆栈的复杂性。例如，考虑以下架构:
Figure 3 — Schematic of a Stacking classifier with two layers of classifiers.
这开始看起来像一个神经网络，其中每个神经元都是一个分类器。第二级中使用的分类器的数量和类型不必与第一级中使用的相同——看看事情是如何很快失控的。例如，一级分类器可以是额外树分类器、决策树分类器和支持向量分类器，二级分类器可以是人工神经网络、随机森林和支持向量分类器。在本文中，我们将实现如图图 2 所示的堆叠分类器架构。
堆叠，还是不堆叠，这是个问题。
在开始堆叠分类器之前，建议您首先考虑所有其他选项，以提高您的预测性能。作为指导，请考虑以下问题，以确定是否应该开始堆叠:
- 你有可能掌握更多的数据吗？在机器学习中，数据为王(即通过收集更多数据可以实现性能的最大提升)；因此，如果这是一种可能性，你应该集中精力，把一个更大更多样化的数据集放在一起。
- 是否有可能向您的数据集添加更多功能？例如，您可以使用图像中的像素亮度值作为特征。您还可以从这些图像中提取纹理特征，这些特征考虑了像素亮度值在空间上的分布。从手头的问题中收集尽可能多的特征是很重要的。这样做只是因为你不知道这些特征中哪些是好的预测器。请记住，任何模型的预测能力应该主要来自用于训练它的特征，而不是来自算法本身。
- 您是否正确地预处理了所有数据？如果你把垃圾推进模型，垃圾就会出来。确保您在视觉上探索了您的所有功能，以便更好地理解它们。注意丢失的值、异常值，并确保您的数据集不会严重失衡。缩放您的功能，删除所有冗余功能，如果您的功能数量很大，请应用功能选择方法，并考虑功能工程。
- 你用你的数据集探索过一大组分类器的预测性能吗？你仔细调过这些分类器吗？
如果你对所有这些问题的回答都是肯定的，并且你愿意做任何事情来提高性能，那么考虑堆栈。
安装必要的模块
实现如图图 2 所示的堆栈架构的最简单方法是使用 MLXTEND Python 库。要安装它，请阅读他们的 GitHub 自述文件在这里。如果您在 Windows 上安装了 Anaconda，请启动 Anaconda 提示符，导航到要安装此模块的 conda 环境，然后运行以下命令:
conda install -c conda-forge mlxtend
或者您可以使用画中画:
pip install mlxtend
免责声明:我几乎可以肯定这不会破坏您的 Python 环境，但是如果真的破坏了，不要怪我。这里有一个关于如何使用 Anaconda 在 Windows 中安装基于 Python 的机器学习环境的指南，供感兴趣的读者参考。
数据集
我们将使用 Scikit-learn 为分类任务生成一个类似 Madelon 的合成数据集。这是我在 Medium 上发表的一篇文章[中用来探索各种模型性能的相同数据集。Madelon 数据集是一个人工数据集，包含放置在边长为 1 的五维超立方体的顶点上的 32 个聚类。聚类被随机标记为 0 或 1 (2 类)。](http://Model Design and Selection with Scikit-learn)
我们将生成的数据集将包含 30 个特征，其中 5 个将提供信息，15 个将是冗余的(但提供信息)，5 个将重复，最后 5 个将是无用的，因为它们将被随机噪声填充。数据集的列将按如下顺序排列:
- 信息特征—第 1–5 列:这些特征是您构建模型时真正需要的唯一特征。因此，一个五维的超立方体。
- 冗余特征——第 6–20 列。这些特征是通过线性组合具有不同随机权重的信息特征而形成的。您可以将这些视为工程特性。
- 重复特征——第 21–25 列:这些特征是从信息特征或冗余特征中随机抽取的。
- 无用特征——第 26–30 列。这些特征充满了随机噪声。
让我们从导入库开始。
Script 1 — Importing all libraries
现在我们可以生成数据了。
Script 2 —Generates the data set. The random_state parameter is fixed so that we can work with the same data.
分割和预处理数据
一旦我们有了数据，我们就可以继续创建一个训练和测试集。
Script 3— Create train and test set. Notice we set the random_state parameter.
现在我们可以扩展我们的数据集。我们将通过 Z 分数标准化来实现。
Script 4 — Scaling the data.
我们已经完成了数据集的预处理。在这个例子中，我们不会去费力地去除高度相关的、冗余的和有噪声的特征。然而，重要的是，在你可能处理的任何项目中，你都要注意这一点。
分类器
出于说明的目的，我们将训练支持向量分类器(SVC)、多层感知器(MLP)分类器、Nu-支持向量分类器(NuSVC)和随机森林(RF)分类器——Scikit-learn 中提供的分类器。将设置这些分类器的超参数——我们将假设我们已经尽了最大努力来调整它们。坦率地说，如果你的分类器是垃圾，那么一堆分类器可能也是垃圾。此外，为了获得可重复的结果，每个分类器上的random_state已经固定。
Script 5— Initializing classifiers.
堆积分类器
为了堆叠它们，我们将使用 MLXTEND 中的 StackingCVClassifier。我建议你看一下官方文档，因为它详细介绍了如何实现 StackingCVClassifier 的有用例子。例如，它向您展示了如何堆叠对不同特征子集进行操作的分类器——这太酷了。最后，我们将把 StackingCVClassifier 中的use_probas参数设置为 True。这意味着第一级分类器将通过概率预测，而不是二进制输出(0 或 1)。然而，这并不意味着您应该使用这个设置，如果您将use_probas设置为 False，可能会对您的工作更有好处；然而，知道这一点的唯一方法是实际去做。作为meta_classifier,我们选择一个 SVC 并使用它的默认参数。
Script 6— Initializing the stacking classifier.
最后，为了方便起见，我们将所有的分类器放在一个字典中。
Script 7— Saving classifiers in a dictionary.
训练分类器
我们训练每个分类器，并将其保存到标记为分类器的字典中。分类器被训练后，将被保存到同一个字典中。
Script 8— Training classifiers.
做预测
现在我们准备在测试集上进行预测。这里我们首先创建结果，一个 pandas DataFrame 对象。然后使用 for 循环，我们遍历训练好的分类器，并将预测存储在结果中。最后，在第 14 行，我们添加了一个标有“Target”的新列，其中包含实际的目标
Script 9— Making predictions on test set.
可视化结果
现在是想象结果的时候了。我们将通过创建一个有 5 个支线剧情的人物来实现。在每个子图上，我们将显示从每个分类器的测试集上获得的概率分布。我们还将报告受试者工作曲线下的面积(AUC)。
Script 10 — Visualizing results.
Figure 4 — Probability distribution for each classifier. The AUC is also reported.
不错！每个分类器单独获得小于 0.909 的 AUC 然而，通过叠加它们，我们得到 AUC = 0.918。这是一个很好的提升，你不觉得吗？
调整堆积分类器
StackingCVClassifier 为您提供了调整每个分类器以及元估计器的参数的选项。但是，我不建议使用 StackingCVClassifier 进行全面的搜索。例如，假设您想要使用四个分类器，每个都有 100 个点的参数网格—老实说，这是一个相当小的网格。探索这个空间需要多长时间？让我们做一个简单的计算:
100 * 100 * 100 * 100 =你必须适应的 1 亿个模型
假设每个模型需要 0.5 秒来拟合，那将需要 5000 万秒或更长时间，简单地说就是 1.6 年。没人有时间做那个。这个计算没有考虑到元分类器不是固定的，并且包含它自己的需要调整的参数。这就是为什么我建议您首先单独调优您的分类器。之后，您可以将它们堆叠起来，只对元分类器进行调优。如果您决定调整分类器堆栈，请确保您为每个分类器调整了参数空间中的最佳点。
在这个例子中，我们首先为元分类器——支持向量分类器——创建一个参数网格。然后，我们使用 GridSearchCV 进行彻底搜索，以确定参数网格中产生最高性能的点。结果被打印到控制台上。
Script 11 — Tuning the meta-classifier.
The AUC of the tuned Stacking classifier is 0.923
很好，通过调整元分类器，我们成功地将堆叠分类器 AUC 从 0.918 推至 0.923。然而，我们还没有完成。让我们看看我们是否能从堆叠分类器中得到更多。为此，我们将创建一组堆叠分类器，每个分类器在第一层都有唯一的分类器组合/堆叠。对于每个堆叠分类器，将调整元分类器。结果将被打印到控制台上。
Script 12— Stacking different classifiers and exploring their performance.
AUC of stack ('SVC', 'MLP'): 0.876
AUC of stack ('SVC', 'NuSVC'): 0.877
AUC of stack ('SVC', 'RF'): 0.877
AUC of stack ('MLP', 'NuSVC'): 0.876
AUC of stack ('MLP', 'RF'): 0.877
AUC of stack ('NuSVC', 'RF'): 0.877
AUC of stack ('SVC', 'MLP', 'NuSVC'): 0.875
AUC of stack ('SVC', 'MLP', 'RF'): 0.876
AUC of stack ('SVC', 'NuSVC', 'RF'): 0.879
AUC of stack ('MLP', 'NuSVC', 'RF'): 0.875
AUC of stack ('SVC', 'MLP', 'NuSVC', 'RF'): 0.923
事实证明，如果我们将所有分类器堆叠在一起，我们会获得最高的 AUC。然而，情况并不总是如此。有时堆叠较少的分类器会产生较高的性能。实际上，我花了整整一天的时间才弄明白应该将哪些分类器堆叠在一起才能获得这种提升。此外，我注意到，有时通过叠加，我获得了较低的性能。诀窍是堆叠分类器，这些分类器弥补彼此的错误，而不是彼此对立。
结束语
我开始这篇文章是为了向读者介绍堆叠技术。它本应该又短又快，但结果却很长。如果你能走到这一步，我很感激你的时间。我希望这对你的努力有所帮助。我想指出的是，堆叠分类器可能并不总是有效，对于小的提升来说，这可能是一个乏味的过程；然而，如果做得正确，运气站在你这边，你将能够获得性能的提升。我建议您首先探索其他选项来提高您的性能。堆积的过程应该留到最后，因为它可能很耗时，回报可能很少。最后，如果你堆放垃圾，垃圾很可能会出来。这篇文章的全部内容可以在这里找到。祝一切顺利，继续学习！
你可以在 LinkedIn 找到我。
Sklearn 让堆叠变得简单
Photo by Jeremy Thomas on Unsplash
sci kit-学习
用几行代码创建一个 StackingClassifier
介绍
整体方法的基本原则是在统一中发现力量。通过组合多种方法，每种方法都有自己的优缺点，可以创建更强大的模型。
整体大于部分之和——亚里士多德
这句话尤其适用于堆叠，这是一种将许多不同的学习者组合成一个更强大的模型的方法。
写这篇文章的主要原因不是解释堆栈是如何工作的，而是演示如何使用 Scikit-Learn V0.22 来简化堆栈管道并创建有趣的模型。
1.堆垛
尽管有许多很棒的资料来源介绍了堆栈(这里的、这里的和这里的)，但是让我快速地让您了解一下。
堆叠是一种技术，它采用几个回归或分类模型，并使用它们的输出作为元分类器/回归器的输入。
本质上，堆叠是一种集成学习技术，很像随机森林，通过组合典型的弱模型来提高预测的质量。
Overview of a single level Stacking model
上图给出了堆叠原理的基本概述。它通常由许多弱基础学习或几个强基础学习组成。然后，元学习器基于每个基础学习器的预测输出进行学习。
2.Sklearn 堆叠
虽然有许多包可以用于堆叠，如 mlxtend 和 vecstack ，但本文将深入探讨 scikit-learn 新版本中新添加的堆叠回归器和分类器。
首先，我们需要确保将 Scikit-learn 升级到版本 0.22:
pip install --upgrade scikit-learn
我们要做的第一个模型是一个分类器，可以预测花的规格。该模型相对简单，我们使用随机森林和 k 近邻作为我们的基本学习器，使用逻辑回归作为我们的元学习器。
One-layer Stacking Model
编码堆叠模型可能相当棘手，因为您将不得不考虑您想要在不同步骤生成的折叠和交叉验证。幸运的是，新的 scikit-learn 版本只需几行代码就可以创建如上所示的模型:
基本上就是这样！只有几行代码的堆叠分类器。
3.交叉验证
堆叠的一个困难是您选择在哪里应用交叉验证。我们可以只在元学习者级别应用它，但是这可能会导致我们的模型过拟合，因为基础学习者的输出可能已经过拟合了。
尽管有许多解决这个问题的策略，但在 scikit-learn 中，堆栈的实现如下:
基础学习者适合于完整的
X，而最终估计者则通过使用cross_val_predict的基础学习者的交叉验证预测来训练。
这意味着每个单独的基础学习者的预测被堆叠在一起，并被用作元学习者的输入来计算预测。然后通过交叉验证来训练这个元学习者。
交叉验证自动设置为 5 倍 CV，但可以手动调整:
但这还不是全部，您还可以加入任何您想要的交叉验证策略:
4.多层堆叠
在第 2 步中，我们已经创建了一个具有单层基本学习者的 StackingClassifier。但是如果你想用另一组基础学习者代替元学习者呢？你如何添加层来增加模型的复杂性？
这里，我们将在步骤 2 中向模型添加另一层学习者，以便了解如何编写解决方案。
在上图中，我们可以看到我们想要添加一个包含两个额外分类器的层，即一个决策树和一个随机森林。
为了做到这一点，我们创建了两个估计列表，每层一个。我们使用最终模型的第二层估计量，即逻辑回归，创建一个 StackingClassifier。然后，我们用第一层估计器创建一个新的 StackingClassifier 来创建完整的模型管道。
正如你所看到的，模型的复杂性随着每一层的增加而迅速增加。此外，如果没有适当的交叉验证，您很容易使用这么多的层来填充数据。
结论
堆叠可能是一个棘手的问题，因为它需要对数据泄漏有深刻的理解，以便选择正确的程序。确保总是对你的模型进行广泛的验证，以便理解它的普遍性。
如果你像我一样，对人工智能、数据科学或心理学充满热情，请随时在 LinkedIn 上添加我。
支持最佳实践:
深度学习在《自然》杂志地震余震论文中的误用
Source: Yuriy Guts selection from Shutterstock
机器学习炒作的危险
人工智能、机器学习、预测建模和数据科学的从业者在过去几年中有了巨大的增长。曾经由知识混合定义的利基领域正在成为一个快速发展的职业。随着围绕人工智能的兴奋继续增长，新一轮的 ML 增强、自动化和 GUI 工具将导致试图建立预测模型的人数进一步增长。
但问题是:虽然使用预测建模工具变得越来越容易，但预测建模知识还没有普及。错误可能是违反直觉的和微妙的，如果你不小心，它们很容易让你得出错误的结论。
我是一名数据科学家，以与数十个专家数据科学团队合作为生。在我的日常工作中，我看到这些团队努力构建高质量的模型。最好的团队一起检查他们的模型来发现问题。有许多难以检测的方法会导致有问题的模型(比如，允许目标泄露到他们的训练数据中)。
识别问题并不有趣。这需要承认令人兴奋的结果“好得难以置信”，或者他们的方法不正确。换句话说，与其说是吸引眼球的性感数据科学炒作，不如说是一门严谨的科学学科。
糟糕的方法会产生糟糕的结果
大约一年前，我在《自然》杂志上读到一篇文章，声称通过使用深度学习预测地震余震达到了前所未有的准确性。读了这篇文章，我内心的雷达对他们的结果产生了深深的怀疑。他们的方法根本不具备仔细预测模型的许多特征。
我开始深入调查。在此期间，这篇文章炸了，成了广为人知的！它甚至被包括在 Tensorflow 的发行说明中，作为深度学习可以做什么的例子。然而，在我的挖掘中，我发现了论文中的重大缺陷。也就是说，数据泄漏会导致不切实际的准确性分数和对模型选择的不重视(当一个更简单的模型提供相同水平的准确性时，您不会构建一个 6 层神经网络)。
The testing dataset had a much higher AUC than the training set . . . this is not normal
对于我之前的观点:这些是微妙的，但是难以置信的基本的预测建模错误，它们可以使整个实验结果无效。数据科学家接受培训，在工作中识别和避免这些问题。我假设这只是作者忽略了，所以我联系了她，让她知道，以便她可以改进她的分析。尽管我们之前有过交流，但她没有回复我关于论文的邮件。
充耳不闻
那么，我该怎么办呢？我的同事告诉我只要发推特就可以了，但我想坚持良好的建模实践。我认为理性和最佳实践会占上风，所以我开始了为期 6 个月的过程来记录我的结果，并与自然分享。
在分享我的结果时，我在 2019 年 1 月收到了《自然》杂志的一份说明，尽管对数据泄露和模型选择的严重担忧使他们的实验无效，但他们认为没有必要纠正错误，因为“德弗里斯等人主要关注的是使用机器学习作为一种工具来提取对自然世界的洞察力，而不是算法设计的细节”。作者给出了更加严厉的回应。
你可以在我的 github 上阅读整个交流。
仅仅说我很失望是不够的。这是一篇重要的论文(这是自然！尽管使用了有缺陷的方法，但还是相信了人工智能的炒作并发表了一篇论文。
然后，就在这个星期，我偶然看到阿诺·米格南和马尔科·布罗卡多写的关于他们在《余震》文章中发现的缺点的文章。这里还有两位擅长地震分析的数据科学家，他们也注意到了论文中的缺陷。我还将我的分析和可复制代码放在了 github 上。
Go run the analysis yourself and see the issue
支持预测建模方法
我想澄清一点:我的目标不是丑化余震论文的作者。我不认为他们是恶意的，我认为他们会说他们的目标只是展示机器学习如何应用于余震。Devries 是一位有成就的地震科学家，她想将最新的方法用于她的研究领域，并从中发现了令人兴奋的结果。
但问题是:他们的见解和结果是基于有根本缺陷的方法。光说“这不是机器学习论文，这是地震论文”是不够的。**如果您使用预测建模，那么您的结果质量由您的建模质量决定。**你的工作变成了数据科学工作，你因为你的科学严谨性而上钩。
对使用最新技术和方法的论文有着巨大的需求。很难收回这些文件。
但如果我们允许带有根本性问题的论文或项目取得进展，这将伤害我们所有人。它破坏了预测建模领域。
请推回坏数据科学。向报社报告不好的发现。如果他们不采取行动，去推特，发布相关信息，分享你的结果，制造噪音。这种类型的集体行动有助于提高对 p 价值观的认识，并打击 p 黑客的流行。如果我们希望我们的领域继续增长并保持可信度，我们需要良好的机器学习实践。
**鸣谢:**我要感谢 DataRobot 所有伟大的数据科学家，他们在过去的一年里给予了我合作和支持，其中包括:Lukas Innig、Amanda Schierz、Jett Oristaglio、Thomas Stearns 和 Taylor Larkin。
在数据科学家的海洋中脱颖而出
Source: Pexels
2019 年，Glassdoor 将数据科学家列为美国排名第一的最佳工作。数据科学职业道路的日益普及导致了针对希望进入该领域的人的学位、项目和训练营的爆炸式增长。据发现数据科学报道，5 年前数据科学学士学位几乎不存在，而现在有超过 50 个。虽然对数据科学家的需求继续增长(事实上报告称从 2018 年到 2019 年需求增长了 29%)，但我认为，由于入门级人才的供应增加，获得数据科学家的第一份工作可能比以往任何时候都难。许多职位描述都强调在该领域拥有多年的工作经验，这并没有什么帮助。事实上，我在上找到的关于数据科学家工作的第一个描述是这样的(注意:这可能完全适合这个特定的工作；用它来说明不需要多年经验的数据科学职位描述很少):
- 本科学历或四年以上工作经验。
- 四年或以上相关工作经验。
Source: http://transpremium.com/the-sad-truth-of-hiring-employees-nowadays/
鉴于所有这些，我收到的最常见的问题之一就是**“我如何进入数据科学领域？”就不足为奇了**
有成千上万种方法可以回答这个问题，但我想集中讨论四种。不过，我的回答对你有所假设。首先，你实际上已经学习了数据科学，并且对该领域的基本原理感到很舒服。如果你不确定，可以看看吴恩达的机器学习课程。如果你对这些材料感到满意，那么我相信我的建议是适用的(如果你也参加了他的深度学习课程会加分)。
要有战略眼光
不幸的是，大多数技术项目并不十分关注实际获得一份工作。相反，学生们花费大量时间去追求完美的平均绩点。虽然渴望取得好成绩肯定没有错，但我发现这耗费了太多的时间，以至于当学生在课程结束时终于出来透透气时，他们发现他们没有花太多时间从战略上考虑如何找到他们梦想中的工作。
我说战略性是因为随着就业市场变得越来越全球化，仅仅通过提交申请和抱最大希望就能找到理想工作的日子已经一去不复返了。几乎每一份工作都会有几十份甚至几百份申请。事实上，谷歌每年收到大约300 万份申请，这意味着你获得工作的预期几率大约为 0.2%。另一方面，哈佛的录取率为 T4 的 4.5%。因此，虽然你仍然可以通过提交简历和通过“标准”程序在谷歌找到一份工作，但你最好还是努力提高自己的胜算。
网络
我提高胜算的第一条建议是建立关系网。不要相信“过程”会帮你找到工作，因为你是最好的候选人。招聘过程非常嘈杂，许多优秀的候选人都被忽略了。人际关系网非常有价值，因为它通常可以帮助你通过简历筛选。大多数公司的正常流程是创建一个职位发布，接收数百名申请人，然后使用一些流程来筛选简历，以选择谁将继续前进。挑选简历的过程，以我的经验来看，是最吵闹的。问题是非常大的公司必须有一个非常标准的过滤器才能处理如此大量的简历，而较小的公司往往几乎没有标准的流程。所以，如果你不完全符合大公司的要求，你就有麻烦了。在小公司，你可能不会被注意到，仅仅是因为你的简历从来没有被看过，或者可能是公司审查你简历时的偏见对你不利。
那么网络有什么帮助呢？建立关系网是获得推荐的第一步。曾在谷歌工作的丹·奎因说(2013 年)
没有比获得谷歌现任员工的推荐更有效的方法了。当我在那里的时候，公司面试的所有人中大约有 4%是员工推荐的，但他们占了被雇用人员的 20%以上。当一个谷歌人推荐你时，招聘团队会非常关注。
我发现几乎每个公司都是如此。公司渴望找到优秀的人才，他们知道他们的招聘过程很嘈杂。所以当一个优秀的员工推荐某人时，他们会倾听。
所以——我的建议是建立关系网，这样你就可以得到推荐，但是从哪里开始呢？以下是一些建议:
- 开始和人们交谈。轻松地去了解别人，并真诚地向他们学习。你班上可能有你不认识的人——去认识他们吧！想象一下，当你得知你曾经坐在像马克·扎克伯格这样的人旁边，却从来没有和他说过一句话。那感觉很糟糕。
- 将人际关系网视为给予的机会，而不仅仅是索取。亚当·格兰特有一本名为《给予与索取》的优秀书籍，讲述了作为给予者的价值。人们很容易将人际关系视为试图从某人身上获取价值。我建议你反其道而行之，试着给你的网络赋予价值。通常，这也会给你带来最大的价值。
- 不要害怕走出你的舒适区去拓展你的人际网络。登上 meetup.com号，找到和你志趣相投的人。联系 LinkedIn 上你钦佩的人。想在脸书找份工作吗？尝试与公司的一些当前数据科学家联系。我很惊讶人们有多么愿意联系。注意:在 LinkedIn 上联系时，不要咄咄逼人，也不要泛泛而谈。以我的经验来看，一封简短、礼貌的短信，表明你理解这个人是做什么的，以及为什么联系是有意义的，会大有帮助。如果他们没有回应，那也没关系，我会避免咄咄逼人。
人脉需要时间，所以越早开始越好。但是，希望通过发展和扩大你的人际网络，你会有了解你和你的技能的人，他们会更愿意给你介绍一份工作。
做一些独特的事情
当我第一次开始雇用数据科学家时，最让我惊讶的事情之一是所有候选人的感觉是多么相似。大多数候选人的 GPA 都不错，知道 Scikit-Learn 和 Pandas，有一些像垃圾邮件分类或情感分析这样的学校项目，甚至一些深度学习的知识也变得越来越普遍。这一切都很好，但问题是，虽然你是一个好的候选人，但你不是一个必须具备的候选人。
你在简历和面试中的目标不应该是在离开公司时认为你是一个强有力的候选人。应该让他们认为你是最佳人选，他们最好现在就雇佣你。
以下是我发现的一些有助于候选人脱颖而出的事情:
- 阅读、理解并完成一篇研究论文。最好是学生不经常实现的(再次强调——努力脱颖而出)。确保你的代码在 GitHub 上，甚至写一篇关于你的经历的博客。
- 通过端到端的机器学习获得定义和解决问题的经验。比如，也许你对梦幻足球充满热情。谈谈你如何定义你的问题，组装你的数据集，分析数据，做出预测，并评估这些预测，以做出更好的幻想足球决策，这真的很有价值。成为一名伟大的数据科学家不仅仅是理解所有的算法。这种类型的项目表明您可以执行整个数据科学流程。
- 花时间去了解公司和工作要求。然后修改你的简历和回答，突出你的技能与公司的要求相符合。这似乎是显而易见的，但在我的经验中很少做得好。
- 为开源做贡献，拥有一个活跃的 GitHub 账户。如今，几乎每个人都链接到 GitHub 账户，但除了几个学校项目，它们几乎都是空的。不知道如何开始为开源做贡献？我建议看一下 Scikit-Learn 关于投稿的指南。他们对初学者如何入门有非常明确的指导方针。设定一个目标，每周向 GitHub 推送一些代码，你将开始开发一个可靠的活动历史来展示。
有一个成长的心态
成长心态是理解能力和智力是可以发展的。德韦克博士创造了这个术语，发现了，“当学生相信他们可以变得更聪明时，他们明白努力会让他们变得更强大。因此，他们会投入额外的时间和精力，从而获得更高的成就。”谷歌还发现，成长思维是成功经理人的一个关键特征。
我非常支持成长型思维，这也是我在招聘时寻找的关键品质之一——尤其是初级职位。我发现具有成长心态的人更有可能将挑战和失败视为成长的机会，接受反馈，并推动自己和团队取得更多成就。我对培养成长心态的建议是:
- 通过不断学习来检验这个想法。如果能力和智力能够得到发展，那么就把工作放在每天去做，并分析结果。我个人确保每天至少花 30 分钟的空闲时间来学习。我对结果感到惊讶。这是我第一次学习强化学习，GANs，CSS，JS，还有很多更多的话题。
- 分享你的成长。当你学习和发展新技能时，通过 GitHub、LinkedIn 和 Medium 等渠道分享它们。分享的行为将有助于巩固你的知识，并经常把你介绍给其他有成长心态的人。
- 专注于你能控制的。在某种程度上，在你对自己的知识感到满意之前，你无法控制学习一个领域要花多少时间。当我刚开始学习强化学习的时候，对我来说是一个缓慢的过程。如果我专注于在一定的时间内“掌握”这个领域，那就很容易放弃。相反，我专注于我能控制的东西:我付出的努力。我当时的目标是每天花 30 分钟学习强化学习。我花了多长时间适应这个地区并不重要。
- 不要害怕拓展和学习数据科学以外的领域。如果你想让学习成为你一生的目标，你就需要让学习变得愉快，并且追随你的兴趣。我花了整整一个夏天来学习如何建立和部署我自己的网站。我还花了一些时间学习围棋语言。
- 试着将失败和挑战视为通往成功和学习的路标。没有人喜欢失败，但是没有失败你不会成长。将失败视为你将自己推出舒适区并不断成长的标志。现在，这只有在你真的花时间思考你的失败以及如何成长和从失败中学习的情况下才有效(然后投入工作去做)。此外，在失败中要有策略。虽然你肯定会从一个耗费了你一生积蓄的商业想法的失败中吸取教训，但那次失败将是非常严重的，而且这条路只能在深思熟虑后才能走。其他故障要安全得多，并且可以通过更少的预先分析来探索。比如我应该花时间学习话题 x 还是话题 y？如果你“失败”并选择了一个你不喜欢的话题，那么转向新的话题是相当容易的。
最后
我对希望进入数据科学领域的人的建议是**要有战略眼光，建立关系网，做一些独特的事情，并有一个成长的心态。**我也在 LinkedIn 上提出了这个问题，所以如果你想查看其他人的建议，你可以在这里查看:
[## Tyler Folkman 在 LinkedIn 上写道:“我正在整理一篇文章，给那些想要……
2019 年 9 月 23 日:泰勒·福克曼在 LinkedIn 上发帖
www.linkedin.com](https://www.linkedin.com/posts/tylerfolkman_datascience-firstjob-advice-activity-6581901513793384448-gq1P)
这篇文章也可以在这里找到。
星巴克:分析咖啡
星巴克拥有独特的方式与购买其产品的顾客建立联系并给予奖励。星巴克奖励计划允许该公司创建忠诚度计划，通过奖励购买特价产品的忠诚顾客。
介绍
S tarbucks 是一家全球性咖啡公司，在 75 个国家销售咖啡、茶、浓缩咖啡、面包和外卖。该公司的价值观之一是“创造一种温暖和归属感的文化，欢迎每一个人。”因此，它利用多种渠道营销其产品，从社交媒体到电视插播和广告。星巴克通过运用营销媒体渠道的组合来执行其非凡的营销策略，创造品牌知名度。星巴克不仅了解它的产品和顾客，而且还了解顾客如何使用技术。星巴克应用程序使客户能够跟踪参与商店的优惠和欢乐时光交易。它允许客户赚取并收集星(每美元收集两颗星)，可在店内或通过该应用进行兑换。
星巴克执行副总裁兼首席战略官马特·莱恩表示:“由于每周有近 1 亿顾客光顾我们的门店，我们正在寻找更多直接和私人接触的机会，为他们提供有意义的特别福利和优惠。”。
在这里，我们将调查和分析三个模拟人们如何做出购买决定以及促销活动如何影响这些决定的文件。A 清理分析后的最终数据的潜行:该数据包含发送给 14，825 客户的 8 个要约的信息，这些客户在完成至少一个要约的同时进行了 26，226 笔交易。以下是两个例子，说明星巴克通过应用程序向顾客提供优惠，鼓励他们购买产品和收集明星。
Buy One, Get One (BOGO) is one of the offers that will be analyzed in our data
Another type of the offer that Starbucks sends to the customers
奖金 :下图是一个交互式的日爆图，显示了更多关于交易数量的细节客户收到要约，通过使用的渠道之一查看该要约，然后进行交易在该要约期限结束前完成要约*。*
Click on the interactive chart to see the number of transactions of two offer type, eight offer ID, and gender.
分析及其结果只是观察性的，不是正式研究的结果。下面列出了一般的业务问题，以指导我们通过分析开发一套启发式方法，其中的发现不能保证是最佳的或合理的。这是我的 Github 库，里面有所有需要的代码。
商业问题
分析的目的是检验星巴克的顾客对一个提议的反应，不管这个提议是 BOGO 还是折扣。并非所有的顾客都有相同的动机去查看报价，然后进行交易以完成报价。许多因素在影响客户如何做出购买决策方面发挥着重要作用；例如，一些客户更喜欢让他们收集越来越多的星星来获得独家津贴甚至免费产品。有时，特定年龄组的顾客更喜欢与其他年龄组不同的产品。此外，我们应该记住，女性顾客对报价的反应可能与男性顾客不同。可以从很多方面进行调查分析，寻找这类问题的答案。所有这些都将有助于星巴克锁定目标顾客，然后根据顾客群个性化定制产品。可以问很多问题；下面是我们将要调查的一些内容:
1.至少收到一份报价的客户数量是多少？
2。谁通常在星巴克消费更多，女性还是男性？
3。对于消费更多的顾客；谁的年收入更多？
4。就性别而言，大多数星巴克顾客的年龄是多少？
5。自优惠活动开始以来，顾客在任何时候都花了多少钱？
6。我们能否按年龄组或性别找到最受欢迎的优惠，然后将其与其他优惠，甚至另一个年龄组的优惠进行比较？
7。哪个优惠对星巴克最有利？BOGO 优惠和折扣优惠有区别吗？如果是这样，男性顾客对这两种报价类型的反应是否和女性顾客一样？
数据集
我们已经拥有和将要读取、清理和分析的数据集:
- portfolio.json —文件中各变量的说明:
-id:offer id
-offer _ type
-难度:完成一个要约所需的最低花费- 奖励:完成一个要约所给的星级
- 持续时间:要约开放的时间，以天为单位
- profile.json —文件中各变量的解释:
- 年龄-成为 _ 会员 _ 日期:客户创建 app 账号的日期
- 性别
- id :客户 id
- 收入
- transcript.json —文件中各变量的解释:
- 事件:记录描述(交易、收到要约、查看等。)
- 人:客户 id
- 时间:自报价开始起的小时数
- 值:报价 id 或交易金额，视事件而定
数据准备
如果您想查看最终结果，而不想深入了解技术细节，请跳到数据探索部分。
注 : 在清洗过程中，一些变量的名称和顺序已经改变，以便于交流。
对这三个文件进行了许多修改和准备，以获得一个最终的干净数据集，该数据集包含每个变量所需的所有信息，从优惠类型到性别和年龄组。已经解决和解决的要点是:
1.第一部分
- 简档文件中的年龄变量包含一个最大值 118 岁，这被认为是一个不寻常的值，将进一步调查。结果，我们发现有 2175 个客户在收入和性别信息方面没有值。配置文件中的所有空值都是年龄值为 118 的客户的一部分。因此，作为配置文件清理过程的一部分，值为 118 的行将被删除，这导致我们拥有 14825 个客户，而不是最初的 17000 个客户。
Printing the number of nulls, and then the age of customers who have nulls
- 投资组合文件中的渠道变量列出了一个字段中用于报价的所有渠道。因此，我们拆分渠道，其中每个渠道本身成为一个变量，如果它已被用作特定报价的渠道，则值为 1 ，而值 0 表示它尚未被使用。
Code for splitting the channels
A view of the channels before being split into 4 variables
The portfolio data after splitting the channels
- 组合文件中的持续时间变量将在清理过程中发挥主要作用，它将与抄本文件中提供的每个抄本的时间进行比较。因此，它被转换为以小时表示，而不是以时间变量表示的天数。
- 简档文件中的变成 _ 成员 _ 开变量可用于查找大多数顾客加入星巴克应用程序的时间趋势。因此，首先，该列被转换为日期而不是整数，然后创建了一个按月的 membership_period ，其中显示了客户成为会员时的月和年。
Before converting become_member_on variable
After converting the variable to date and creating membership period
-
在抄本文件中的值变量包含了一系列信息，这些信息取决于客户所做的事件。如果客户进行了交易，这意味着该值是交易金额。否则，我们将获得客户已经收到、查看或完成的优惠 id。因此，我们将值拆分为两列:记录(报价 id 或交易)和记录 _ 值(金额或 id)。因此，创建了两个新的数据集。第一个是报价，包括客户已收到、查看或完成的客户 id 和报价 id 等信息。另一个文件是具有交易金额而不是报价 id 的交易。
-
基于针对要约的事件变量创建的三个数据框架，创建了新的数据框架offers _ count；已接收、已查看或已完成。这三个集合被合并以创建完整的比较，该比较说明了接收、查看或完成报价的客户数量，包括完成百分比。
if complete(not_viewed) is positive that indicates some customers completed an offer without viewing it
2.第二部分
在浏览数据以找到启发式发现之前，让我们总结一下我们将在探索步骤中关注的重要文件，然后合并它们以创建一个最终的、干净的数据集用于进一步分析。
A.轮廓
B.处理
C.提议
D.报价比较
created by merging offers_count data and portfolio data after being clean
- 这四个数据集被合并以创建将被分析的完整数据。然后，根据我们想要使用的变量创建子数据集。例如，可以只为女性顾客创建一个子数据集，为只完成至少一个报价的女性顾客创建另一个子数据集。此外，我们还可以为 only offer 5 创建一个子数据集，其中只有男性客户完成了该报价，并且在报价结束前至少进行了一次交易。
- 客户 id和报价 id在整个数据集中进行了清理，以便在分析时有简单的数字，而不是散列。
- 通过比较交易的时间和要约的持续时间，我们删除了要约结束后发生的交易。
接下来，我们将检查从最终的干净数据集创建的每个子数据集，以回答之前发布的问题。
数据探索
请注意，所有图表都是交互式的，因为当将悬停在图表的任何部分时，您可以获得更多信息。此外，一些图表在左上角有过滤器，您可以通过性别或报价 id 过滤图表。
1.客户数量
甜甜圈图显示了女性客户占 41.30% 和男性客户占 57.20% 的数据总体情况，无论他们是否在要约结束前完成要约、达成交易。还有，选择其他为性别的客户只占 1.43% 。我们是否可以认为，男性顾客越多，就意味着他们的交易量就越大？接下来，我们将进一步调查这些数字。
2.按性别分列的收入
我们可以看到，平均而言，男顾客一年挣的比女顾客少。事实上，女性顾客的年收入高于所有性别类型的平均收入。当女顾客平均每年挣大约7.1 万时，男顾客平均每年挣大约6.1 万。
这里，该图表明，总体而言，我们的大多数客户收入在 50k 和 75k 之间。平均每收入率客户数为 163 个客户。因此，当少数人每年挣超过 100k 时，一些顾客挣不到 50k 而另一些挣在 76k 和 100k 之间。总体来看，我们可以看到大多数女客户赚在 60k — 75k 之间，大多数赚客户赚 45k — 60k 。接下来，我们将确认女性和男性客户收入比率之间的差异。
在这里，我们证实了以前的收入分布的发现。该图证实，所有性别类型的大多数顾客的收入在 40k 和 60k 之间。大多数女顾客的年收入在 60k 和 80k 之间，而大多数男顾客的年收入在 40k 和 60k 之间。
3.按性别划分的年龄
箱线图描述了按性别分列的年龄分布概况。他们显示，平均而言，女性顾客比男性顾客年龄大。
从另一个角度看按性别划分的年龄分布。箱线图证实了大多数男女顾客的年龄都在 40 岁到 60 岁之间。
4.交易的时间和金额
自优惠开始以来的交易趋势显示，平均而言，从优惠开始到 10 天内的任何时间，女性顾客支付的金额都比男性顾客支付的金额多**。回到我们在第一张图中关注的问题；尽管男性顾客的数量更多，但女性顾客支付的费用比男性顾客支付的费用更多。唯一的例外是在要约开始后 228 小时后进行的交易。自优惠活动开始以来，平均而言，所有顾客在任何时候支付的总金额为16.55 美元**。**我们可以看到女性顾客支付的比任何时候的平均都多！而男性顾客支付的时间比平均水平少。同样，我们可以观察到一些 高峰 随着时间的推移，男女双方平均比平时付出更多。这可能是在周末或白天的特定时间。**
该图显示了自这些优惠开始以来的任何时间在两个优惠上花费的平均金额。我们可以看到，平均而言，所有顾客对优惠 5 的反应和支付都比他们在任何时候对优惠 7 的反应和支付都多**。**
5.报价分析
Top offers by the total transaction amount
在这里我们可以看到，报价 5 和报价 7 是所有完成报价并完成至少一笔交易的客户中支付的总交易金额最多的:分别为**$ 194632和$ 149296.9，**。接下来，我们将查看与优惠类型和性别相关的最高优惠。
Top BOGO offers by the total transaction amount
BOGO offers by the total transaction amount paid by gender
优惠 2 在四个 BOGO 优惠中获得最高金额，所有客户支付的总金额为 $110113.8 。总的来说，女性顾客支付的比男性顾客支付的要多 BOGO 的任何一项优惠。女和男顾客对报价 2 和报价 1 的支付最多。
Top Discount offers by the total transaction amount
Discount offers by the total transaction amount paid by gender
优惠 5 是四个优惠中金额最高的，所有顾客共支付194632.34 美元。总体而言，在任何折扣优惠中，女性顾客比男性顾客支付的多。女和男顾客对优惠 5 和优惠 7 的支付最高。
6.提供完成率
在之前的清理过程中对数据进行了进一步清理之后，下面是每个职位按性别划分的完成率比较。完成百分比来自完成报价的客户数量与收到报价的客户数量。所有客户的总体完成率；女、男、其他。 Offer 5 是最受欢迎的 Offer，完成率为 82%。显然，女性顾客对优惠的回应率总是高于男性顾客。两种性别都最喜欢出价 5 英镑。然而，尽管超过 80%的女性客户完成了报价 3 和 4 ，但平均有 55%的男性客户完成了这些报价**。****
柱状图展示了之前发现的数字，从中我们可以看到女性顾客对优惠的行为和男性顾客行为之间的明显差异。总体来说， offer 8 是最不的热门 offer， offer 5 是最的热门 offer 男女通用。报价 8 是最不受女性客户欢迎的报价，而报价 3** 是最不受男性客户欢迎的报价。**
该图显示了所有客户女性和男性的每个年龄组的每次报价完成率。比如在年龄层 35 岁， offer 6 有最高完成率 82.61% 。而年龄层 25 岁更喜欢报价 5 最多，完成率 77.55% 。
调查结果摘要
- 向 14，825 名客户发送了八个优惠(4 个 BOGO 和 4 个折扣)，他们完成了 26，226 笔交易，同时完成了至少一个优惠。
- 在所有数据中，女性客户占 41.30%，男性客户占 57.20%，无论他们是否在要约结束前完成要约、达成交易。
- 平均来说，男顾客一年赚的钱比女顾客少。女性顾客的年收入高于所有性别类型的平均收入。女性顾客平均年收入约为 7.1 万英镑，而男性顾客平均年收入约为 6.1 万英镑。
- 所有性别类型的大多数客户收入在 4 万到 6 万之间。大多数女性顾客年收入在 6 万到 8 万英镑之间，而大多数男性顾客年收入在 4 万到 6 万英镑之间。
- 平均而言，女性顾客比男性顾客年龄大。男女顾客的年龄大多在 40 至 60 岁之间。
- 平均而言，自优惠开始后的任何时候，所有客户支付的总金额为 16.55 美元。女性顾客在任何时候都比平均水平付出更多！而男性顾客大多数时候支付的费用低于平均水平。
- 报价 5 和 7 是所有完成报价并至少完成一笔交易的客户支付的最大交易总额:分别为 194，632 美元和 149，296.9 美元。平均而言，所有客户对优惠 5 的反应和支付的费用几乎总是高于他们在任何时候对优惠 7 的反应和支付的费用。
- 在 BOGO 的四个优惠中，优惠 2 的金额最高，所有客户支付的总金额为 110113.8 美元。女性顾客比男性顾客为 BOGO 提供的任何产品支付更多的钱。
- 在四个折扣优惠中，优惠 5 的金额最高。女性顾客比男性顾客支付更多的折扣。优惠 5 是最受欢迎的优惠，完成率为 82%。
- 就客户年龄而言，25 岁的客户完成率最高，为 77.55%。而 35 岁和 45 岁的客户更喜欢 offer 6，完成率分别为 82.61%和 83.33%。
星巴克优惠优化
介绍
促销优惠目前相当普遍。几乎每一家销售消费品的公司都会时不时地推出某种促销活动——无论是因为竞争加剧，还是为了扩大客户群或增加收入。由于发送这些报价是有成本的，所以通过设计有效的促销策略来最大化这些报价成功的可能性是至关重要的。
在本帖中，我们将分析模拟促销优惠数据，这些数据模拟了星巴克奖励移动应用程序上的客户行为，以获得商业见解，然后将这些发现转化为一个“可解释的”机器学习模型，该模型将预测客户是否会对优惠做出反应。目标是只向那些更有可能响应的客户发送报价，并且只发送那些最有可能成功赢得客户的报价。
对于这个项目，我们将遵循最广泛使用的分析过程模型 CRISP-DM 。
商业理解
每隔几天，星巴克就会向手机应用程序的用户发出一次报价。优惠可以仅仅是饮料的广告，也可以是实际的优惠，如折扣或 BOGO(买一送一)。某些用户可能在特定的几周内收不到任何优惠。
并非所有用户都收到相同的报价，这是这个数据集要解决的挑战。
这个数据集是真实星巴克应用程序的简化版本，因为底层模拟器只有一种产品，而星巴克实际上销售几十种产品。
每个报价都有一个难度级别，并且在报价到期前有一个有效期。例如，难度级别为 7 的 BOGO 报价可能仅在 5 天内有效。这意味着如果顾客在优惠到期前消费 7 美元或更多，他将免费获得价值 7 美元的产品。信息优惠的难度级别为 0，因为它们不要求进行任何购买。它们也有有效期，即使这些广告只是提供产品信息；例如，如果信息性报价有 7 天的有效期，我们可以假设客户在收到广告后的 7 天内感受到了报价的影响。
然后是客户数据，其中包含人口统计信息，如年龄、性别、收入以及会员开始日期。
有交易数据显示用户在应用程序上进行的购买，包括购买的时间戳和购买的金额。该交易数据还包含用户收到的每个要约的记录，以及用户实际查看要约的记录。也有用户完成报价的记录。
我们将结合交易、人口统计和优惠数据，然后标记有效的客户-优惠组合。有效报价是指客户受到报价的影响并进行交易以完成报价的报价。接下来，我们将进行探索性数据分析，以培养对客户和/或与优惠有效性相关的优惠属性的直觉。我们将使用我们在数据分析阶段的发现来建立一个受监督的机器学习模型，该模型将客户和提供细节的组合作为输入，并预测它是否将是一个有效的组合。我们将只发送那些模型预测对客户有效的报价。
数据理解
在这一节中，我们将粗略地看一下数据，以便对数据形式的一切有一个高层次的了解。
数据包含在三个文件中:
- portfolio.json —包含报价 id 和关于每个报价的元数据(持续时间、类型等)。)
- profile.json —每个客户的人口统计数据
- transcript.json 记录交易、收到的报价、查看的报价和完成的报价
以下是文件中每个变量的模式和解释:
portfolio.json
- id(字符串)—优惠 id
- offer_type (string) —优惠的类型，即 BOGO、折扣、信息
- 难度(int)——完成一项提议所需的最低花费
- 奖励(int) —为完成一项提议而给予的奖励
- duration(int)-要约开放的时间，以天为单位
- 频道(字符串列表)
profile.json
- 年龄(整数)—客户的年龄
- 成为会员日期(int) —客户创建 app 帐户的日期
- 性别(str) —客户的性别(请注意，有些条目包含“O”代表其他，而不是 M 或 F)
- id (str) —客户 id
- 收入(浮动)—客户的收入
抄本. json
- 事件(str) —记录描述(即交易、收到的报价、查看的报价等。)
- 人员(字符串)—客户 Id
- time (int) —测试开始后的时间，以小时为单位。数据开始于时间 t=0
- value —(字符串字典)—报价 id 或交易金额，具体取决于记录
以下是数据概述:
- 有 10 个报价，这些报价的子集被发送给 17000 个客户。
- 报价有效期从 3 天到 10 天不等。
- 报价的难度从 0 到 20 不等。最容易的报价的购买金额阈值为 5，最难的报价的购买金额阈值为 20。
- 优惠相关事件和交易数据的有效期为 30 天。
- 最近一次会员是 2018 年 7 月，说明数据是 1 岁。
数据准备
在这里，我们将首先进行数据辩论，然后使用可视化进行数据分析。
数据清理
对于每个数据集，我们将评估数据的不一致性和特征，然后我们将使用清理功能一起解决这些问题。
在数据理解阶段，我们知道客户 Id 和报价 Id 列中的数据不是用户友好的格式。我们需要使用一个通用的映射函数将这些列转换成更易读的格式，该函数将唯一的整数值映射到每个 id。
根据我们的评估，以下是我们需要遵循的清洁步骤。
投资组合
- 将唯一的整数 id 映射到每个优惠 id。
- 将频道分成单独的列。
- 放弃电子邮件渠道，因为它是所有报价的公共渠道。
简介
- 将唯一的整数 id 映射到每个人。
- 删除 118 岁人员的概要文件，因为这是为缺少性别和收入数据的人员填充的默认年龄。
- 将“成为成员日期”转换为日期格式。
- 从“成为成员于”创建新列“成员资格 _ 持续时间”,因为“成为成员于”列在其当前格式下不是很有用。我们将以最近的“成为会员日期”作为参考，以天为单位计算会员持续时间。
- 删除“成为成员于”列
成绩单
对于抄本数据，清理过程稍微复杂一些。以下是初始步骤:
- 将唯一的整数 id 映射到每个人，并使用在清理个人资料和投资组合数据中使用的 id 映射字典提供
- 从值列提取金额、优惠 id 和奖励
- 隐蔽时间从几小时到几天。由于优惠持续时间以天为单位，这将帮助我们比较数据，并根据优惠接收日期和优惠持续时间得出优惠到期日期。
- 将抄本数据帧分割成包含交易和非交易数据的两个独立的数据帧。
交易金额没有映射的优惠 id。因此，了解客户在特定优惠期间消费金额的唯一方法是，总结客户在优惠期间的交易金额。
让我们画出交易金额。
大多数购买金额在 100 美元以下。然而，有许多高价值交易，最大交易金额高达 1062 美元。我们从投资组合数据中得知，最困难的报价要求客户花费 20 美元或更多。因此，远远超过 20 美元的高价值交易可能是团体订单，不受促销优惠的影响。出于我们的分析和建模目的，我们将使用四分位数范围中的最大阈值将这些交易标记为异常值。在要约期内，如果客户有一笔或多笔异常交易，我们不会将其视为客户的有效要约。
接下来，我们将按照以下步骤将所有数据集合并到单个数据帧中:
- 通过为事件创建单独的列，将 non_transactions 数据转换为宽格式。
- 根据交易数据，计算每位客户在 30 天促销期间的总消费金额。从该计算中排除异常交易。
- 使用通用客户和报价 id 列将所有数据集合并到一个数据框架中。
- 对于多次向客户发送相同报价的情况，我们将只考虑第一次。
识别受影响的客户
在我们绘制数据以寻找趋势和模式之前，我们需要对客户进行分类，并提供有效和无效类别的 id 组合。
为此，我们将查看发送给客户的每份促销优惠，并确定该优惠是否影响了他/她。根据客户对促销活动的反应，可以分为四大类。
- 不受影响:如果客户看到了报价，但没有完成，那么他不受报价的影响。他知道该优惠，但没有受到影响而购买。
- 受影响:客户查看了报价并完成了交易。经验法则是，如果客户意识到该优惠，然后选择完成它，那么该优惠会影响他。但是，我们将排除那些符合此标准但在报价期间有异常交易的客户-报价组合。
- 完成但未查看:客户完成了报价但未查看，或者在报价完成后查看了报价。因为他不知道这个提议，所以这并没有影响到他。可能他或她是一个高级客户，不管促销优惠，他们的平均消费都比普通客户高。也可能只是普通顾客的一次性高额交易。无论哪种方式，它都不同于前两类客户报价组合。我们无法断定，如果顾客看到了报价，他是否会受到影响。
- 未查看未完成—这些是客户未查看或在到期后查看的不完整优惠。同样，我们也不能说如果顾客看了报价会有什么样的反应。
在合并的数据中，这些类别将分别映射到 0、1、2 和 3 值。
值得一提的是，这些不是客户分组，而是客户-报价组合，因为同一个客户对不同的报价会有不同的反应。此外，理想情况下，我们将清理与第三和第四类相关的数据。但在此之前，我们将绘制所有四个类别的数据，并看看它们如何相互比较。
这里要注意的另一件事是，由于信息提供从未完成，默认情况下，它们将属于“未受影响”类别。
数据分析
在本节中，我们将执行可视化数据分析，以获得对数据的一些关键见解，并找到与相关业务问题相关的答案。
我们将绘制一些客户和优惠属性，以直观地了解这些属性如何影响客户对促销优惠的反应。在我们对这些图进行视觉分析的过程中，我们将忽略信息提供的趋势。
分类特征
对于分类特征，我们将通过比较不同类别的响应比率(受影响与未受影响)来判断影响。
报价类型
- 似乎顾客对折扣的反应比 BOGO 更好。
优惠 Id
- 根据客户回应率，id 为 7、6 和 4 的优惠分别是前三名。以下是这些优惠的详细信息。
7:10 天内消费 10 美元，获得 2 美元作为折扣。
6:7 天内消费 7 美元，获得 3 美元作为折扣。
4:7 天内消费 5 美元，免费获得价值 5 美元的产品。
- 这里要注意的一个有趣的点是，BOGO 的出价与出价 id 7 的难度相同(出价 id 1 和 2 ),即使奖励比率更高，成功率也更低。消费者似乎更喜欢以折扣价购买产品，而不是免费获得额外的产品。
难度
- 难度为 7 级的提议有最好的回应率。
- 对于具有最高难度级别的报价，大多数报价没有被客户查看。但是，难易程度和回答率之间没有明确的相关性。
持续时间
- 持续时间越长的优惠成功率越高，可能是因为顾客有更长的时间购买更多的东西。
性别
- 女性比男性有更好的回应率。
- 尽管属于“其他”性别的顾客数量相对较少，但他们的回应率高于男性。
数值特征
接下来，我们将通过比较回应和非回应要约的分布来探讨数字特征在成功要约中的影响。
总花费金额
- 受优惠影响的顾客平均每小时花费的时间比没有响应的顾客多得多。这在预期行上。
- 总花费几乎总是在 200 美元以下。
- 难度级别分别为 20 和 10 的出价 Ids 5 和 10 的总花费金额略高。
- 似乎有两大类。完成报价的人(第 1 类和第 2 类)和没有完成报价的人(第 0 类和第 3 类)。
- 0 和 1 的支出总额分布相似，段 2 和 3 也是如此。
- 并且没有明显的趋势来将观看的和未观看的片段彼此区分开来。让我们看看这一趋势是否适用于其他客户属性，如年龄、收入和会员资格期限。
收入
- 大多数优惠都发给了收入在 40-80，000 之间的客户。
- 在所有报价中，受影响的部分比不受影响的部分属于高收入群体。可以有把握地推测，属于高收入群体的客户更有可能对促销优惠做出反应，而不管优惠种类如何。
- 就像“支出总额”一样，细分市场 1 和 2 与细分市场 0 和 3 具有相似性。这种相似性没有我们在“总支出”中看到的那么深刻。
- 与“总支出”不同，已完成和未完成部分之间没有明确的界限。
年龄
- 大多数优惠都是发给 40 到 70 岁的顾客。
- 不同细分市场之间的“年龄”分布差异不如收入和支出总额的差异大。然而，与其他类别相比，第 3 类似乎总是属于较低的年龄组。最年轻的会员更有可能不查看和/或不完成报价。
会员期限
- 大多数优惠是发给会员资格不到 2 年的客户的。
- 平均而言，对优惠活动做出回应的顾客成为会员的时间比没有做出回应的顾客长。
- 成员资格期限很好地分离了不同的部分。这似乎是决定客户对报价作出反应的可能性的一个重要特征。
- 此外，在“每小时花费的金额”中观察到的趋势似乎或多或少也适用于此，即类别 1 和类别 2 具有相似性。类别 0 和 3 也是如此。然而，类别 3 比类别 0 具有更广泛的成员分布。
特征创建
从上面的可视化扩展，我们将创建一些更多的功能，将年龄，收入和成员资格持续时间等连续功能分离到不同的人口统计组。
我们将比较这些人口统计群体，以了解他们中的哪一个对优惠的反应更好。
难度比
难度比是由出价难度除以持续时间得出的。
- 难度比较低的报价似乎有更好的响应率。
收入群体
- 收入在 100，000 以上的客户的回答率最高，其次是收入在 80-100，000 之间的客户。
- 从上面的图像中可以清楚地看出收入和客户反应之间的正相关关系。
- 我们可以有把握地推断，收入超过 6 万英镑的人更有可能对这些提议做出回应。
会员范围
- 会员时间不到 1 年的客户是响应最慢的客户。
- 响应最快的客户是那些拥有 1 至 2.5 年(365-900 天)会员资格的客户，其次是拥有 2.5-4 年会员资格的客户。
- 在会员资格达到 1-2.5 年后，回复率会随着会员资格持续时间的增加而持续下降。
年龄组
- 80 岁以上年龄组的客户回答率最高，其次是 61-80 岁年龄组的客户。
- 年龄和客户的反应之间存在正相关关系。老年顾客似乎比年轻顾客有更好的回应率。
- 可以有把握地推断，40 岁以上的顾客更有可能对这些优惠做出反应。
建模和评估
在这一部分，我们将建立一个预测模型，预测客户是否会对报价做出反应。由于发送报价会增加组织的成本，我们不想向不太可能做出回应的客户发送报价。此外，我们不想错过向真正会做出回应的客户发送报价的机会。由于我们需要优化精确度和召回率，我们将使用 F1 分数作为模型评估指标。
为预测模型选择特征
作为第一步，我们需要只为模型选择相关的特性。我们的目标是建立一个预测模型，根据客户的人口统计数据和优惠属性，对客户和优惠 id 组合是否有效进行分类。我们将删除所有与交易数据相关的内容，包括“总消费金额”。尽管“总支出”很好地区分了“受影响”和“不受影响”的类别，但将其作为一个独立的特征将导致数据泄露。
此外，从数据分析部分，可以有把握地推测，没有明显的趋势将类别 2 与其他类别分开。尽管类别 1 和类别 2 有相似之处，但我们不能将它们合并，因为类别 2 中可能有一些高价值客户，他们的平均支出高于普通客户。向这些客户发送报价没有太大的商业意义。我们不能将类别 2 与“未受影响”组合并，因为这两个组之间没有相似之处。
但是，我们可以合并类别 0 和类别 3，因为它们具有相似的特征，并且可以不向没有查看报价的客户发送报价，即使在查看后也很可能不会做出响应。
朴素预测器
我们将从一个简单的分类器开始，该分类器对所有客户进行随机分类，并提供“受影响”或“不受影响”的 id 组合。
这仅仅是为了展示一个没有智能的基本模型将如何运行。我们可以用这个来比较我们将要构建的机器学习算法的性能。
似乎如果我们随机分配报价，有 50%的机会获得成功报价。
准备建模数据
当要素处于相对相似的规模并且接近正态分布时，许多机器学习算法会工作得更好。通常的过程是应用变换，例如，从数字特征中去除偏斜的对数变换。此外，最好是one-hot-encodee 分类特征，以避免在名义变量中引入平凡性等原因。
对于我们的初始模型，我们将不应用数值变换。稍后，我们将探讨这个选项来提高模型性能。
初始模型评估
我们将考虑以下监督模型，并查看哪种模型最能概括数据集:
- 逻辑回归
- Ada 增强
- 随机森林
- 轻型 GBM
对于这些模型中的每一个，我们将使用 5 重交叉验证来查看模型分数在不同的数据子集上如何变化。现在，我们将保留模型参数的默认设置。
结果如下:
LightGBM 是这里的赢家。它具有最佳的平均交叉验证分数，并且交叉验证分数的低标准偏差值表明模型在输入数据变化时的稳健性。
特征重要性和模型可解释性
我们将使用排列重要性来检查模型的特征重要性。它通过删除变量或移动行来检查模型结果的变化量，从而确定输入变量的权重。
报价难度、会员时长、收入、报酬、性别似乎是最重要的特征。
接下来，我们将查看一个使用 lime 的预测示例，以解释模型如何进行预测。
验证数据行预测的示例说明:
决策界限似乎或多或少地证实了我们在 EDA 阶段开发的直觉。
性能调整
在本节中，我们将使用 RandomizedSearchCV 通过调整 LightGBM 模型的参数来进一步提高模型得分。
- num_leaves:较大的 num_leaves 值有助于提高准确性，但可能会导致过度拟合。为了防止这种情况，我们应该让它比 2^(max_depth).小
- min_data_in_leaf:其最优值取决于训练样本数和 num _ leaf。将其设置为较大的值可以避免树长得太深，但可能会导致欠拟合。实际上，对于大型数据集，将其设置为数百或数千就足够了。
- max_depth: max_depth 用于显式限制树的深度。
- boosting _ type:“dart”通常提供更好的准确性
与使用默认参数的未优化模型的分数(0.79)相比，我们可以优化模型以获得更好的分数(. 80)。如果我们与朴素预测相比，使用这种模型可以大大提高我们成功报价的机会，而不仅仅是随机分配报价。
结论
倒影:
- 清理抄本数据是这个项目最具挑战性的方面之一。
- 优惠的难度和奖励金额以及会员期限、收入和客户年龄是决定优惠成功几率的最重要因素。
- 折扣优惠类型、更长的持续时间、更低的难度是增加成功优惠概率的一些优惠属性。
- 一些客户认为增加响应概率的原因是更高的收入(> 60 k)、更长的会员资格(甜蜜点在 1 到 2.5 年的范围内)、女性和更高的年龄(> 40 岁)。
改进空间:
- 仍然有进一步改进模型性能的空间。我们可以简单地尝试其他高性能模型，如 XGboost、Catboost，看看它们是否比 LightGBM 更通用。另一个常用于提高模型性能的策略是将一堆模型堆叠在一起。此外，由于这是一个不平衡的数据集，我们当然可以探索的另一个选项是SMOTE——从少数类中过采样数据。
- 使用交易数据来预测客户的反应会导致数据泄露。但是，与客户购买量相关的历史数据可以帮助我们更好地预测客户对报价的反应。例如，如果一个客户的平均支出比普通客户高得多，向他们发送低难度的报价就没有多大的商业意义。我们宁愿把高难度的报价发给他们。此外，根据目前的数据，很难判断信息提供是否有任何影响。将客户的历史平均消费率与优惠期的平均消费率进行比较有助于我们确定信息性优惠的效果。
- 有很多情况下，顾客没有看就利用了优惠。从商业角度来看，我们最好避免发送这些报价。在我们的分析中，我们找不到有助于预测这部分客户的属性。然而，一个解决办法是提供优惠券。这样，只有希望利用优惠的客户才会完成优惠。
- 由于所有的优惠都没有发送给所有的用户，我们可以通过将所有的优惠映射到所有的客户来创建一个数据集，然后预测客户对每个优惠的反应。如果一个客户可能会对多个报价做出反应，我们只需要选择那些能带来最大业务的报价，例如，难度最高或报酬率最低的报价。
本次实现的全部代码可以在 这里 访问。
扫一扫
1617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
