PostgreSQL 示例

本文档提供了使用 MCP 数据库工具与 PostgreSQL 数据库交互的实用示例。这些示例展示了如何利用 PostgreSQL 的特性进行数据分析和查询优化。

基础查询示例

列出所有表

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

查看表结构

SELECT column_name, data_type, character_maximum_length, is_nullable
FROM information_schema.columns
WHERE table_name = 'customers'
ORDER BY ordinal_position;

基本数据查询

-- 查询客户表中的前 10 条记录
SELECT * FROM customers LIMIT 10;

-- 按条件筛选
SELECT * FROM customers WHERE city = '北京' AND status = 'active';

-- 排序查询
SELECT customer_id, name, registration_date
FROM customers
ORDER BY registration_date DESC
LIMIT 20;

PostgreSQL 特有功能

JSON 数据查询

PostgreSQL 提供了强大的 JSON 处理功能，可以直接查询和操作 JSON 数据：

-- 查询 JSON 字段中的特定属性
SELECT
  id,
  name,
  preferences->>'theme' AS theme,
  preferences->>'language' AS language
FROM users;

-- 使用 JSON 条件过滤
SELECT * FROM products
WHERE attributes->>'color' = 'red'
AND CAST(attributes->>'weight' AS INTEGER) < 100;

-- 使用 JSON 数组
SELECT * FROM orders
WHERE items @> '[{"product_id": 123}]'::jsonb;

全文搜索

PostgreSQL 的全文搜索功能可以高效地搜索文本内容：

-- 创建全文搜索向量
SELECT to_tsvector('english', description) FROM products;

-- 使用全文搜索查询
SELECT title, description
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'comfortable & durable');

-- 带排名的全文搜索
SELECT title, description,
       ts_rank(to_tsvector('english', description), to_tsquery('english', 'comfortable & durable')) AS rank
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'comfortable & durable')
ORDER BY rank DESC;

数组操作

PostgreSQL 支持数组数据类型，可以直接在 SQL 中操作数组：

-- 查询包含特定标签的产品
SELECT * FROM products WHERE 'organic' = ANY(tags);

-- 数组交集
SELECT * FROM products WHERE tags && ARRAY['eco-friendly', 'sustainable'];

-- 展开数组
SELECT id, unnest(tags) AS tag FROM products;

-- 数组聚合
SELECT category, array_agg(name) AS product_names
FROM products
GROUP BY category;

高级查询技巧

窗口函数

窗口函数可以在不改变结果集行数的情况下执行计算：

-- 按类别计算产品排名
SELECT
  name,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank
FROM products;

-- 计算移动平均
SELECT
  date,
  sales,
  AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg
FROM daily_sales;

-- 计算累计总和
SELECT
  date,
  sales,
  SUM(sales) OVER (ORDER BY date) AS cumulative_sales
FROM daily_sales;

公共表表达式 (CTE)

使用 CTE 可以简化复杂查询：

-- 使用 CTE 查找高价值客户
WITH high_value_customers AS (
  SELECT
    customer_id,
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 10000
)
SELECT c.name, c.email, hvc.total_spent
FROM high_value_customers hvc
JOIN customers c ON hvc.customer_id = c.id
ORDER BY hvc.total_spent DESC;

-- 递归 CTE 查询组织结构
WITH RECURSIVE org_hierarchy AS (
  -- 基础查询：找出顶级管理者
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 递归查询：找出每个员工的下属
  SELECT e.id, e.name, e.manager_id, oh.level + 1
  FROM employees e
  JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT id, name, level
FROM org_hierarchy
ORDER BY level, name;

性能优化技巧

使用 EXPLAIN ANALYZE

使用 EXPLAIN ANALYZE 可以查看查询计划和执行时间，帮助优化查询：

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.name
ORDER BY order_count DESC;

优化索引使用

确保查询使用了适当的索引：

-- 检查查询是否使用了索引
EXPLAIN
SELECT * FROM orders
WHERE customer_id = 123 AND order_date > '2023-01-01';

-- 创建复合索引以优化特定查询
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

分区表查询

对于分区表，确保查询包含分区键以提高性能：

-- 高效的分区表查询（包含分区键）
SELECT * FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
AND product_id = 456;

实际应用场景

场景 1：销售数据分析

分析过去 12 个月的销售趋势：

-- 按月统计销售额
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS monthly_sales,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(amount) / COUNT(DISTINCT customer_id) AS avg_per_customer
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- 计算同比增长
WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS sales
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '24 months'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  current_year.month,
  current_year.sales AS current_sales,
  previous_year.sales AS previous_sales,
  (current_year.sales - previous_year.sales) / previous_year.sales * 100 AS growth_percent
FROM monthly_sales current_year
JOIN monthly_sales previous_year
  ON current_year.month = previous_year.month + INTERVAL '1 year'
WHERE current_year.month >= CURRENT_DATE - INTERVAL '12 months'
ORDER BY current_year.month;

场景 2：客户细分分析

根据购买行为对客户进行细分：

-- RFM (Recency, Frequency, Monetary) 分析
WITH customer_rfm AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date) AS recency,
    COUNT(*) AS frequency,
    SUM(amount) AS monetary
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    NTILE(5) OVER (ORDER BY recency DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency) AS f_score,
    NTILE(5) OVER (ORDER BY monetary) AS m_score
  FROM customer_rfm
)
SELECT
  customer_id,
  r_score,
  f_score,
  m_score,
  CONCAT(r_score::TEXT, f_score::TEXT, m_score::TEXT) AS rfm_segment
FROM rfm_scores
ORDER BY r_score DESC, f_score DESC, m_score DESC;

场景 3：库存管理

分析库存状态和补货需求：

-- 识别需要补货的产品
SELECT
  p.id,
  p.name,
  p.category,
  i.current_stock,
  i.reorder_level,
  COALESCE(o.pending_orders, 0) AS pending_orders,
  i.current_stock - COALESCE(o.pending_orders, 0) AS effective_stock
FROM products p
JOIN inventory i ON p.id = i.product_id
LEFT JOIN (
  SELECT
    product_id,
    SUM(quantity) AS pending_orders
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.id
  WHERE o.status = 'processing'
  GROUP BY product_id
) o ON p.id = o.product_id
WHERE i.current_stock - COALESCE(o.pending_orders, 0) < i.reorder_level
ORDER BY (i.current_stock - COALESCE(o.pending_orders, 0)) / i.reorder_level;

与 AI 的交互示例

以下是一些与 AI 交互使用 PostgreSQL 数据的示例对话：

您：能否帮我分析过去 6 个月中，哪些产品的销售增长最快？

AI：我来帮您分析。以下是过去 6 个月销售增长最快的产品：

产品名称         | 6个月前销售额 | 当前销售额 | 增长百分比
---------------|------------|----------|--------
智能手表 Pro     | ¥125,430   | ¥356,780 | 184.4%
无线耳机 X3      | ¥89,670    | ¥213,450 | 138.0%
智能家居套装     | ¥56,890    | ¥132,670 | 133.2%
超薄笔记本电脑   | ¥432,780   | ¥876,540 | 102.5%
健康监测手环     | ¥78,340    | ¥145,670 | 86.0%

智能手表 Pro 的销售额增长最为显著，增长了 184.4%。无线耳机和智能家居产品也表现出色。这可能与最近的营销活动和产品升级有关。

您：我们的高价值客户主要来自哪些城市？他们平均消费是多少？

AI：根据数据分析，您的高价值客户（年消费超过 ¥10,000）主要分布在以下城市：

城市     | 高价值客户数量 | 平均年消费额
--------|------------|------------
北京     | 342        | ¥18,765
上海     | 287        | ¥19,432
深圳     | 213        | ¥17,890
广州     | 176        | ¥16,543
杭州     | 124        | ¥15,876

上海的高价值客户虽然数量不是最多，但平均消费额最高，达到 ¥19,432。北京的高价值客户数量最多，有 342 位。这些数据可以帮助您更有针对性地制定营销策略和客户服务计划。

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

PostgreSQL 示例

基础查询示例

列出所有表

查看表结构

基本数据查询

PostgreSQL 特有功能

JSON 数据查询

全文搜索

数组操作

高级查询技巧

窗口函数

公共表表达式 (CTE)

性能优化技巧

使用 EXPLAIN ANALYZE

优化索引使用

分区表查询

实际应用场景

场景 1：销售数据分析

场景 2：客户细分分析

场景 3：库存管理

与 AI 的交互示例

FilesExpand file tree

postgresql-examples.md

Latest commit

History

postgresql-examples.md

File metadata and controls

PostgreSQL 示例

基础查询示例

列出所有表

查看表结构

基本数据查询

PostgreSQL 特有功能

JSON 数据查询

全文搜索

数组操作

高级查询技巧

窗口函数

公共表表达式 (CTE)

性能优化技巧

使用 EXPLAIN ANALYZE

优化索引使用

分区表查询

实际应用场景

场景 1：销售数据分析

场景 2：客户细分分析

场景 3：库存管理

与 AI 的交互示例