sql-query-helper

SQL Query Helper Skill

This skill generates, optimizes, and explains SQL queries.

Overview

Generates SQL queries from natural language and optimizes existing queries.

Main Features

• Query Generation: Automatically generates SQL from requirements
• Query Optimization: Index utilization, N+1 resolution
• Query Explanation: Explains complex queries in a human-readable form
• Performance Analysis: Interpretation of EXPLAIN plans
• Migration: Generation of DDL statements
• Database Support: PostgreSQL、MySQL、SQLite、SQL Server

Usage

以下の要件でSQLクエリを生成：
テーブル: users, orders
条件: 2024年に3回以上注文したユーザーのリスト
ソート: 注文回数の降順

Generation Examples

Basic SELECT

Requirement: List of email addresses of active users

Generated Query:

SELECT email
FROM users
WHERE active = true
ORDER BY email;

JOIN query

Requirement: List of users and their orders (including users with no orders)

Generated Query:

SELECT
    u.id,
    u.name,
    u.email,
    o.id AS order_id,
    o.total,
    o.created_at AS order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.created_at DESC;

Aggregate Query

Requirement: Total purchase amount per user (only those with purchases)

Generated Query:

SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent,
    AVG(o.total) AS avg_order_value
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;

Subquery

Requirement: Users with above-average order amounts

Generated Query:

SELECT
    u.name,
    o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > (
    SELECT AVG(total)
    FROM orders
)
ORDER BY o.total DESC;

Query Optimization

Resolving the N+1 Problem

Inefficient (N+1):

-- 1. すべてのユーザーを取得
SELECT * FROM users;

-- 2. 各ユーザーの注文を個別に取得（Nクエリ）
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
-- ... (ユーザー数だけ繰り返し)

Optimized (1 query):

SELECT
    u.*,
    o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Index Utilization

Slow Query:

SELECT * FROM users
WHERE email = 'user@example.com';  -- インデックスなし

Adding an Index:

CREATE INDEX idx_users_email ON users(email);

After Optimization:

-- 同じクエリでも高速化
SELECT * FROM users
WHERE email = 'user@example.com';  -- インデックス使用

WHERE vs HAVING

Inefficient:

SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING user_id > 100;  -- GROUP BY後にフィルタ

Optimized:

SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE user_id > 100  -- GROUP BY前にフィルタ
GROUP BY user_id;

EXISTS vs IN

Slow with large amounts of data:

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

Optimized:

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

Query Explanation

Input:

SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 3
ORDER BY order_count DESC
LIMIT 10;

Explanation:

このクエリは、**2024年以降に登録したユーザーの中で、4回以上注文したユーザーのトップ10**を取得します。

実行順序:
1. FROM: users テーブルを基準に
2. LEFT JOIN: orders テーブルを結合（注文がなくてもユーザーは含まれる）
3. WHERE: 2024年1月1日以降に作成されたユーザーのみ
4. GROUP BY: ユーザーごとにグループ化
5. HAVING: 注文が4回以上のグループのみ
6. SELECT: ユーザー名と注文数を選択
7. ORDER BY: 注文数の降順
8. LIMIT: 上位10件のみ

結果:
- 列: name（ユーザー名）、order_count（注文回数）
- 行数: 最大10行

DDL Generation

Requirement: Creating a users table

Generated DDL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active);
CREATE INDEX idx_users_created_at ON users(created_at);

Differences by Database

PostgreSQL

-- RETURNING句
INSERT INTO users (name, email)
VALUES ('John', 'john@example.com')
RETURNING id, created_at;

-- ARRAY型
SELECT ARRAY_AGG(name) FROM users;

MySQL

-- AUTO_INCREMENT
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- LIMIT OFFSET
SELECT * FROM users LIMIT 10 OFFSET 20;

SQLite

-- AUTOINCREMENT
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT
);

Version Information

• Skill version: 1.0.0
• Last updated: 2025-01-22

---

Usage Examples:

SQLクエリを生成：
- テーブル: products, categories
- 取得: カテゴリ別の商品数と平均価格
- 条件: 在庫ありの商品のみ
- ソート: 商品数の多い順

An optimized SQL query will be generated!