Synthèse automatique

Une référence rapide qui rappelle les patrons SQL courants : CTE, fonctions de fenêtrage, jointures, pagination et stratégies d'indexation.

Points forts

Couvre les CTE simples et chaînées avec des exemples directement réutilisables
Tableau comparatif clair des fonctions de fenêtrage (ROW_NUMBER, RANK, LAG/LEAD, agrégats glissants)
Inclut la pagination keyset comme alternative rapide au OFFSET/LIMIT
Liste d'anti-patrons avec correctifs concrets (SELECT *, NOT IN avec NULL, N+1)

Limites

Contenu surtout composé de tableaux de synthèse, sans explication approfondie des cas limites
Ne précise pas de dialecte SQL particulier (PostgreSQL, MySQL, etc.)
Les ressources détaillées sont renvoyées vers des fichiers de référence externes non inclus

Quand l'utiliser

À utiliser lorsqu'on rédige ou révise une requête SQL et qu'on veut un aide-mémoire immédiat sur les CTE, fenêtres, jointures ou index.

Quand l'éviter

À éviter pour du diagnostic de performance réel sur une base précise ou pour des syntaxes propres à un SGBD non couvertes ici.

Exemples

Chained CTE for users and orders

Write a SQL query using chained CTEs that lists active users with their order count, including users with zero orders.

Running total with window functions

Show me a SQL query that computes daily revenue alongside the previous day's revenue and a running total.

Keyset pagination

Convert this OFFSET/LIMIT pagination query to keyset pagination and explain why it scales better.

name: sql-patterns description: "Quick reference for common SQL patterns, CTEs, window functions, and indexing strategies. Triggers on: sql patterns, cte example, window functions, sql join, index strategy, pagination sql." allowed-tools: "Read Write"

SQL Patterns

Quick reference for common SQL patterns.

CTE (Common Table Expressions)

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

Chained CTEs

WITH
    active_users AS (
        SELECT id, name FROM users WHERE status = 'active'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) as order_count
        FROM orders GROUP BY user_id
    )
SELECT u.name, COALESCE(o.order_count, 0) as orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

Window Functions (Quick Reference)

| Function | Use | |----------|-----| | ROW_NUMBER() | Unique sequential numbering | | RANK() | Rank with gaps (1, 2, 2, 4) | | DENSE_RANK() | Rank without gaps (1, 2, 2, 3) | | LAG(col, n) | Previous row value | | LEAD(col, n) | Next row value | | SUM() OVER | Running total | | AVG() OVER | Moving average |

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

JOIN Reference

| Type | Returns | |------|---------| | INNER JOIN | Only matching rows | | LEFT JOIN | All left + matching right | | RIGHT JOIN | All right + matching left | | FULL JOIN | All rows, NULL where no match |

Pagination

-- OFFSET/LIMIT (simple, slow for large offsets)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

-- Keyset (fast, scalable)
SELECT * FROM products WHERE id > 42 ORDER BY id LIMIT 20;

Index Quick Reference

| Index Type | Best For | |------------|----------| | B-tree | Range queries, ORDER BY | | Hash | Exact equality only | | GIN | Arrays, JSONB, full-text | | Covering | Avoid table lookup |

Anti-Patterns

| Mistake | Fix | |---------|-----| | SELECT * | List columns explicitly | | WHERE YEAR(date) = 2024 | WHERE date >= '2024-01-01' | | NOT IN with NULLs | Use NOT EXISTS | | N+1 queries | Use JOIN or batch |

Additional Resources

For detailed patterns, load:

./references/window-functions.md - Complete window function patterns
./references/indexing-strategies.md - Index types, covering indexes, optimization

Modèles SQL

Recommandé pour