sql - 使用 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 Enterprise Guide 编写一些程序/数据查询。我无权修改存储在“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 负责为您翻译内容，但这可能会令人困惑。例如，我见过用 VARCHAR(400) 列定义的“惰性”数据库表，其值永远不会超过某个较小的长度(例如人名列)。在数据库中这不是什么大问题，但由于 SAS 没有 VARCHAR 数据类型，因此它为每行创建一个 400 个字符宽的变量。即使使用数据集压缩，这也确实会使生成的 SAS 数据集变得不必要的大。

另一种方法是使用“显式传递”，即使用相关 DBMS 的实际语法编写 native 查询。这些查询完全在 DBMS 上执行，并将结果返回给 SAS(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;

请注意，括号内的所有内容都是 native 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
   ;

这将指示先前分配的 LIBNAME 语句，SAS 通过该语句连接到 Teradata。该 WHERE 子句的语法与 SAS 是否能够将完整查询传递给 Teradata 非常相关。 (您的示例没有显示“a”和“b”所指的内容。SAS 执行联接的唯一方法很可能是将两个表拖回本地工作 session 并在 SAS 服务器上执行联接.

我强烈建议您尝试说服 Teradata 管理员允许您在某些实用程序数据库中创建“驱动程序”表。这个想法是，您可以在 Teradata 中创建一个相对较小的表，其中包含要提取的 ID，然后使用该表执行显式联接。我确信您需要接受更正式的数据库培训才能做到这一点(例如如何定义适当的索引以及如何“收集统计数据”)，但有了这些知识和能力，您的工作将会飞速发展。

我可以继续说下去，但我就到此为止了。我每天都会在全局最大的 Teradata 环境之一中广泛使用 SAS 和 Teradata。我喜欢用这两种语言进行编程。