AI_AGG
Description
Aggregates a specific column using a large language model according to user-provided instructions.
Syntax
AI_AGG([<resource_name>], <expr>, <instruction>)
Parameter Description
| Parameter | Description |
|---|---|
<resource_name> | The specified resource name, optional. |
<expr> | The text column to aggregate. The number of characters in a single text must be less than 128K. |
<instruction> | The instruction to execute, only accepts literals. |
Return Value
Returns a string containing the aggregation result.
If all input values are NULL, returns NULL.
The result is generated by the language model, so the output is not fixed.
Example
The following table simulates customer support tickets:
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 AI_AGG to summarize customer issues by problem subject:
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. |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
case 2：
The following table simulates the user review table of 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 well, feel comfortable, look good, and arrived quickly!'),
(2, 1001, 4, 'The quality is good, but the soles are a little stiff and need a few days to break in.'),
(3, 1001, 3, 'The appearance matches the picture, but there was a slight glue odor when it arrived.'),
(4, 1002, 5, 'The cup is compact, juices quickly, and is easy to clean. It is convenient to take to work.'),
(5, 1002, 3, 'It is somewhat loud but acceptable. A full charge makes only five cups.'),
(6, 1002, 2, 'It could not be charged after two weeks, and the replacement process was too slow.'),
(7, 1003, 5, 'The fabric is breathable, the cuffs are thoughtfully designed, and the UPF50+ protection works well.'),
(8, 1003, 4, 'The color looks good, but the zipper sticks and requires force.'),
(9, 1004, 5, 'The noise cancellation works well on the subway, and the battery lasts a week on one charge.');
Using AI_AGG to summarize and evaluate:
SET default_ai_resource = 'ai_resource_name';
SELECT
product_id,
AI_AGG(
comment,
'Summarize multiple customer reviews in one sentence. Highlight the benefits and drawbacks that matter most to buyers, using no more than 50 characters.'
) AS Review summary
FROM product_reviews
GROUP BY product_id;
+------------+--------------------------------------------------------------------------------------------------------------+
| product_id | Review summary |
+------------+--------------------------------------------------------------------------------------------------------------+
| 1003 | The product is breathable, provides good sun protection, and has an attractive color, but the zipper is difficult to use. |
| 1004 | Users rate the product highly for noise cancellation and battery life. It needs to be charged once a week. |
| 1001 | Buyers generally find the shoes comfortable, attractive, and quick to ship, but the soles are stiff and have a slight glue odor. |
| 1002 | Buyers find the juicer cup compact, portable, fast, and easy to clean, but its battery life is short and the replacement process is slow. |
+------------+--------------------------------------------------------------------------------------------------------------+