SkillAtlasSkill 详情

sql

A self-recommending skill catalog for 17 coding assistants — Claude Code,

审核状态:已审核Quality 80Security 92

复制安装命令

用 Codex 或 Claude 安装复制这段 Prompt,粘贴到 Codex、Claude 或其他助手里,让它先审查 Skill 页面再帮你安装。

复制前请先查看来源、License 和安全提示。

项目 README

来源文件:README.md

抓取于 2026年8月13日
  ████████    ███████    ███████ 
  ██    ██   ██         ██    ██ 
  ██    ██   ██         ██       
  ██    ██   ██         ██       
  ████████    ██████    ██       
  ██  ██           ██   ██       
  ██   ██          ██   ██       
  ██    ██         ██   ██    ██ 
  ██    ██   ███████     ███████ 

rsc — 264 agent skills, one CLI, zero bloat

A self-recommending skill catalog for 17 coding assistants — Claude Code, Codex, GitHub Copilot, Cursor, Gemini, Windsurf, Cline, Antigravity, Zed and more. Describe what you want in plain language. It reads your repo, installs only the skills that fit — one at a time — into every assistant you pick, and keeps them equipped as you work.

From "document my company" to "ship a FastAPI service" to "grow my YouTube channel" — 264 skills across 21 domains, every one researched against live 2025-2026 sources and adversarially scored ≥ 8.5/10 before it shipped.

npx @ericrisco/rsc            # plain-language wizard — no jargon, installs what your project needs

🤖 For agents: "install this harness"

If you're an AI coding agent (Claude Code, Cursor, Copilot, Codex, Gemini…) and your user asked you to install rsc / this harness / these skills, run this in the root of the project they're equipping — not inside a clone of this repo (see the note under Install):

npx @ericrisco/rsc@latest

That launches a plain-language wizard: it asks which assistants to target and what the user wants, reads the repo, then installs the floor (orient + rsc-suggest + bro + harness + init) plus only the skills that fit — one at a time. No global install and no API key needed.

  • Choose assistants non-interactively: npx @ericrisco/rsc@latest --target claude (comma-separate for several).
  • Already installed, just refreshing skills + hooks: rsc sync (or re-run the command above).
  • Add one skill by id: rsc add <id> · browse the catalog: rsc consult "<what you want>" or rsc list.

From then on it's self-driving: rsc-suggest proposes the next skill as tasks appear, and in Claude Code a hook re-asserts the spec-first new-feature gate on every turn — so a feature request routes through specify before any skill writes code.


Why this exists

Most skill packs dump hundreds of files into your context and call it a day. This one is the opposite bet:

  • Granular by default. The unit of installation is one skill. Install fastapi without ever pulling go. Nothing you don't use touches your context.
  • Self-recommending. Both the terminal (rsc consult) and the chat (rsc-suggest, an always-on detector) watch what you're doing and propose the next skill the moment a task needs it — a one-word confirm installs it.
  • Not code-only. First-class support for running a company: bookkeeping, invoicing, hiring, GDPR, pitch decks, SEO, a YouTube/TikTok/LinkedIn presence — each wired to a 02-DOCS/ knowledge loop that learns from your own results.
  • Honestly good. Every skill was built by a research → spec → implement → adversarial review pipeline and had to clear an objective rubric (scripts/skill-rubric.md, written before any skill existed). The bar was real: skills that scored 8.0 were sent back and fixed, not waved through.

skills/<name>/ is the single source of truth. There are no bundles to argue over: you start with a tiny floor and grow one piece at a time.


Install

npx @ericrisco/rsc            # no install step — runs the latest published catalog

Prefer the short rsc command? Install once, globally:

npm install -g @ericrisco/rsc   # then just: rsc

Run it inside any project and describe what you want. Working on the catalog itself? Clone and link:

git clone https://github.com/ericrisco/rsc-harness.git ~/rsc-skills
cd ~/rsc-skills && npm install && npm link

Run it inside the project you're equipping — not inside this repo. The catalog's own package.json is named @ericrisco/rsc, so npx @ericrisco/rsc from within a rsc-harness clone resolves to the local (unlinked) bin and dies with sh: rsc: command not found. Working on the catalog itself? Use node scripts/rsc.js …, the npm link above, or pin the published build with npx @ericrisco/rsc@latest ….

The first run asks which assistants you want — Claude Code, Codex, Copilot, Cursor, Gemini, Windsurf, Cline and 11 more (pick any combination) — and installs the floor: orient + rsc-suggest (always-on) + bro (on request) + harness + init. In Claude Code it also wires a SessionStart hook (so your assistant proposes new skills on its own) and a UserPromptSubmit hook that re-asserts the SDD new-feature gate on every turn — so a feature request, in any language, routes through specify first, before any skill writes code. Opt out per project with .rsc/.no-feature-gate.

Everything stays in the project, and the real skill files are written once to .rsc/skills/<id>/. Each assistant you pick gets a lightweight symlink back to that shared base — no copy is duplicated across IDEs. (If the filesystem can't symlink, it falls back to a real copy automatically.)


30-second tour

$ rsc
 ██████╗ ███████╗ ██████╗     ← animated gradient wordmark
 ██╔══██╗██╔════╝██╔════╝
 ██████╔╝███████╗██║
  264 skills · one CLI · zero bloat

What do you want to do?          ↑↓ move · enter select
❯ Base install — the essentials (orient + suggest + bro + harness + init)
  Base + Spec-Driven Development — specify → plan → implement → ship
  Pick skills by hand, by area

Pick by area and you get a checkbox list — ↑↓ to move, space to toggle, enter to confirm:

Languages:                       ↑↓ move · space toggle · a all · enter confirm
❯ ◉ typescript
  ◯ python
  ◉ go
  ◯ rust

Then it asks which assistants to install for — tick as many as you like:

Which assistants do you want to install for?   space toggle · a all · enter confirm
❯ ◉ Claude Code      (.claude/skills/)   ⟵ detected here
  ◉ Codex CLI        (AGENTS.md)
  ◯ GitHub Copilot   (.github/copilot-instructions.md)
  ◯ Cursor           (.cursor/rules/)
  ◉ Windsurf         (.windsurf/rules/)
  ◯ Cline            (.clinerules/)
  …17 in total — Gemini, Antigravity, Zed, Continue, Roo, Amp, opencode, Jules, Junie, Kiro, Aider

It detects your stack, asks which assistants to install for (the one it found in your folder is pre-marked), installs only what you chose, then prints the exact next steps for Claude Code / Codex / Cursor / Gemini / Antigravity — and from there keeps proposing the skills a task needs.


The CLI

rsc                                  # plain-language wizard (recommended) — pick skills AND assistants
rsc add fastapi postgresdb           # install specific skills, by name
rsc add youtube-api remotion-video   # …grow a channel, edit with Remotion
rsc add fastapi --target claude,codex   # install into several assistants at once
rsc install --profile minimal        # the floor: orient + suggest + bro + harness + init
rsc install --profile core           # floor + the full SDD workflow
rsc install --profile full           # everything (all 264)
rsc install --profile full --without go
rsc consult "I want to launch a SaaS"  # recommend only, no install
rsc registry refresh                 # write .rsc/skill-registry.{json,md}
rsc list                             # what rsc has installed
rsc doctor                           # health check (state, hook, counts)
rsc sync --target claude,codex       # refresh managed skills/hooks from the current package version
rsc backups                          # list project-local snapshots
rsc restore latest --dry-run         # preview restoring the newest snapshot
rsc restore <snapshot-id>            # restore a project-local snapshot
rsc upgrade --dry-run                # show npm upgrade + sync commands
rsc uninstall postgresdb --dry-run   # preview a removal

Update

rsc is an npm package, so updating is two steps — bump the package, then re-sync what's already wired into your project:

npm install -g @ericrisco/rsc@latest   # global install: pull the newest catalog
rsc sync                               # refresh managed skills + hooks (auto-detects your assistant)

Not sure what a bump touches? Preview the exact commands without writing anything:

rsc upgrade --dry-run                  # prints the npm install + rsc sync lines for your target

Running through npx (no global install)? There's nothing to upgrade — npx @ericrisco/rsc@latest always fetches the latest published catalog; just run rsc sync afterwards if the project already has skills installed.

Every sync snapshots the project first, so a bad update is always reversible:

rsc backups                            # list project-local snapshots
rsc restore latest --dry-run           # preview restoring the newest
rsc restore <snapshot-id>              # restore it

How recommendation works

Two faces, one catalog (manifest.json):

  • In the terminal — rsc / rsc consult rank the catalog against your words (multilingual TF-IDF blended with exact tag/id weights and intent synonyms), merge that with what they detect in your repo, and expand via each skill's recommends.
  • In the chat — rsc-suggest is a tiny always-on skill. When a task would benefit from a skill you don't have, it names it and (one-word confirm) runs rsc add <id> for you. It's the floor — installed with every profile.

Repo detection maps real signals to skills: package.json + next → nextjs; go.mod → go; pyproject.toml → fastapi; *.sql/prisma/ → postgresdb; Dockerfile/.github/ → docker/github-actions; and so on. An empty repo just asks in plain language.


The catalog

264 skills, grouped by what you're trying to do. Click any skill to read its SKILL.md. It fires on its own when a task matches.

🧭 Core & control plane

The front door and the workspace brain.

init · harness · orient · suggest · bro · author-skill · sdd-init

harness is the Karpathy chaos→knowledge engine — a 01-TOOLS/ layer (one folder per provider, each with a working test_connection) and a 02-DOCS/ self-improving wiki. It governs software or a whole company. orient is the always-on compass that keeps a non-technical human oriented after every step. bro is installed with every profile and rewrites any answer in plain, natural language when the user asks — without making its full body always-on.

📦 The 02-DOCS/ brain is now 100% Open Knowledge Format (OKF v0.1) conformant

Google Cloud published the Open Knowledge Format — a vendor-neutral standard for portable, agent-readable knowledge — built on the same Karpathy LLM-wiki pattern our 02-DOCS/ engine has used from day one. We independently converged on the same design, so adopting the standard cost almost nothing. As of now, every 02-DOCS/wiki/ is a valid, portable OKF bundle:

  • Markdown + YAML frontmatter, type on every concept doc, OKF-standard fields (title, description, resource, tags, timestamp).
  • Standard markdown links (not wikilinks) form the knowledge graph — any OKF consumer reads it, and it stays a native Obsidian vault (graph, backlinks, Properties, Bases). Same files, no export step.
  • Reserved files honored: index.md (no frontmatter) for navigation, log.md (newest-first, ISO 8601) for history.

Tarball a wiki/ and any OKF tool — including Google's own viewer — can read it. And the brain now keeps your repo clean: a loose file it ingests (a PDF at the root, anything in inbox/) is moved into raw/, never left as clutter.

📐 Spec-Driven Development

Take a fuzzy intent to a shipped, verified change — phase by phase. npx @ericrisco/rsc install --profile core.

sdd · constitution · idea-refinement · specify · clarify · plan · tasks · analyze · decision-challenge · implement · source-grounded-development · verify · review · simplify-code · ship · debug · worktrees · parallel

💼 Run a business

finance-ops · invoicing · bookkeeping · pricing · sales-pipeline · lead-gen · cold-outreach · proposals · contracts · customer-support · client-onboarding · retention · hiring · people-ops · inventory · logistics-ops · procurement · meeting-notes · sop-builder · project-ops

💸 Raise & model money

pitch-deck · investor-materials · financial-model · fundraising · unit-economics · grants

⚖️ Legal, privacy & compliance

gdpr-privacy · terms-conditions · compliance · data-policy · ip-trademark

📣 Market & brand

marketing · seo-geo · content-engine · social-publisher · brand-voice · brand-identity · newsletter · landing-copy · ads · article-writing · case-studies · video-shorts · podcast · market-research · competitor-watch · press-kit · community · webinar · review-management

🎬 Grow a channel

Each with a 02-DOCS feedback loop that learns from your own results. remotion-video edits programmatically — transitions, Whisper captions, silence removal.

youtube-api · youtube-strategy · youtube-ideation · youtube-thumbnails · youtube-packaging · remotion-video · tiktok-api · instagram-api · shortform-strategy · shortform-ideation · shortform-packaging · shortform-editing · viral-score · linkedin-api · linkedin-strategy · linkedin-content · linkedin-carousels · linkedin-outreach · medium-writing · medium-publishing · medium-strategy

🔌 Connect & automate

stripe · email-connector · google-workspace · notion-connector · whatsapp-telegram · automation-flows · api-connector-builder · webhooks · data-scraper · spreadsheet-ops · calendar-scheduling · document-processing · e-signature

⚙️ Automation

Operate the big automation platforms programmatically or via MCP — create and manage automations dynamically, not just design them on a canvas. automation-strategy decides whether / what / which platform; the platform skills drive the live REST API or MCP server (harness connectors ship for each). Complements automation-flows (visual design + importable workflow JSON).

automation-strategy · n8n · make · zapier · power-automate

📊 Data & analytics

analytics · dashboard · kpi-framework · reporting · ab-testing · forecasting · data-cleaning · business-intelligence

🤖 AI — build it in

building-agents · rag · embeddings-search · prompt-engineering · llm-pipeline · agent-eval · chatbot · ai-media · replicate-images · structured-extraction · agent-safety · cost-tracking

🛰️ AI — run it on

replicate · runpod · modal · huggingface · ollama · together-fireworks · fal

🎓 AI — train it

Train and adapt open models end to end: classic ML, deep learning, NLP, fine-tuning (with Unsloth), building training datasets, choosing open-weight models by license/size, and serving them at throughput with vLLM. Facts that move monthly (versions, model licenses) are verified at author time and hedged.

machine-learning · deep-learning · nlp · finetuning · training-data · unsloth · open-weights · vllm

🗣️ Languages

typescript · python · java · csharp-dotnet · php · ruby · cpp · elixir · bash-scripting · sql · go

🏗️ Frameworks & app stacks

fastapi · nextjs · react · react-native · vue-nuxt · angular · svelte · astro · solid-js · htmx · nodejs · nestjs · django · laravel · rails · spring-boot · phoenix · flutter · swift-ios · kotlin-android · compose-multiplatform · expo · tauri · electron · rust · wordpress · shopify · no-code-app · chrome-extension · api-design

🎮 Game development

Three engines + engine-agnostic disciplines. Every engine skill pins the current version and bans deprecated APIs, so the agent stops emitting stale Godot-3 / legacy-Unity code.

godot · unity · unreal · game-design · game-storytelling · level-design · gamedev-shaders · gamedev-multiplayer · gamedev-physics · gamedev-pathing · gamedev-shipping

🗄️ Databases & data layer

postgresdb · mysql · mongodb · redis · supabase · neon · planetscale · sqlite-turso · prisma-orm · drizzle-orm · firebase · dynamodb · vector-db · clickhouse-analytics · duckdb · db-migrations · backups

☁️ Ship & operate — platforms

vercel · netlify · cloudflare · railway · render · fly-io · coolify · hetzner · digitalocean · aws-essentials · gcp-essentials

🛠️ Ship & operate — devops

docker · github-actions · git-workflow · domains-dns · monitoring · email-deliverability · scaling · deployment · deprecation

🔒 Ship & operate — quality & security

code-review · security-scan · secure-coding · testing-py · testing-web · testing-go · e2e-testing · accessibility · performance · error-handling · observability

🎨 Design & content craft

design · presentations · course-storytelling · course-builder · technical-writing · translation-l10n

🧠 Knowledge & meta

knowledge-ops · codebase-onboarding · research-ops · decision-records · continuous-learning · skill-scout · context-budget · roast-me · fable-operator


Multi-target

skills/<name>/ is the catalog source. On install the real files land once in the project at .rsc/skills/<id>/; each assistant you pick gets a symlink (or a converted file) back to that shared base — pick several and nothing is duplicated. The wizard asks which ones; --target a,b does it non-interactively.

TargetSkill destination (→ .rsc/skills/<id>/)Always-on detector
claude.claude/skills/<id>/ → symlink (copy on Windows)SessionStart hook in .claude/settings.json
codex.codex/rsc/<id>/ → symlinkblock in AGENTS.md
copilot.github/rsc/<id>/ → symlinkblock in .github/copilot-instructions.md
cursor.cursor/rules/<id>.mdc (converted)always-apply rule
gemini.gemini/rsc/<id>/ → symlinkblock in GEMINI.md
windsurf.windsurf/rsc/<id>/ → symlinkrule in .windsurf/rules/rsc-suggest.md
cline.clinerules/rsc/<id>/ → symlinkrule in .clinerules/rsc-suggest.md
antigravity.antigravity/rsc/<id>/ → symlinkblock in .antigravity/AGENTS.md
zed.zed/rsc/<id>/ → symlinkblock in AGENTS.md
continue.continue/rsc/<id>/ → symlinkrule in .continue/rules/rsc-suggest.md
roo.roo/rsc/<id>/ → symlinkrule in .roo/rules/rsc-suggest.md
amp.amp/rsc/<id>/ → symlinkblock in AGENTS.md
opencode.opencode/rsc/<id>/ → symlinkblock in AGENTS.md
jules.jules/rsc/<id>/ → symlinkblock in AGENTS.md
junie.junie/rsc/<id>/ → symlinkblock in .junie/guidelines.md
kiro.kiro/rsc/<id>/ → symlinkdoc in .kiro/steering/rsc-suggest.md
aider.aider/rsc/<id>/ → symlinkblock in CONVENTIONS.md

codex, zed, amp, opencode and jules all share the one root AGENTS.md; the block is idempotent, so picking several writes it once.


Skill format

Each skill is a directory under skills/<name>/ whose SKILL.md frontmatter drives both triggering and the installer's recommendations:

---
name: my-skill
description: Use when [specific triggers]… Triggers: 'phrase', 'frase'. NOT x (that is sibling).
tags: [keyword, keyword]        # what the consult advisor searches over
recommends: [sibling-skill]     # what the system offers to install next
profiles: [core, full]          # optional: named-profile membership
origin: risco
---

The full agent-skill spec lives at agentskills.io/specification.


Repo layout & contributing

skills/<name>/ is the single source of truth — every skill is authored there, once. After editing any skill:

npm run manifest      # regenerate manifest.json from skills/*/SKILL.md
npm run validate      # ajv-validate frontmatter + check recommends integrity
npm test              # unit + integration tests
bash scripts/eval-lint.sh   # validate every skills/*/evals/cases.yaml

manifest.json is generated, never hand-edited; CI runs npm run manifest:check and fails if it's stale or the skill count drifts. Adding a skill is: create skills/<id>/SKILL.md with tags + recommends, run npm run manifest, done — the rubric to hold it to is scripts/skill-rubric.md.

This is a personal catalog. Bug reports welcome via GitHub issues; PRs fixing detector patterns, provider endpoints, or typos are appreciated.

License

MIT. See LICENSE.

数据与 AI内容与创作商业与运营

低风险

  • 来源需自行核对维护者身份。
  • 包含脚本或命令调用,安装前请复核。
  • 未检测到明显外部权限要求。
  • 未检测到高风险命令。
  • 扫描发现:0 条。

Codex — Git Clone 安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 克隆仓库:git clone https://github.com/ericrisco/rsc-harness.git
  3. 将 "skills/sql" 文件夹复制到 Codex 的 skills 目录中。
  4. 重启 Codex 让新的 skill 生效。

Codex — 手动复制安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 从源仓库下载 SKILL.md 及相关文件。
  3. 在 Codex 的 skills 目录中创建新文件夹。
  4. 将所有 skill 文件复制到新文件夹中。
  5. 重启 Codex 让新的 skill 生效。

Claude Code — Git Clone 安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 克隆仓库:git clone https://github.com/ericrisco/rsc-harness.git
  3. 将 "skills/sql" 文件夹复制到 Claude Code 的 skills 目录中。
  4. 重启 Claude Code 让新的 skill 生效。

Claude Code — 手动复制安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 从源仓库下载 SKILL.md 及相关文件。
  3. 在 Claude Code 的 skills 目录中创建新文件夹。
  4. 将所有 skill 文件复制到新文件夹中。
  5. 重启 Claude Code 让新的 skill 生效。

Cursor — Git Clone 安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 克隆仓库:git clone https://github.com/ericrisco/rsc-harness.git
  3. 将 "skills/sql" 文件夹复制到 Cursor 的 skills 目录中。
  4. 重启 Cursor 让新的 skill 生效。

Cursor — 手动复制安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 从源仓库下载 SKILL.md 及相关文件。
  3. 在 Cursor 的 skills 目录中创建新文件夹。
  4. 将所有 skill 文件复制到新文件夹中。
  5. 重启 Cursor 让新的 skill 生效。

GitHub Copilot — Git Clone 安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 克隆仓库:git clone https://github.com/ericrisco/rsc-harness.git
  3. 将 "skills/sql" 文件夹复制到 GitHub Copilot 的 skills 目录中。
  4. 重启 GitHub Copilot 让新的 skill 生效。

GitHub Copilot — 手动复制安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 从源仓库下载 SKILL.md 及相关文件。
  3. 在 GitHub Copilot 的 skills 目录中创建新文件夹。
  4. 将所有 skill 文件复制到新文件夹中。
  5. 重启 GitHub Copilot 让新的 skill 生效。

Windsurf — Git Clone 安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 克隆仓库:git clone https://github.com/ericrisco/rsc-harness.git
  3. 将 "skills/sql" 文件夹复制到 Windsurf 的 skills 目录中。
  4. 重启 Windsurf 让新的 skill 生效。

Windsurf — 手动复制安装

  1. 安装前请先查看来源仓库和风险报告。
  2. 从源仓库下载 SKILL.md 及相关文件。
  3. 在 Windsurf 的 skills 目录中创建新文件夹。
  4. 将所有 skill 文件复制到新文件夹中。
  5. 重启 Windsurf 让新的 skill 生效。
查看 SKILL.md 原文
name: sql
description: "Use when writing or reviewing advanced SQL query logic independent of any one engine — multi-table joins, window functions, CTEs including recursive ones, GROUP BY and GROUPING SETS aggregation, and set operations — or when a query returns too many rows, too few, or wrong totals. NOT engine internals, indexes or EXPLAIN (that is `postgresdb`), NOT MySQL config (that is `mysql`), NOT OLAP columnar specifics (that is `duckdb`)."
tags: [sql, query, joins, window-functions, cte]
recommends: [postgresdb, mysql, duckdb, drizzle-orm]
origin: risco

SQL — engine-agnostic query craft

This skill is the portable query-writing layer that sits above any one database engine. It owns the SELECT-side craft: joins and what each does to row count and NULLs, window functions (PARTITION/ORDER/frame), CTEs (including recursive), aggregation (GROUP BY/GROUPING SETS/HAVING), set operations (UNION/INTERSECT/EXCEPT), conditional logic (CASE/COALESCE/NULLIF), and the NULL three-valued-logic traps that quietly corrupt results across every engine. You write queries a reviewer accepts on Postgres, MySQL 8, SQLite, DuckDB, SQL Server, or BigQuery with minimal change, and you flag exactly where a construct is non-portable and what the dialect substitute is. The target standard is SQL:2023 (ISO/IEC 9075:2023), the ninth edition published June 2023; window functions have been standard since SQL:2003, so they are safe to assume everywhere.

This is about thinking in sets and frames, not about one product's planner, DDL, indexing, or ops.

When to use

  • Writing a non-trivial read query: multi-table join, "top-N per group", running totals, period-over-period deltas, dedup, pivots, cohort/funnel shaping.
  • Reaching for a window function and unsure about PARTITION BY vs GROUP BY, or ROWS vs RANGE vs GROUPS frames.
  • Structuring a query with CTEs or recursive CTEs (hierarchies, graph walks, generated series).
  • Aggregation shaping: GROUP BY, HAVING, GROUPING SETS/ROLLUP/CUBE, conditional aggregates.
  • Combining result sets with UNION/INTERSECT/EXCEPT; deciding ALL vs distinct.
  • Debugging a query that returns too many rows (join fan-out), too few (NULL-eating NOT IN), or wrong aggregates (counting joined duplicates).
  • Translating a procedural loop ("for each row, query again") into one set-based statement.
  • Reviewing SQL for portability and correctness regardless of the target engine.

When NOT to use

The askRoute to
Engine-level Postgres: DDL types, indexes, EXPLAIN, VACUUM, RLS, pooling../postgresdb/SKILL.md
MySQL-specific behavior/config (InnoDB, buffer pool)../mysql/SKILL.md
DuckDB local-analytics / columnar specifics../duckdb/SKILL.md
ClickHouse columnar OLAP engine specifics../clickhouse-analytics/SKILL.md
ORM/builder API ergonomics (the API, not the emitted SQL)../drizzle-orm/SKILL.md, ../prisma-orm/SKILL.md
Schema design / DDL / migrations../db-migrations/SKILL.md
BI dashboards, reporting layout, metric definitions../business-intelligence/SKILL.md
Cleaning messy data as a pipeline task../data-cleaning/SKILL.md

The defining line: sql = portable query-language craft; engine skills = one product's behavior, storage, and operations. When the engine isn't decided, or the question is "how do I express this in SQL at all" rather than "how does Postgres run it" — you are in the right place.

Non-negotiables

  1. Explicit JOIN syntax, never comma-joins. FROM a, b WHERE a.id = b.a_id hides the join condition in the filter — drop the WHERE clause by accident and you get a silent cross product.
  2. Alias and qualify every column in a multi-table query. SELECT id, name is ambiguous and breaks the moment two joined tables share a column name; SELECT o.id, c.name survives schema changes.
  3. NOT EXISTS over NOT IN whenever the inner side is nullable. NOT IN returns zero rows if the subquery yields a single NULL (3VL UNKNOWN is never TRUE); NOT EXISTS is NULL-safe. Standard, not engine-specific.
  4. Know your implicit window frame. A window function with ORDER BY but no explicit frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which lumps tied rows together — not ROWS. This silently wrong running total is the single most common window bug, identical across engines. Write the frame explicitly.
  5. Every non-aggregated SELECT column appears in GROUP BY. Engines that let you skip it (old MySQL) return an arbitrary row per group — a correctness landmine, not a convenience.
  6. UNION ALL unless you genuinely need dedup. Bare UNION sorts/hashes to remove duplicates — real cost — and silently collapses rows you meant to keep. Add ALL by default; remove it deliberately.
  7. Reason about NULL 3VL before writing any predicate. NULL = NULL is UNKNOWN, x <> 5 excludes NULL x, and COUNT(col) skips NULLs while COUNT(*) does not. Decide what NULL means before the WHERE.
  8. One set-based statement beats a procedural loop. "For each row, run another query" is almost always a join or a window function — orders of magnitude faster and atomic. Reach for sets first.

Decision tables

JOIN chooser

WantUseRow-count effectNULL behavior
Only matching pairsINNER JOINCan shrink and fan out on 1-to-manyUnmatched rows dropped
All left rows + matchesLEFT JOIN≥ left row countRight columns NULL when no match
All rows from bothFULL JOIN≥ max(left, right)NULLs on whichever side lacks a match
Every combinationCROSS JOINleft × right (multiplies!)None
"Left rows that have a match"semi-join via EXISTS= left, no duplicationNo right columns added
"Left rows with no match"anti-join via NOT EXISTS≤ leftNULL-safe (unlike NOT IN)

A 1-to-many JOIN fans out the left row once per match. If you then SUM/COUNT, the aggregate is inflated. Use a semi-join (EXISTS) when you only want existence, not the joined columns.

GROUP BY vs window function

You want…UseResult
One row per group (collapse detail)GROUP BYFewer rows; only group keys + aggregates survive
Keep every row and add a per-group number... OVER (PARTITION BY …)Same row count; aggregate alongside detail

Rule of thumb: if the question is "per X, the total/rank/previous," and you still want the individual rows, it is a window function. If you only want the rollup, it is GROUP BY.

Frame chooser (ROWS / RANGE / GROUPS)

Frame unitCounts byUse forPortability
ROWSPhysical rowsRunning totals, moving averagesEverywhere
RANGEValue range of the ORDER BY key"All rows within ±N of this value/date"Everywhere
GROUPSPeer groups (tied rows)"N distinct ordering-value steps back"Not in MySQL 8

ROWS and RANGE plus EXCLUDE and numeric RANGE offsets work on Postgres 11+ and SQLite 3.28+. MySQL 8 supports only ROWS and RANGE — no GROUPS, no EXCLUDE. See references/window-functions.md.

Subquery vs JOIN vs CTE

NeedReach for
Existence / anti-existence testcorrelated EXISTS / NOT EXISTS
Combine columns from another tableJOIN
Name an intermediate result, reuse or read it cleanlyCTE (WITH)
Hierarchy, graph walk, generated seriesrecursive CTE (WITH RECURSIVE)

Copy-paste patterns

Every fence is sql. Full depth in references/.

Top-N per group — never LIMIT inside a correlated subquery.

-- Bad: correlated subquery runs once per customer; non-portable LIMIT placement
SELECT * FROM orders o
WHERE o.id IN (
  SELECT id FROM orders i WHERE i.customer_id = o.customer_id
  ORDER BY i.amount DESC LIMIT 3
);

-- Good: one pass, ranked, then filtered
SELECT customer_id, id, amount
FROM (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
) ranked
WHERE rn <= 3;

Running total — make the frame explicit so ties don't lump.

-- Bad: no frame -> implicit RANGE, tied dates collapse into one running value
SELECT day, SUM(amount) OVER (ORDER BY day) AS running FROM sales;

-- Good: explicit ROWS frame counts physical rows
SELECT day,
       SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM sales;

Period-over-period with LAG.

-- Good: previous row's value per partition; NULL on the first row is expected
SELECT month, revenue,
       revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS delta,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month))
             / NULLIF(LAG(revenue) OVER (PARTITION BY product_id ORDER BY month), 0), 2) AS pct_change
FROM monthly_revenue;

NULLIF(prev, 0) guards against divide-by-zero; the first row's LAG is NULL by design.

Dedup keeping latest — QUALIFY is convenient but narrow.

-- Portable: rank then filter in an outer query
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
  FROM users
) d WHERE rn = 1;

-- DuckDB / BigQuery / Snowflake only: QUALIFY skips the wrapper. NOT in Postgres/MySQL/SQLite.
SELECT * FROM users
QUALIFY ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) = 1;

Recursive CTE with a depth guard — always bound the recursion.

-- Good: org chart walk; depth column stops runaway / cyclic graphs
WITH RECURSIVE tree AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, t.depth + 1
  FROM employees e JOIN tree t ON e.manager_id = t.id
  WHERE t.depth < 50            -- hard ceiling; for true cycles track a path array
)
SELECT * FROM tree;

Conditional aggregation / pivot — FILTER reads cleaner than CASE.

-- Portable everywhere: CASE inside the aggregate
SELECT region,
       SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'open' THEN amount ELSE 0 END) AS open
FROM invoices GROUP BY region;

-- Postgres/SQLite/DuckDB: FILTER is the standard, more readable form. NOT in MySQL/SQL Server.
SELECT region,
       SUM(amount) FILTER (WHERE status = 'paid') AS paid,
       SUM(amount) FILTER (WHERE status = 'open') AS open
FROM invoices GROUP BY region;

GROUPING SETS / ROLLUP — one scan, multiple aggregation levels.

-- Good: subtotals per (region, product), per region, and grand total in one query
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product);   -- = GROUPING SETS ((region,product),(region),())

Anti-join via NOT EXISTS — the NULL-safe "rows with no match."

-- Good: customers who never ordered; correct even if orders.customer_id has NULLs
SELECT c.id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

The NOT IN-NULL footgun.

-- Bad: if ANY returned customer_id is NULL, this yields ZERO rows, silently
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Good: NOT EXISTS, or NOT IN with an explicit IS NOT NULL filter on the inner column
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Portability quick map

ConstructNotes
QUALIFYDuckDB / BigQuery / Snowflake only — elsewhere wrap in a subquery and filter rn
FILTER (WHERE …)Postgres / SQLite / DuckDB — MySQL & SQL Server need CASE
GROUPS frame, EXCLUDEPostgres 11+, SQLite 3.28+ — not in MySQL 8
EXCEPTStandard; Oracle spells it MINUS
Row limitingLIMIT … OFFSET (Postgres/MySQL/SQLite/DuckDB) vs FETCH FIRST n ROWS ONLY (standard/SQL Server 2012+) vs TOP n (SQL Server)
Set-op column matchBy position and type, not by name — order your columns identically

Full six-engine matrix in references/portability.md.

Anti-patterns / rationalizations -> STOP

RationalizationRealitySTOP
"NOT IN is clearer than NOT EXISTS"One NULL in the inner set returns zero rows, silentlyUse NOT EXISTS for nullable inner columns
"SELECT * is fine in this query"Hides which columns matter; breaks GROUP BY, ambiguous on joinsProject explicit, qualified columns
"Old MySQL let me skip the GROUP BY column"You get an arbitrary row per groupList every non-aggregated column
"No frame needed, I just want a running sum"Implicit RANGE lumps tied rows -> wrong totalWrite ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
"I'll loop in app code and query per row"N+1 round trips; a window function does it in one scanExpress it as one set-based statement
"UNION to merge these results"Pays a dedup sort and drops rows you wantedUNION ALL unless dedup is the goal
"COUNT(*) after the join is the real count"A 1-to-many join fanned the rows outCount on the base table or use a semi-join
"Add DISTINCT to fix the duplicate rows"Masks a fan-out join instead of fixing itFind the join multiplying rows; fix the grain

Verify

Run scripts/verify.sh from your project root. It is read-only, never connects to a database, and runs on stock macOS bash 3.2. It heuristically scans discovered .sql files and warns on the footguns above (NOT IN (SELECT …), comma-joins with WHERE-join predicates, SELECT * alongside GROUP BY, window OVER (… ORDER BY …) with no explicit frame) and, if sqlfluff is installed, lints with --dialect ansi. It exits non-zero only on a real sqlfluff lint error or unbalanced parens/quotes (dollar-quote aware); every heuristic is advisory [warn], and an empty target passes clean.

See Also

  • references/window-functions.md — ranking/offset/aggregate-over catalog, every frame unit worked, EXCLUDE, named windows, implicit-frame trap, per-engine matrix.
  • references/joins-and-sets.md — every join type with row-count reasoning, semi/anti/lateral joins, set ops + ALL/dedup/MINUS, the fan-out-inflates-aggregates bug.
  • references/ctes-and-recursion.md — CTE structuring, recursive template (hierarchy/graph/series) with cycle + depth guards, the optimization-fence portability note.
  • references/portability.md — full dialect matrix across Postgres / MySQL 8 / SQLite / DuckDB / SQL Server / BigQuery.
  • Siblings: ../postgresdb/SKILL.md, ../mysql/SKILL.md, ../duckdb/SKILL.md, ../clickhouse-analytics/SKILL.md, ../drizzle-orm/SKILL.md, ../prisma-orm/SKILL.md, ../db-migrations/SKILL.md. ORM/engine internals are out of scope here — this skill owns the SQL those tools ultimately emit.

发现问题?提交给管理员复核

评分:

评论 (0)

暂无评论,成为第一个评论者吧!