ApsaraDB for SelectDB：AI_AGG

文法

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

參數說明

傳回值

• 返回包含彙總結果的字串。

• 當輸入有值全為 NULL 時返回 NULL。

• 結果為大模型產生，所以返回內容並不固定。

舉例

樣本 1： 如下表類比某個客服工單:

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.');

可以通過 AI_AGG 總結不同問題類型下客戶遇到的問題

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.                                        |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

樣本 2： 下表類比了電商平台的使用者評價表

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, '鞋子尺碼剛好，穿著舒服，顏色也好看，物流很快！'),
(2, 1001, 4, '品質不錯，就是鞋底有點硬，需要磨合幾天。'),
(3, 1001, 3, '外觀和圖片一樣，但收到時有輕微膠味。'),
(4, 1002, 5, '杯子小巧，出汁快，清洗也方便，上班帶著剛好。'),
(5, 1002, 3, '聲音有點大，不過能接受，充滿電只能榨 5 杯。'),
(6, 1002, 2, '用了兩周就充不進電，售後換貨流程太慢。'),
(7, 1003, 5, '面料透氣不悶熱，袖口設計很貼心，UPF50+ 確實曬不黑。'),
(8, 1003, 4, '顏色好看，但拉鏈有點卡頓，需要用力。'),
(9, 1004, 5, '降噪給力，地鐵裡也能安靜聽歌，續航一周充一次。');

使用 AI_AGG 總結彙總評價：

SET default_ai_resource = 'ai_resource_name';
SELECT
    product_id,
    AI_AGG(
        comment,
        '請把多條使用者評價總結成一句話，突出買家最關心的優點和缺點，控制在50字以內。'
    ) AS 評價摘要
FROM product_reviews
GROUP BY product_id;

+------------+--------------------------------------------------------------------------------------------------------------+
| product_id | 評價摘要                                                                                                     |
+------------+--------------------------------------------------------------------------------------------------------------+
|       1003 | 該產品面料透氣、防晒效果好且顏色美觀，但拉鏈使用不順暢。                                                     |
|       1004 | 使用者評價該產品降噪效果好，續航能力強，一周充一次電。                                                         |
|       1001 | 買家普遍認為鞋子穿著舒適、外觀好看且物流快，但鞋底偏硬且有輕微膠味。                                         |
|       1002 | 買家認為該榨汁杯小巧便攜、出汁快且易清洗，但電池續航短且售後換貨流程慢。                                     |
+------------+--------------------------------------------------------------------------------------------------------------+