聚合查询

对查询结果进行分组和聚合

聚合查询允许您执行以下作：

  • 应用简单的映射功能。
  • 根据字段值对数据进行分组。
  • 对分组数据应用聚合函数。

本文介绍了 FT 的基本用法。AGGREGATE 命令。有关更多详细信息，请参阅命令规范和聚合参考文档。

本文中的示例使用具有以下字段的架构：

字段名称 字段类型
condition TAG
price NUMERIC

简单映射

这APPLY子句允许您将简单映射函数应用于基于查询表达式返回的结果集。

FT.AGGREGATE index "query_expr" LOAD n "field_1" .. "field_n" APPLY "function_expr" AS "result_field"

以下是查询语法的更详细说明：

  1. 查询表达式：您可以使用与FT.SEARCH命令。您可以替换query_expr替换为此查询主题的文章中解释的任何表达式。向量搜索查询是一个例外。您不能将向量搜索与聚合查询结合使用。
  2. Loaded fields：如果字段值尚未加载到聚合管道中，您可以通过LOAD第。此子句采用字段数 （n），后跟字段名称 （"field_1" .. "field_n").
  3. Mapping function：此 mapping 函数对字段值进行作。特定字段引用为@field_name在函数表达式中。结果以result_field.

以下示例说明如何计算新自行车的折扣价格：

The field __key is a built-in field.

The output of this query is:

1) "1"
2) 1) "__key"
   1) "bicycle:0"
   2) "price"
   3) "270"
   4) "discounted"
   5) "243"
3) 1) "__key"
   1) "bicycle:5"
   2) "price"
   3) "810"
   4) "discounted"
   5) "729"
4) 1) "__key"
   1) "bicycle:6"
   2) "price"
   3) "2300"
   4) "discounted"
   5) "2070"
...

Grouping with aggregation

The previous example did not group the data. You can group and aggregate data based on one or many criteria in the following way:

FT.AGGREGATE index "query_expr" ...  GROUPBY n "field_1" .. "field_n" REDUCE AGG_FUNC m "@field_param_1" .. "@field_param_m" AS "aggregated_result_field"

Here is an explanation of the additional constructs:

  1. Grouping: you can group by one or many fields. Each ordered sequence of field values then defines one group. It's also possible to group by values that resulted from a previous APPLY ... AS.
  2. Aggregation: you must replace AGG_FUNC with one of the supported aggregation functions (e.g., SUM or COUNT). A complete list of functions is available in the aggregations reference documentation. Replace aggregated_result_field with a value of your choice.

The following query shows you how to group by the field condition and apply a reduction based on the previously derived price_category. The expression @price<1000 causes a bicycle to have the price category 1 if its price is lower than 1000 USD. Otherwise, it has the price category 0. The output is the number of affordable bicycles grouped by price category.

1) "3"
2) 1) "condition"
   1) "refurbished"
   2) "num_affordable"
   3) "1"
3) 1) "condition"
   1) "used"
   2) "num_affordable"
   3) "1"
4) 1) "condition"
   1) "new"
   2) "num_affordable"
   3) "3"
Note:
You can also create more complex aggregation pipelines with FT.AGGREGATE. Applying multiple reduction functions under one GROUPBY clause is possible. In addition, you can also chain groupings and mix in additional mapping steps (e.g., GROUPBY ... REDUCE ... APPLY ... GROUPBY ... REDUCE)

Aggregating without grouping

You can't use an aggregation function outside of a GROUPBY clause, but you can construct your pipeline in a way that the aggregation happens on a single group that spans all documents. If your documents don't share a common attribute, you can add it via an extra APPLY step.

Here is an example that adds a type attribute bicycle to each document before counting all documents with that type:

The result is:

1) "1"
2) 1) "type"
   1) "bicycle"
   2) "num_total"
   3) "10"

Grouping without aggregation

It's sometimes necessary to group your data without applying a mathematical aggregation function. If you need a grouped list of values, then the TOLIST function is helpful.

The following example shows how to group all bicycles by condition:

The output of this query is:

1) "3"
2) 1) "condition"
   1) "refurbished"
   2) "bicycles"
   3) 1) "bicycle:9"
3) 1) "condition"
   1) "used"
   2) "bicycles"
   3) 1) "bicycle:1"
      1) "bicycle:2"
      2) "bicycle:3"
      3) "bicycle:4"
4) 1) "condition"
   1) "new"
   2) "bicycles"
   3) 1) "bicycle:0"
      1) "bicycle:5"
      2) "bicycle:6"
      3) "bicycle:8"
      4) "bicycle:7"
RATE THIS PAGE
Back to top ↑