筛选和分组：SQL、Linux 脚本与 Ruby 之比较

通过 Oracle SQL、Linux 和 Ruby 解决与数据集相关的问题

 

针对 Linux 的 Oracle 数据库版本在 1998 年发布，自那以后，陆续推出了可以在 Linux 上使用的其他 Oracle 应用产品和中间件。随着 Oracle 坚不可摧 Linux 支持计划的实行，许多组织将他们的 Oracle 安装移植到了 Linux 平台。这些变化导致越来越多的 DBA 需要调查资源（如脚本），以便管理在 Linux 上运行的服务器。

在中，我说过，系统管理员与 DBA 之间在职责方面存在一个传统的差异，但实际情况中，这个差异并不明显。公司会雇佣一些可以解决数据库级以及操作系统级问题的员工。至少，安装 Oracle 数据库软件的职责要求 DBA 与底层操作系统进行交互。

即使出现了许多新的脚本语言，管理员仍然需要使用 shell 脚本作为可在所有系统上使用的技术。现有脚本已经开发并在许多服务器上运行了数年 — 这些脚本需要维护和升级。Shell 脚本是一个针对各类 IT 专业人员的非常有用的工具。

在本文中，您将了解 Oracle SQL、Linux 操作系统命令和脚本的一些可转换的常用概念。特别是，本文将说明如何使用“面向集的”方法看待与需要分组、排序和筛选的数据集相关的问题。这些操作对于了解 SQL 的人员来说很熟悉，但在使用 Linux 命令和/或 Ruby 编程语言时，通常也需要这些操作。

我不会尝试以任何严谨的方式讨论数学集合论，而是在整个范围内查找人类语言中存在的所有模糊边界（以及典型业务问题）。Oracle SQL 提供了比脚本更纯粹的数据集操作方式：每一列都包含已定义类型的域，并且逻辑数据独立于底层物理表示进行处理。正常情况下，从查询返回的数据与 shell 命令的输出不同。以下内容针对教学目的，即，为了解 SQL 的人员提供他们所熟知的类比，以便在需要时利用他们解决问题的能力在数据库外部工作。本文将提供一个解决问题的不同角度，并鼓励您钻研新的、不熟悉的领域。

数据集

SQL 不是过程语言。相反，它用于查看、汇总或操作数据集。然而，执行某些 shell 命令会导致输出被写入。这些结果也可以解释为一组数据。它可能不是特别结构化的数据集，但大多数情况下都是。许多不同的 shell 命令会将一组数据作为输出，但以下示例将使用 ls 命令，大多数用户应该熟悉这个命令。后续命令将修改给定目录中的文件列表。首先是一个简单的长列表命令：

ls -l	  

最终输出取决于您的目录。假设结果如下：

total 60K
-rw-r--r--  1 root   dba 1.7K May 13 09:02 xe_s000_2072.trc
-rw-r--r--  1 cas    dba  929 Apr 28 22:21 xe_smon_3664.trc
-rw-r--r--  1 cas    dba  794 Apr 26 17:19 xe_smon_3676.trc
-rw-r--r--  1 oracle dba  792 Apr 23 21:52 xe_smon_2120.trc
-rw-r--r--  1 oracle dba  794 Apr 19 17:37 xe_smon_3364.trc
-rw-r--r--  1 root   dba 5.5K Jun 20  2006 xe_s000_2412.trc
-rw-r--r--  1 cas    dba  790 May 25  2006 xe_smon_540.trc

第一列包含一组目录权限。第二列显示链接数量。第三列指明所有者。第四列表示相关组。第五列指出文件大小（以字节为单位）。第六列是文件创建日期（会导致某些问题；如下所示）。第七列指出文件名称。

可以使用一个表来容纳这些数据：

FILE_PERMISSIONS	NUM_LINKS	FILE_OWNER	FILE_GROUP	SIZE	CREATED_AT	FILE_NAME
-rw-r--r--	1	root	dba	1675	May 13 09:02	xe_s000_2072.trc
-rw-r--r--	1	root	dba	5532	Jun 20 2006	xe_s000_2412.trc
-rw-r--r--	1	oracle	dba	792	Apr 23 21:52	xe_smon_2120.trc
-rw-r--r--	1	oracle	dba	794	Apr 19 17:37	xe_smon_3364.trc
-rw-r--r--	1	cas	Dba	929	Apr 28 22:21	xe_smon_3664.trc
-rw-r--r--	1	cas	dba	794	Apr 26 17:19	xe_smon_3676.trc
-rw-r--r--	1	cas	dba	790	May 25 2006	xe_smon_540.trc

后面的示例将构建这个结果的“虚构表”（ls -l 命令的输出已在上面列出），以说明如何限制、排序和汇总数据。

注意事项

至少对于查看数据的人来说，这个数据集的结构很明显。然而，请注意第一行与结构不符：total 60K。这一开始就显示了方法的局限性；某些输出不是结构化数据的一部分。

要整理这组数据，您可以将输出重定向到一个文件，随后可以通过后续命令操作这个文件。

ls -l > test.txt

然后，您可以使用喜欢的文本编辑器编辑该文件，并删除第一行。您可以调整数据，直到满足您的结构化要求。结构化程度取决于您的目标对象。可读报表不同于要加载到 Oracle 表中的文件。

如果这个手动中间步骤不是一个选项（或破坏了您的自动化体验），请考虑使用 grep -v 选项（本文稍后有述）传送输出。该命令用于排除匹配给定模式的行。

您可能还注意到，日期格式不一致。除了以几个不同的格式显示之外，也不能进行临时排序，并且还包含了空格。这再一次展示了数据库存储类型化数据的有用性。Oracle 提供了大量函数，用于操作和排序日期。在数据库中，将日期作为结构化数据通常更易于操作。

如果必须在命令行按日期排序，您可能还需要 -t 或类似选项，以便在传送给后续命令之前进行此排序。

限制和排序数据

许多 Linux 命令都包含允许您以有用方式排序数据的选项。例如，如果使用简单的 ls 命令，您可以指定 -t 选项按时间排序，或指定 -X 选项按扩展名排序。假设您希望只选择文件的所有者和名称，并按所有者排序结果，但 ls 没有包含允许此类输出的选项。如果您将数据集置于表中，可以执行以下操作：

SELECT file_owner, file_name FROM imaginary_table ORDER BY owner;

但是，您可以使用管道操作符将命令的输出传送（或重定向）到其他命令，以获得所需结果。首先将数据限制为上面讨论的那两列：

[root@linux-server test]# ls -l | awk '{print $3"\t"$9}'

root    xe_s000_2072.trc
root    xe_s000_2412.trc
oracle  xe_smon_2120.trc
oracle  xe_smon_3364.trc
cas     xe_smon_3664.trc
cas     xe_smon_3676.trc
cas     xe_smon_540.trc

在本例中，使用了 awk 实用程序筛选数据，以便仅打印所有者和文件名。您可能已经注意到，所有者是虚构表的第三列，因此 $3 表示该列。\t 表示将使用一个标签将数据与下一列分隔开。但是，为什么要使用 $9？

仔细查看数据后发现，日期数据分为三列。由于使用了空格分隔这三部分，数据确实“看上去像是”三个单独的列。

现在，需要将这些结果传送到其他命令，以进行正确排序。排序实用程序将按字母顺序或指定的其他顺序打印各行。因此，您将获得以下所需结果：

[root@linux-server test]# ls -l | awk '{print $3"\t"$9}' | sort

cas     xe_smon_3664.trc
cas     xe_smon_3676.trc
cas     xe_smon_540.trc
oracle  xe_smon_2120.trc
oracle  xe_smon_3364.trc
root    xe_s000_2072.trc
root    xe_s000_2412.trc

现在，假设您只想查看 oracle 所有的文件。SQL 语句将使用诸如 WHERE owner='oracle' 的 WHERE 子句。可以使用实用程序的 grep 选项限制数据，方法如下：

[root@linux-server test]# ls -l | awk '{print $3"\t"$9}' | sort | grep oracle

oracle  xe_smon_2120.trc
oracle  xe_smon_3364.trc

同样，可以使用 -v 标记将数据限制到非 oracle 所有（或等同于 WHERE owner <> 'oracle'）的行：

[root@linux-server test]# ls -l | grep -v oracle

cas     xe_smon_3664.trc
cas     xe_smon_3676.trc
cas     xe_smon_540.trc
root    xe_s000_2072.trc
root    xe_s000_2412.trc

特殊数据和分组数据

首先，假设只显示所有者数据（等效于 SELECT owner FROM imaginary_table ORDER BY 1）：

[root@linux-server test]# ls -l | awk '{print $3}' | sort

cas
cas
cas
oracle
oracle
root
root

您可能希望查看目录中文件的所有者的简单列表。在 SQL 中，这将表达为：

SELECT DISTINCT owner FROM imaginary_table ORDER BY 1

您可以使用 uniq 命令获得类似结果：

[root@linux-server test]# ls -l | awk '{print $3}' | sort | uniq

cas
oracle
root

uniq 操作符具有一个 count 函数，允许您获得与给定所有者关联的每个文件的计数。在 SQL 中，您将编写以下查询：

SELECT count(*), owner FROM imaginary_table GROUP BY owner

通过为 uniq 添加 -c 选项，您可以获得以下结果：

[root@linux-server test]# ls -l | awk '{print $3}' | sort | uniq -c
      1
      3 cas
      2 oracle
      2 root

输出开头处的 1 是什么？如果您回头查看 ls -l 命令的最初输出，可以看到有一行的内容是 total 60K。该输出再一次被命令解释为不包含任何数据的列 3。如果这不可接受，可以如前所示使用 grep 排除该行：

[root@linux-server test]# ls -l |grep -v total |awk '{print $3}' | sort | uniq -c
      3 cas
      2 oracle
      2 root

上面的所有示例都使用 ls 命令提供输出。但是，产生输出的任何命令都可以通过类似的方式使用。另一个方法是，考虑是否可以将命令的输出重定向到一个文件。如果可以，得到的文件将包含随后可操作的数据集。

从 Shell 命令到 Ruby

为了避免您认为熟悉这种类型的语法会占用您学习新事物和热门技术的时间，请考虑这些常用编码在 Ruby 编程语言中出现的方式。作为 Rails Web 应用程序开发框架的一部分，Ruby 获得了广泛的接受。无论如何，它是一种非常有用的通用语言，可用于解决系统管理员和 DBA 所关心的各种问题。

Oracle 和 SQL 有一个共同的特征，即，它们都是相对“密集”或“简洁”的语言。此类语言能够以简洁的方式表达极其强大的语句。这就使您能够创建更加简洁、更易于维护的程序。

以下示例可以在交互式 Ruby Shell (irb) 中执行，irb 包含在典型 Ruby 版本中。除了 Ruby 编程语言本身所需的前提条件以外，这些示例不需要任何其他安装条件。sort、uniq 和 grep 的以前用法反映在 ruby Array 类的方法名称中。

在命令行键入“irb”，转到 irb 提示符：

[root@linux-server test]# irb
irb(main):001:0>  

创建数组对象中包含的值列表：

myList = ['ruby','sql','ruby','bash','python','perl','java','sql']
=> ["ruby", "sql", "ruby", "bash", "python", "perl", "java", "sql"]

在运行 irb 时，第二行将显示计算出的表达式的值。该值列表类似于前面示例中使用的 ls 命令的结果。该值列表将充当将要进行排序和限制的最初列表。

现在，可以对该列表进行排序：

irb(main):002:0> myList.sort
=> ["bash", "java", "perl", "python", "ruby", "ruby", "sql", "sql"]

可以返回一个特殊结果列表：

irb(main):003:0> myList.uniq
=> ["ruby", "sql", "bash", "python", "perl", "java"]

甚至还可以使用带有正则表达式匹配功能的 grep 方法：

irb(main):004:0> myList.grep(/r/)
=> ["ruby", "ruby", "perl"]

您还可以通过在 uniq 方法返回的数组上调用 size 方法，来模拟 uniq -c 选项：

irb(main):005:0> myList.uniq.size
=> 6

Ruby 粘合剂

SQL 对于在 Oracle 数据库中操作数据集很有用。Shell 脚本（包含 Linux 命令）在文件系统级别工作良好。如果需要在数据库内部和外部操作数据集，可以执行哪些操作？

Ruby 可以充当“粘合剂”语言，促进独立技术或应用程序层之间的通讯。此功能的一个示例是，使用 Ruby 提供数据库与操作系统之间的交互。考虑 web 应用程序中一个相当普遍的情况：即，数据库中的一个记录包含对文件系统上某个文件的引用。例如，一个用户上载了图片，随后使用该用户的 id 将该图片保存到文件系统。如果您需要确定哪个用户（了解具体名称）上载了文件，需要使用一个衔接这两个领域的解决方案。

除了 Ruby 之外，这些示例还需要一些额外安装。在运行以下示例之前，还应该安装 Oracle Client（连同配置好的、到包含 HR 模式的 Oracle 数据库 [如 Oracle 数据库快捷版] 的连接）和 OCI8 gem (ruby-oci9)（参见页面顶部的“下载”部分）。

创建示例数据

首先，导航到将在其中创建文件的目录，以模拟该情景。您可以在 irb 中逐行输入以下程序，或者在包含先前创建的文件的目录中，将整个清单保存到一个文件中，然后在命令行执行它（ruby <文件名>）。清单中的注释行前面有一个 # 符号：

# First, enter some preliminary commands to create files that will 
# simulate data that was uploaded by users. Load the OCI8 package 
# and make a connection to your database:

require 'OCI8'
conn =OCI8.new('hr', 'hr', 'xe')

# Next create a SQL query that will return all of the users 
# that have an "a" in their last name.  
# This simulates arbitrary user uploaded files:

sql="select employee_id||'_'||upper(last_name)|| '.gif' " 
sql+="from employees where upper(last_name) like '%A%'"

# Now we will create the actual (empty) files.  
# They will be named using the employee id and last name. 
# as specified in the first (and only) field in the query
conn.exec(sql) { |r| File.new(r[0],'w') }

如果您在 irb 中运行了这些示例，可以在提示符下键入“exit”，然后查看已创建的文件。

创建文件系统/数据库协调报告

接下来，使用 Ruby 创建一个报告，以列出每个用户，并在与文件关联的用户的名称前面包含一个 X。

# Load OCI8
require 'OCI8'

# Make a connection to the Oracle Database containing the HR sample schema
conn =OCI8.new('hr', 'hr', 'xe')

# Create a SQL query
sql="select employee_id||'_'||upper(last_name)|| '.gif',"
sql+=" last_name, first_name from employees order by 2,3"

# Execute the query.  Each result is an array of strings representing
# each field in the query.  If a file exists on the file system that matches
# the pattern we specified earlier, display an X
conn.exec(sql) do |r| 
  if File.exists?(r[0]) 
      print 'X ' 
  else
      print '  '
  end 

# Display the last name followed by the first name
  puts "#{r[1]}, #{r[2]}"
end

该报告的结果将写入标准输出，应与以下内容类似：

X Abel, Ellen
X Ande, Sundar
X Atkinson, Mozhe
X Austin, David
X Baer, Hermann
X Baida, Shelli
X Banda, Amit
X Bates, Elizabeth
  Bell, Sarah
  Bernstein, David
  Bissot, Laura
  Bloom, Harrison
  Bull, Alexis
X Cabrio, Anthony
...
  Weiss, Matthew
X Whalen, Jennifer
  Zlotkey, Eleni

注意，（按照预期）应该只列出姓氏中包含“a”并且名称前包含“X”（表示存在关联文件）的用户。

Oracle 提供了许多完全在数据库中处理该情况的方法。例如，可以使用 BLOB 在数据库本身内将图像作为二进制数据存储。然而，如果该设计不适用，这也有助于用户了解其他一些替代方法。

总结和操作比较

在下面的表中，每种情况的数据来源不同，但处理方法类似。SQL 数据集是针对数据库表的查询结果。Linux 数据集是传送到所列出命令的某个命令的结果。上面所述的 Ruby 示例的数据集是一个 Ruby 字符串数组。表达式均具有代表性；根据所讨论的数据集，特定值将有所不同。

SQL	Linux 命令	Ruby	说明
WHERE col1 = 'value'	grep value	myArr.grep('value')	限制结果，返回匹配项
WHERE col1 != 'value'	grep -v value	MyArr.– myArr.grep('value')	限制结果，返回不匹配的项
ORDER BY 1	sort	myArr.sort	按序排列结果
SELECT DISTINCT col1	uniq field1	myArr.uniq	从结果中删除重复项
SELECT COUNT(*) , col1 FROM y GROUP BY x	sort | uniq -c	myArr.uniq.size	找出在一组结果中存在多少给定结果
SELECT col2, col1	awk '{print $2 "\t" $1}'	myArr.each{|rec| puts "#{rec[1,2]} #{rec[0,1]}"}	将结果限制为给定结果数据行的特定部分

在某一点，利用 Oracle SQL 的能力执行复杂的数据聚合更加容易。此外，解释构造良好的 SQL 语句比解释一系列传送的 Linux 命令更加简单。Oracle 提供了许多工具，可用于在以某种方式构建此类数据之后将其导入。Oracle 外部表或 SqlLoader(sqlldr) 可用于将此类数据快速导入表中。

脚本可用于快速构建某些所需功能的原型。符合给定业务需求的解决方案可以获得更广泛的使用，并且需要增强。在处理需要复杂筛选和分析的数据集时，您可以将这些脚本转换为 Oracle 数据库支持的程序，以便可靠地维护和存储数据。可以立即获得的最大效益可能就是使用 Oracle SQL 了 — 设计的目的是为了这类操作。

结论

总之，组织应该尽量使用可重复的流程和设计良好的系统。“艺术家”（通常是程序员）可以创建最适合需求的软件。组织要求或业务需求不可避免地会更改，扮演“执行者”角色的人员应该紧跟潮流，以快速适应不断变化的环境。执行者（通常是系统管理员或 DBA）可以通过较少的时间和资源来开发解决方案。他们需要快速找出合格的技术以解决问题，并具有可用于满足技术和业务需求的所有基础算法。他们通常没有奢侈的广泛测试流程（程序员通常有），但有机会提供独特的创造性解决方案，这些对于正在执行的操作是不可或缺的。

如果您熟悉 SQL，可以使用本文中引入的概念来查看脚本和命令构造。您已经掌握了多种问题解决策略，可以在其他设置中使用它们。如果您了解脚本并且希望深入探究 SQL，将发现过程方法已经为您提供了某些基本资源，当您深入更加一致、面向集合的思考方法时可以使用这些资源。无论在哪种情况下，您都会发现，在您获得新技能以满足紧迫的日常业务和技术需求时，大部分现有知识都可以派上用场。