分析函数路径

Vertica 通过将同一查询中的多个 SQL-99 分析函数分组到 Analytic Group 区域，来尝试对这些分析函数进行优化。

对于每个分析组，Vertica 将根据需要执行数据分布式排序和重新分段。

可根据查询计划判断需要多少次排序和重新分段。

例如，以下查询计划显示 FIRST_VALUE 和 LAST_VALUE 函数在同一分析组中，因为它们的 OVER 子句相同。相反，ROW_NUMBER() 具有不同的 ORDER BY 子句，因此它在其他分析组中。由于这两个组共享同一 PARTITION BY deal_stage 子句，因此无需在组间对数据重新分段：

EXPLAIN SELECT
  first_value(deal_size) OVER (PARTITION BY deal_stage
  ORDER BY deal_size),
  last_value(deal_size) OVER (PARTITION BY deal_stage
  ORDER BY deal_size),
  row_number() OVER (PARTITION BY deal_stage
  ORDER BY largest_bill_amount)
  FROM customer_dimension;

 Access Path:
 +-ANALYTICAL [Cost: 1K, Rows: 50K] (PATH ID: 1)
 |  Analytic Group
 |   Functions: row_number()
 |   Group Sort: customer_dimension.deal_stage ASC, customer_dimension.largest_bill_amount ASC NULLS LAST
 |  Analytic Group
 |   Functions: first_value(), last_value()
 |   Group Filter: customer_dimension.deal_stage
 |   Group Sort: customer_dimension.deal_stage ASC, customer_dimension.deal_size ASC NULL LAST
 |  Execute on: All Nodes
 | +---> STORAGE ACCESS for customer_dimension [Cost: 263, Rows: 50K]
         (PATH ID: 2)
 | |      Projection: public.customer_dimension_DBD_1_rep_vmart_vmart_node0001
 | |      Materialize: customer_dimension.largest_bill_amount,
          customer_dimension.deal_stage, customer_dimension.deal_size
 | |      Execute on: All Nodes

另请参阅

调用分析函数