AIを活用したSQLクエリの構築 | オリジナル、AI翻訳
データベーススキーマ (PostgreSQL):
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100),
last_name VARCHAR(100),
date_of_birth DATE,
gender VARCHAR(10),
phone_number VARCHAR(20),
address TEXT,
city VARCHAR(100),
state VARCHAR(100),
country VARCHAR(100),
zip_code VARCHAR(20),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE,
last_login TIMESTAMP WITH TIME ZONE,
is_active BOOLEAN DEFAULT TRUE,
role VARCHAR(50),
profile_picture_url VARCHAR(255),
bio TEXT,
failed_login_attempts INTEGER DEFAULT 0,
account_balance NUMERIC(10, 2) DEFAULT 0.00
);
上記はPostgreSQLデータベースです。以下はテーブル定義です。自然言語クエリの指示は以下の通りです。
2025年現在で20歳のユーザーで、先月のある日頃に作成され、先週のような最近のログインをしたユーザーで、ファーストネームがAndyのユーザーを取得したいです。そのようなユーザーをクエリするSQLを教えてください。
生成されたSQLクエリ:
SELECT *
FROM users
WHERE first_name ILIKE 'andy'
AND CAST(created_at AS DATE) BETWEEN
(CURRENT_DATE - INTERVAL '1 MONTH' - INTERVAL '1 DAY')
AND
(CURRENT_DATE - INTERVAL '1 MONTH' + INTERVAL '1 DAY')
AND EXTRACT(YEAR FROM AGE(date_of_birth)) = 20
AND last_login >= CURRENT_TIMESTAMP - INTERVAL '7 DAYS';
以下は、GrokのようなAIシステムとのユーザーのやり取りを追跡するために使用できるconversationsテーブルのPostgreSQLテーブル定義です。このテーブルは、会話に関する情報、ユーザー、コンテンツ、タイムスタンプ、関連するメタデータを追跡するように設計されています。
CREATE TABLE conversations (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
title VARCHAR(255),
conversation_type VARCHAR(50) DEFAULT 'chat',
started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
ended_at TIMESTAMP WITH TIME ZONE,
status VARCHAR(50) DEFAULT 'active',
message_count INTEGER DEFAULT 0,
last_message_at TIMESTAMP WITH TIME ZONE,
is_archived BOOLEAN DEFAULT FALSE,
ai_model_version VARCHAR(100),
total_tokens_used INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
説明:
- id: 各会話の一意の識別子。
- user_id: ユーザーを特定のユーザーにリンクするために
usersテーブルを参照します。 - title: 会話のタイトル（例: ユーザー定義または自動生成された要約）。
- conversation_type: 会話の種類を示します（例: ‘chat’, ‘query’, ‘voice’）。
- started_at: 会話が開始された時刻。
- ended_at: 会話が終了した時刻（進行中の場合はNULL可能）。
- status: 会話の状態を追跡します（例: ‘active’, ‘completed’, ‘paused’）。
- message_count: 会話で交換されたメッセージの数を追跡します。
- last_message_at: 最新のメッセージの時刻。
- is_archived: 会話がアーカイブされているかどうかを示すブール値。
- ai_model_version: 使用されたAIモデルのバージョン（例: ‘Grok 3’）。
- total_tokens_used: 会話のトークン使用量を追跡します（APIの請求または分析に役立ちます）。
- created_atとupdated_at: 会話レコードが作成された時刻と最後に更新された時刻。
- FOREIGN KEY:
usersテーブルとの参照整合性を確保し、ON DELETE CASCADEで関連するユーザーが削除された場合に会話も削除されます。
このテーブルは柔軟に設計されており、個々のメッセージを別のmessagesテーブルに保存するか、感情分析や会話タグなどのフィールドを追加するなど、特定のニーズに基づいて拡張できます。メッセージテーブルやこのテーブルと対話するためのクエリが必要な場合はお知らせください!
ユーザーが自然言語（例: “最新の5つの会話を表示”または”タイトルにjavaという単語を含む会話”）を使用して自身の会話をクエリできるフロントエンドを有効にするには、conversationsテーブルからの結果を認証済みユーザーの会話に制限するSQLクエリを確実に作成する必要があります。これは、ユーザーのID（例: user_id）をクエリに組み込み、自然言語をSQL条件に変換することで実現されます。以下に、conversationsテーブルと対話するためのプロンプトと対応するSQLクエリを示しますが、ユーザーは常に自身のデータのみにアクセスできるようにします。
主な考慮事項
- ユーザー認証: フロントエンドは認証済みの
user_idをバックエンドに渡す必要があります。 - 自然言語解析: 自然言語入力を解析して、主要な条件（例: “最新の5”または”タイトルにjavaという単語を含む”）を特定します。
- セキュリティ: 常に
user_idでフィルタリングして、ユーザーが他人の会話にアクセスできないようにします。 - テーブル参照: 先に定義した
conversationsテーブルを使用します。
自然言語プロンプトとSQLクエリ
プロンプト1: “最新の5つの会話を表示”
解析された要件:
- 認証済みユーザーの
user_idで会話をフィルタリングします。 started_at（またはlast_message_atを最新順にする）で降順に並べ替えます。- 結果を5件に制限します。
SQLクエリ:
SELECT *
FROM conversations
WHERE user_id = :authenticated_user_id
ORDER BY started_at DESC
LIMIT 5;
:authenticated_user_idはログインユーザーのIDを表すプレースホルダーで、フロントエンド/バックエンドから安全に渡されます。started_at DESCで最新の会話が返されるようにします。LIMIT 5で出力を5つの会話に制限します。
プロンプト2: “タイトルにjavaという単語を含む会話”
解析された要件:
- 認証済みユーザーの
user_idで会話をフィルタリングします。 titleに”java”（大文字小文字を区別しない）が含まれる会話を検索します。- PostgreSQLの
ILIKEを使用して部分一致、大文字小文字を区別しないマッチングを行います。
SQLクエリ:
SELECT *
FROM conversations
WHERE user_id = :authenticated_user_id
AND title ILIKE '%java%';
:authenticated_user_idでユーザーの会話のみをクエリします。ILIKE '%java%'で”java”を含むタイトル（例: “Java Tips”、”About Java Programming”）にマッチします。
フロントエンドの実装に関する注意事項
- ユーザー認証:
- フロントエンドは認証済みユーザーのID（例: JWTトークンまたはセッションから）を取得し、バックエンドAPIに送信する必要があります。
- バックエンドは
user_idを検証し、WHERE句で結果をフィルタリングする必要があります。
- 自然言語処理:
- シンプルなルールベースのパーサーまたはAIモデル（例: Grok）を使用して、自然言語クエリを解析し、以下を抽出します:
- 意図: 会話をリスト表示します。
- フィルタ: キーワード（例: “java”）、ソート（例: “最新”）、または制限（例: “5”）。
- 例として、”最新の5”を
ORDER BY started_at DESC LIMIT 5に、”タイトルにjavaという単語を含む”をtitle ILIKE '%java%'にマッピングします。
- シンプルなルールベースのパーサーまたはAIモデル（例: Grok）を使用して、自然言語クエリを解析し、以下を抽出します:
- API設計:
- 以下のようなエンドポイント（例:
/api/conversations）を作成します:user_id: 認証済みセッションから。query: 自然言語入力または構造化パラメータ（例:{ filter: "title contains java" }）。
- バックエンドはクエリをSQLに変換し、
user_idが常にWHERE句に含まれるようにします。
- 以下のようなエンドポイント（例:
- セキュリティ:
- ユーザーが直接SQLを入力したり、
user_idフィルタを変更したりできないようにします。 - SQLインジェクションを防ぐためにパラメータ化クエリ（例:
:authenticated_user_id）を使用します。 ILIKE検索で悪意のあるパターンを避けるために入力を検証します。
- ユーザーが直接SQLを入力したり、
- フロントエンド表示:
- 会話リストを表示するために
id、title、started_at、last_message_at、statusなどのフィールドを返します。 - UIにソートやフィルタリングオプション（例: “最新”のドロップダウンまたはタイトル検索用のテキスト入力）を提供します。
- 会話リストを表示するために
例のワークフロー
- ユーザーがログインし、フロントエンドがその
user_id（例:123）を取得します。 - ユーザーが検索バーに”最新の5つの会話を表示”と入力します。
- フロントエンドはバックエンドにリクエストを送信します:
/api/conversations?user_id=123&query=最新の5. - バックエンドはクエリを解析し、以下のSQLを生成します:
SELECT * FROM conversations WHERE user_id = 123 ORDER BY started_at DESC LIMIT 5; - バックエンドは結果をJSONとして返し、フロントエンドは会話を表示します。
追加の注意事項
titleがNULLの場合は、2番目のクエリにAND title IS NOT NULLを追加して、NULLのタイトルにマッチしないようにします。- より複雑なクエリ（例: “先週のjavaに関する会話”）の場合は、条件を組み合わせます:
SELECT * FROM conversations WHERE user_id = :authenticated_user_id AND title ILIKE '%java%' AND started_at >= CURRENT_TIMESTAMP - INTERVAL '7 DAYS'; - より高度な自然言語パーサーが必要な場合は、AIモデル（例: GrokをxAIのAPI経由で）を統合して、複雑なクエリをSQLにマッピングします。APIの詳細については、xAI APIを参照してください。
フロントエンドコード、バックエンドAPIの設定、または追加のクエリ例が必要な場合はお知らせください!