日本オラクルと協創！企業内のあらゆる業務データを元に回答する”生成AIエージェント”とは？ - (Part 3)

SELECT p."PRODUCT_NAME", p."PRICE" 
FROM "IM_TEST"."PRODUCTS" p 
WHERE p."PRODUCT_NAME" = 'CV-SP900_300L'

SELECT p."PRODUCT_NAME", p."PRODUCT_ID", ST."AVAILABLE_QUANTITY"
FROM "IM_TEST"."PRODUCTS" p
JOIN "IM_TEST"."STOCK" ST ON p."PRODUCT_ID" = ST."PRODUCT_ID"
JOIN "IM_TEST"."SITE" SI ON SI."SITE_ID" = ST."SITE_ID"
WHERE SI."SITE_NAME" = '大阪'
ORDER BY ST."AVAILABLE_QUANTITY" DESC
FETCH FIRST 1 ROWS ONLY;

CREATE VIEW MASTER AS
SELECT
    P.PRODUCT_ID AS PRODUCT_ID,
    S.SITE_ID AS SITE_ID,
    P.CATEGORY_ID AS CATEGORY_ID,
    P.PRODUCT_NAME AS PRODUCT_NAME,
    S.SITE_NAME AS SITE_NAME,
    C.CATEGORY_NAME AS CATEGORY_NAME,
    P.PRICE AS PRICE,
    ST.STOCK AS STOCK,
    ST.RESERVED_QUANTITY AS RESERVED_QUANTITY,
    ST.AVAILABLE_QUANTITY AS AVAILABLE_QUANTITY
FROM PRODUCTS P
JOIN STOCK ST ON P.PRODUCT_ID = ST.PRODUCT_ID
JOIN SITE S ON ST.SITE_ID = S.SITE_ID
JOIN CATEGORY C ON P.CATEGORY_ID = C.CATEGORY_ID;

COMMENT ON TABLE MASTER IS 'Contains information about products, products_category, site, stock and products_detail. Each product has a unique identifier and name.';
COMMENT ON COLUMN MASTER.PRODUCT_ID IS '製品コード。Example: 1 for CV-SP900_300L product.';
...

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(                                              
      profile_name => 'GENAI_LLAMA3_MASTER',
      attributes   => '{"provider": "oci",
                        "credential_name": "Select AI_CRED",
                        "object_list": [{"owner": "IM_TEST", "name": "MASTER"}],
                        "model": "meta.llama-3-70b-instruct",
                        "comments": true,
                        "temperature": 0
                       }');
END;
/

質問: 製品CV-SP900_300Lの現在の販売価格はいくらですか?

AI Generated SQL:
SELECT DISTINCT m."PRODUCT_NAME", m."PRICE" 
FROM "IM_TEST"."MASTER" m 
WHERE m."PRODUCT_NAME" = 'CV-SP900_300L'

Output: 
------------------------------------------------------------
製品CV-SP900_300Lの現在の販売価格は32,654円です。

質問: 大阪拠点で最も引当可能な在庫が多い製品とその在庫数を教えてください

AI Generated SQL:
SELECT DISTINCT m."PRODUCT_ID", m."PRODUCT_NAME", m."AVAILABLE_QUANTITY"
FROM "IM_TEST"."MASTER" m
WHERE m."SITE_NAME" = '大阪'
ORDER BY m."AVAILABLE_QUANTITY" DESC
FETCH FIRST 1 ROW ONLY

Output: 
------------------------------------------------------------
大阪拠点で最も引当可能な在庫が多い製品は『PV-BH900SL』で、その引当可能な在庫数は278です。

CREATE VIEW PRODUCTS_DETAIL AS
SELECT
    P.PRODUCT_ID AS PRODUCT_ID,
    P.CATEGORY_ID AS CATEGORY_ID,
    C.CATEGORY_NAME AS CATEGORY_NAME,
    P.PRODUCT_NAME AS PRODUCT_NAME,
    P.PRICE AS PRICE,
    PD.DESC_CHUNK_ID AS DESC_CHUNK_ID,
    PD.DESC_CHUNK AS DESC_CHUNK,
    PD.DESC_VEC AS DESC_VEC
FROM PRODUCTS P
JOIN CATEGORY C ON P.CATEGORY_ID = C.CATEGORY_ID
JOIN PRODUCTS_DESC PD ON P.PRODUCT_ID = PD.PRODUCT_ID;

COMMENT ON TABLE PRODUCTS_DETAIL IS 'Contains information about the feature of each product. If specific product features or ambiguous information is input, this view should be queried.';
COMMENT ON COLUMN PRODUCTS_DETAIL.PRODUCT_ID IS 'Product code(ID). Unique product names for air conditioners, cleaners, and other home appliances.Example: 1 for CV-SP900_300L product. If needed to join with Stock table, join on this column';
...

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(                                              
      profile_name => 'GENAI_COHERE_COMMAND_R_VECTOR',
      attributes   => '{"provider": "oci",
                        "credential_name": "SELECTAI_CRED",
                        "object_list": [{"owner": "IM_TEST", "name": "PRODUCTS_DETAIL"}, {"owner": "IM_TEST", "name": "SITE"}, {"owner": "IM_TEST", "name": "STOCK"}], 
                        "model": "cohere.command-r-16K",
                        "oci_apiformat" : "COHERE",
                        "comments": true,
                        "temperature": 0
                       }');
END;
/

Please generate an SQL query to answer product description and feature-related questions. This query aims to search a vector database and extract the most relevant information.

The possible prompts fall into three main categories.
1. Question [Tell me about the functionality of product xxxx yyyy.]
For a prompt similar to this question, generate SQL as follows.

"SELECT DESC_CHUNK, DESC_CHUNK_ID, PRODUCT_ID
FROM PRODUCTS_DETAIL
WHERE PRODUCT_NAME = 'xxxx' 
ORDER BY VECTOR_DISTANCE(DESC_VEC, query_vector) 
FETCH FIRST 10 ROWS ONLY"

2. Question [Give me N(number) products like zzzz(home appliances) with category_name that have more than y(number) in stock at xxxx(city name in Japan) site.]
In addition to the PRODUCTS_DETAIL table, there are the STOCK table and the SITE table.
The STOCK table has columns SITE_ID, PRODUCT_ID, STOCK, RESERVED_QUANTITY, and AVAILABLE_QUANTITY.
The SITE table contains SITE_ID and SITE_NAME columns.
When information on stock is queried, the STOCK and PRODUCTS_DETAIL tables are joined on PRODUCT_ID.
When information on sites is queried, the STOCK and SITE tables are joined on SITE_ID.
The ROWS ONLY clause indicates how many rows to fetch. Fix this at 5 ROWS ONLY.
Be sure to use multi-vector similarity search in this case as well.
In this case, please generate SQL as follows.

"SELECT PD.PRODUCT_NAME, PD.PRODUCT_ID
FROM PRODUCTS_DETAIL PD
JOIN STOCK ST ON PD.PRODUCT_ID = ST.PRODUCT_ID
JOIN SITE SI ON ST.SITE_ID = SI.SITE_ID
WHERE ST.STOCK >= y
AND SI.SITE_NAME = 'xxxx'
AND PD.CATEGORY_NAME = 'zzzz'
ORDER BY VECTOR_DISTANCE(PD.DESC_VEC, query_vector)
FETCH FIRST N PARTITIONS BY PD.PRODUCT_ID, 5 ROWS ONLY"

質問: 製品MRO-S7B_Mで肉や魚を上手に解凍するコツを教えてください

AI Generated SQL: 
SELECT DESC_CHUNK, DESC_CHUNK_ID, PRODUCT_ID
FROM PRODUCTS_DETAIL
WHERE PRODUCT_NAME = 'MRO-S7B_M'
ORDER BY VECTOR_DISTANCE(DESC_VEC, query_vector) 
FETCH FIRST 10 ROWS ONLY

final_answer:
肉や魚を解凍するときは、ラップなどの包装を外し、発泡スチロール製のトレーにのせます。トレーがない場合は、クッキングシートやキッチンペーパーを敷き、その上にのせます。食品の分量は100~1000gを目安に、2個以上を同時に解凍するときは、同じ種類・大きさ・重さにそろえます。食品はテーブルプレートの中央に置きます。

質問: 東京拠点の引当可能な在庫数が100個以下の冷蔵庫の製品の製品名と在庫数、脱臭機能があればその機能を説明して。

AI Generated SQL:
SELECT PD.PRODUCT_NAME, PD.DESC_CHUNK, ST.AVAILABLE_QUANTITY
FROM PRODUCTS_DETAIL PD
JOIN STOCK ST ON PD.PRODUCT_ID = ST.PRODUCT_ID
JOIN SITE SI ON ST.SITE_ID = SI.SITE_ID
WHERE ST.AVAILABLE_QUANTITY <= 100
AND SI.SITE_NAME = '東京'
AND PD.CATEGORY_NAME = '冷蔵庫'
ORDER BY VECTOR_DISTANCE(PD.DESC_VEC, query_vector)
FETCH FIRST 5 PARTITIONS BY PD.PRODUCT_ID, 3 ROWS ONLY

final_answer:
製品名はR-27TVとR-WXC74Vです。在庫数はそれぞれ69個と46個です。脱臭機能はどちらの製品にも搭載されており、4種類の脱臭素材の組み合わせによって、硫黄系・窒素系・アルデヒド系・酸系の4大臭気を含む庫内の様々な匂いを脱臭します。