TPC-DS标准规范(4)

TPC-DS是一套决策支持系统测试基准，主要针对零售行业。提供99个SQL查询（SQL99或2003），分析数据量大，测试数据与实际商业数据高度相似，同时具有各种业务模型（分析报告型，数据挖掘型等等）。国内目前相关的翻译文章较少。本文尝试对官网的TPC BENCHMARK DS Standard Specification（下称“原文”）进行翻译。翻译主要参照的是2017年发布的2.6.0版本。

由于原文一共137页，本文在翻译的时候会进行一定的压缩，突出较为关键的信息。本文章节名称，序号，小标题等均严格按照原文翻译排序。

4 查询概述

4.1 查询的要求和定义

4.1.1 查询的定义和可用性

4.1.1.1 查询一般分为以下几种：

a) 商务问题，说明了可以使用查询的商务环境。商务问题列于附录B。

b) TPC提供的查询模板中指定的功能查询定义（功能查询定义部分见4.1.2节）。

c) 替代参数，其描述生成EQT所需的替换值。

d) 在查询验证中使用的答案集（见7.3节）。

4.1.1.2 由于TPC-DS查询集较大，本文档不包含查询的全部内容。获取查询集的信息，请参见表0-1。

4.1.2 功能查询定义

4.1.2.1 每个查询的功能由其查询模板和dsqgen定义。

4.1.3 dsqgen将查询模板转换为功能齐全的SQL，称为可执行查询文本（EQT）。dsqgen的主版本号和次版本号必须与TPC-DS规范的主版本号相同。dsqgen的源代码可以下载（参见表0-1）。

4.1.3.1 查询模板符合SQL1999的核心（OLAP修订），以及以下非标准添加：

1. 模板可以注释返回值的行数。

2. 模板与dsqgen之间有替换标签，单个模板可以生成大量语法不同，功能一致的查询。

4.1.3.2 每个查询的EQT都要从功能查询定义或得到批准的查询变体（见附录C）中获取。除了4.3.2和4.2.5中的允许的特殊情况外，所有查询文本的执行都必须在TPC提供的ull中使用。

4.1.3.3 任何EQT功能与TPC提供的模板生成的相应EQT的功能不匹配的查询模板都是无效模板。

4.1.3.4 所有查询模板及替换参数都应披露。

4.1.3.5 基准测试人员可以修改模板中的语句，从而适应第4.2.3节中定义的产品功能或查询方言的微小差异。

4.1.3.6 如果第4.2.3条允许的更改，无法使基准测试者生成可由DBMS执行的EQT，则可以提交一个备用查询模板（见4.3.2.4节）。

4.1.3.7 如果基准提交中使用的查询模板与TPC提供的模板不一致，则该模板必须满足第4.2.3和4.2.5节的要求。

4.2 查询修改方法

4.2.1 这些查询必须以商业上可用的SQL语言实现。由于ISO SQL语言不断发展，TPC-DS基准规范允许与TPC提供的查询模板中使用的SQL语句有某些偏差。

4.2.2 允许以下四种类型的偏差：

a) 根据第4.2.3节定义的查询小修改。
b) 第4.2.4节定义的限制行数的修改。
c) 第4.2.5节中定义的提取查询的修改。
d) 附录C允许的查询变体。

4.2.3 查询小修改(Minor Query Modifications)

4.2.3.1 基准查询实现需要适应每个特定环境，因此可以做一些特定调整以及针对方言的语法变化。第4.2.3.4中描述的修改有：

1. 被定义为是小修改。

2. 不需要得到批准。

3. 可以与其他小修改结合。

4. 可用于修改功能查询定义或该定义批准的查询变体。

不属于第4.2.3.4节所述范围内的修改不属于小修改，该修改不符合规定。除非它们是附录C中允许的查询变体的组成部分。

4.2.3.2 对功能查询定义或批准的变体的查询小修改，在查询集中的应用必须一致。例如，如果在一个查询中使用了特定的供应商日期表达方式或表名命名方式，则在涉及日期表达式或表名的其他所有查询中必须都使用这一方式。

4.2.3.3 查询小修改的使用应当合法且被披露。

4.2.3.4 以下修改属于小修改：

a) 表：

1. 表名：可以修改每个查询的CREATE TABLE，CREATE VIEW，DROP VIEW和FROM子句中，找到的表和视图名称，以反映SUT的常规命名约定。

2. 表空间引用：可以使用符合第3节要求的表空间引用扩充CREATE TABLE语句。

3. WITH()子句：使用“with()”语法的查询，可以用语义上等效的，表或视图查询替换。

b) Joins：

1. Outer Join：对于外连接查询，可以使用供应商自己的非指定语法。例如，连接表达式“CUSTOMER LEFT OUTER JOIN ORDERS ON C_CUSTKEY = O_CUSTKEY”可以通过将CUSTOMER和ORDERS添加到from子句中，并添加特别标记的JOIN（例如，C_CUSTKEY * = O_CUSTKEY）来替换。

2. Inner Join：对于内连接查询，可以使用供应商自己的非指定语法。例如，连接表达式“FROM CUSTOMER，ORDERS WHERE C_CUSTKEY = O_CUSTKEY”可能被修改为使用JOIN子句的形式，例如“FROM CUSTOMER JOIN ORDERS ON C_CUSTKEY = O_CUSTKEY”。

c) 运算符(Operators)：

1. 显性的ASC-ASC可以被显性添加到ORDER BY子句中的列里。

2. 关系运算符(例如，“>”， “<=”， “!=”)可以被供应商自己的等效运算符替换。

3. 对于使用字符串连接运算符的查询，可以使用供应商自己的语法（例如，可以用+代替||）。

4. “Rollup(x, y)”形式可以被替换成“x, y with rollup”。

d) 控制语句(Control statements)：

1. 命令分隔符(Command delimiters)：EQT的末尾可以插入附加语法，以便表示查询到此结束并请求执行。 这种命令分隔符的示例是分号或单词“GO”。

2. 事务控制语句(Transaction control statements)：CREATE/DROP TABLE或CREATE/DROP VIEW语句之后可以跟一个COMMIT WORK语句或等效的供应商自己的事务控制语句。

3. 相关视图(Dependent views)：如果实现使用涉及视图的变体，并且实现仅支持“DROP RESTRICT”语法（必须首先删除所有相关对象），则可以添加相关视图的附加DROP语句。

e) 别名(Alias)：

1. Select-list表达式别名：对于包含SELECT-list的项（例如“AS”子句）的别名定义的查询，可以使用供应商自己的非指定语法。可行的例子包括“TITLE <string>”或“WITH HEADING <string>”。使用select-list表达式的别名是可选的。

2. GROUP BY和ORDER BY：对于仅仅为了给表达式分组或排序，而使用视图，嵌套表表达式(nested table-expression)或select-list别名的这类查询，供应商可以使用自己的GROUP BY或ORDER BY子句，替换这些视图，嵌套表表达式或select-list。可行的例子包括“GROUP BY”，“GROUP BY <expression>”，“ORDER BY <ordinal>”和“ORDER BY <expression>”。

3. 相关名称：表名别名可以添加到EQT中。 表名别名之前的关键字“AS”可能会被省略。

4. 嵌套表表达式别名：对于涉及嵌套表表达式的查询，表别名之前的嵌套关键字“AS”可能会被省略。

5. 列别名：列名的别名可能添加到EQT的任何SELECT列表中。这些列别名可以在之后的查询中引用，例如GROUP BY或ORDER BY子句。

f) 表达式与功能：

1. 日期表达式：对于包含涉及操纵日期的表达式的查询（例如，添加/减去日期/月/月/年或从日期中提取年份），可以使用供应商自己的非指定语法。例子包括使用“YEAR（<column>）”从日期列中提取年，或者使用“DATE（<date>）+ 3 MONTHS”将3个月添加到日期列中。

2. 输出格式化功能：仅为影响输出格式（例如处理空字符串）或中间算术结果精度（如COALESCE或CAST）的标量函数，可以放到查询最外面的SELECT中。

3. 聚集函数：标度因子较大时，聚集可能导致越界。因此AVG和COUNT可以被替换为等效的供应商自己的函数以扩展处理的值的范围（例如，AVG_BIG和COUNT_BIG）。

4. 子字符串标量函数：对于使用SUBSTRING()标量函数的查询，可以使用供应商自己的非指定语法替换。例如“SUBSTRING(S_ZIP, 1, 5)”。

5. 标准差函数：对于使用标准差函数(stddev_samp)的查询，可以使用供应商自己的语法（例如stdev，stddev）。

6. 显性转换：可以使用仅为影响整数列或值的运算结果精度的标量函数（如CAST），语法上在修改前后一致，仅运算精度改变。

7. 数学功能：供应商自己的数学表达式可用于替换EQT中的数学函数。替换语法必须实现ISO SQL标准中定义的数学函数的完整语义行为（例如处理NULL）。例如，avg()可以由average()或数学表达式（如sum()/count()替代）。

8. 日期转换：符合2.2.2节中定义的，对日期列或日期常量进行的显性转换，要保证该日期可以完成必要的日期算术结果。替换语法必须具有相同的语义行为。

9. 转换语法：供应商自己的转换语法可替换EQT中存在的转换函数，前提是供应商的转换语法在语义上等同于EQT中提供的语法。

10. 现有标量函数：仅影响输出格式或结果精度的查询模板中的现有标量函数（如CAST）可以被修改。结果语法必须与查询模板中的语义行为一致。

g) 常规

1. 分隔标志符(Delimited identifiers)：如果标识符名称与给定实现中的保留字冲突，则可以使用分隔标识符。

2. 括号：允许在表达式和子查询之间添加或删除括号。必须一起添加或删除前括号“(”及其对应的后括号“)”。

3. 序数词(Ordinals)：序数词与列名引用可以相互替换。如：“select a,b from T order by 2”等价于“select a,b from T order by b”。

4.2.4 行限制修改

4.2.4.1 一些查询要求返回给定数量的行（例如，“返回前10个所选行”）。如果N是要返回的行数，则查询必须完全返回前N行，除非结果集少于N行，否则必须返回所有行。可以通过以下四种方式实现：

1. 可以使用由测试人员的交互式SQL接口支持的，供应商自己的控制语句（例如，SET ROWCOUNT n）来限制返回的行数。

2. 实现特定层来识别控制语句（参见第8.2.4节），从而控制读取行的循环次数，以此限制返回的行数（例如，rowcount <= n）。

3. 供应商自己的SQL语法可以添加到查询模板的SELECT语句中，以限制返回的行数（例如SELECT FIRST n）。 这种语法的添加不算作查询小修改，因为它完成了功能查询定义的功能需求，并且没有定义标准化语法。 在所有其他方面，查询必须满足4.1.2节的要求。 添加的语法必须仅针对答案集的大小，并且不能对表，索引或访问路径等等进行任何其他显性引用。

4. 使用select子句封装SQL语句（或迭代OLAP查询的语句）并加上行数限制。例如，如果Q是原始查询文本，那么修改可以是：SELECT * FROM（Q） WHERE rownum <= n。这种语法不算作查询小修改，因为它完成了功能查询定义的功能需求，并且没有定义标准化语法。 在所有其他方面，查询必须满足第4.1.2节的要求。添加的语法必须仅针对答案集的大小，并且不能对表，索引或访问路径等等进行任何其他显性引用。

测试人员必须使用上述方法中的其中一种，并且对于所有返回行限定都用这一种方式实现。

4.2.5 提取查询修改

4.2.5.1 如第1.4节所述，一些查询会返回大型结果集，结果将被保存以备后续分析。基准测试允许DBMS的替代方法将这些结果行提取到文件。可以通过SQL前端工具处理它们并将行输出到一个文件中。如果对任何流的查询返回10000或更多结果行，则供应商可以使用以下提取工具或方法中的一个，将所有流中的查询的行提取到文件中：

1. 供应商自己的SQL语法可以添加到查询模板的SELECT中，以此重定向返回行，例如“Unload to file ‘outputfile’ Select c1, c2 …”。

2. 供应商可以使用，测试人员SQL交互接口支持的，供应商自己的控制语句，如：

set output_file = ‘outputfile’
select c1, c2…;
unset output_file;

3. 实现特定层（见8.2.4节）来识别控制语句，以用于调用提取工具或方法。

4.2.5.2 如果使用这些替代提取选项中的一个，则输出应格式化为分隔或固定宽度的ASCII文本。

4.2.5.3 如果使用这些替代提取选项之一，则必须满足以下条件：

测试人员可以只选择4.2.5.1中的一个选项。 对于所有需要提取处理的查询，必须使用统一使用该选项。

1. 如果提取语法修改查询SQL，那么查询在所有其他方面必须满足第4.1.2节的要求。添加的语法必须仅处理抽取工具或方法，不得对表，索引或访问路径进行任何额外的显性引用。

2. 测试人员必须证明所使用的文件名称和提取设施本身不向DBMS提供提示或优化，使得查询具有额外的性能收益，而不仅仅只是加速提取行。

所使用的工具或方法必须满足与工具或方法结合使用的查询的所有ACID要求。

4.2.6 查询变体(Query Variants)

4.2.6.1 查询变体是备用查询模板，它是为了让供应商克服特定的功能障碍或产品缺陷而创建的，这些缺陷无法通过查询小修改来解决。

4.2.6.2 在使用新的变体之前，需要先得到批准。批准过程被定义在第4.2.7节。

4.2.6.3 已经被批准的查询变体在附录C中进行了总结。

4.2.7 查询变体批准

4.2.7.1 如果符合以下条件之一，则新的查询变体将被考虑批准：

a) 即使在按照第4.2.3节的要求下进行了查询小修改之后，供应商也不能使用功能性查询定义或已批准的变体成功地针对资格数据库运行EQT。

b) 建议的变体包含新的或增强的SQL语法，与新的ISO SQL标准的批准委员会草案中定义的基准相关。

c) 新变体包含的语法使该的变体更接近ISO SQL标准。

d) 建议的变体包含语法小差异，它们可以直接映射到功能性查询定义中使用的ISO SQL语法，并提供与ISO SQL标准基本类似的功能。

4.2.7.2 要获得批准，变体应具有以下属性——并非所有的属性都是必要的。相反，变体的综合评价将是批准该变体的决定性因素。

a) 变体只是寻求功能兼容性的语法，而不是性能增益。

b) 变体影响的部分很小，只限于修补遗漏的功能。

c) 变体基于对业务问题的了解，而不是基于对SUT的了解或测试数据库中特定数据值的了解。

d) 变体在不同的供应商之间具有广泛的适用性。

e) 变体是非程序性的。

f) 变体是用ISO SQL批准的语法来实现功能性查询定义。

g) 变体是供应商可以实现并希望应用于即将实现的基准中的。

4.2.7.3 拟批准的变体应符合4.2.8节定义的实施准则和4.2.9节定义的代码标准。

4.2.7.4 提议的查询变体的批准将由TPC-DS小组委员会自行决定，并受TPC政策的约束。

4.2.7.5 提交审批的所有提议的查询变体将被记录下来，同时还会记录他们为什么被批准或不被批准的原因。

4.2.8 变体实现指南

4.2.8.1 当提议的查询变体包括创建表格时，数据类型应符合2.2.2节。

4.2.8.2 当所提出的查询变体包括创建新实体（例如，游标，视图或表格）时，实体名称将确保新创建的实体不干扰其他查询会话，并且不在多个查询会话之间共享。

4.2.8.3 在建议的查询变体中创建的任何实体也必须在该变体中删除。

4.2.8.4 如果在建议的查询变体中使用CREATE TABLE语句，则可能包含表空间引用（例如IN <表空间名称>）。在建议的查询变体中创建的所有表必须使用单个表空间。

4.2.9 代码风格

4.2.9.1 实现者可以用任何代码风格编写EQT，包括：

a) 使用换行符，制表符或空格。

b) 使用大写或小写文本。

4.2.9.2 所使用的代码风格对SUT没有影响，且必须从始至终使用同一种风格。

4.3 替代参数生成

4.3.1 每个查询都有一个或多个替代参数，必须使用dsqgen为查询流生成EQT。为了生成所需数量的查询流，dsqgen必须与RNGSEED，INPUT和STREAMS选项一起使用。选择RNGSEED选项的值作为数据库加载时间（加载结束时间）结束的timestamp，以第7.4.3.8节中定义的格式mmddhhmmsss表示。STREAMS选项<S>的值是每个吞吐量测试期间要执行的流数Sq的两倍(S=2*Sq)。INPUT选项的值<input.txt>是一个包含所有99个查询模板，且按照数字顺序排列的文件。

4.3.2 Query_0.sql是在功率测试期间执行的查询序列，query_1.sql到query_ [Sq].sql是在第一个吞吐量测试期间要执行的查询序列，query_ [Sq+1].sql到query_ [2*Sq].sql是在第二个吞吐量测试期间要执行的查询序列。