AI_AGG

Syntax

AI_AGG([<resource_name>], <expr>, <instruction>)

Parameters

Return value

• Returns the aggregated result as a string.

• Returns NULL if all input values for a group are NULL.

• The result is generated by a large language model, so the return value is non-deterministic.

Examples

Example 1: The following statement creates a table that simulates a support ticket system.

CREATE TABLE support_tickets (
    ticket_id      BIGINT,
    customer_name  VARCHAR(100),
    subject        VARCHAR(200),
    details        TEXT
)
DUPLICATE KEY(ticket_id)
DISTRIBUTED BY HASH(ticket_id) BUCKETS 5
PROPERTIES (
    "replication_num" = "1"
);

INSERT INTO support_tickets VALUES
(1, 'Alice', 'Login Failure', 'Cannot log in after password reset. Tried clearing cache and different browsers.'),
(2, 'Bob', 'Login Failure', 'Same problem as Alice. Also seeing 502 errors on the SSO page.'),
(3, 'Carol', 'Payment Declined', 'Credit card charged twice but order still shows pending.'),
(4, 'Dave', 'Slow Dashboard', 'Dashboard takes >30 seconds to load since the last release.'),
(5, 'Eve', 'Login Failure', 'Getting redirected back to login after entering 2FA code.');

You can use the AI_AGG function to summarize customer issues by problem type.

SELECT
    subject,
    AI_AGG(
        'ai_resource_name',
        details,
        'Summarize every ticket detail into one short paragraph of 40 words or less.'
    ) AS ai_summary
FROM support_tickets
GROUP BY subject;

+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| subject          | ai_summary                                                                                                                                                                                                |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Slow Dashboard   | The dashboard loading time has significantly increased to over 30 seconds following the latest release, indicating a potential issue with the recent update.                                              |
| Login Failure    | User experiences login issues, including redirection post-2FA, inability to log in after password reset despite using different browsers and clearing cache, and encountering 502 errors on the SSO page. |
| Payment Declined | The customer's credit card was charged twice, but the order status remains pending, indicating a potential issue with the transaction processing or system update.                                        |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Example 2: The following statement creates a table that simulates a user review table for an e-commerce platform.

CREATE TABLE product_reviews (
    review_id   BIGINT,
    product_id  BIGINT,
    rating      TINYINT,
    comment     STRING
)
DUPLICATE KEY(review_id)
DISTRIBUTED BY HASH(product_id) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);

INSERT INTO product_reviews VALUES
(1, 1001, 5, 'The shoes fit true to size, are comfortable to wear, look great, and shipping is fast!'),
(2, 1001, 4, 'Good quality, but the sole is a bit stiff and needs a few days to break in.'),
(3, 1001, 3, 'Looks the same as the picture, but there was a slight glue smell upon arrival.'),
(4, 1002, 5, 'The cup is compact, juices quickly, and is easy to clean. Perfect to bring to the office.'),
(5, 1002, 3, 'It''s a bit loud, but acceptable. A full charge can only make about 5 cups.'),
(6, 1002, 2, 'After two weeks, it stopped charging. The after-sales exchange process is too slow.'),
(7, 1003, 5, 'The fabric is breathable and not stuffy. The cuff design is thoughtful, and UPF50+ really works.'),
(8, 1003, 4, 'Nice color, but the zipper sticks and requires some force to pull.'),
(9, 1004, 5, 'Excellent noise cancellation, so I can listen to music quietly on the subway. The battery lasts a week per charge.');

Use AI_AGG to create review summaries for each product:

SET default_ai_resource = 'ai_resource_name';
SELECT
    product_id,
    AI_AGG(
        comment,
        'Summarize multiple user reviews into a single sentence, highlighting the pros and cons that buyers care about most. Keep it under 50 words.'
    ) AS review_summary
FROM product_reviews
GROUP BY product_id;

+------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| product_id | review_summary                                                                                                                                    |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
|       1003 | The product features breathable fabric, good sun protection, and an attractive color, but the zipper is not smooth.                                 |
|       1004 | Users praise the product for its excellent noise cancellation and long battery life, requiring only one charge per week.                            |
|       1001 | Buyers generally find the shoes comfortable and stylish, and appreciate the fast shipping, but note the sole is stiff and there is a slight smell. |
|       1002 | Buyers find the juicer cup compact and easy to clean, but complain about short battery life and a slow after-sales exchange process.                |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------+