Categories:: Aggregate functions (General) String & binary functions (Large Language Model)

AI_AGG¶

Reduces a column of text data using a natural language task description.

For example, AI_AGG(reviews, 'Summarize the book reviews in 200 words') will return a summary of user feedback.

Unlike COMPLETE (SNOWFLAKE.CORTEX) and SUMMARIZE (SNOWFLAKE.CORTEX), this function supports datasets larger than the maximum language model context window.

See also:: AI_SUMMARIZE_AGG

Syntax¶

AI_AGG( <expr>, <task_description> )

Copy

Arguments¶

Required:

expr: This is an expression that contains text on which an aggregation operation is to be performed, such as restaurant reviews or phone transcripts.
task_description: A string containing a natural language description of the aggregation to perform, for example “Summarize the reviews” or “Identify all people mentioned and write a short biography for each of them”.

Returns¶

Returns a string containing the result of the aggregation.

Usage notes¶

For optimal performance, follow these guidelines:

Use plain English text for the task description.
Describe the text provided in the task description. For example, instead of a task description like “summarize”, use “Summarize the phone call transcripts”.
Describe the intended use case. For example, instead of “find the best review”, use “Find the most positive and well-written restaurant review to highlight on the restaurant website”.
Consider breaking the task description into multiple steps. For example, instead of “Summarize the new articles”, use “You will be provided with news articles from various publishers presenting events from different points of view. Please create a concise and elaborative summary of source texts without missing any crucial information.”.

Examples¶

AI_AGG can be used as a simple scalar function on string constants. In the following example, AI_AGG is used to summarize product ratings, which are provided as a single string.

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

Copy

This product has a generally positive rating, with most reviewers praising its quality. Key strengths include high satisfaction levels, with 2 reviewers giving it an "Excellent" rating. However, some reviewers had a more neutral experience, with 1 rating it "Mediocre". Overall, it's a solid choice, but may not exceed expectations for everyone.

AI_AGG can also be used on a column of data. In the following example, the product ratings from the above example are provided as a column in a table using a Common Table Expression.

WITH reviews AS (
            SELECT 'Excellent' AS review
  UNION ALL SELECT 'Excellent'
  UNION ALL SELECT 'Great'
  UNION ALL SELECT 'Mediocre'
)
SELECT AI_AGG(review,
              'Summarize the product ratings for a blog post targeting consumers')
  FROM reviews;

Copy

This product has a 4.25/5 overall rating, with most reviewers (2) giving it an "Excellent" rating and one reviewer giving it a "Great" rating. The majority of consumers are impressed with its performance, suggesting it's a reliable and high-quality option. However, one reviewer had a mediocre experience, indicating it may not meet everyone's expectations.

AI_AGG can also be used in combination with GROUP BY. The following example summarizes product ratings for two products (identified by the column product_id) in a table of reviews.

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'
)
SELECT product_id,
       AI_AGG(review, 'Summarize the product ratings for a blog post targeting consumers') AS summarized_review
  FROM reviews
 GROUP BY 1;

Copy

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PRODUCT_ID | SUMMARIZED_REVIEW                                                                                                                                                                                                                                                                                                                                                     |
|------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1          | This product has a 4.25/5 overall rating, with most reviewers (2) giving it an "Excellent" rating and one reviewer giving it a "Great" rating. The majority of consumers are impressed with its performance, suggesting it's a reliable and high-quality option. However, one reviewer had a mediocre experience, indicating it may not meet everyone's expectations. |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2          | This product has received extremely negative reviews, with a 1/5-star rating based on 2 reviews. Consumers have rated it as "Terrible" and "Bad", citing no redeeming qualities or positive aspects. The product has failed to meet user expectations, and potential buyers are advised to exercise caution before purchasing.                                        |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The task description can be used for various aggregation tasks and to configure the style and tone of the response. The following example uses a task description to find the most positive rating for each product and provide French and Polish translations of the rating.

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;

Copy

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