用于数据分析的实用SQL

Pandas是一个非常流行的数据分析工具。它具有许多有用的功能，它＆＃39;在测试和广泛接受的战斗中。但是，Pandas并不总是这项工作的最佳工具。

自20世纪70年代以来，SQL数据库已经存在。世界上一些最聪明的人致力于快速有效地切割，骰子，获取和操纵数据。 SQL数据库已经出现了很长的路，许多开发人员和数据科学家丢失了他们已经拥有的数据库可以做的事情！

在本文中，我演示了如何使用SQL来执行快速和高效的数据分析。

想象一个简单的表格，其中一个用户，每个用户都有一个用户名和指示如果用户被激活或不激活。一个简单的数据分析任务是回答有多少激活和灭活的用户？

创建表用户（ID串行主键，用户名文本不是null，激活的布尔值不是null）;插入用户（用户名，激活）选择MD5（随机（）::文本）::文本作为用户名，随机（）＆gt; 0。 9从Generate_series激活（1,1000000）;

#bech.py​​来自memory_profiler导入配置文件@profile def运行（）：＃todo：用代码替换为基准打印（＆＃39;做工作！＆＃39;）如果_name__ ==＆＃34; __主要__＆＃34; ： 跑步 （）

$ Python Bench.py​​工作！文件名：BENCH.PY线＃MEM USAGE INTEMENTING LINE目录===================================== ======================= 3 38.9 MIB 38.9 MIB 1 @profile 4 def run（）：5＃todo：用代码替换为基准6 38.9 mib 0.0 MIB 1打印（＆＃39;做工作！＆＃39;）

您可以在本文中或在内存 - Profiler文档中找到有关此方法的更多详细信息。

将PSYCOPG2导入PANDAS作为PD Connection = PSYCOPG2。连接（DBNAME =＆＃39; db＆＃39;）与连接。 CURSOR（）作为光标：光标。执行（＆＃39;从用户选择*＆＃39;）df = pd。 dataframe（光标。fetchall（），列= [＆＃39; id＆＃39;，＆＃39;用户名＆＃39;，＆＃39;激活的＆＃39;]，）结果= df。 groupby（by =＆＃39;激活＆＃39;）。 count（）打印（结果）

脚本使用PSYCOPG2创建与数据库的连接。然后，它将数据从用户表中获取到熊猫DataFrame，并调用GroupBy来获取激活和灭活用户的计数。

（venv）$ python test_pandas_naive.py id username激活false 99071 99071 filename：test_pandas_naive.py line＃mem用法增量出现行目录==================== ======================================== 3 38.8 MIB 38.8 MIB 1 @profile 4 def RUN（）：5 41.2 MIB 2.3 MIB 1导入PSYCOPG2 6 78.2 MIB 37.1 MIB 1进口熊猫作为PD 7 8 78.6 MIB 0.4 MIB 1连接= PSYCOPG2.CONNECT（DBNAME =＆＃39; DB＆＃39;）9 78.6 MIB 0.0 MIB 1使用Connection.Cursor（）作为游标：10 179.9 MIB 101.3 MIB 1 Cursor.execute（＆＃39;从用户＆＃39选择*）11 386.0 MIB 12.7 MIB 2 DF = PD.DATAFMRAME（12 373.4 MIB 193.5 MIB 1 Cursor.FetchAll（），13 373.4 MIB 0.0 MIB 1列= [＆＃39; ID＆＃39 ;,＆＃39;用户名＆＃39 ;,＆＃39;活化＆＃39;]，14）15 16 386.0 MIB 0.0 mib 1结果= df.groupby（by =＆＃39;激活的＆＃39;）。count（）17 386.0 mib 0.0 mib 1 print（结果）

要查看程序的内存使用，我们使用包内存 - profiler。我们过去使用了这种技术，找到了使用Python将数据加载到PostgreSQL中的最快方法。

输出显示程序中每行的整体内存用法，以及列中每行添加的附加内存;递增＆＃34;。此程序的输出显示一些有趣的查找：

熊猫单独消耗〜37米的内存：只是进口熊猫，在甚至做任何事情之前，消耗大量的内存。有关比较，导入PSYCOPG2仅为程序添加2.3MB内存。

将数据提取到内存中消耗了额外的〜300MB：当我们将数据寄出到内存中时，然后进入熊猫DataFrame时，程序占用了300MB。参考，数据库中表的大小仅为65MB。

如果我们忽略分析器本身所消耗的38MB，则该程序消耗了347MB的内存，并且在没有Profiler的情况下执行此脚本需要1.101s完成。

我们的快速分析表明，获取数据消耗最多的内存。要优化它，我们可以尝试获取更少的数据。例如，我们and＃39; t真的使用username列，所以也许我们不能从数据库中获取它：

（VENV）$ Python Test_Pandas.py ID激活False 900029 True 99971 Filename：test_pandas.py＃MEM用法增量出现行目录======================== ================================ 3 38.7 MIB 38.7 MIB 1 @profile 4 def Run（）：5 41.0 MIB 2.3 MIB 1进口PSYCOPG2 6 78.3 MIB 37.2 MIB 1进口熊猫作为PD 7 8 78.6 MIB 0.3 MIB 1连接= PSYCOPG2.CONNECT（DBNAME =＆＃39; DB＆＃39;）9 78.6 MIB 0.0 MIB 1带连接.Cursor（）作为游标：10 132.1 MIB 53.6 MIB 1 CURSOR.execute（＆＃39;从用户激活，从用户激活）11 142.1 MIB -90.7 MIB 2 DF = PD.DATAFARMARMA（12 232.8 MIB 100.7 MIB 1 CURSOR.FECHER（ ），13 232.8 MIB 0.0 MIB 1列= [＆＃39; ID＆＃39;，＆＃39;激活的＆＃39;]，14）15 16 142.1 MIB 0.0 MIB 1结果= df.groupby（by =＆＃39 ;激活＆＃39;）。COUNT（）17 142.1 MIB 0.0 MIB 1打印（结果）

通过显式向查询列表列出列表并仅获取我们需要的东西，程序现在仅消耗232MB，或193MB，而不会出于分析器的开销。这是从之前尝试消耗347MB的内存的尝试改进。

与Profiler的脚本执行0.839秒，以前的程序占用1.1s。

程序中最多的内存仍将数据被获取到内存中。如果不是首先获取数据并使用熊猫聚合，则会汇总数据库中的数据，并从结果中创建Pandas DataFrame：

$ python test_db.py被激活的cnt 0 false 900029 1 true 99971文件名：test_db.py＃mem使用增量出现线目录========================= =============================== 3 38.6 MIB 38.6 MIB 1 @Profile 4 DEF RUN（）：5 41.0 MIB 2.3 MIB 1进口PSYCOPG2 6 78.0 MIB 37.0 MIB 1进口熊猫PD 7 8 78.3 MIB 0.4 MIB 1连接= PSYCOPG2.CONNECT（DBNAME =＆＃39; DB＆＃39;）9 78.3 MIB 0.0 MIB 1带连接.Cursor（）作为游标：10 78.3 MIB 0.0 MIB 1 CURSOR.execute（＆＃39;＆＃39;＆＃39; 11选择被激活的，计数（*）从用户13组的CNT 12由激活的14＆＃39;＆＃39; ＆＃39;）15 78.3 mib 0.0 mib 2结果= pd.dataframe（16 78.3 mib 0.0 mib 1 cursor.fetchall（），17 78.3 mib 0.0 mib 1列= [＆＃39;活化＆＃39;＆＃39 ; CNT＆＃39;]，18）19 79.3 MIB 1.0 MIB 1打印（结果）

与以前的尝试相比，这是一个大的飞跃。在数据库中执行处理，并获取聚合结果仅在79MB内存时，如果我们删除了分析器的开销，则为40MB。这是一个很大的改善！

在没有Profiler的情况下执行脚本0.380s，这是前一个程序的两倍，占用0.839s。

此时唯一重要的内存Hog是Pandas本身。只是为了乐趣和参考，让＆＃39; s看内容没有熊猫的消耗量：

$ python test_db_plain.py（[（false，900029），（true，99971）]，）文件名：test_db_plain.py＃mem使用增量出现行目录========================= ======================================= 3 38.9 MIB 38.9 MIB 1 @profile 4 def运行（）：5 41.2 MIB 2.4 MIB 1导入PSYCOPG2 6 7 41.7 MIB 0.5 MIB 1 CONNECTION = PSYCOPG2.CONNECT（DBNAME =＆＃39; DB＆＃39;）8 41.7 MIB 0.0 MIB 1带CONNECTION.CURSOR（）作为光标： 9 41.7 MIB 0.0 MIB 1 CURSOR.execute（＆＃39;＆＃39;＆＃39; 10选择被激活，计数（*）从用户12组的CNT 11由激活的13＆＃39;＆＃39;＆＃ 39;）14 41.7 MIB 0.0 MIB 1结果= CURSOR.FECHETALL（），15 16 41.7 MIB 0.0 MIB 1打印（结果）

删除熊猫并将结果保存为元组的Python列表后，如果我们忽略分析器开销，则程序会使用41MB的内存，或仅为2.8MB。这是我们开始的地方的巨大差异！

时序也很低。如果没有配置文件，则该程序仅在0.114秒内完成。那个＆＃39; s 70％少于使用熊猫的尝试，总体上90％比第一个程序快90％。

该基准测试并未提及数据库本身消耗的内存 - 这是故意的。数据库通常使用可配置的内存量，而不是在内部管理不同缓冲区和系统组件之间的分配。多年来，数据库很擅长管理他们的记忆，所以你赢了＆＃39; t必须。无论您是如何决定使用数据库，内存都已付出代价，所以您可以使用它！

消耗很多内存的程序是巨大的痛苦。开发人员需要强大的开发环境，迭代速度较慢，整个过程需要更多时间。从基础设施的角度来看，资源成本金钱，以及比较越多，你必须支付的越多。成本很快堆积。

所有这些都不是说熊猫是不必要的，或者它可以被替换。 Pandas提供了很大的好处，证明了自己非常有价值。数据库可以说同样的事情。

要利用两个世界，并创建也很快的轻量级节目，一起使用SQL和Pandas！

我专注于熊猫和Numpy，因为它们是最受欢迎的，但文章中描述的概念适用于其他工具和语言，如R，Julia，Matlab，SA等。为了使参数更引人注目，我包括交互式十六进制笔记本，您可以自己尝试。

SQL查询语言在40多年前发明，它是查询关系数据的最流行的语言。 SQL在ANSI标准中定义，但流行数据库引擎之间仍然存在细微差异，例如PostgreSQL，MySQL，Oracle，SQL Server等。

选择＆lt;表达式＆gt;来自＆lt; tables＆gt;加入＆lt;到其他表＆gt;在＆lt;连接条件＆gt;其中＆lt;通过＆lt;表达式＆gt;＆lt;谓词＆gt;通过＆lt;表达＆gt;限制＆gt;

在PostgreSQL中，Select子句真的是强制性的，因此您可以混合和匹配以执行您想要的。

它有时有助于将大型查询拆分为较小的步骤。使用SQL，您可以定义公共表表达式或＆＃34; CTE＆＃34;简而言之，与带有条款：

用电子邮件（选择＆＃39; [email protected]'作为电子邮件）选择*来自电子邮件;电子邮件───────────────────────────────────────────────────────────────────

您可以在单个查询中拥有多个CTE＆＃39; s，它们甚至可以相互依赖：

通过电子邮件（选择＆＃39; [email protected]'作为电子邮件），randalized_emails（从电子邮件中选择较低（电子邮件）作为电子邮件）选择* from normalized_emails;电子邮件───────────────────────────────────────────────────────────────────

常见的表格表达式是将大查询分成较小的块的重要方法，执行递归查询甚至缓存中间结果！

生成数据非常方便。有时您需要生成练习的数据，有时您需要生成时间序列或小表加入。有几种方法可以在SQL中生成数据：

用dt（选择1作为id，＆＃39; haki＆＃39;作为名称联盟全部选择2，＆＃39; benita＆＃39;）选择*来自dt; ID│名称────────────1│haki 2│benita

连接查询结果非常常见，但对于生成数据可能有点繁琐。

dt为（选择* from（值（1，＆＃39; haki＆＃39;），（2，＆＃39; benita＆＃39;））作为t（id，name））选择* from dt;

使用值关键字您可以提供行列表，然后使用A＆＃34定义名称和类型;表别名列表＆＃34; t（..）。 t可以是任何名称。当您需要生成小组数据时，或者作为文档调用它，＆＃34;常量表＆＃34;

使用dt（选择Untnest（array [1,2]）为n）选择* from dt; n───12

这比值更加限制，因为它只能生成相同数据类型的一维表，但我们将稍后使用它。

使用dt（从generate_series（0,5）为t（n））选择* from dt; n──0 1 2 3 4 5

函数生成_series接受三个参数：启动，停止和步骤。在上面的示例中，我们未指定步骤，因此使用默认值1。我们可以提供不同的步骤来生成不同的系列：

使用dt（从generate_series（0， - 开始10， - stop 2 - stop 2 - stop 2 - stop 2 - stop 2 - stop 2 - stop 2 - stop 2 - stop）select * from dt; n────0 2 4 4 6 8 10

函数生成_series不仅限于整数，也可以用于其他类型。一个常见的例子正在生成日期范围：

与daterange是（选择*从Generate_series（＆＃39; 2021-01-01 Utc＆＃39; :: timestamptz， - start＆＃39; 2021-01-02 Utc＆＃39; :: timestamptz， - 停止间隔＆＃39; 1小时＆＃39; - 步骤）作为t（hh））选择*来自daterange; hh────────────────────2021-01-0100：00：00 + 00 2021-01-01 01：00：00 + 00 2021 -01-01 02:00：00 + 00 ... 2021-01-01 22：00：00 + 00 2021-01-01 23：00：00 + 00 2021-01-02 00：00：00 + 00

要生成24小时范围，我们将使用开始和结束数据提供Generate_series，并将步骤设置为1小时的时间间隔。

如上所述，Generate_series是A＆＃34;表功能＆＃34;表函数有一个小技巧，以包括结果中的行号：

与daterange是（选择* from generate_series（＆＃39; 2021-01-01＆＃39; :: timestamptz， - start＆＃39; 2021-01-02＆＃39; :: timestamptz， - 停止间隔＆＃ 39; 1小时＆＃39; - 步骤），属于t（hh，n））选择*来自daterange; HH│Ñ────────────────────────┼────2021年1月1日00：00：00 + 00│1 2021-01- 01 01：00：00 + 00│22021-01-01 02：00：00 + 00│3... 2021-01-01 22:00：00 + 00│232021-01-01 23:00： 00 + 00│242021-01-02 00：00：00 + 00│25

要生成随机数，PostgreSQL提供了一个随机函数，返回0到1之间的值：

- 0到100之间的随机浮动选择随机（）* 100; 59。 17508391168769 - 1到100之间的随机整数选择CEIL（随机（）* 100）; 59 - 随机整数在11到100之间选择10 + CEIL（随机（）* 90）; 59.

它＆＃39;是使用圆形而不是CEIL或地板来生成一系列整数的常见错误。使用圆形可能产生不一致的分布。考虑以下查询以在0 - 4范围内生成随机整数，而不是CEIL：

选择圆形（随机（）* 3）为n，count（*）从generate_series（0,1000）组1; n│count─────────0│1501│3282│341 3│182

请注意，值0和3的内容如何小于1和2.使用圆形，随机值小于0.5将被舍入为0，随机数大于2.5将舍入为3，而例如随机值将舍入3，而随机值0.5和1.5之间将四舍五入到1.这使得边缘不太可能出现。

可以通过舍入或向下来解决这个问题。考虑使用CEIL的相同查询：

选择CEIL（随机（）* 3）为n，count（*）从generate_series（0,1000）组1; n│计数──────────1│3282│3393│334

您可以使用随机函数从值列表中选择一个随机值：

选择（array [＆＃39;红色＆＃39;＆＃39;绿色＆＃39;，＆＃39;蓝色＆＃39;]）[ceil（comand（）* 3）]作为generate_series的颜色（1,5 ）;颜色──────绿色绿色蓝色蓝色

该表达式定义了一种颜色数组，然后随机使用从阵列中获取随机元素。请注意，在PostgreSQL中，阵列从1开始：

- 在PostgreSQL阵列开始于1选择（阵列[＆＃39;红色＆＃39;＆＃39;绿色＆＃39;＆＃39;蓝色＆＃39;]）[1];阵列──────红色

在培训模型时，对表的随机部分进行采样是一个很常见的。提取表的随机部分的简单方法是随机与限制组合：

DB =＃与样本（通过随机（）限制10000从用户订购的*）选择count（*）从样本中选择count（*）;计数──────10000（1行）时间：205.643毫秒

从表中以随机顺序排序，从表中抽出10K随机行，然后拍摄前10k行。

随机使用样本数据很大，但对于非常大的数据集，它可能效率低下。 PostgreSQL提供了采样比例的其他比例的方法，这些方法更适合大表。

PostgreSQL提供了两个采样方法，系统和伯尔瑙。要对表进行采样，请使用FROM子句中的Tablesample关键字，并提供采样方法以及它＆＃39; s的参数。例如，使用系统采样方法采样10％的表：

DB =＃具有样本（选择*来自用户Tablesample System（10））从样本中选择Count（*）;计数──────95400（1行）时间：13.690毫秒

系统采样方法通过采样块而不是行工作，这使得它非常快。我们采样的表包含1米行，并且样本略小于100k行。对于大型数据集＆＃39; s并不罕见，以损害性能的准确性。

DB =＃与样本（选择*来自用户TablesampleBernoulli（10））从样本中选择Count（*）;计数───────100364（1行）时间：54.593毫秒

与系统采样方法不同，Bernoulli在行级别工作，使其能够慢一点，但结果更好分布式。

这些是使用不同的采样方法采样10％的表中的2％的时间：

分析数据时的常用任务是拆分数据集以进行培训和测试。训练数据集用于训练模型，测试数据集用于评估模型。

要将您看到的内容与练习甚至练习，请使用某些随机数据生成一笔交易表：

将表交易创建为SELECT ID，＆＃39; 2021-01-01＆＃39; :: date +间隔＆＃39; 1天＆＃39; * CEIL（随机（）* 365）作为billed_at，round（10 + 90 * randu（））作为charged_amount，wandul（）＆gt; 0。 6从Generate_series（1,10）为ID订单的报告（1,10）按1;

交易表包括交易的日期和金额，并指示交易是否被报告为欺诈行为。

在0到365至1月1日之间添加随机数，2021年，在那年内产生随机日期。

产生我们想要估算的参数。在我们的假数据中，我们希望有40％的欺诈交易。使用表达式，我们生成了一个布尔值，它将评估为tife〜40％的时间。

db =＃从事务中选择*; ID││billed_at│charged_amount reported_as_fraud────┼─────────────────────┼──────────────── ┼───────────────1│2021-05-22 00:00:00│54│t 2│2021-05-31 00:00:00│63 │f3│2021-11-11 00:00:00│26│t 4│2021-07-04 00:00:00│64│t 5│2021-02-27 00:00:00│90│ 6│2021-05-21 00:00:00│20│t 7│2021-07-29 00:00:00│69│t 8│2021-02-24 00:00:00│20│f9│ 2021-05-07 00:00:00│36│F10│2021-05-05 00:00:00│38│f

要测试将事务分类为欺诈性的模型，我们希望将表拆分为培训和测试数据集。 这样做的一种方法是添加列，但我们将要创建两个单独的表格。 要创建类似于PostgreSQL中的现有表的表，可以使用以下命令： 创建表Transaction_training作为没有数据的表事务; 创建表Transaction_test作为没有数据的表事务; 这是一个非常方便的语法！ 我们只是告诉PostgreSQL创建类似于另一个表的表，但没有数据。 接下来，我们希望将数据拆分在交易表之间 ......