使用Proc sql和Teradata在SAS中编写高效查询

Question

编辑：这是一组更完整的代码，确切显示了下面每个答案所发生的事情。

libname output '/data/files/jeff'
%let DateStart = '01Jan2013'd;
%let DateEnd = '01Jun2013'd;
proc sql;
CREATE TABLE output.id AS (
  SELECT DISTINCT id
  FROM mydb.sale_volume AS sv
  WHERE sv.category IN ('a', 'b', 'c') AND
    sv.trans_date BETWEEN &DateStart AND &DateEnd
)
CREATE TABLE output.sums AS (
  SELECT id, SUM(sales)
  FROM mydb.sale_volue AS sv
  INNER JOIN output.id AS ids
    ON ids.id = sv.id
  WHERE sv.trans_date BETWEEN &DateStart AND &DateEnd
  GROUP BY id
)
run;

目的是仅根据类别成员资格在表中查询一些ID。然后，我总结了这些成员在所有类别中的活动。

上面的方法比以下方法慢得多：

1. 运行第一个查询以获取子集
2. 运行第二个查询求和每个ID
3. 运行内部连接第三个结果集的第三个查询。

如果我理解正确，那么确保完全通过我的所有代码而不是交叉加载可能会更有效率。

在昨天发表问题后，一位成员建议我从另一个针对我的情况的绩效问题中受益，我可能会从中受益。

我正在使用《 SAS企业指南》编写一些程序/数据查询。我没有权限修改存储在“ Teradata”中的基础数据。

我的基本问题是在这种环境下编写高效的SQL查询。例如，我查询一个大表（具有数千万条记录）以查找ID的一小部分。然后，我使用此子集再次查询较大的表：

proc sql;
CREATE TABLE subset AS (
  SELECT
    id
  FROM
    bigTable
  WHERE
    someValue = x AND
    date BETWEEN a AND b

)

这可以在几秒钟内完成，并返回90k ID。接下来，我要针对大表查询这组ID，随后便出现了问题。我想随着时间的推移为ID的值求和：

proc sql;
CREATE TABLE subset_data AS (
  SELECT
    bigTable.id,
    SUM(bigTable.value) AS total
  FROM
    bigTable
  INNER JOIN subset
    ON subset.id = bigTable.id
  WHERE
    bigTable.date BETWEEN a AND b
  GROUP BY
    bigTable.id
)

无论出于何种原因，这都需要花费很长时间。不同之处在于第一个查询标记为“
someValue”。第二个查看所有活动，而不管’someValue’中的内容是什么。例如，我可以标记所有订购比萨饼的客户。然后，我将查看订购披萨的所有客户的每次购买。

我对SAS不太熟悉，因此我正在寻找有关如何更有效地执行此操作或加快操作速度的任何建议。我愿意接受任何想法或建议，如果可以提供更多详细信息，请告诉我。我想我很惊讶第二个查询需要这么长时间来处理。

Answer 1

使用SAS访问Teradata（或与此相关的任何其他外部数据库）中的数据时，最重要的了解是SAS软件准备了SQL并将其提交给数据库。这样做的目的是使您（用户）摆脱所有数据库特定的细节。SAS使用称为“隐式传递”的概念来执行此操作，这仅意味着SAS会将​​SAS代码转换为DBMS代码。发生的许多事情包括数据类型转换：SAS仅具有两种（也只有两种）数据类型，即数字和字符。

SAS deals with translating things for you but it can be confusing. For
example, I’ve seen “lazy” database tables defined with VARCHAR(400) columns
having values that never exceed some smaller length (like column for a
person’s name). In the data base this isn’t much of a problem, but since SAS
does not have a VARCHAR data type, it creates a variable 400 characters wide
for each row. Even with data set compression, this can really make the
resulting SAS dataset unnecessarily large.

另一种方法是使用“显式传递”，在这种情况下，您可以使用相关DBMS的实际语法编写本机查询。这些查询完全在DBMS上执行，并将结果返回给SAS（后者仍将为您进行数据类型转换。例如，这是一个“直通”查询，该查询执行对两个表的联接并将SAS数据集创建为结果：

proc sql;
   connect to teradata (user=userid password=password mode=teradata);
   create table mydata as
   select * from connection to teradata (
      select a.customer_id
           , a.customer_name
           , b.last_payment_date
           , b.last_payment_amt
      from base.customers a
      join base.invoices b
      on a.customer_id=b.customer_id
      where b.bill_month = date '2013-07-01'
        and b.paid_flag = 'N'
      );
quit;

请注意，括号对中的所有内容都是本机Teradata SQL，并且联接操作本身正在数据库中运行。

您在问题中显示的示例代码 不是 SAS /
Teradata程序的完整，有效的示例。为了提供更好的帮助，您需要显示真实程序，包括所有库引用。例如，假设您的真实程序如下所示：

proc sql;
   CREATE TABLE subset_data AS
   SELECT bigTable.id,
          SUM(bigTable.value) AS total
   FROM   TDATA.bigTable bigTable
   JOIN   TDATA.subset subset
   ON     subset.id = bigTable.id
   WHERE  bigTable.date BETWEEN a AND b
   GROUP BY bigTable.id
   ;

这将指示SAS通过其连接到Teradata的先前分配的LIBNAME语句。如果SAS甚至能够将完整的查询传递给Teradata，则WHERE子句的语法将非常相关。（您的示例未显示“
a”和“ b”指的是什么。SAS可能执行联接的唯一方法是将两个表都拖回到本地工作会话中，然后在SAS服务器上执行联接。

我可以强烈建议的一件事是，您尝试说服Teradata管理员允许您在某些实用程序数据库中创建“驱动程序”表。这个想法是，您将在Teradata内部创建一个相对较小的表，其中包含要提取的ID，然后使用该表执行显式联接。我敢肯定，您需要进行一些正式的数据库培训（例如如何定义适当的索引以及如何“收集统计信息”），但是有了这些知识和能力，您的工作就会顺利进行。

我可以继续下去，但我会在这里停止。我每天都广泛使用SAS与Teradata，而据我所知，SAS是地球上最大的Teradata环境之一。我都喜欢编程。