TABLESAMPLE 实现

设计页面

此 wiki 页面是针对一项在编写时尚未在 PostgreSQL 中存在的特性的设计讨论。该特性现已从 9.5 版本开始可用，详情请参阅官方文档.

介绍

TABLESAMPLE 是一个有趣的 sql 子句。它在 SQL 标准 2003 中定义。以下是一个示例：
SELECT avg(salary) FROM emp TABLESAMPLE SYSTEM (50); 它将返回一个基础表的样本，其大小取决于括号中指定的数字。SQL 标准 2003 中对该查询规范的详细描述见下方.
Microsoft SQL Server 和 DB2 已经实现了该子句。查询表的样本在人们的工作中经常发生。一篇关于详细阐述抽样用法的论文可在IBM 的一篇论文中找到。在第 1 页和第 2 页中，作者描述了快速抽样方法在发现数据中的总体趋势和模式方面的优势和用法。
对于 PostgreSQL 来说，实现此功能并将其提供给用户将会很有用。
这目前是 Google Summer of Code 2012 项目的一部分 --- 为 Postgres 实现 TableSample.

项目详情

关于 TABLESAMPLE 子句

概念

在<table reference> 中，可以指定<sample clause> 以返回取决于<sample method> 和<sample percentage> 的结果行子集。如果<sample clause> 包含<repeatable clause>，那么对于给定的<repeat argument>，该<table reference> 的重复执行将返回一个结果表，该结果表具有相同的行，前提是满足某些实现定义的条件。

语法

<table reference> ::= <table factor> | <joined table>
<table factor> ::= <table primary> [ <sample clause> ]
<table primary> ::= <table or query name> [ [ AS ] <correlation name> ]
<sample clause> ::= TABLESAMPLE <sample method> <left paren> 
                    <sample percentage> <right paren> [ <repeatable clause> ]
<sample method> ::= BERNOULLI | SYSTEM
<repeatable clause> ::= REPEATABLE <left paren> <repeat argument> <right paren>
<sample percentage> ::= <numeric value expression>
<repeat argument> ::= <numeric value expression>

一般规则

令 TP 为<table factor> TF 中立即包含的<table primary>。令 RT 为 TP 的结果。情况

如果指定了<sample clause>，那么
(a) 令 N 为 RT 中的行数，令 S 为<sample percentage> 的值。

(b) 如果 S 是空值，或者 S < 0（零），或者 S > 100，那么将引发异常条件：“数据异常 — 无效的样本大小”。

(c) 如果指定了<repeatable clause>，那么令 RPT 为<repeat argument> 的值。如果 RPT 是空值，那么将引发异常条件：“数据异常 — 抽样子句中的无效重复参数”。
(d) 情况
如果<sample method> 指定了 BERNOULLI，那么 TF 的结果将是一个包含大约 (N ∗ S/100) 行 RT 的表。RT 的行包含在 TF 结果中的概率为 S/100。此外，RT 的给定行是否包含在 TF 结果中与 RT 的其他行是否包含在 TF 结果中无关。

否则，TF 的结果将是一个包含大约 (N ∗ S/100) 行 RT 的表。RT 的行包含在 TF 结果中的概率为 S/100。
(e) 如果 TF 包含外部引用，那么每次使用给定的外部引用值集评估 TF 时都会生成一个具有相同行的表。
否则，TF 的结果为 RT。

- sample method 以两种类型指定：BERNOULLI 和 SYSTEM
- BERNOULLI 意味着以指定的概率选择元组。
- SYSTEM 意味着以指定的概率选择页面。

使用 Tablesample

TABLESAMPLE 是一个处理表抽样的查询。查询“select * from foo TABLESAMPLE SYSTEM (1)”类似于“select * from foo where random()<0.01”。当您查询 tablesample 时，您必须指定抽样方法。目前，有两种方法，SYSTEM 和 BERNOULLI，因为它们是 ANSI SQL 所要求的。如果对某些用户来说有趣并且必要，可能会添加其他抽样方法以提供支持。您可以选择指定 REPEATABLE 选项，这可以使您在不同的运行中获得相同的样本。
直接使用 TABLESAMPLE 的 select 查询将使用一个名为 SAMPLESCAN 的扫描节点。如果您使用 explain 来查看 postgres 优化器使用的查询计划，您将找到“样本扫描”，它直接扫描采样表。
通常，TABLESAMPLE 只能用于 SELECT 查询。您也可以将其与任何连接查询和聚合一起使用。
TABLESAMPLE 目前只适用于抽样百分比，您只能指定一个浮点数（或返回浮点数的表达式），查询将取该百分比的样本。您不能像在 SQL Server 中那样指定查询中的行数。

SYSTEM 选项

TABLESAMPLE SYSTEM 方法返回大约的百分比行。它为基础关系的每个物理存储页面生成一个随机数。根据该随机数和指定的抽样百分比，它要么包含要么排除相应的存储页面。如果包含该页面，则整个页面将在结果集中返回。由于抽样是在块（页面）级别上进行的，因此存在一些副作用。

结果集大小将在不同的运行中有所不同。结果集大小与总元组大小的百分比有时会大于，有时会小于指定的百分比。您可以使用“limit <number>”来获取前 <number> 个元组。
如果基础关系仅包含一个页面，那么要么返回整个页面，要么不返回任何元组。

BERNOULLI 选项

TABLESAMPLE BERNOULLI 方法直接对基础关系的每一行进行采样。此抽样方法实际上将扫描整个关系并随机挑选单个元组（它基本上对每个元组进行“掷硬币”）。此算法可以提供更好的随机分布，但对于较小的百分比而言速度会更慢。

REPEATABLE 选项

在 REPEATABLE 子句中，您可以指定一个随机种子号。该数字将用于为 Postgres 后端的 PRNG 随机生成器生成一个种子。在不同的运行中，如果数字相同，那么这些运行的结果集将相同，前提是表没有进行任何更改。如果指定了不同的数字，则结果集通常会不同。以下对表的操作被视为更改：插入、更新、删除、索引重建、索引碎片整理、恢复数据库以及附加数据库。参考

示例

选择样本

 
Select * 
from foo TABLESAMPLE SYSTEM (10);    --Returns about 10% of rows in foo using SYSTEM method
Select *
from foo TABLESAMPLE BERNOULLI (10); --Using BERNOULLI sampling method

删除一定百分比的行

Delete
from foo TABLESAMPLE SYSTEM (1);  --Delete 1 percent of rows from foo

更新一定百分比的行

Update foo TABLESAMPLE SYSTEM (1)
set col1='col1';  -- Update the attribute value of col1 in 1 percent of rows in foo to "col1"

使用 limit 和 order by 选择样本

Select *
from foo TABLESAMPLE SYSTEM (1)
order by col1
limit 100;  -- Select 1 percent of rows from foo, display the first 100 rows, order by column col1

使用 repeatable 选择

Select *
from foo TABLESAMPLE SYSTEM (1) REPEATABLE (200); 
Select *
from foo TABLESAMPLE SYSTEM (1) REPEATABLE (200); --The result set is the same as above 
Select *
from foo TABLESAMPLE SYSTEM (1) REPEATABLE (100); --The result set different from above

参考文献