Postgres数据科学：使用PL / R与Magick的分析和图形输出

欢迎来到＆＃34的第2集; PostgreSQL数据Pontiff＆＃34的乐园;系列！在本分期付款中，我的目标是实现三个目标。首先，您应该了解PostgreSQL的SQL语言如何通过内置聚合和其他功能（如公共表表达式（CTE）和窗口函数）执行有趣的数据分析。其次，您将了解本地SQL如何与PL / R中的R代码相结合。最后，我将展示如何使用pl / r挖掘r语言＆＃39; s生成可视图形的能力，这有助于了解计算值。所以让＆＃39;开始。

到目前为止，您可能会想知道，＆＃39; s与集中的标题？为什么＆＃34;热magick＆＃34 ;?好吧，首先，我以为它是吸引人的;-)。但是，严重，它有一个目的。我将在剧集的其余部分使用的示例数据是NOAA气象站在美国周围各个位置捕获的历史日常最高温度数据。这解释了＆＃34;热辣＆＃34;部分。参考＆＃34; magick＆＃34;是一个叫做的弓，叫做＆＃34; magick＆＃34;它使用libmagick生成和捕获绘制的数据。更稍后的更多信息。

一个免责声明在这里有所相关，这是本系列的其余集。虽然我喜欢有真实的数据来说明在一起使用的PostgreSQL和R的功能，但我不是一个合适的和＃34;数据科学家＆＃34;特别是本文的实例，另外我不是A＆＃34;气候科学家＆＃34;所以请明白我的观点不是为了气候变化的明确结论，它是炫耀我正在使用的工具的很酷功能。

创建表temp_obs（站文本，名称文本，obs_date日期，tmax int，tmin int，tobs int，主键（Obs_date，Station））;

大多数字段名称应该是非常不言自明的。 ToB柱表示在一天的特定时间观察到的温度。在至少一个似乎是1600（下午4点）的案例中，但发现信息很乏味，并且我没有尝试查找每个车站，因为我打算将分析基于Tmax（即每日最大值）温度）。

下载的CSV文件可以使用诸如：的命令加载到表中。

其中＆＃39;＆lt; path＆gt; /＆lt; filename＆gt;＆＃39;指向下载的CSV文件。该文件需要在Postgres用户读取的PostgreSQL Server上的位置，以便为此工作。如果您的工作站（以及CSV文件位置）与数据库服务器不同，则还可以使用PSQL \ Copy命令加载文件。请参阅细节的PostgreSQL文档，但命令将看起来像上面的那样。

多年来，从PL / R捕获图形输出的话题是粘性点的一点。选择并不伟大。您可以将绘图图像写入存储系统上的文件，或者您可以使用=诸如以下内容的复杂代码，以捕获图像并将其返回给SQL客户端：

库（Cairodevice）库（RGTK2）PixMap＆lt; - gdkpixmapnew（w = 500，h = 500，深度= 24）Ascairodevice（PixMap）＆lt;生成一个曲线＆gt; plot_pixbuf＆lt; - gdkpixbufgetfromwrawable（null，pixmap，pixmap $ getColormap（），0,0,0,0,500,500）缓冲器＆lt; - gdkpixbufsavetobufferv（plot_pixbuf，＆＃34; jpeg＆＃34;，字符（0），字符（0））$缓冲返回（缓冲区）

在这个例子中，除了之外的所有内容都会生成一个曲线＆gt;是从内存中捕获500 x 500像素图像并返回它所需的代码。这效果足够好，但有两个缺点：

一旦了解模式，它就足够了才能复制。但通常，您的数据库将在A＆＃34上运行;无头＆＃34;系统（即没有X窗口系统）。使用虚拟帧缓冲区（VFB）可以解决后一种问题，但不是每个DBA都有所需的访问，以确保vfb正常运行和配置。

我一直在寻找一个更好的答案。相对较新的（至少与2003年以来一直存在的PL / R相比）＆＃34; Magick＆＃34;包只是需要的东西。上面的代码段可以像这样重写：

图书馆（Magick）图＆LT; - image_Graph（宽度= 500，高度= 500，Res = 96）＆lt;生成一个曲线> dev.off（）图像_write（format =＆＃34; png＆＃34;）

这更简单，更可读，具有较少的依赖性，并且重要的是不需要X窗口系统，也不需要VFB。

好的，所以现在预赛结束了，我们有数据，让我们的第一个看看它。

首先，我们将定义名为Rawplot（）的PL / R函数。该函数将通过观察日期获取TMAX，了解专门请求的气象站。然后它将绘制数据并将结果图形输出返回为二进制流到SQL级呼叫者。

创建或替换功能Rawplot（Stanum Text，Staname Text，Startdate Date，Enddate Date）返回Bytea AS $$库（RPOSTGRESQL）库（Magick）库（GGPlot2）图3（GGPlot2）图1LT; Image_Graph（宽度= 1600，高度= 900，Res = 96）SQLTMPL =＆＃34;选择OBS_DATE，来自TEMP_OBS的TMAX，其中TMAX不是NULL和Station =＆＃39;％s＆＃39;和obs_date＆gt; =＆＃39;％s＆＃39;和obs_date＆lt; ＆＃39;％s＆＃39;订购1＆＃34; sql = sprintf（sqltmpl，stanum，startdate，enddate）drv＆lt; dbdriver（＆＃34; postgreSql＆＃34;）conn＆lt; - dbconnect（drv，user =＆＃34; postgres＆＃34;，dbname =＆＃ 34;演示＆＃34;，端口=＆＃34; 55610＆＃34;，host =＆＃34; / tmp＆＃34;）数据＆lt; - dbgetquery（conn，sql）dbdisconnect（conn）数据$ obs_date＆lt; - as.date（数据$ obs_date）数据$ mean_tmax＆lt; - rep（均值（数据$ tmax），长度（数据$ tmax））打印（ggplot（数据，aes（x = obs_date））+ scale_color_manual（值= c（ ＆＃34; Darkorchid4＆＃34 ;,＆＃34; Darkgreen＆＃34;））+ Geom_point（AES（Y = Tmax，Color =＆＃34; Tmax＆＃34;））+ Geom_line（AES（y = mean_tmax，颜色=＆＃34;均值＆＃34;），size = 2）+实验室（标题= sprintf（＆＃34;最大温度乘日 - ％s＆＃34; staname），y =＆＃34; temp（f） ＆＃34;，x =＆＃34;日期＆＃34;）+ theme_bw（base_size = 15）+ geom_smooth（aes（y = tmax）））dev.off（）映像_write（格式=＃34; png＆ ＃34;）$$语言plr;

有很多吸收，所以让＆＃39;一次将其分解在几行。

创建或替换功能Rawplot（Stanum Text，Staname Text，Startdate日期，Enddate Date）返回Bytea AS $$语言PLR;

SQL函数声明代码与PostgreSQL中定义的任何SQL函数大致相同。它从创建或替换才能允许我们重新定义函数而不是SQL接口，而无需首先删除它。在这里，我们将四个参数传递到用于选择感兴趣的气象站的功能中。这些为绘图提供详细的站名称，并将行限制为所需的日期范围。

请注意，该函数返回数据类型Bytea，这意味着＆＃34;字节数组＆＃34;或换句话说二进制。 PL / R关于返回Bytea的功能具有特殊功能。 PL / R将以二进制形式将R数据类型转换为其对应的PostgreSQL数据类型。从R返回绘图对象时，图像二进制文件被一些识别信息包裹，必须剥离以获得图像本身。但后来更多。

前面提到的Magick包将用于捕获绘图图形。 GGPLOT2用于创建具有大量功率和灵活性的漂亮看板。我们可以单独花费几个关于GGPOT的博客系列，但有足够的信息可以使用GGPlot可用，因此我们将在大多数情况下光调其使用情况。

RPOSTGRESQL包是严格来说，在PL / R功能中不需要。这里使用的原因是从R客户端测试代码时需要。正如往往的情况一样，方便在R中直接开发R代码，然后将其粘贴到PL / R功能中。 PL / R包括兼容功能允许在从PostgreSQL查询数据时要使用的RPOSTGRESQL语法。

也如前所述，几行R代码使用Magick包来捕获并返回绘图图形。那些是以下内容：

图＆lt; - image_graph（宽度= 1600，height = 900，res = 96）[......＆＃34;获取数据＆＃34;和＃34;生成绘图＆＃34;代码进入这里......] dev.off（）映像_write（图，格式=＆＃34; png＆＃34;）

下一部分代码查询感兴趣的数据并根据绘图需要格式化：

sqltmpl =＆＃34;选择OBS_DATE，来自TEMP_OBS的TMAX，其中TMAX不是NULL和Station =＆＃39;％s＆＃39;和obs_date＆gt; =＆＃39;％s＆＃39;和obs_date＆lt; ＆＃39;％s＆＃39;订购1＆＃34; sql = sprintf（sqltmpl，stanum，startdate，enddate）drv＆lt; dbdriver（＆＃34; postgreSql＆＃34;）conn＆lt; - dbconnect（drv，user =＆＃34; postgres＆＃34;，dbname =＆＃ 34;演示＆＃34;，端口=＆＃34; 55610＆＃34;，host =＆＃34; / tmp＆＃34;）数据＆lt; - dbgetquery（conn，sql）dbdisconnect（conn）数据$ obs_date＆lt; - as.date（数据$ obs_date）数据$ mean_tmax＆lt; - rep（均值（数据$ tmax），长度（数据$ tmax））

首先，我们看到SQLTMPL，它是主要数据访问SQL查询的模板。退回的站，第一个日期和结束日期有更换的参数。下一行将参数分配到模板中以创建存储在SQL变量中的完全形成的SQL。

接下来的四行连接到postgreSQL并执行查询。只有dbgetquery（）行在pl / r中执行任何内容，但如果我们从R客户端查询，则需要其他三行。

Stanza中的最后两行确保我们的日期列以R原生形式，并为我们生成/填充AMEAN列。

print（ggplot（数据，aes（x = obs_date））+ scale_color_manual（值= c（＆＃34; darkorchid4＆＃34;＆＃34; darkgreen＆＃34;））+ geom_point（aes（y = tmax，color = ＆＃34; tmax＆＃34;））+ geom_line（aes（y = mean_tmax，color =＆＃34;均值＆＃34;），size = 2）+ labs（title = sprintf（＆＃34;最大温度 - ％s＆＃34;，staname），y =＆＃34; temp（f）＆＃34;，x =＆＃34;日期＆＃34;）+ theme_bw（base_size = 15）+ geom_smooth（aes（y = Tmax）））））

如上所述，使用GGPOT的细节留给读者解码。然而，一点值得一提，部分原因是我花了很长时间才弄清楚，是GGPlot（）呼叫必须在打印（）呼叫中包裹，如图所示。否则返回的绘图将是空的。我终于找到了这个事实埋藏在GGPlot文档中的一个原因，说明＆＃34;如果要在函数或循环中绘制绘图，请明确调用print（）。＆＃34;创建PL / R功能时，R代码正文放置在名为R功能内，因此此规则适用。

选择OctEt_Length（PLR_Get_Raw（Rawplot（＆＃39; USC00042706＆＃39;＆＃39; el Cajon，Ca＆＃39; 1979-101＆＃39 ;,＆＃39; 2020-10- 01＆＃39;））））; Octet_Length --------------369521（1行）DO $$宣布Stanum Text =＆＃39; USC00042706＆＃39 ;; staname text =＆＃39; el cajon，ca us＆＃39 ;; Startdate Date =＆＃39; 1979-10-01＆＃39 ;; enddate日期=＆＃39; 2020-10-01＆＃39 ;; l_lob_id oid;记录;在Select PLR_GET_RAW（RAWPOROT（Stanum，Staname，StartDate，Enddate）中开始为r为img循环l_lob_id：= lo_from_bytea（0，r.img）;执行lo_export（l_lob_id，＆＃39; /tmp/rawplot.png'）;执行lo_unlink（l_lob_id）;结束循环;结尾; $$;

第一个示例显示返回图像的总八位字母（即，以字节为单位）为369521.第二查询是从查询中获取流式二进制文件并将其指向磁盘上的文件中的稍微复杂的方法。为什么不直接在您说的PL / R功能中创建文件？嗯，这是一个简单的方法，以抓住这个博客的目的。如果我正在为某些＆＃34做这项工作;现实世界＆＃34;目的，推测我将把图像流向某人＆＃39; s浏览器或类似的东西。

我们已经看到了原始数据看起来像什么，这是一个很好的开始。但现在我们将使用大多数旧的SQL潜水有点深入，尽管仍然被PL / R功能包裹，以便我们可以想象结果。此功能为我们提供了每年最高温度为100度或更高的日子数。

创建或替换功能count100plus（Stanum文本，staname文本，启动日期，enddate日期）返回Bytea AS $$库（RPOSTGRESQL）库（Magick）库（GGPlot2）图3（GGPlot2）图1R; Image_Graph（宽度= 1600，高度= 900，Res = 96）SQLTMPL =＆＃34;选择extract（从Obs_date）的extract（从Obs_date），计数（tmax）为来自temp_obs的tcnt，其中tmax不是null和Station =＆＃39;％s＆＃39;和obs_date＆gt; =＆＃39;％s＆＃39;和obs_date＆lt; ＆＃39;％s＆＃39;和tmax＆gt; = 100组1级1＆＃34; sql = sprintf（sqltmpl，stanum，startdate，enddate）drv＆lt; dbdriver（＆＃34; postgreSql＆＃34;）conn＆lt; - dbconnect（drv，user =＆＃34; postgres＆＃34;，dbname =＆＃ 34;演示＆＃34;，端口=＆＃34; 55610＆＃34;，host =＆＃34; / tmp＆＃34;）数据＆lt; - dbgetquery（conn，sql）dbdisconnect（conn）数据$ mean_tcnt＆lt; - rep（平均值（数据$ tcnt），长度（数据$ tcnt））打印（ggplot（数据，aes（x =年））+ scale_color_manual（值= c（＆＃34; darkorchid4＆＃34;＆＃34; darkgreen＆＃34; darkgreen＆ ＃34;））+ geom_point（aes（y = tcnt，color =＆＃34; tcnt＆＃34;））+ geom_line（aes（y = mean_tcnt，color =＆＃34;均值＆＃34;平均值＆＃34;），size = 2 ）+实验室（标题= Sprintf（＆＃34;按年份的100+学位（f）天数 - ％s＆＃34; staname），y =＆＃34; count＆＃34;，x =＆＃34;年和＃34;）+主题_BW（base_size = 15）+ geom_smooth（aes（y = tcnt）））dev.off（）映像_write（format =＆＃34; png＆＃34;）$$语言plr; Do $$宣布Stanum Text =＆＃39; USC00042706＆＃39 ;; staname text =＆＃39; el cajon，ca us＆＃39 ;; Startdate Date =＆＃39; 1979-10-01＆＃39 ;; enddate日期=＆＃39; 2020-10-01＆＃39 ;; l_lob_id oid;记录;在select plr_get_raw中开始（count100plus（stanum，staname，startdate，enddate））作为img循环l_lob_id：= lo_from_bytea（0，r.img）;执行lo_export（l_lob_id，＆＃39; /tmp/count100plus.png'）;执行lo_unlink（l_lob_id）;结束循环;结尾; $$;

sqltmpl =＆＃34;选择extract（从Obs_date）的extract（从Obs_date），计数（tmax）为来自temp_obs的tcnt，其中tmax不是null和Station =＆＃39;％s＆＃39;和obs_date＆gt; =＆＃39;％s＆＃39;和obs_date＆lt; ＆＃39;％s＆＃39;和tmax＆gt; = 100组1级1＆＃34;

此SQL语句是在大于或等于100 f的日子逐年汇总核心的核心工作。

多年前的计算机（或者他们是人或机械设备，而不是电子）在贝尔实验室展开统计过程控制（SPC）。后来被爱德华州德军推广和发展。阅读更多关于SPC历史的信息。我们赢得了所有深度，但刚刚说出以下分析来自SPC技术。

这种分析的一个场所是数据遵循正常分布。为了确保至少一个近似的正态分布，SPC通常依赖于中央极限定理。换句话说，通过将原始数据分组到样品中，所得到的数据倾向于所需的形式。

我们需要用温度数据解决的另一个问题是它季节性变化。我们不能很好地期待第1周（1月初）的最高温度与第32周（8月中旬）相同。采取的方法处理这是标准得分或Z分数。

对于DataSet的所有年份，通过ISO周数，确定平均TMAX（XB或＃34; X-Bar＆＃34;）和TMAX值（R）的范围。

对于每周数（1-53），确定总体平均值（有时称为＆＃34;盛大平均＆＃34;或xbb，或＆＃34; x-bar-bar＆＃34;），平均范围（ RB或＆＃34; R-Bar＆＃34;）和标准偏差（SD）。

在进入＆＃34之前请允许我另一个题为;最终＆＃34;解决方案。有时，R代码理想地是通过多个PL / R函数的常见和重复使用。幸运的是Pl / R提供了一种方便的方法来做到这一点。一个特殊的表，plr_modules，如果存在它以包含R函数。这些函数被提取并加载到R解释器上初始化。表PLR_MODULES定义如下

其中modseq用于控制安装顺序，并且莫德斯rc包含要执行的R代码的文本。 PLR_Modules必须通过所有，但是只有数据库管理员拥有和可写的是明智的。请注意，您可以使用Reload_plr_modules（）来强制将PLR_Modules表行的重新加载到当前会话R解释器中。

返回我们手头的问题，以下将创建一个R功能，可以以前一节中描述的方式总结和突变我们的原始数据。

插入plr_modules值（0，$ m $ obsdata＆lt;函数（stanum，startdate，enddate）{库（rpostgresql）库（Reshape2）sqltmpl =＆＃34;在g（年，星期，xb，r）为（选择extract（从Obs_date）作为年份提取（从Obs_date）的提取（从Obs_date），AVG（TMAX）为XB，Max（Tmax） - min（tmax）为来自temp_obs的r，其中tmax不是null和Station =＆＃39 ;％s＆＃39; obs_date＆gt; =＆＃39;％s＆＃39;和obs_date＆lt;＆＃39;％s＆＃39; group by 1,2），s（周，xbb，rb，sd） AS（选择周，AVG（XB）作为XBB，AVG（R）为RB，SDDEV_SAMP（XB）按G组的SD为SD），Z（年，周，ZXB，ZR，XBB，RB，SD）为（选择G.Year，G.Week，（G.XB - S.XBB）/ S.SD作为ZXB，（GR-S.RB）/ S.SD作为ZR，S.XBB，S.RB，S.SD从G加入G.Week = S.Week）选择一年，周，案例当周＆lt; 10那么年::文本|| ＆＃39; -W0＆＃39; ||周::文本|| ＆＃39; -1＆＃39;其他年份::文本|| ＆＃39; -W＆＃39; ||周::文本|| ＆＃39; -1＆＃39;以idate，zxb，0.0作为zxbb，3.0作为ucl，-3.0作为lcl，zr，xbb，rb，sd从z达到1,2＆＃34; sql = sprintf（sqltmpl，stanum，startdate，enddate）drv＆lt; dbdriver（＆＃34; postgreSql＆＃34;）conn＆lt; - dbconnect（drv，user =＆＃34; postgres＆＃34;，dbname =＆＃ 34;演示＆＃34;，port =＆＃34; 55610＆＃34;，host =＆＃34; / tmp＆＃34;）数据＆lt; - dbgetquery（conn，sql）dbdisconnect（conn）数据$ lt; - isoWeek :: IsoWeek2date（数据$ idate）返回（数据）} $ m $）;选择reload_plr_modules（）;

R函数有很多情况，但几乎所有有趣的位都在模板的SQL中。 SQL语句逐步构建，一系列常用表表达式（CTE）。这是原生PostgreSQL功能有效的一个很好的例子。

在检查SQL语句之前，另一个快速的侧栏。请注意R码中的$ M $。封装的R代码是一个额外的字符串常量。在处理可能具有嵌入式引号的长字符串常量时，美元引号特别有用，这些引号有含义何时进行评估。而不是将引号加倍（或加倍加倍等），两个美元符号与A＆＃34;标签＆＃34;长度为零或更长（在这种情况下，在这种情况下＆＃34; m＆＃34;）用于分隔字符串。如果您要注意，您可能已经注意到以前的PL / R函数定义中使用的$$分隔符，甚至在DO陈述中，出于同样的原因。这是我谦虚地观点中的PostgreSQL最酷的未被关象力之一。

g（年，周，xb，r）为（选择ASE提取物（从Obs_date）提取物（从Obs_date）提取 ......