本节介绍了如何优化在其 SELECT 列表中具有 DISTINCT 关键字的查询。优化 DISTINCT 查询的技术类似于优化 GROUP BY 查询的技术，因为处理使用 DISTINCT 的查询时，Vertica 优化器会将查询重写为 GROUP BY 查询。
本页下的各节介绍了各种具体情况：
这几节中的示例使用了以下表：
=> CREATE TABLE table1 (
a INT,
b INT,
c INT
);
SELECT 查询列表中的 DISTINCT
- 1: 查询在 SELECT 列表中没有聚合
- 2: COUNT (DISTINCT) 和其他 DISTINCT 聚合
- 3: 近似计数区分函数
- 4: 单个 DISTINCT 聚合
- 5: 多个 DISTINCT 聚合
1 - 查询在 SELECT 列表中没有聚合
如果查询内部的 SELECT 列表中没有聚合，Vertica 会将查询视为使用了 GROUP BY 的查询进行处理。
例如，您可以将以下查询：
SELECT DISTINCT a, b, c FROM table1;
重写为：
SELECT a, b, c FROM table1 GROUP BY a, b, c;
为了最大限度提高执行速度，请为 GROUP BY 查询应用GROUP BY 查询中所述的优化技术。
2 - COUNT (DISTINCT) 和其他 DISTINCT 聚合
计算 DISTINCT 聚合通常需要比其他聚合更多的工作。另外，与具有多个 DISTINCT 聚合的查询相比，使用单个 DISTINCT 聚合的查询使用的资源较少。
提示
当所有不同聚合列具有相似数量的不同值时，Vertica 会更高效地执行具有多个不同聚合的查询。以下查询会返回 date_dimension 表的 date_key 列中不同值的数量：
=> SELECT COUNT (DISTINCT date_key) FROM date_dimension;
COUNT
-------
1826
(1 row)
此示例会返回对所有 inventory_fact 记录计算表达式 x+y 得到的所有不同值。
=> SELECT COUNT (DISTINCT date_key + product_key) FROM inventory_fact;
COUNT
-------
21560
(1 row)
可以使用 LIMIT 关键字限制返回的行数来创建等同的查询：
=> SELECT COUNT(date_key + product_key) FROM inventory_fact GROUP BY date_key LIMIT 10;
COUNT
-------
173
31
321
113
286
84
244
238
145
202
(10 rows)
此查询会返回具有特定不同 date_key 值的所有记录中不同 product_key 值的数量。
=> SELECT product_key, COUNT (DISTINCT date_key) FROM inventory_fact
GROUP BY product_key LIMIT 10;
product_key | count
-------------+-------
1 | 12
2 | 18
3 | 13
4 | 17
5 | 11
6 | 14
7 | 13
8 | 17
9 | 15
10 | 12
(10 rows)
该查询通过常数 1 对 product_key 表中每个不同的 inventory_fact 值进行计数。
=> SELECT product_key, COUNT (DISTINCT product_key) FROM inventory_fact
GROUP BY product_key LIMIT 10;
product_key | count
-------------+-------
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1
6 | 1
7 | 1
8 | 1
9 | 1
10 | 1
(10 rows)
该查询会选择每个不同的 date_key 值，并针对具有特定 product_key 值的所有记录为不同的product_key 值进行计数。然后，它会对具有特定 qty_in_stock 值的所有记录中的 product_key 值进行求和，并按照 date_key 对结果进行分组。
=> SELECT date_key, COUNT (DISTINCT product_key), SUM(qty_in_stock) FROM inventory_fact
GROUP BY date_key LIMIT 10;
date_key | count | sum
----------+-------+--------
1 | 173 | 88953
2 | 31 | 16315
3 | 318 | 156003
4 | 113 | 53341
5 | 285 | 148380
6 | 84 | 42421
7 | 241 | 119315
8 | 238 | 122380
9 | 142 | 70151
10 | 202 | 95274
(10 rows)
该查询会选择每个不同的 product_key 值，然后针对具有特定 date_key 值的所有记录为不同的product_key 值进行计数。它还会对具有特定 warehouse_key 值的所有记录中的不同 product_key 值进行计数。
=> SELECT product_key, COUNT (DISTINCT date_key), COUNT (DISTINCT warehouse_key) FROM inventory_fact
GROUP BY product_key LIMIT 15;
product_key | count | count
-------------+-------+-------
1 | 12 | 12
2 | 18 | 18
3 | 13 | 12
4 | 17 | 18
5 | 11 | 9
6 | 14 | 13
7 | 13 | 13
8 | 17 | 15
9 | 15 | 14
10 | 12 | 12
11 | 11 | 11
12 | 13 | 12
13 | 9 | 7
14 | 13 | 13
15 | 18 | 17
(15 rows)
该查询会选择每个不同的 product_key 值，为具有特定 date_key 值的所有记录的不同 warehouse_key 和 product_key 值进行计数，然后对具有特定 qty_in_stock 值的记录中的所有 product_key 值进行求和。然后，它会返回具有特定 product_version 值的记录中 product_key 值的数量。
=> SELECT product_key, COUNT (DISTINCT date_key),
COUNT (DISTINCT warehouse_key),
SUM (qty_in_stock),
COUNT (product_version)
FROM inventory_fact GROUP BY product_key LIMIT 15;
product_key | count | count | sum | count
-------------+-------+-------+-------+-------
1 | 12 | 12 | 5530 | 12
2 | 18 | 18 | 9605 | 18
3 | 13 | 12 | 8404 | 13
4 | 17 | 18 | 10006 | 18
5 | 11 | 9 | 4794 | 11
6 | 14 | 13 | 7359 | 14
7 | 13 | 13 | 7828 | 13
8 | 17 | 15 | 9074 | 17
9 | 15 | 14 | 7032 | 15
10 | 12 | 12 | 5359 | 12
11 | 11 | 11 | 6049 | 11
12 | 13 | 12 | 6075 | 13
13 | 9 | 7 | 3470 | 9
14 | 13 | 13 | 5125 | 13
15 | 18 | 17 | 9277 | 18
(15 rows)
以下示例会返回 warehouse 维度表中的仓库数。
=> SELECT COUNT(warehouse_name) FROM warehouse_dimension;
COUNT
-------
100
(1 row)
下一个示例会返回供应商总数：
=> SELECT COUNT(*) FROM vendor_dimension;
COUNT
-------
50
(1 row)
3 - 近似计数区分函数
聚合函数 COUNT(DISTINCT) 计算数据集中非重复值的确切数量。COUNT(DISTINCT) 在使用 GROUPBY PIPELINED 算法执行计算时表现良好。
当满足以下条件时，聚合 COUNT 操作在数据集上表现良好：
-
目标表投影之一包含 ORDER BY 子句，便于对聚合进行排序。
-
非重复值的数量非常小。
-
需要使用哈希聚合来执行查询。
或者，当满足以下条件时，考虑使用 APPROXIMATE_COUNT_DISTINCT 函数而不是 COUNT(DISTINCT)：
-
您有一个大型数据集，而且您不需要获得非重复值的确切数量。
-
COUNT(DISTINCT) 对于给定数据集的性能是不足的。
-
您在同一个查询中计算几个非重复值计数。
-
COUNT(DISTINCT) 的计划使用哈希聚合。
APPROXIMATE_COUNT_DISTINCT 返回的预期值等于 COUNT(DISTINCT)，误差呈对数正态分布，标准差为 s。您可以通过设置函数的可选误差容限实参来控制标准偏差（默认情况下为 1.25%）。
其他 APPROXIMATE_COUNT_DISTINCT 函数
下面是 Vertica 支持您一起使用的其他两个函数（而不是 APPROXIMATE_COUNT_DISTINCT）： APPROXIMATE_COUNT_DISTINCT_SYNOPSIS 和 APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS。当满足以下条件时使用这些函数：
-
您有一个大型数据集，而且您不需要获得非重复值的确切数量。
-
COUNT(DISTINCT) 对于给定数据集的性能是不足的。
-
您希望预先计算非重复值计数，并在之后使用不同的方法合并它们。
将这两个函数一起使用，如下所示：
-
向 APPROXIMATE_COUNT_DISTINCT_SYNOPSIS 传递数据集以及正态分布的置信区间。此函数会返回称为“二进制概要对象”的数据子集。
-
将概要传递给 APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS 函数，然后对概要计算非重复值近似计数。
您还可以使用 APPROXIMATE_COUNT_DISTINCT_SYNOPSIS_MERGE，它将多个概要合并为一个概要。使用此功能，您可以通过合并一个或多个涵盖最近、较短时间段的概要来不断更新“主”概要。
示例
以下示例显示如何使用 APPROXIMATE_COUNT_DISTINCT 函数来保持给定时间段内单击给定网页的用户的大致计数。
-
创建
pviews表来存储有关网站访问的数据（访问时间、访问的网页和访问者）：=> CREATE TABLE pviews( visit_time TIMESTAMP NOT NULL, page_id INTEGER NOT NULL, user_id INTEGER NOT NULL) ORDER BY page_id, visit_time SEGMENTED BY HASH(user_id) ALL NODES KSAFE PARTITION BY visit_time::DATE GROUP BY CALENDAR_HIERARCHY_DAY(visit_time::DATE, 2, 2);pviews通过散列user_id数据进行分段，因此给定用户的所有访问都存储在同一节点的同一段上。这可以防止稍后执行 COUNT (DISTINCT user_id) 时数据低效跨节点传输。该表还在访问时使用分层分区来优化 ROS 存储。这样做可以提高按时间筛选数据时的性能。
-
将数据加载到
pviews中：=> INSERT INTO pviews VALUES ('2022-02-01 10:00:02',1002,1), ('2022-02-01 10:00:03',1002,2), ('2022-02-01 10:00:04',1002,1), ('2022-02-01 10:00:05',1002,3), ('2022-02-01 10:00:01',1000,1), ('2022-02-01 10:00:06',1002,1), ('2022-02-01 10:00:07',1002,3), ('2022-02-01 10:00:08',1002,1), ('2022-02-01 10:00:09',1002,3), ('2022-02-01 10:00:12',1002,2), ('2022-02-02 10:00:01',1000,1), ('2022-02-02 10:00:02',1002,4), ('2022-02-02 10:00:03',1002,2), ('2022-02-02 10:00:04',1002,1), ('2022-02-02 10:00:05',1002,3), ('2022-02-02 10:00:06',1002,4), ('2022-02-02 10:00:07',1002,3), ('2022-02-02 10:00:08',1002,4), ('2022-02-02 10:00:09',1002,3), ('2022-02-02 10:00:12',1002,2), ('2022-03-02 10:00:01',1000,1), ('2022-03-02 10:00:02',1002,1), ('2022-03-02 10:00:03',1002,2), ('2022-03-02 10:00:04',1002,1), ('2022-03-02 10:00:05',1002,3), ('2022-03-02 10:00:06',1002,4), ('2022-03-02 10:00:07',1002,3), ('2022-03-02 10:00:08',1002,6), ('2022-03-02 10:00:09',1002,5), ('2022-03-02 10:00:12',1002,2), ('2022-03-02 11:00:01',1000,5), ('2022-03-02 11:00:02',1002,6), ('2022-03-02 11:00:03',1002,7), ('2022-03-02 11:00:04',1002,4), ('2022-03-02 11:00:05',1002,1), ('2022-03-02 11:00:06',1002,6), ('2022-03-02 11:00:07',1002,8), ('2022-03-02 11:00:08',1002,6), ('2022-03-02 11:00:09',1002,7), ('2022-03-02 11:00:12',1002,1), ('2022-03-03 10:00:01',1000,1), ('2022-03-03 10:00:02',1002,2), ('2022-03-03 10:00:03',1002,4), ('2022-03-03 10:00:04',1002,1), ('2022-03-03 10:00:05',1002,2), ('2022-03-03 10:00:06',1002,6), ('2022-03-03 10:00:07',1002,9), ('2022-03-03 10:00:08',1002,10), ('2022-03-03 10:00:09',1002,7), ('2022-03-03 10:00:12',1002,1); OUTPUT -------- 50 (1 row) => COMMIT; COMMIT -
通过使用 CREATE TABLE...AS SELECT 查询
pviews来创建pview_summary表。此表的每一行汇总了从pviews中选择的给定日期的数据：-
partial_visit_count在pviews中存储具有该日期的行数（网站访问）。 -
daily_users_acdp使用 APPROXIMATE_COUNT_DISTINCT_SYNOPSIS 构建一个概要，该概要近似计算在该日期访问该网站的不同用户 (user_id) 的数量。
=> CREATE TABLE pview_summary AS SELECT visit_time::DATE "date", COUNT(*) partial_visit_count, APPROXIMATE_COUNT_DISTINCT_SYNOPSIS(user_id) AS daily_users_acdp FROM pviews GROUP BY 1; CREATE TABLE => ALTER TABLE pview_summary ALTER COLUMN "date" SET NOT NULL; -
-
更新
pview_summary表，使其像pviews一样进行分区。REORGANIZE 关键字强制表数据的立即重新分区：=> ALTER TABLE pview_summary PARTITION BY "date" GROUP BY CALENDAR_HIERARCHY_DAY("date", 2, 2) REORGANIZE; vsql:/home/ale/acd_ex4.sql:93: NOTICE 8364: The new partitioning scheme will produce partitions in 2 physical storage containers per projection vsql:/home/ale/acd_ex4.sql:93: NOTICE 4785: Started background repartition table task ALTER TABLE -
使用 CREATE TABLE...LIKE 创建两个 ETL 表
pviews_etl和pview_summary_etl，它们分别具有与pviews和pview_summary相同的 DDL。这些表用于处理传入数据：=> CREATE TABLE pviews_etl LIKE pviews INCLUDING PROJECTIONS; CREATE TABLE => CREATE TABLE pview_summary_etl LIKE pview_summary INCLUDING PROJECTIONS; CREATE TABLE -
将新数据加载到
pviews_etl：=> INSERT INTO pviews_etl VALUES ('2022-03-03 11:00:01',1000,8), ('2022-03-03 11:00:02',1002,9), ('2022-03-03 11:00:03',1002,1), ('2022-03-03 11:00:04',1002,11), ('2022-03-03 11:00:05',1002,10), ('2022-03-03 11:00:06',1002,12), ('2022-03-03 11:00:07',1002,3), ('2022-03-03 11:00:08',1002,10), ('2022-03-03 11:00:09',1002,1), ('2022-03-03 11:00:12',1002,1); OUTPUT -------- 10 (1 row) => COMMIT; COMMIT -
在
pview_summary_etl中汇总新数据：=> INSERT INTO pview_summary_etl SELECT visit_time::DATE visit_date, COUNT(*) partial_visit_count, APPROXIMATE_COUNT_DISTINCT_SYNOPSIS(user_id) AS daily_users_acdp FROM pviews_etl GROUP BY visit_date; OUTPUT -------- 1 (1 row) -
使用 COPY_PARTITIONS_TO_TABLE 将
pviews_etl数据附加到pviews：=> SELECT COPY_PARTITIONS_TO_TABLE('pviews_etl', '01-01-0000'::DATE, '01-01-9999'::DATE, 'pviews'); COPY_PARTITIONS_TO_TABLE ---------------------------------------------------- 1 distinct partition values copied at epoch 1403. (1 row) => SELECT COPY_PARTITIONS_TO_TABLE('pview_summary_etl', '01-01-0000'::DATE, '01-01-9999'::DATE, 'pview_summary'); COPY_PARTITIONS_TO_TABLE ---------------------------------------------------- 1 distinct partition values copied at epoch 1404. (1 row) -
为所有数据（包括刚刚从
pviews_etl复制的分区）创建视图和不同（近似）视图：=> SELECT "date" visit_date, SUM(partial_visit_count) visit_count, APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS(daily_users_acdp) AS daily_users_acd FROM pview_summary GROUP BY visit_date ORDER BY visit_date; visit_date | visit_count | daily_users_acd ------------+-------------+----------------- 2022-02-01 | 10 | 3 2022-02-02 | 10 | 4 2022-03-02 | 20 | 8 2022-03-03 | 20 | 11 (4 rows) -
按月创建视图和不同（近似）视图：
=> SELECT DATE_TRUNC('MONTH', "date")::DATE "month", SUM(partial_visit_count) visit_count, APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS(daily_users_acdp) AS monthly_users_acd FROM pview_summary GROUP BY month ORDER BY month; month | visit_count | monthly_users_acd ------------+-------------+------------------- 2022-02-01 | 20 | 4 2022-03-01 | 40 | 12 (2 rows) -
将每日概要合并到每月概要：
=> CREATE TABLE pview_monthly_summary AS SELECT DATE_TRUNC('MONTH', "date")::DATE "month", SUM(partial_visit_count) partial_visit_count, APPROXIMATE_COUNT_DISTINCT_SYNOPSIS_MERGE(daily_users_acdp) AS monthly_users_acdp FROM pview_summary GROUP BY month ORDER BY month; CREATE TABLE -
按月创建根据合并的概要生成的视图和不同的视图：
=> SELECT month, SUM(partial_visit_count) monthly_visit_count, APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS(monthly_users_acdp) AS monthly_users_acd FROM pview_monthly_summary GROUP BY month ORDER BY month; month | monthly_visit_count | monthly_users_acd ------------+---------------------+------------------- 2019-02-01 | 20 | 4 2019-03-01 | 40 | 12 (2 rows) -
您可以使用每月摘要生成年度摘要。当需要处理大量数据时，这种方法可能比使用每日摘要快：
=> SELECT DATE_TRUNC('YEAR', "month")::DATE "year", SUM(partial_visit_count) yearly_visit_count, APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS(monthly_users_acdp) AS yearly_users_acd FROM pview_monthly_summary GROUP BY year ORDER BY year; year | yearly_visit_count | yearly_users_acd ------------+--------------------+------------------ 2022-01-01 | 60 | 12 (1 row) -
删除 ETL 表：
=> DROP TABLE IF EXISTS pviews_etl, pview_summary_etl; DROP TABLE
另请参阅
4 - 单个 DISTINCT 聚合
计算 DISTINCT 聚合时，Vertica 首先会删除聚合参数中的所有重复值，从而找到非重复值。然后，它会计算聚合。
例如，您可以将以下查询：
SELECT a, b, COUNT(DISTINCT c) AS dcnt FROM table1 GROUP BY a, b;
重写为：
SELECT a, b, COUNT(dcnt) FROM
(SELECT a, b, c AS dcnt FROM table1 GROUP BY a, b, c)
GROUP BY a, b;
为了最大限度提高执行速度，请为 GROUP BY 查询应用优化技术。
5 - 多个 DISTINCT 聚合
如果查询具有多个 DISTINCT 聚合，则无法通过简单地重写 SQL 来计算它们。以下查询就无法通过简单地重写来提高性能：
SELECT a, COUNT(DISTINCT b), COUNT(DISTINCT c) AS dcnt FROM table1 GROUP BY a;
对于具有多个 DISTINCT 聚合的查询，没有一种投影设计可以避免使用 GROUPBY HASH 以及对数据进行重新分段。要提升此查询的性能，请确保它具有大量内存可用。有关为查询分配内存的详细信息，请参阅资源管理器。