データ合成から利用まで: Autonomous AI Database だけでどこまでできるかやってみた

-- サプライヤーテーブル
CREATE TABLE suppliers (
    supplier_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    supplier_name   VARCHAR2(100 CHAR) NOT NULL
)

-- 店舗テーブル
CREATE TABLE stores (
    store_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    store_name      VARCHAR2(100 CHAR) NOT NULL,
    location        VARCHAR2(255 CHAR) NOT NULL,
    employee_count  NUMBER
)

-- 商品テーブル
CREATE TABLE products (
    product_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_name    VARCHAR2(100 CHAR) NOT NULL,
    unit_price      NUMBER(10,2) NOT NULL CHECK (unit_price >= 0),
    supplier_id     NUMBER NOT NULL,
    category        VARCHAR2(50 CHAR),
    CONSTRAINT fk_products_supplier FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id)
        ON DELETE SET NULL
)

-- 売上テーブル
CREATE TABLE sales (
    sale_id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sale_date       DATE DEFAULT SYSDATE NOT NULL,
    product_id      NUMBER NOT NULL,
    store_id        NUMBER NOT NULL,
    quantity        NUMBER NOT NULL CHECK (quantity >= 0),
    sale_amount     NUMBER(12,2) NOT NULL CHECK (sale_amount >= 0),
    CONSTRAINT fk_sales_product FOREIGN KEY (product_id)
       REFERENCES products(product_id)
       ON DELETE CASCADE,
    CONSTRAINT fk_sales_store FOREIGN KEY (store_id)
        REFERENCES stores(store_id)
        ON DELETE CASCADE
)

BEGIN
    DBMS_CLOUD_AI.generate_synthetic_data(
        profile_name => '<PROFILE>',
        object_name  => 'SUPPLIERS',
        owner_name   => '<DB_USER>',
        record_count => 3 );
END;

BEGIN
    DBMS_CLOUD_AI.generate_synthetic_data(
        profile_name => '<PROFILE>',
        object_name  => 'STORES',
        owner_name   => '<DB_USER>',
        record_count => 5,
        user_prompt => 'LOCATION としてはアメリカのいずれかの州としてください。STORE名は独創的な名前にしてください。' );
END;

BEGIN
    DBMS_CLOUD_AI.generate_synthetic_data(
        profile_name => '<PROFILE>',
        object_name  => 'products',
        owner_name   => '<DB_USER>',
        record_count => 20 );
END;

BEGIN
    DBMS_CLOUD_AI.generate_synthetic_data(
        profile_name => '<PROFILE>',
        object_name  => 'sales',
        owner_name   => '<DB_USER>',
        record_count => 50 );
END;

CREATE OR REPLACE VIEW sales_analysis_v AS
SELECT
    s.sale_id,
    s.sale_date,
    s.product_id,
    p.product_name,
    p.category,
    p.unit_price,
    p.supplier_id,
    sup.supplier_name,
    s.store_id,
    st.store_name,
    st.location,
    st.employee_count,
    s.quantity,
    s.sale_amount
FROM sales s
    JOIN products  p   ON s.product_id = p.product_id
    JOIN suppliers sup ON p.supplier_id = sup.supplier_id
    JOIN stores    st  ON s.store_id = st.store_id;

BEGIN
    DBMS_CLOUD_AI.CREATE_PROFILE(
        profile_name => 'openai_selectai_sales',
        attributes   => JSON_OBJECT(
            'provider'        VALUE 'openai',
            'credential_name' VALUE 'OPENAI_CRED',
            'object_list'     VALUE JSON_ARRAY(
                JSON_OBJECT(
                    'owner' VALUE 'NGSW_TEST',
                    'name'  VALUE 'SALES_ANALYSIS_V'
                )
            )
        ),
        status      => 'enabled',
        description => 'A profile with open ai model for SELECT AI on synthetic sales data'
    );
END;

SELECT DBMS_CLOUD_AI.GENERATE(
    prompt => '最も売上個数の多い店舗は？',
    profile_name => 'openai_selectai_sales',
    action => 'showsql')
FROM dual;

SELECT "STORE_NAME" AS most_sold_store FROM "NGSW_TEST"."SALES_ANALYSIS_V"
ORDER BY "QUANTITY" DESC FETCH FIRST 1 ROW ONLY

COMMENT ON VIEW sales_analysis_v IS
'Fact view representing individual sales events joined with products, suppliers, and stores.
Use this view for aggregated analysis by store, product, supplier, category, or date.';

COMMENT ON COLUMN sales_analysis_v.quantity IS
'Units sold in this individual sale record. For total units by store or product, aggregate using SUM(quantity).';

COMMENT ON COLUMN sales_analysis_v.sale_amount IS
'Revenue for this sale record. Usually aggregated with SUM(sale_amount).';

COMMENT ON COLUMN sales_analysis_v.store_id IS
'Identifier for the store. Use as grouping key when aggregating sales by store.';

COMMENT ON COLUMN sales_analysis_v.store_name IS
'Human-readable store name. Often grouped together with store_id.';

COMMENT ON COLUMN sales_analysis_v.product_id IS
'Identifier for the product. Use for product-level groupings.';

SELECT "STORE_NAME" AS most_sold_store FROM "NGSW_TEST"."SALES_ANALYSIS_V"
GROUP BY "STORE_NAME"
ORDER BY SUM("QUANTITY") DESC FETCH FIRST ROW ONLY

CREATE JSON DUALITY VIEW store_dv as
SELECT JSON {
    '_id'          : st.store_id,
    'storeName'    : st.store_name,
    'location'     : st.location,
    'employeeCount': st.employee_count
}
FROM stores st;