类别：

/sql-reference/functions-aggregation`（通用）:doc:/sql-reference/functions-string`（大语言模型）

AI_AGG

使用自然语言指令减少一列文本数据。

例如，AI_AGG(reviews, 'Describe the most common complaints mentioned in the book reviews') 将返回用户反馈的摘要。

与 COMPLETE (SNOWFLAKE.CORTEX) 和 SUMMARIZE (SNOWFLAKE.CORTEX) 不同，此函数支持大于最大语言模型上下文窗口的数据集。

另请参阅:

AI_SUMMARIZE_AGG

语法

AI_AGG( <expr>, <instruction> )

实参

必填：

expr

此表达式包含要对执行聚合操作的文本，例如餐厅评论或电话记录。

instruction

包含要执行的聚合的、使用自然语言规范的字符串，例如“总结评论”或“识别提及的所有人，并为他们写一段简短的传记”。

返回

返回包含聚合的结果的字符串。

在以下情况下，该函数可能表明您提供的数据不包含答案：

  • 您没有提供明确的指令来指定如何汇总数据

  • 数据中没有完成指令所需的信息

使用说明

为了获得最佳性能，请遵循以下指导原则：

  • 使用纯英文文本作为指令。

  • 提供陈述性指令，而非提问。例如，使用“汇总电话记录”，而非提出“可以汇总一下吗？”这种问题。

  • 描述指令中提供的文本。例如，使用“汇总电话记录”，而非“汇总”这样的指令。

  • 描述预期用例。例如，并非“查找最佳评论”，而是使用“查找适合在餐厅网站上重点展示的最正面、写得最好的餐厅评论”。

  • Multiple columns can be used in the string expression using CONCAT or the || operator. See the example below.

  • 考虑将指令分成多个步骤。例如，不是“总结新文章”，而是使用“您将收到多篇来自不同出版商的新闻文章，它们从不同角度介绍事件。请在不遗漏任何关键信息的情况下，创建一份简明但详尽的源文本摘要。”

示例

AI_AGG 可以用作字符串常量的简单标量函数。在以下示例中，AI_AGG 用于汇总产品评价，这些评价以单个字符串形式提供。

SELECT AI_AGG('[Excellent, Excellent, Great, Mediocre]',
              'Summarize the product ratings for a blog post targeting consumers');

Overall, the product has received overwhelmingly positive reviews, with the majority of users rating it as 'Excellent' or 'Great'. Only a small percentage of users had a mediocre experience with the product. This suggests that the product is well-liked by most consumers and is a great option for those looking for a reliable choice.

AI_AGG 也可以用于一列数据。在以下示例中，使用 公用表表达式 将上述示例中的产品评价作为表中的列提供。

WITH reviews AS (
            SELECT 'The restaurant was excellent.' AS review
  UNION ALL SELECT 'Excellent! I loved the pizza!'
  UNION ALL SELECT 'It was great, but the service was meh.'
  UNION ALL SELECT 'Mediocre food and mediocre service'
)
SELECT AI_AGG(review, 'Summarize the restaurant reviews for potential consumers')
  FROM reviews;

Reviews for this restaurant are mixed. Some customers had a very positive experience, describing the restaurant as "excellent" and loving the pizza. However, others had a more neutral or negative experience, citing mediocre food and service.

AI_AGG can be used on multiple columns of data using CONCAT or the || operator.

WITH reviews AS (
            SELECT 'The restaurant was excellent.' AS review, 'Pizza' AS menu_item
  UNION ALL SELECT 'Excellent! I loved the pizza!', 'Pizza'
  UNION ALL SELECT 'It was great, but the service was meh.', 'Burger'
  UNION ALL SELECT 'Mediocre food and mediocre service', 'Pancakes'
)
SELECT AI_AGG('Menu Item: ' || menu_item || '\nReview: ' || review,
              'Summarize the restaurant reviews for potential consumers')
  FROM reviews;

Based on the reviews, the restaurant seems to receive high praise for their pizza, with two reviews using the word "excellent" to describe their experience. However, the reviews for other menu items, such as burgers and pancakes, are more mixed, with some customers expressing disappointment with the service or finding the food to be just mediocre. Overall, potential consumers may want to consider ordering pizza if they decide to dine at this restaurant.

AI_AGG 也可以与 GROUP BY 结合使用。以下示例汇总了评论表中两种产品（由列标识 product_id 区分）的产品评价。

WITH reviews AS (
            SELECT 1 AS restaurant_id, 'The restaurant was excellent.' AS review
  UNION ALL SELECT 1, 'Excellent! I loved the pizza!'
  UNION ALL SELECT 1, 'It was great, but the service was meh.'
  UNION ALL SELECT 1, 'Mediocre food and mediocre service'
  UNION ALL SELECT 2, 'Terrible quality ingredients, I should have eaten at home.'
  UNION ALL SELECT 2, 'Bad restaurant, I would avoid this place.'
)
SELECT restaurant_id,
       AI_AGG(review, 'Summarize the restaurant reviews for potential consumers')
  FROM reviews
 GROUP BY 1;

+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| RESTAURANT_ID | SUMMARIZED_REVIEW                                                                                                                                                                                                                                 |
|---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1             | Reviews for this restaurant are mixed. Some customers had a very positive experience, describing the restaurant as "excellent" and loving the pizza. However, others had a more neutral or negative experience, citing mediocre food and service. |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2             | Two reviewers had extremely negative experiences at this restaurant, citing poor quality ingredients and advising others to avoid it.                                                                                                             |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

指令可用于各种聚合任务，也可用于配置回应的风格和语气。以下示例使用指令来查找每种商品的最正面评价，并提供该评价的法语和波兰语翻译。

WITH reviews AS (
            SELECT 1 AS product_id, 'Excellent' AS review
  UNION ALL SELECT 1, 'Excellent'
  UNION ALL SELECT 1, 'Great'
  UNION ALL SELECT 1, 'Mediocre'
  UNION ALL SELECT 2, 'Terrible'
  UNION ALL SELECT 2, 'Bad'
  UNION ALL SELECT 2, 'Average'
)
SELECT product_id,
       AI_AGG(review, 'Identify the most positive rating and translate it into French and Polish, one word only') AS summarized_review
  FROM reviews
 GROUP BY 1;

+------------+--------------------+
| PRODUCT_ID | SUMMARIZED_REVIEW  |
|------------+--------------------+
| 1          | French: Excellent  |
|            | Polish: Doskonały  |
+------------+--------------------+
| 2          | French: Moyen      |
|            | Polish: Przeciętny |
+------------+--------------------+

另请参阅 AI_SUMMARIZE_AGG。