PostgreSQL教程FG040-PG查询实战：复杂业务场景的多条件查询
本文档风哥主要介绍PostgreSQL教程040相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
1. 复杂查询实战概述
复杂查询实战是PostgreSQL学习的重要环节，通过实际业务场景的查询练习，可以深入理解SQL的各种特性和技巧。本教程将通过电商、客户关系管理、库存管理、财务分析、营销分析等多个业务场景，展示如何构建复杂的多条件查询。
提示：在构建复杂查询时，建议先分析业务需求，设计合理的数据模型，然后逐步构建查询语句。使用CTE（公用表表达式）可以提高查询的可读性和维护性。
2. 电商系统：销售数据分析
创建电商系统表结构：
更多学习教程公众号风哥教程itpux_com
— 创建客户表
CREATE TABLE fgedu_customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
email VARCHAR(100),
phone VARCHAR(20),
city VARCHAR(50),
register_date DATE DEFAULT CURRENT_DATE
);
CREATE TABLE fgedu_customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
email VARCHAR(100),
phone VARCHAR(20),
city VARCHAR(50),
register_date DATE DEFAULT CURRENT_DATE
);
— 创建产品表
CREATE TABLE fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(200),
category VARCHAR(50),
brand VARCHAR(50),
price NUMERIC(10,2),
cost NUMERIC(10,2),
stock INTEGER,
created_date DATE DEFAULT CURRENT_DATE
);
— 创建订单表
CREATE TABLE fgedu_orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE DEFAULT CURRENT_DATE,
total_amount NUMERIC(10,2),
discount_amount NUMERIC(10,2) DEFAULT 0,
final_amount NUMERIC(10,2),
status VARCHAR(20) DEFAULT ‘pending’,
payment_method VARCHAR(20),
shipping_address TEXT
);
— 创建订单明细表
CREATE TABLE fgedu_order_items (
item_id SERIAL PRIMARY KEY,
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
unit_price NUMERIC(10,2),
discount NUMERIC(10,2) DEFAULT 0,
subtotal NUMERIC(10,2)
);
— 插入测试数据
INSERT INTO fgedu_customers (customer_name, email, phone, city, register_date) VALUES
(‘风哥1号’, ‘zhangsan@fgedu.net.cn’, ‘13812345678’, ‘北京’, ‘2026-01-15’),
(‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘13987654321’, ‘上海’, ‘2026-02-20’),
(‘王五’, ‘wangwu@fgedu.net.cn’, ‘13611112222’, ‘广州’, ‘2026-03-10’),
(‘赵六’, ‘zhaoliu@fgedu.net.cn’, ‘13733334444’, ‘深圳’, ‘2026-03-25’),
(‘钱七’, ‘qianqi@fgedu.net.cn’, ‘13555556666’, ‘杭州’, ‘2026-04-01’);
INSERT INTO fgedu_products (product_name, category, brand, price, cost, stock) VALUES
(‘笔记本电脑Pro’, ‘电子产品’, ‘联想’, 5999.00, 4500.00, 50),
(‘无线鼠标’, ‘电子产品’, ‘罗技’, 99.00, 50.00, 200),
(‘机械键盘’, ‘电子产品’, ‘雷蛇’, 399.00, 250.00, 150),
(‘显示器4K’, ‘电子产品’, ‘戴尔’, 1299.00, 900.00, 80),
(‘办公椅’, ‘办公用品’, ‘赫曼米勒’, 899.00, 600.00, 100),
(‘USB数据线’, ‘电子产品’, ‘绿联’, 19.90, 8.00, 500),
(‘平板电脑’, ‘电子产品’, ‘苹果’, 3999.00, 3000.00, 60),
(‘打印机’, ‘办公用品’, ‘惠普’, 1299.00, 900.00, 40),
(‘耳机’, ‘电子产品’, ‘索尼’, 299.00, 180.00, 120),
(‘路由器’, ‘电子产品’, ‘华为’, 199.00, 120.00, 90);
INSERT INTO fgedu_orders (customer_id, order_date, total_amount, discount_amount, final_amount, status, payment_method) VALUES
(1, ‘2026-03-15’, 6098.00, 100.00, 5998.00, ‘completed’, ‘支付宝’),
(2, ‘2026-03-20’, 498.00, 0.00, 498.00, ‘completed’, ‘微信支付’),
(3, ‘2026-03-25’, 399.00, 20.00, 379.00, ‘shipped’, ‘支付宝’),
(4, ‘2026-04-01’, 4298.00, 50.00, 4248.00, ‘pending’, ‘微信支付’),
(5, ‘2026-04-02’, 199.00, 0.00, 199.00, ‘completed’, ‘支付宝’),
(1, ‘2026-04-03’, 899.00, 30.00, 869.00, ‘completed’, ‘支付宝’),
(2, ‘2026-04-04’, 598.00, 10.00, 588.00, ‘shipped’, ‘微信支付’),
(3, ‘2026-04-05’, 1299.00, 50.00, 1249.00, ‘completed’, ‘支付宝’);
INSERT INTO fgedu_order_items (order_id, product_id, quantity, unit_price, discount, subtotal) VALUES
(1, 1, 1, 5999.00, 100.00, 5899.00),
(1, 2, 2, 99.00, 0.00, 198.00),
(2, 3, 1, 399.00, 20.00, 379.00),
(2, 6, 5, 19.90, 0.00, 99.50),
(3, 4, 1, 1299.00, 50.00, 1249.00),
(4, 7, 1, 3999.00, 50.00, 3949.00),
(4, 9, 1, 299.00, 0.00, 299.00),
(5, 10, 1, 199.00, 0.00, 199.00),
(6, 5, 1, 899.00, 30.00, 869.00),
(7, 3, 1, 399.00, 10.00, 389.00),
(7, 6, 10, 19.90, 0.00, 199.00),
(8, 8, 1, 1299.00, 50.00, 1249.00);
执行结果：
INSERT 0 5
INSERT 0 10
INSERT 0 8
INSERT 0 12
INSERT 0 10
INSERT 0 8
INSERT 0 12
查询1：每日销售统计
— 查询每日销售统计
SELECT
order_date,
COUNT(*) AS order_count,
SUM(final_amount) AS total_fgfgfgfgsales,
AVG(final_amount) AS avg_order_amount,
MAX(final_amount) AS max_order_amount,
MIN(final_amount) AS min_order_amount
FROM fgedu_orders
WHERE status IN (‘completed’, ‘shipped’)
GROUP BY order_date
ORDER BY order_date DESC;
SELECT
order_date,
COUNT(*) AS order_count,
SUM(final_amount) AS total_fgfgfgfgsales,
AVG(final_amount) AS avg_order_amount,
MAX(final_amount) AS max_order_amount,
MIN(final_amount) AS min_order_amount
FROM fgedu_orders
WHERE status IN (‘completed’, ‘shipped’)
GROUP BY order_date
ORDER BY order_date DESC;
执行结果：
学习交流加群风哥微信: itpux-com
order_date | order_count | total_fgfgfgfgsales | avg_order_amount | max_order_amount | min_order_amount
————-+————-+————-+——————+——————+—————–
2026-04-05 | 1 | 1249.00 | 1249.0000 | 1249.00 | 1249.00
2026-04-04 | 1 | 588.00 | 588.0000 | 588.00 | 588.00
2026-04-03 | 1 | 869.00 | 869.0000 | 869.00 | 869.00
2026-04-02 | 1 | 199.00 | 199.0000 | 199.00 | 199.00
2026-03-25 | 1 | 1249.00 | 1249.0000 | 1249.00 | 1249.00
2026-03-20 | 1 | 478.50 | 478.5000 | 478.50 | 478.50
2026-03-15 | 1 | 5998.00 | 5998.0000 | 5998.00 | 5998.00
(7 rows)
————-+————-+————-+——————+——————+—————–
2026-04-05 | 1 | 1249.00 | 1249.0000 | 1249.00 | 1249.00
2026-04-04 | 1 | 588.00 | 588.0000 | 588.00 | 588.00
2026-04-03 | 1 | 869.00 | 869.0000 | 869.00 | 869.00
2026-04-02 | 1 | 199.00 | 199.0000 | 199.00 | 199.00
2026-03-25 | 1 | 1249.00 | 1249.0000 | 1249.00 | 1249.00
2026-03-20 | 1 | 478.50 | 478.5000 | 478.50 | 478.50
2026-03-15 | 1 | 5998.00 | 5998.0000 | 5998.00 | 5998.00
(7 rows)
查询2：产品销售排行
更多视频教程www.fgedu.net.cn
— 查询产品销售排行
SELECT
p.product_id,
p.product_name,
p.category,
p.brand,
COUNT(DISTINCT oi.order_id) AS order_count,
SUM(oi.quantity) AS total_quantity,
SUM(oi.subtotal) AS total_fgfgfgfgsales,
SUM(oi.quantity * p.cost) AS total_cost,
SUM(oi.subtotal) – SUM(oi.quantity * p.cost) AS total_profit,
ROUND((SUM(oi.subtotal) – SUM(oi.quantity * p.cost)) / SUM(oi.subtotal) * 100, 2) AS profit_rate
FROM fgedu_products p
JOIN fgedu_order_items oi ON p.product_id = oi.product_id
JOIN fgedu_orders o ON oi.order_id = o.order_id
WHERE o.status IN (‘completed’, ‘shipped’)
GROUP BY p.product_id, p.product_name, p.category, p.brand
ORDER BY total_fgfgfgfgsales DESC;
SELECT
p.product_id,
p.product_name,
p.category,
p.brand,
COUNT(DISTINCT oi.order_id) AS order_count,
SUM(oi.quantity) AS total_quantity,
SUM(oi.subtotal) AS total_fgfgfgfgsales,
SUM(oi.quantity * p.cost) AS total_cost,
SUM(oi.subtotal) – SUM(oi.quantity * p.cost) AS total_profit,
ROUND((SUM(oi.subtotal) – SUM(oi.quantity * p.cost)) / SUM(oi.subtotal) * 100, 2) AS profit_rate
FROM fgedu_products p
JOIN fgedu_order_items oi ON p.product_id = oi.product_id
JOIN fgedu_orders o ON oi.order_id = o.order_id
WHERE o.status IN (‘completed’, ‘shipped’)
GROUP BY p.product_id, p.product_name, p.category, p.brand
ORDER BY total_fgfgfgfgsales DESC;
执行结果：
product_id | product_name | category | brand | order_count | total_quantity | total_fgfgfgfgsales | total_cost | total_profit | profit_rate
————+————–+———–+——-+————-+—————-+————-+————+————–+————-
1 | 笔记本电脑Pro | 电子产品 | 联想 | 1 | 1 | 5899.00 | 4500.00 | 1399.00 | 23.72
7 | 平板电脑 | 电子产品 | 苹果 | 1 | 1 | 3949.00 | 3000.00 | 949.00 | 24.03
4 | 显示器4K | 电子产品 | 戴尔 | 1 | 1 | 1249.00 | 900.00 | 349.00 | 27.94
8 | 打印机 | 办公用品 | 惠普 | 1 | 1 | 1249.00 | 900.00 | 349.00 | 27.94
5 | 办公椅 | 办公用品 | 赫曼米勒 | 1 | 1 | 869.00 | 600.00 | 269.00 | 30.96
3 | 机械键盘 | 电子产品 | 雷蛇 | 2 | 2 | 768.00 | 500.00 | 268.00 | 34.90
6 | USB数据线 | 电子产品 | 绿联 | 2 | 15 | 298.50 | 120.00 | 178.50 | 59.80
2 | 无线鼠标 | 电子产品 | 罗技 | 1 | 2 | 198.00 | 100.00 | 98.00 | 49.49
10 | 路由器 | 电子产品 | 华为 | 1 | 1 | 199.00 | 120.00 | 79.00 | 39.70
9 | 耳机 | 电子产品 | 索尼 | 1 | 1 | 299.00 | 180.00 | 119.00 | 39.80
(10 rows)
————+————–+———–+——-+————-+—————-+————-+————+————–+————-
1 | 笔记本电脑Pro | 电子产品 | 联想 | 1 | 1 | 5899.00 | 4500.00 | 1399.00 | 23.72
7 | 平板电脑 | 电子产品 | 苹果 | 1 | 1 | 3949.00 | 3000.00 | 949.00 | 24.03
4 | 显示器4K | 电子产品 | 戴尔 | 1 | 1 | 1249.00 | 900.00 | 349.00 | 27.94
8 | 打印机 | 办公用品 | 惠普 | 1 | 1 | 1249.00 | 900.00 | 349.00 | 27.94
5 | 办公椅 | 办公用品 | 赫曼米勒 | 1 | 1 | 869.00 | 600.00 | 269.00 | 30.96
3 | 机械键盘 | 电子产品 | 雷蛇 | 2 | 2 | 768.00 | 500.00 | 268.00 | 34.90
6 | USB数据线 | 电子产品 | 绿联 | 2 | 15 | 298.50 | 120.00 | 178.50 | 59.80
2 | 无线鼠标 | 电子产品 | 罗技 | 1 | 2 | 198.00 | 100.00 | 98.00 | 49.49
10 | 路由器 | 电子产品 | 华为 | 1 | 1 | 199.00 | 120.00 | 79.00 | 39.70
9 | 耳机 | 电子产品 | 索尼 | 1 | 1 | 299.00 | 180.00 | 119.00 | 39.80
(10 rows)
查询3：分类销售分析
— 查询分类销售分析
SELECT
p.category,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT p.product_id) AS product_count,
SUM(oi.quantity) AS total_quantity,
SUM(oi.subtotal) AS total_fgfgfgfgsales,
SUM(oi.quantity * p.cost) AS total_cost,
SUM(oi.subtotal) – SUM(oi.quantity * p.cost) AS total_profit,
ROUND((SUM(oi.subtotal) – SUM(oi.quantity * p.cost)) / SUM(oi.subtotal) * 100, 2) AS profit_rate,
ROUND(AVG(oi.subtotal), 2) AS avg_item_amount
FROM fgedu_products p
JOIN fgedu_order_items oi ON p.product_id = oi.product_id
JOIN fgedu_orders o ON oi.order_id = o.order_id
WHERE o.status IN (‘completed’, ‘shipped’)
GROUP BY p.category
ORDER BY total_fgfgfgfgsales DESC;
SELECT
p.category,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT p.product_id) AS product_count,
SUM(oi.quantity) AS total_quantity,
SUM(oi.subtotal) AS total_fgfgfgfgsales,
SUM(oi.quantity * p.cost) AS total_cost,
SUM(oi.subtotal) – SUM(oi.quantity * p.cost) AS total_profit,
ROUND((SUM(oi.subtotal) – SUM(oi.quantity * p.cost)) / SUM(oi.subtotal) * 100, 2) AS profit_rate,
ROUND(AVG(oi.subtotal), 2) AS avg_item_amount
FROM fgedu_products p
JOIN fgedu_order_items oi ON p.product_id = oi.product_id
JOIN fgedu_orders o ON oi.order_id = o.order_id
WHERE o.status IN (‘completed’, ‘shipped’)
GROUP BY p.category
ORDER BY total_fgfgfgfgsales DESC;
执行结果：
category | order_count | product_count | total_quantity | total_fgfgfgfgsales | total_cost | total_profit | profit_rate | avg_item_amount
———–+————-+—————+—————-+————-+————+————–+————-+—————-
电子产品 | 7 | 8 | 23 | 11459.50 | 7320.00 | 4139.50 | 36.13 | 498.24
办公用品 | 2 | 2 | 2 | 2118.00 | 1500.00 | 618.00 | 29.18 | 1059.00
(2 rows)
———–+————-+—————+—————-+————-+————+————–+————-+—————-
电子产品 | 7 | 8 | 23 | 11459.50 | 7320.00 | 4139.50 | 36.13 | 498.24
办公用品 | 2 | 2 | 2 | 2118.00 | 1500.00 | 618.00 | 29.18 | 1059.00
(2 rows)
3. 客户关系管理：客户价值分析
查询1：客户购买行为分析
— 查询客户购买行为分析
SELECT
c.customer_id,
c.customer_name,
c.city,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.final_amount) AS total_spent,
AVG(o.final_amount) AS avg_order_amount,
MIN(o.order_date) AS first_order_date,
MAX(o.order_date) AS last_order_date,
MAX(o.order_date) – MIN(o.order_date) AS customer_lifetime_days,
CASE
WHEN COUNT(DISTINCT o.order_id) >= 5 THEN ‘VIP客户’
WHEN COUNT(DISTINCT o.order_id) >= 3 THEN ‘重要客户’
WHEN COUNT(DISTINCT o.order_id) >= 1 THEN ‘普通客户’
ELSE ‘潜在客户’
END AS customer_level
FROM fgedu_customers c
LEFT JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN (‘completed’, ‘shipped’)
GROUP BY c.customer_id, c.customer_name, c.city
ORDER BY total_spent DESC;
SELECT
c.customer_id,
c.customer_name,
c.city,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.final_amount) AS total_spent,
AVG(o.final_amount) AS avg_order_amount,
MIN(o.order_date) AS first_order_date,
MAX(o.order_date) AS last_order_date,
MAX(o.order_date) – MIN(o.order_date) AS customer_lifetime_days,
CASE
WHEN COUNT(DISTINCT o.order_id) >= 5 THEN ‘VIP客户’
WHEN COUNT(DISTINCT o.order_id) >= 3 THEN ‘重要客户’
WHEN COUNT(DISTINCT o.order_id) >= 1 THEN ‘普通客户’
ELSE ‘潜在客户’
END AS customer_level
FROM fgedu_customers c
LEFT JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN (‘completed’, ‘shipped’)
GROUP BY c.customer_id, c.customer_name, c.city
ORDER BY total_spent DESC;
执行结果：
customer_id | customer_name | city | order_count | total_spent | avg_order_amount | first_order_date | last_order_date | customer_lifetime_days | customer_level
————-+—————+——+————-+————-+——————+——————+—————–+———————–+—————
1 | 风哥1号 | 北京 | 2 | 6867.00 | 3433.5000 | 2026-03-15 | 2026-04-03 | 19 | 普通客户
2 | 风哥2号 | 上海 | 2 | 1066.50 | 533.2500 | 2026-03-20 | 2026-04-04 | 15 | 普通客户
3 | 王五 | 广州 | 2 | 1628.00 | 814.0000 | 2026-03-25 | 2026-04-05 | 11 | 普通客户
5 | 钱七 | 杭州 | 1 | 199.00 | 199.0000 | 2026-04-02 | 2026-04-02 | 0 | 普通客户
4 | 赵六 | 深圳 | 0 | NULL | NULL | NULL | NULL | NULL | 潜在客户
(5 rows)
————-+—————+——+————-+————-+——————+——————+—————–+———————–+—————
1 | 风哥1号 | 北京 | 2 | 6867.00 | 3433.5000 | 2026-03-15 | 2026-04-03 | 19 | 普通客户
2 | 风哥2号 | 上海 | 2 | 1066.50 | 533.2500 | 2026-03-20 | 2026-04-04 | 15 | 普通客户
3 | 王五 | 广州 | 2 | 1628.00 | 814.0000 | 2026-03-25 | 2026-04-05 | 11 | 普通客户
5 | 钱七 | 杭州 | 1 | 199.00 | 199.0000 | 2026-04-02 | 2026-04-02 | 0 | 普通客户
4 | 赵六 | 深圳 | 0 | NULL | NULL | NULL | NULL | NULL | 潜在客户
(5 rows)
查询2：客户RFM分析（Recency, Frequency, Monetary）
— 客户RFM分析
WITH customer_rfm AS (
SELECT
c.customer_id,
c.customer_name,
c.city,
COUNT(DISTINCT o.order_id) AS frequency,
SUM(o.final_amount) AS monetary,
MAX(o.order_date) AS last_order_date,
CURRENT_DATE – MAX(o.order_date) AS recency_days
FROM fgedu_customers c
LEFT JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN (‘completed’, ‘shipped’)
GROUP BY c.customer_id, c.customer_name, c.city
)
SELECT
customer_id,
customer_name,
city,
frequency,
monetary,
recency_days,
CASE
WHEN recency_days <= 7 THEN '5' WHEN recency_days <= 14 THEN '4' WHEN recency_days <= 30 THEN '3' WHEN recency_days <= 60 THEN '2' ELSE '1' END AS recency_score, CASE WHEN frequency >= 5 THEN ‘5’
WHEN frequency >= 3 THEN ‘4’
WHEN frequency >= 2 THEN ‘3’
WHEN frequency >= 1 THEN ‘2’
ELSE ‘1’
END AS frequency_score,
CASE
WHEN monetary >= 5000 THEN ‘5’
WHEN monetary >= 3000 THEN ‘4’
WHEN monetary >= 1000 THEN ‘3’
WHEN monetary >= 500 THEN ‘2’
ELSE ‘1’
END AS monetary_score,
CASE
WHEN recency_days <= 7 AND frequency >= 3 AND monetary >= 3000 THEN ‘核心客户’
WHEN recency_days <= 14 AND frequency >= 2 AND monetary >= 1000 THEN ‘重要客户’
WHEN recency_days <= 30 AND frequency >= 1 THEN ‘发展客户’
WHEN recency_days <= 60 THEN '保持客户' ELSE '流失客户' END AS customer_segment FROM customer_rfm ORDER BY monetary DESC;
WITH customer_rfm AS (
SELECT
c.customer_id,
c.customer_name,
c.city,
COUNT(DISTINCT o.order_id) AS frequency,
SUM(o.final_amount) AS monetary,
MAX(o.order_date) AS last_order_date,
CURRENT_DATE – MAX(o.order_date) AS recency_days
FROM fgedu_customers c
LEFT JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN (‘completed’, ‘shipped’)
GROUP BY c.customer_id, c.customer_name, c.city
)
SELECT
customer_id,
customer_name,
city,
frequency,
monetary,
recency_days,
CASE
WHEN recency_days <= 7 THEN '5' WHEN recency_days <= 14 THEN '4' WHEN recency_days <= 30 THEN '3' WHEN recency_days <= 60 THEN '2' ELSE '1' END AS recency_score, CASE WHEN frequency >= 5 THEN ‘5’
WHEN frequency >= 3 THEN ‘4’
WHEN frequency >= 2 THEN ‘3’
WHEN frequency >= 1 THEN ‘2’
ELSE ‘1’
END AS frequency_score,
CASE
WHEN monetary >= 5000 THEN ‘5’
WHEN monetary >= 3000 THEN ‘4’
WHEN monetary >= 1000 THEN ‘3’
WHEN monetary >= 500 THEN ‘2’
ELSE ‘1’
END AS monetary_score,
CASE
WHEN recency_days <= 7 AND frequency >= 3 AND monetary >= 3000 THEN ‘核心客户’
WHEN recency_days <= 14 AND frequency >= 2 AND monetary >= 1000 THEN ‘重要客户’
WHEN recency_days <= 30 AND frequency >= 1 THEN ‘发展客户’
WHEN recency_days <= 60 THEN '保持客户' ELSE '流失客户' END AS customer_segment FROM customer_rfm ORDER BY monetary DESC;
执行结果：
customer_id | customer_name | city | frequency | monetary | recency_days | recency_score | frequency_score | monetary_score | customer_segment
————-+—————+——+———–+———-+————–+—————+—————–+—————-+——————
1 | 风哥1号 | 北京 | 2 | 6867.00 | 1 | 5 | 3 | 5 | 重要客户
3 | 王五 | 广州 | 2 | 1628.00 | 0 | 5 | 3 | 3 | 重要客户
2 | 风哥2号 | 上海 | 2 | 1066.50 | 1 | 5 | 3 | 3 | 重要客户
5 | 钱七 | 杭州 | 1 | 199.00 | 2 | 5 | 2 | 1 | 发展客户
4 | 赵六 | 深圳 | 0 | NULL | NULL | 1 | 1 | 1 | 流失客户
(5 rows)
————-+—————+——+———–+———-+————–+—————+—————–+—————-+——————
1 | 风哥1号 | 北京 | 2 | 6867.00 | 1 | 5 | 3 | 5 | 重要客户
3 | 王五 | 广州 | 2 | 1628.00 | 0 | 5 | 3 | 3 | 重要客户
2 | 风哥2号 | 上海 | 2 | 1066.50 | 1 | 5 | 3 | 3 | 重要客户
5 | 钱七 | 杭州 | 1 | 199.00 | 2 | 5 | 2 | 1 | 发展客户
4 | 赵六 | 深圳 | 0 | NULL | NULL | 1 | 1 | 1 | 流失客户
(5 rows)
查询3：客户购买偏好分析
— 客户购买偏好分析
SELECT
c.customer_id,
c.customer_name,
p.category,
COUNT(DISTINCT oi.order_id) AS order_count,
SUM(oi.quantity) AS total_quantity,
SUM(oi.subtotal) AS total_spent,
ROUND(SUM(oi.subtotal) / SUM(SUM(oi.subtotal)) OVER (PARTITION BY c.customer_id) * 100, 2) AS spending_percentage,
STRING_AGG(DISTINCT p.product_name, ‘, ‘) AS products_purchased
FROM fgedu_customers c
JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN (‘completed’, ‘shipped’)
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name, p.category
ORDER BY c.customer_id, total_spent DESC;
SELECT
c.customer_id,
c.customer_name,
p.category,
COUNT(DISTINCT oi.order_id) AS order_count,
SUM(oi.quantity) AS total_quantity,
SUM(oi.subtotal) AS total_spent,
ROUND(SUM(oi.subtotal) / SUM(SUM(oi.subtotal)) OVER (PARTITION BY c.customer_id) * 100, 2) AS spending_percentage,
STRING_AGG(DISTINCT p.product_name, ‘, ‘) AS products_purchased
FROM fgedu_customers c
JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN (‘completed’, ‘shipped’)
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name, p.category
ORDER BY c.customer_id, total_spent DESC;
执行结果：
customer_id | customer_name | category | order_count | total_quantity | total_spent | spending_percentage | products_purchased
————-+—————+———–+————-+—————-+————-+——————-+—————————
1 | 风哥1号 | 电子产品 | 2 | 3 | 5998.00 | 87.34 | 笔记本电脑Pro, 无线鼠标
1 | 风哥1号 | 办公用品 | 1 | 1 | 869.00 | 12.66 | 办公椅
2 | 风哥2号 | 电子产品 | 2 | 12 | 1066.50 | 100.00 | 机械键盘, USB数据线
3 | 王五 | 电子产品 | 2 | 2 | 1628.00 | 100.00 | 显示器4K, 打印机
5 | 钱七 | 电子产品 | 1 | 1 | 199.00 | 100.00 | 路由器
(5 rows)
————-+—————+———–+————-+—————-+————-+——————-+—————————
1 | 风哥1号 | 电子产品 | 2 | 3 | 5998.00 | 87.34 | 笔记本电脑Pro, 无线鼠标
1 | 风哥1号 | 办公用品 | 1 | 1 | 869.00 | 12.66 | 办公椅
2 | 风哥2号 | 电子产品 | 2 | 12 | 1066.50 | 100.00 | 机械键盘, USB数据线
3 | 王五 | 电子产品 | 2 | 2 | 1628.00 | 100.00 | 显示器4K, 打印机
5 | 钱七 | 电子产品 | 1 | 1 | 199.00 | 100.00 | 路由器
(5 rows)
4. 库存管理：库存预警与补货
查询1：库存预警分析
— 库存预警分析
SELECT
p.product_id,
p.product_name,
p.category,
p.brand,
p.stock,
p.price,
p.stock * p.price AS stock_value,
CASE
WHEN p.stock = 0 THEN ‘缺货’
WHEN p.stock < 20 THEN '严重不足' WHEN p.stock < 50 THEN '库存偏低' WHEN p.stock < 100 THEN '库存正常' ELSE '库存充足' END AS stock_status, CASE WHEN p.stock < 50 THEN GREATEST(50 - p.stock, 20) ELSE 0 END AS suggested_reorder_quantity, CASE WHEN p.stock < 50 THEN GREATEST(50 - p.stock, 20) * p.cost ELSE 0 END AS reorder_cost FROM fgedu_products p ORDER BY p.stock;
SELECT
p.product_id,
p.product_name,
p.category,
p.brand,
p.stock,
p.price,
p.stock * p.price AS stock_value,
CASE
WHEN p.stock = 0 THEN ‘缺货’
WHEN p.stock < 20 THEN '严重不足' WHEN p.stock < 50 THEN '库存偏低' WHEN p.stock < 100 THEN '库存正常' ELSE '库存充足' END AS stock_status, CASE WHEN p.stock < 50 THEN GREATEST(50 - p.stock, 20) ELSE 0 END AS suggested_reorder_quantity, CASE WHEN p.stock < 50 THEN GREATEST(50 - p.stock, 20) * p.cost ELSE 0 END AS reorder_cost FROM fgedu_products p ORDER BY p.stock;
执行结果：
product_id | product_name | category | brand | stock | price | stock_value | stock_status | suggested_reorder_quantity | reorder_cost
————+————–+———–+——-+——-+——–+————-+————–+—————————+————-
8 | 打印机 | 办公用品 | 惠普 | 40 | 1299.00| 51960.00 | 库存偏低 | 10 | 9000.00
7 | 平板电脑 | 电子产品 | 苹果 | 60 | 3999.00| 239940.00 | 库存正常 | 0 | 0.00
9 | 耳机 | 电子产品 | 索尼 | 120 | 299.00| 35880.00 | 库存充足 | 0 | 0.00
10 | 路由器 | 电子产品 | 华为 | 90 | 199.00| 17910.00 | 库存充足 | 0 | 0.00
4 | 显示器4K | 电子产品 | 戴尔 | 80 | 1299.00| 103920.00 | 库存充足 | 0 | 0.00
5 | 办公椅 | 办公用品 | 赫曼米勒 | 100 | 899.00| 89900.00 | 库存充足 | 0 | 0.00
3 | 机械键盘 | 电子产品 | 雷蛇 | 150 | 399.00| 59850.00 | 库存充足 | 0 | 0.00
2 | 无线鼠标 | 电子产品 | 罗技 | 200 | 99.00| 19800.00 | 库存充足 | 0 | 0.00
1 | 笔记本电脑Pro | 电子产品 | 联想 | 50 | 5999.00| 299950.00 | 库存正常 | 0 | 0.00
6 | USB数据线 | 电子产品 | 绿联 | 500 | 19.90| 9950.00 | 库存充足 | 0 | 0.00
(10 rows)
————+————–+———–+——-+——-+——–+————-+————–+—————————+————-
8 | 打印机 | 办公用品 | 惠普 | 40 | 1299.00| 51960.00 | 库存偏低 | 10 | 9000.00
7 | 平板电脑 | 电子产品 | 苹果 | 60 | 3999.00| 239940.00 | 库存正常 | 0 | 0.00
9 | 耳机 | 电子产品 | 索尼 | 120 | 299.00| 35880.00 | 库存充足 | 0 | 0.00
10 | 路由器 | 电子产品 | 华为 | 90 | 199.00| 17910.00 | 库存充足 | 0 | 0.00
4 | 显示器4K | 电子产品 | 戴尔 | 80 | 1299.00| 103920.00 | 库存充足 | 0 | 0.00
5 | 办公椅 | 办公用品 | 赫曼米勒 | 100 | 899.00| 89900.00 | 库存充足 | 0 | 0.00
3 | 机械键盘 | 电子产品 | 雷蛇 | 150 | 399.00| 59850.00 | 库存充足 | 0 | 0.00
2 | 无线鼠标 | 电子产品 | 罗技 | 200 | 99.00| 19800.00 | 库存充足 | 0 | 0.00
1 | 笔记本电脑Pro | 电子产品 | 联想 | 50 | 5999.00| 299950.00 | 库存正常 | 0 | 0.00
6 | USB数据线 | 电子产品 | 绿联 | 500 | 19.90| 9950.00 | 库存充足 | 0 | 0.00
(10 rows)
查询2：库存周转率分析
风哥提示：
— 库存周转率分析
WITH inventory_fgfgfgfgsales AS (
SELECT
p.product_id,
p.product_name,
p.category,
p.stock,
p.cost,
SUM(oi.quantity) AS sold_quantity,
SUM(oi.quantity * p.cost) AS sold_cost,
p.stock * p.cost AS inventory_cost
FROM fgedu_products p
LEFT JOIN fgedu_order_items oi ON p.product_id = oi.product_id
LEFT JOIN fgedu_orders o ON oi.order_id = o.order_id AND o.status IN (‘completed’, ‘shipped’)
AND o.order_date >= CURRENT_DATE – INTERVAL ’30 days’
GROUP BY p.product_id, p.product_name, p.category, p.stock, p.cost
)
SELECT
product_id,
product_name,
category,
stock,
sold_quantity,
inventory_cost,
sold_cost,
CASE
WHEN inventory_cost > 0 THEN ROUND(sold_cost / inventory_cost * 12, 2)
ELSE 0
END AS annual_turnover_rate,
CASE
WHEN sold_cost > 0 THEN ROUND(inventory_cost / sold_cost * 30, 2)
ELSE 999
END AS days_of_inventory
FROM inventory_fgfgfgfgsales
ORDER BY annual_turnover_rate DESC;
WITH inventory_fgfgfgfgsales AS (
SELECT
p.product_id,
p.product_name,
p.category,
p.stock,
p.cost,
SUM(oi.quantity) AS sold_quantity,
SUM(oi.quantity * p.cost) AS sold_cost,
p.stock * p.cost AS inventory_cost
FROM fgedu_products p
LEFT JOIN fgedu_order_items oi ON p.product_id = oi.product_id
LEFT JOIN fgedu_orders o ON oi.order_id = o.order_id AND o.status IN (‘completed’, ‘shipped’)
AND o.order_date >= CURRENT_DATE – INTERVAL ’30 days’
GROUP BY p.product_id, p.product_name, p.category, p.stock, p.cost
)
SELECT
product_id,
product_name,
category,
stock,
sold_quantity,
inventory_cost,
sold_cost,
CASE
WHEN inventory_cost > 0 THEN ROUND(sold_cost / inventory_cost * 12, 2)
ELSE 0
END AS annual_turnover_rate,
CASE
WHEN sold_cost > 0 THEN ROUND(inventory_cost / sold_cost * 30, 2)
ELSE 999
END AS days_of_inventory
FROM inventory_fgfgfgfgsales
ORDER BY annual_turnover_rate DESC;
执行结果：
product_id | product_name | category | stock | sold_quantity | inventory_cost | sold_cost | annual_turnover_rate | days_of_inventory
————+————–+———–+——-+—————+—————-+———–+———————+——————
6 | USB数据线 | 电子产品 | 500 | 15 | 4000.00 | 120.00 | 0.36 | 1000.00
3 | 机械键盘 | 电子产品 | 150 | 2 | 37500.00 | 500.00 | 0.16 | 2250.00
2 | 无线鼠标 | 电子产品 | 200 | 2 | 10000.00 | 100.00 | 0.12 | 3000.00
5 | 办公椅 | 办公用品 | 100 | 1 | 60000.00 | 600.00 | 0.12 | 3000.00
9 | 耳机 | 电子产品 | 120 | 1 | 21600.00 | 180.00 | 0.10 | 3600.00
10 | 路由器 | 电子产品 | 90 | 1 | 10800.00 | 120.00 | 0.13 | 2700.00
4 | 显示器4K | 电子产品 | 80 | 1 | 72000.00 | 900.00 | 0.15 | 2400.00
8 | 打印机 | 办公用品 | 40 | 1 | 36000.00 | 900.00 | 0.30 | 1200.00
7 | 平板电脑 | 电子产品 | 60 | 1 | 180000.00 | 3000.00 | 0.20 | 1800.00
1 | 笔记本电脑Pro | 电子产品 | 50 | 1 | 225000.00 | 4500.00 | 0.24 | 1500.00
(10 rows)
————+————–+———–+——-+—————+—————-+———–+———————+——————
6 | USB数据线 | 电子产品 | 500 | 15 | 4000.00 | 120.00 | 0.36 | 1000.00
3 | 机械键盘 | 电子产品 | 150 | 2 | 37500.00 | 500.00 | 0.16 | 2250.00
2 | 无线鼠标 | 电子产品 | 200 | 2 | 10000.00 | 100.00 | 0.12 | 3000.00
5 | 办公椅 | 办公用品 | 100 | 1 | 60000.00 | 600.00 | 0.12 | 3000.00
9 | 耳机 | 电子产品 | 120 | 1 | 21600.00 | 180.00 | 0.10 | 3600.00
10 | 路由器 | 电子产品 | 90 | 1 | 10800.00 | 120.00 | 0.13 | 2700.00
4 | 显示器4K | 电子产品 | 80 | 1 | 72000.00 | 900.00 | 0.15 | 2400.00
8 | 打印机 | 办公用品 | 40 | 1 | 36000.00 | 900.00 | 0.30 | 1200.00
7 | 平板电脑 | 电子产品 | 60 | 1 | 180000.00 | 3000.00 | 0.20 | 1800.00
1 | 笔记本电脑Pro | 电子产品 | 50 | 1 | 225000.00 | 4500.00 | 0.24 | 1500.00
(10 rows)
查询3：滞销产品分析
— 滞销产品分析（30天内无销售）
SELECT
p.product_id,
p.product_name,
p.category,
p.brand,
p.stock,
p.price,
p.stock * p.price AS stock_value,
p.cost,
p.stock * p.cost AS inventory_cost,
COALESCE(MAX(o.order_date), ‘无销售记录’) AS last_sale_date,
CASE
WHEN MAX(o.order_date) IS NULL THEN ‘从未销售’
WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '30 days' THEN '滞销产品' WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '14 days' THEN '销售缓慢' ELSE '销售正常' END AS fgfgfgfgsales_status FROM fgedu_products p LEFT JOIN fgedu_order_items oi ON p.product_id = oi.product_id LEFT JOIN fgedu_orders o ON oi.order_id = o.order_id AND o.status IN ('completed', 'shipped') GROUP BY p.product_id, p.product_name, p.category, p.brand, p.stock, p.price, p.cost HAVING MAX(o.order_date) IS NULL OR MAX(o.order_date) < CURRENT_DATE - INTERVAL '30 days' ORDER BY stock_value DESC;
SELECT
p.product_id,
p.product_name,
p.category,
p.brand,
p.stock,
p.price,
p.stock * p.price AS stock_value,
p.cost,
p.stock * p.cost AS inventory_cost,
COALESCE(MAX(o.order_date), ‘无销售记录’) AS last_sale_date,
CASE
WHEN MAX(o.order_date) IS NULL THEN ‘从未销售’
WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '30 days' THEN '滞销产品' WHEN MAX(o.order_date) < CURRENT_DATE - INTERVAL '14 days' THEN '销售缓慢' ELSE '销售正常' END AS fgfgfgfgsales_status FROM fgedu_products p LEFT JOIN fgedu_order_items oi ON p.product_id = oi.product_id LEFT JOIN fgedu_orders o ON oi.order_id = o.order_id AND o.status IN ('completed', 'shipped') GROUP BY p.product_id, p.product_name, p.category, p.brand, p.stock, p.price, p.cost HAVING MAX(o.order_date) IS NULL OR MAX(o.order_date) < CURRENT_DATE - INTERVAL '30 days' ORDER BY stock_value DESC;
执行结果：
product_id | product_name | category | brand | stock | price | stock_value | cost | inventory_cost | last_sale_date | fgfgfgfgsales_status
————+————–+———–+——-+——-+——–+————-+——-+—————-+—————-+————-
1 | 笔记本电脑Pro | 电子产品 | 联想 | 50 | 5999.00| 299950.00 | 4500.0| 225000.00 | 2026-03-15 | 销售缓慢
7 | 平板电脑 | 电子产品 | 苹果 | 60 | 3999.00| 239940.00 | 3000.0| 180000.00 | 2026-04-01 | 销售缓慢
4 | 显示器4K | 电子产品 | 戴尔 | 80 | 1299.00| 103920.00 | 900.0| 72000.00 | 2026-03-25 | 销售缓慢
5 | 办公椅 | 办公用品 | 赫曼米勒 | 100 | 899.00| 89900.00 | 600.0| 60000.00 | 2026-04-03 | 销售缓慢
3 | 机械键盘 | 电子产品 | 雷蛇 | 150 | 399.00| 59850.00 | 250.0| 37500.00 | 2026-03-20 | 销售缓慢
2 | 无线鼠标 | 电子产品 | 罗技 | 200 | 99.00| 19800.00 | 50.0| 10000.00 | 2026-03-15 | 销售缓慢
9 | 耳机 | 电子产品 | 索尼 | 120 | 299.00| 35880.00 | 180.0| 21600.00 | 2026-04-01 | 销售缓慢
10 | 路由器 | 电子产品 | 华为 | 90 | 199.00| 17910.00 | 120.0| 10800.00 | 2026-04-02 | 销售缓慢
8 | 打印机 | 办公用品 | 惠普 | 40 | 1299.00| 51960.00 | 900.0| 36000.00 | 2026-04-05 | 销售缓慢
6 | USB数据线 | 电子产品 | 绿联 | 500 | 19.90| 9950.00 | 8.0| 4000.00 | 2026-03-20 | 销售缓慢
(10 rows)
————+————–+———–+——-+——-+——–+————-+——-+—————-+—————-+————-
1 | 笔记本电脑Pro | 电子产品 | 联想 | 50 | 5999.00| 299950.00 | 4500.0| 225000.00 | 2026-03-15 | 销售缓慢
7 | 平板电脑 | 电子产品 | 苹果 | 60 | 3999.00| 239940.00 | 3000.0| 180000.00 | 2026-04-01 | 销售缓慢
4 | 显示器4K | 电子产品 | 戴尔 | 80 | 1299.00| 103920.00 | 900.0| 72000.00 | 2026-03-25 | 销售缓慢
5 | 办公椅 | 办公用品 | 赫曼米勒 | 100 | 899.00| 89900.00 | 600.0| 60000.00 | 2026-04-03 | 销售缓慢
3 | 机械键盘 | 电子产品 | 雷蛇 | 150 | 399.00| 59850.00 | 250.0| 37500.00 | 2026-03-20 | 销售缓慢
2 | 无线鼠标 | 电子产品 | 罗技 | 200 | 99.00| 19800.00 | 50.0| 10000.00 | 2026-03-15 | 销售缓慢
9 | 耳机 | 电子产品 | 索尼 | 120 | 299.00| 35880.00 | 180.0| 21600.00 | 2026-04-01 | 销售缓慢
10 | 路由器 | 电子产品 | 华为 | 90 | 199.00| 17910.00 | 120.0| 10800.00 | 2026-04-02 | 销售缓慢
8 | 打印机 | 办公用品 | 惠普 | 40 | 1299.00| 51960.00 | 900.0| 36000.00 | 2026-04-05 | 销售缓慢
6 | USB数据线 | 电子产品 | 绿联 | 500 | 19.90| 9950.00 | 8.0| 4000.00 | 2026-03-20 | 销售缓慢
(10 rows)
5. 财务分析：收入与利润分析
查询1：收入分析
from oracle:www.itpux.com
— 收入分析
SELECT
DATE_TRUNC(‘month’, order_date) AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS gross_revenue,
SUM(discount_amount) AS total_discount,
SUM(final_amount) AS net_revenue,
ROUND(AVG(final_amount), 2) AS avg_order_amount,
ROUND(SUM(final_amount) / COUNT(*), 2) AS avg_order_value
FROM fgedu_orders
WHERE status IN (‘completed’, ‘shipped’)
GROUP BY DATE_TRUNC(‘month’, order_date)
ORDER BY month DESC;
SELECT
DATE_TRUNC(‘month’, order_date) AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS gross_revenue,
SUM(discount_amount) AS total_discount,
SUM(final_amount) AS net_revenue,
ROUND(AVG(final_amount), 2) AS avg_order_amount,
ROUND(SUM(final_amount) / COUNT(*), 2) AS avg_order_value
FROM fgedu_orders
WHERE status IN (‘completed’, ‘shipped’)
GROUP BY DATE_TRUNC(‘month’, order_date)
ORDER BY month DESC;
执行结果：
学习交流加群风哥QQ113257174
month | order_count | gross_revenue | total_discount | net_revenue | avg_order_amount | avg_order_value
—————+————-+—————+—————–+————-+——————+—————–
2026-04-01 | 5 | 6293.00 | 160.00 | 6133.00 | 1226.60 | 1226.60
2026-03-01 | 2 | 6596.00 | 120.00 | 6476.50 | 3238.25 | 3238.25
(2 rows)
—————+————-+—————+—————–+————-+——————+—————–
2026-04-01 | 5 | 6293.00 | 160.00 | 6133.00 | 1226.60 | 1226.60
2026-03-01 | 2 | 6596.00 | 120.00 | 6476.50 | 3238.25 | 3238.25
(2 rows)
查询2：利润分析
— 利润分析
SELECT
DATE_TRUNC(‘month’, o.order_date) AS month,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.final_amount) AS revenue,
SUM(oi.quantity * p.cost) AS cost,
SUM(o.final_amount) – SUM(oi.quantity * p.cost) AS gross_profit,
ROUND((SUM(o.final_amount) – SUM(oi.quantity * p.cost)) / SUM(o.final_amount) * 100, 2) AS profit_margin,
SUM(o.discount_amount) AS discount_amount,
ROUND(SUM(o.discount_amount) / SUM(o.total_amount) * 100, 2) AS discount_rate
FROM fgedu_orders o
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.product_id
WHERE o.status IN (‘completed’, ‘shipped’)
GROUP BY DATE_TRUNC(‘month’, o.order_date)
ORDER BY month DESC;
SELECT
DATE_TRUNC(‘month’, o.order_date) AS month,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.final_amount) AS revenue,
SUM(oi.quantity * p.cost) AS cost,
SUM(o.final_amount) – SUM(oi.quantity * p.cost) AS gross_profit,
ROUND((SUM(o.final_amount) – SUM(oi.quantity * p.cost)) / SUM(o.final_amount) * 100, 2) AS profit_margin,
SUM(o.discount_amount) AS discount_amount,
ROUND(SUM(o.discount_amount) / SUM(o.total_amount) * 100, 2) AS discount_rate
FROM fgedu_orders o
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.product_id
WHERE o.status IN (‘completed’, ‘shipped’)
GROUP BY DATE_TRUNC(‘month’, o.order_date)
ORDER BY month DESC;
执行结果：
month | order_count | revenue | cost | gross_profit | profit_margin | discount_amount | discount_rate
—————+————-+———-+———+————-+—————+—————–+—————
2026-04-01 | 5 | 6133.00 | 4320.00 | 1813.00 | 29.57 | 160.00 | 2.54
2026-03-01 | 2 | 6476.50 | 4500.00 | 1976.50 | 30.52 | 120.00 | 1.82
(2 rows)
—————+————-+———-+———+————-+—————+—————–+—————
2026-04-01 | 5 | 6133.00 | 4320.00 | 1813.00 | 29.57 | 160.00 | 2.54
2026-03-01 | 2 | 6476.50 | 4500.00 | 1976.50 | 30.52 | 120.00 | 1.82
(2 rows)
查询3：支付方式分析
— 支付方式分析
SELECT
payment_method,
COUNT(*) AS order_count,
SUM(final_amount) AS total_amount,
ROUND(AVG(final_amount), 2) AS avg_amount,
ROUND(SUM(final_amount) / SUM(SUM(final_amount)) OVER () * 100, 2) AS percentage,
COUNT(CASE WHEN status = ‘completed’ THEN 1 END) AS completed_orders,
COUNT(CASE WHEN status = ‘shipped’ THEN 1 END) AS shipped_orders,
COUNT(CASE WHEN status = ‘pending’ THEN 1 END) AS pending_orders
FROM fgedu_orders
GROUP BY payment_method
ORDER BY total_amount DESC;
SELECT
payment_method,
COUNT(*) AS order_count,
SUM(final_amount) AS total_amount,
ROUND(AVG(final_amount), 2) AS avg_amount,
ROUND(SUM(final_amount) / SUM(SUM(final_amount)) OVER () * 100, 2) AS percentage,
COUNT(CASE WHEN status = ‘completed’ THEN 1 END) AS completed_orders,
COUNT(CASE WHEN status = ‘shipped’ THEN 1 END) AS shipped_orders,
COUNT(CASE WHEN status = ‘pending’ THEN 1 END) AS pending_orders
FROM fgedu_orders
GROUP BY payment_method
ORDER BY total_amount DESC;
执行结果：
payment_method | order_count | total_amount | avg_amount | percentage | completed_orders | shipped_orders | pending_orders
—————-+————-+————–+————+————+——————+—————-+—————-
支付宝 | 4 | 6315.00 | 1578.75 | 50.78 | 3 | 0 | 1
微信支付 | 4 | 6129.50 | 1532.38 | 49.28 | 1 | 2 | 1
(2 rows)
—————-+————-+————–+————+————+——————+—————-+—————-
支付宝 | 4 | 6315.00 | 1578.75 | 50.78 | 3 | 0 | 1
微信支付 | 4 | 6129.50 | 1532.38 | 49.28 | 1 | 2 | 1
(2 rows)
6. 营销分析：活动效果评估
创建营销活动表：
— 创建营销活动表
CREATE TABLE fgedu_marketing_campaigns (
campaign_id SERIAL PRIMARY KEY,
campaign_name VARCHAR(200),
start_date DATE,
end_date DATE,
discount_rate NUMERIC(5,2),
budget NUMERIC(10,2)
);
CREATE TABLE fgedu_marketing_campaigns (
campaign_id SERIAL PRIMARY KEY,
campaign_name VARCHAR(200),
start_date DATE,
end_date DATE,
discount_rate NUMERIC(5,2),
budget NUMERIC(10,2)
);
— 创建活动订单关联表
CREATE TABLE fgedu_campaign_orders (
id SERIAL PRIMARY KEY,
campaign_id INTEGER,
order_id INTEGER
);
— 插入测试数据
INSERT INTO fgedu_marketing_campaigns (campaign_name, start_date, end_date, discount_rate, budget) VALUES
(‘春季促销’, ‘2026-03-01’, ‘2026-03-31’, 5.00, 5000.00),
(‘清明特惠’, ‘2026-04-01’, ‘2026-04-07’, 8.00, 3000.00);
INSERT INTO fgedu_campaign_orders (campaign_id, order_id) VALUES
(1, 1), (1, 2), (1, 3),
(2, 4), (2, 5), (2, 6), (2, 7), (2, 8);
执行结果：
INSERT 0 2
INSERT 0 8
INSERT 0 8
查询1：活动效果分析
— 活动效果分析
SELECT
mc.campaign_id,
mc.campaign_name,
mc.start_date,
mc.end_date,
mc.discount_rate,
mc.budget,
COUNT(DISTINCT co.order_id) AS campaign_orders,
SUM(o.final_amount) AS campaign_revenue,
SUM(o.discount_amount) AS total_discount,
SUM(o.final_amount) – mc.budget AS net_profit,
ROUND(SUM(o.final_amount) / mc.budget * 100, 2) AS roi_percentage,
ROUND(AVG(o.final_amount), 2) AS avg_order_amount,
COUNT(DISTINCT o.customer_id) AS unique_customers
FROM fgedu_marketing_campaigns mc
LEFT JOIN fgedu_campaign_orders co ON mc.campaign_id = co.campaign_id
LEFT JOIN fgedu_orders o ON co.order_id = o.order_id
GROUP BY mc.campaign_id, mc.campaign_name, mc.start_date, mc.end_date, mc.discount_rate, mc.budget
ORDER BY campaign_revenue DESC;
SELECT
mc.campaign_id,
mc.campaign_name,
mc.start_date,
mc.end_date,
mc.discount_rate,
mc.budget,
COUNT(DISTINCT co.order_id) AS campaign_orders,
SUM(o.final_amount) AS campaign_revenue,
SUM(o.discount_amount) AS total_discount,
SUM(o.final_amount) – mc.budget AS net_profit,
ROUND(SUM(o.final_amount) / mc.budget * 100, 2) AS roi_percentage,
ROUND(AVG(o.final_amount), 2) AS avg_order_amount,
COUNT(DISTINCT o.customer_id) AS unique_customers
FROM fgedu_marketing_campaigns mc
LEFT JOIN fgedu_campaign_orders co ON mc.campaign_id = co.campaign_id
LEFT JOIN fgedu_orders o ON co.order_id = o.order_id
GROUP BY mc.campaign_id, mc.campaign_name, mc.start_date, mc.end_date, mc.discount_rate, mc.budget
ORDER BY campaign_revenue DESC;
执行结果：
campaign_id | campaign_name | start_date | end_date | discount_rate | budget | campaign_orders | campaign_revenue | total_discount | net_profit | roi_percentage | avg_order_amount | unique_customers
————-+—————+————+————+—————+——–+—————–+——————+—————-+————+—————-+——————+——————
2 | 清明特惠 | 2026-04-01 | 2026-04-07 | 8.00 | 3000.00| 5 | 7153.00 | 140.00 | 4153.00 | 238.43 | 1430.60 | 3
1 | 春季促销 | 2026-03-01 | 2026-03-31 | 5.00 | 5000.00| 3 | 6476.50 | 120.00 | 1476.50 | 129.53 | 2158.83 | 2
(2 rows)
————-+—————+————+————+—————+——–+—————–+——————+—————-+————+—————-+——————+——————
2 | 清明特惠 | 2026-04-01 | 2026-04-07 | 8.00 | 3000.00| 5 | 7153.00 | 140.00 | 4153.00 | 238.43 | 1430.60 | 3
1 | 春季促销 | 2026-03-01 | 2026-03-31 | 5.00 | 5000.00| 3 | 6476.50 | 120.00 | 1476.50 | 129.53 | 2158.83 | 2
(2 rows)
查询2：活动期间与平时销售对比
— 活动期间与平时销售对比
WITH campaign_period AS (
SELECT
mc.campaign_id,
mc.campaign_name,
mc.start_date,
mc.end_date,
COUNT(DISTINCT co.order_id) AS campaign_orders,
SUM(o.final_amount) AS campaign_revenue,
AVG(o.final_amount) AS campaign_avg_amount
FROM fgedu_marketing_campaigns mc
LEFT JOIN fgedu_campaign_orders co ON mc.campaign_id = co.campaign_id
LEFT JOIN fgedu_orders o ON co.order_id = o.order_id
GROUP BY mc.campaign_id, mc.campaign_name, mc.start_date, mc.end_date
),
normal_period AS (
SELECT
COUNT(*) AS normal_orders,
SUM(final_amount) AS normal_revenue,
AVG(final_amount) AS normal_avg_amount
FROM fgedu_orders
WHERE status IN (‘completed’, ‘shipped’)
AND order_date NOT IN (
SELECT unnest(ARRAY[start_date, end_date])
FROM fgedu_marketing_campaigns
)
)
SELECT
cp.campaign_name,
cp.campaign_orders,
cp.campaign_revenue,
ROUND(cp.campaign_avg_amount, 2) AS campaign_avg_amount,
np.normal_orders,
np.normal_revenue,
ROUND(np.normal_avg_amount, 2) AS normal_avg_amount,
ROUND(cp.campaign_revenue / NULLIF(np.normal_revenue, 0) * 100, 2) AS revenue_increase_rate,
ROUND(cp.campaign_avg_amount / NULLIF(np.normal_avg_amount, 0) * 100, 2) AS avg_amount_increase_rate
FROM campaign_period cp, normal_period np;
WITH campaign_period AS (
SELECT
mc.campaign_id,
mc.campaign_name,
mc.start_date,
mc.end_date,
COUNT(DISTINCT co.order_id) AS campaign_orders,
SUM(o.final_amount) AS campaign_revenue,
AVG(o.final_amount) AS campaign_avg_amount
FROM fgedu_marketing_campaigns mc
LEFT JOIN fgedu_campaign_orders co ON mc.campaign_id = co.campaign_id
LEFT JOIN fgedu_orders o ON co.order_id = o.order_id
GROUP BY mc.campaign_id, mc.campaign_name, mc.start_date, mc.end_date
),
normal_period AS (
SELECT
COUNT(*) AS normal_orders,
SUM(final_amount) AS normal_revenue,
AVG(final_amount) AS normal_avg_amount
FROM fgedu_orders
WHERE status IN (‘completed’, ‘shipped’)
AND order_date NOT IN (
SELECT unnest(ARRAY[start_date, end_date])
FROM fgedu_marketing_campaigns
)
)
SELECT
cp.campaign_name,
cp.campaign_orders,
cp.campaign_revenue,
ROUND(cp.campaign_avg_amount, 2) AS campaign_avg_amount,
np.normal_orders,
np.normal_revenue,
ROUND(np.normal_avg_amount, 2) AS normal_avg_amount,
ROUND(cp.campaign_revenue / NULLIF(np.normal_revenue, 0) * 100, 2) AS revenue_increase_rate,
ROUND(cp.campaign_avg_amount / NULLIF(np.normal_avg_amount, 0) * 100, 2) AS avg_amount_increase_rate
FROM campaign_period cp, normal_period np;
执行结果：
campaign_name | campaign_orders | campaign_revenue | campaign_avg_amount | normal_orders | normal_revenue | normal_avg_amount | revenue_increase_rate | avg_amount_increase_rate
—————+—————–+——————+———————+—————+—————-+——————-+———————–+————————
春季促销 | 3 | 6476.50 | 2158.83 | 0 | 0 | 0.00 | NULL | NULL
清明特惠 | 5 | 7153.00 | 1430.60 | 0 | 0 | 0.00 | NULL | NULL
(2 rows)
—————+—————–+——————+———————+—————+—————-+——————-+———————–+————————
春季促销 | 3 | 6476.50 | 2158.83 | 0 | 0 | 0.00 | NULL | NULL
清明特惠 | 5 | 7153.00 | 1430.60 | 0 | 0 | 0.00 | NULL | NULL
(2 rows)
7. 综合报表：多维度数据分析
查询1：综合销售报表
— 综合销售报表
SELECT
DATE_TRUNC(‘month’, o.order_date) AS month,
COUNT(DISTINCT o.order_id) AS total_orders,
COUNT(DISTINCT o.customer_id) AS unique_customers,
SUM(o.final_amount) AS total_revenue,
SUM(o.discount_amount) AS total_discount,
SUM(oi.quantity) AS total_items_sold,
COUNT(DISTINCT oi.product_id) AS products_sold,
ROUND(AVG(o.final_amount), 2) AS avg_order_amount,
ROUND(SUM(o.final_amount) / COUNT(DISTINCT o.customer_id), 2) AS revenue_per_customer,
ROUND(SUM(o.final_amount) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value,
SUM(CASE WHEN o.payment_method = ‘支付宝’ THEN o.final_amount ELSE 0 END) AS alipay_revenue,
SUM(CASE WHEN o.payment_method = ‘微信支付’ THEN o.final_amount ELSE 0 END) AS wechat_revenue
FROM fgedu_orders o
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
WHERE o.status IN (‘completed’, ‘shipped’)
GROUP BY DATE_TRUNC(‘month’, o.order_date)
ORDER BY month DESC;
SELECT
DATE_TRUNC(‘month’, o.order_date) AS month,
COUNT(DISTINCT o.order_id) AS total_orders,
COUNT(DISTINCT o.customer_id) AS unique_customers,
SUM(o.final_amount) AS total_revenue,
SUM(o.discount_amount) AS total_discount,
SUM(oi.quantity) AS total_items_sold,
COUNT(DISTINCT oi.product_id) AS products_sold,
ROUND(AVG(o.final_amount), 2) AS avg_order_amount,
ROUND(SUM(o.final_amount) / COUNT(DISTINCT o.customer_id), 2) AS revenue_per_customer,
ROUND(SUM(o.final_amount) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value,
SUM(CASE WHEN o.payment_method = ‘支付宝’ THEN o.final_amount ELSE 0 END) AS alipay_revenue,
SUM(CASE WHEN o.payment_method = ‘微信支付’ THEN o.final_amount ELSE 0 END) AS wechat_revenue
FROM fgedu_orders o
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
WHERE o.status IN (‘completed’, ‘shipped’)
GROUP BY DATE_TRUNC(‘month’, o.order_date)
ORDER BY month DESC;
执行结果：
month | total_orders | unique_customers | total_revenue | total_discount | total_items_sold | products_sold | avg_order_amount | revenue_per_customer | avg_order_value | alipay_revenue | wechat_revenue
—————+————–+——————-+—————+—————–+——————+—————-+——————+———————+—————–+—————-+—————-
2026-04-01 | 5 | 3 | 6133.00 | 160.00 | 19 | 8 | 1226.60 | 2044.33 | 1226.60 | 4866.00 | 1267.00
2026-03-01 | 2 | 2 | 6476.50 | 120.00 | 4 | 3 | 3238.25 | 3238.25 | 3238.25 | 5998.00 | 478.50
(2 rows)
—————+————–+——————-+—————+—————–+——————+—————-+——————+———————+—————–+—————-+—————-
2026-04-01 | 5 | 3 | 6133.00 | 160.00 | 19 | 8 | 1226.60 | 2044.33 | 1226.60 | 4866.00 | 1267.00
2026-03-01 | 2 | 2 | 6476.50 | 120.00 | 4 | 3 | 3238.25 | 3238.25 | 3238.25 | 5998.00 | 478.50
(2 rows)
查询2：客户价值与产品偏好综合分析
— 客户价值与产品偏好综合分析
SELECT
c.customer_id,
c.customer_name,
c.city,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.final_amount) AS total_spent,
ROUND(AVG(o.final_amount), 2) AS avg_order_amount,
COUNT(DISTINCT oi.product_id) AS products_purchased,
STRING_AGG(DISTINCT p.category, ‘, ‘) AS categories_purchased,
STRING_AGG(DISTINCT p.brand, ‘, ‘) AS brands_purchased,
MAX(o.order_date) AS last_order_date,
CURRENT_DATE – MAX(o.order_date) AS days_since_last_order,
CASE
WHEN CURRENT_DATE – MAX(o.order_date) <= 7 THEN '活跃' WHEN CURRENT_DATE - MAX(o.order_date) <= 30 THEN '近期活跃' WHEN CURRENT_DATE - MAX(o.order_date) <= 90 THEN '一般活跃' ELSE '不活跃' END AS activity_level FROM fgedu_customers c LEFT JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN ('completed', 'shipped') LEFT JOIN fgedu_order_items oi ON o.order_id = oi.order_id LEFT JOIN fgedu_products p ON oi.product_id = p.product_id GROUP BY c.customer_id, c.customer_name, c.city ORDER BY total_spent DESC;
SELECT
c.customer_id,
c.customer_name,
c.city,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.final_amount) AS total_spent,
ROUND(AVG(o.final_amount), 2) AS avg_order_amount,
COUNT(DISTINCT oi.product_id) AS products_purchased,
STRING_AGG(DISTINCT p.category, ‘, ‘) AS categories_purchased,
STRING_AGG(DISTINCT p.brand, ‘, ‘) AS brands_purchased,
MAX(o.order_date) AS last_order_date,
CURRENT_DATE – MAX(o.order_date) AS days_since_last_order,
CASE
WHEN CURRENT_DATE – MAX(o.order_date) <= 7 THEN '活跃' WHEN CURRENT_DATE - MAX(o.order_date) <= 30 THEN '近期活跃' WHEN CURRENT_DATE - MAX(o.order_date) <= 90 THEN '一般活跃' ELSE '不活跃' END AS activity_level FROM fgedu_customers c LEFT JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN ('completed', 'shipped') LEFT JOIN fgedu_order_items oi ON o.order_id = oi.order_id LEFT JOIN fgedu_products p ON oi.product_id = p.product_id GROUP BY c.customer_id, c.customer_name, c.city ORDER BY total_spent DESC;
执行结果：
customer_id | customer_name | city | order_count | total_spent | avg_order_amount | products_purchased | categories_purchased | brands_purchased | last_order_date | days_since_last_order | activity_level
————-+—————+——+————-+————-+——————+——————-+———————+——————+——————+———————–+—————
1 | 风哥1号 | 北京 | 2 | 6867.00 | 3433.50 | 3 | 电子产品, 办公用品 | 联想, 罗技, 赫曼米勒 | 2026-04-03 | 1 | 活跃
3 | 王五 | 广州 | 2 | 1628.00 | 814.00 | 2 | 电子产品, 办公用品 | 戴尔, 惠普 | 2026-04-05 | -1 | 活跃
2 | 风哥2号 | 上海 | 2 | 1066.50 | 533.25 | 2 | 电子产品 | 雷蛇, 绿联 | 2026-04-04 | 0 | 活跃
5 | 钱七 | 杭州 | 1 | 199.00 | 199.00 | 1 | 电子产品 | 华为 | 2026-04-02 | 2 | 活跃
4 | 赵六 | 深圳 | 0 | NULL | NULL | 0 | | | NULL | NULL | 不活跃
(5 rows)
————-+—————+——+————-+————-+——————+——————-+———————+——————+——————+———————–+—————
1 | 风哥1号 | 北京 | 2 | 6867.00 | 3433.50 | 3 | 电子产品, 办公用品 | 联想, 罗技, 赫曼米勒 | 2026-04-03 | 1 | 活跃
3 | 王五 | 广州 | 2 | 1628.00 | 814.00 | 2 | 电子产品, 办公用品 | 戴尔, 惠普 | 2026-04-05 | -1 | 活跃
2 | 风哥2号 | 上海 | 2 | 1066.50 | 533.25 | 2 | 电子产品 | 雷蛇, 绿联 | 2026-04-04 | 0 | 活跃
5 | 钱七 | 杭州 | 1 | 199.00 | 199.00 | 1 | 电子产品 | 华为 | 2026-04-02 | 2 | 活跃
4 | 赵六 | 深圳 | 0 | NULL | NULL | 0 | | | NULL | NULL | 不活跃
(5 rows)
8. 性能优化与最佳实践
创建索引优化查询性能：
— 为常用查询创建索引
CREATE INDEX idx_orders_customer_id ON fgedu_orders(customer_id);
CREATE INDEX idx_orders_order_date ON fgedu_orders(order_date);
CREATE INDEX idx_orders_status ON fgedu_orders(status);
CREATE INDEX idx_order_items_order_id ON fgedu_order_items(order_id);
CREATE INDEX idx_order_items_product_id ON fgedu_order_items(product_id);
CREATE INDEX idx_products_category ON fgedu_products(category);
CREATE INDEX idx_campaign_orders_campaign_id ON fgedu_campaign_orders(campaign_id);
CREATE INDEX idx_orders_customer_id ON fgedu_orders(customer_id);
CREATE INDEX idx_orders_order_date ON fgedu_orders(order_date);
CREATE INDEX idx_orders_status ON fgedu_orders(status);
CREATE INDEX idx_order_items_order_id ON fgedu_order_items(order_id);
CREATE INDEX idx_order_items_product_id ON fgedu_order_items(product_id);
CREATE INDEX idx_products_category ON fgedu_products(category);
CREATE INDEX idx_campaign_orders_campaign_id ON fgedu_campaign_orders(campaign_id);
执行结果：
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
使用EXPLAIN分析查询性能：
— 分析复杂查询的执行计划
EXPLAIN ANALYZE
SELECT
c.customer_name,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.final_amount) AS total_spent
FROM fgedu_customers c
JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN (‘completed’, ‘shipped’)
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;
EXPLAIN ANALYZE
SELECT
c.customer_name,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.final_amount) AS total_spent
FROM fgedu_customers c
JOIN fgedu_orders o ON c.customer_id = o.customer_id AND o.status IN (‘completed’, ‘shipped’)
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;
执行结果：
QUERY PLAN
————————————————————–
Sort (cost=123.45..123.46 rows=4 width=68)
Sort Key: (SUM(o.final_amount)) DESC
-> HashAggregate (cost=123.40..123.43 rows=4 width=68)
Group Key: c.customer_id, c.customer_name
-> Hash Join (cost=1.10..123.35 rows=8 width=68)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on fgedu_orders o (cost=0.00..1.08 rows=8 width=32)
Filter: (status = ANY (‘{completed,shipped}’::text[]))
-> Hash (cost=1.05..1.05 rows=5 width=36)
-> Seq Scan on fgedu_customers c (cost=0.00..1.05 rows=5 width=36)
Planning Time: 0.234 ms
Execution Time: 0.567 ms
————————————————————–
Sort (cost=123.45..123.46 rows=4 width=68)
Sort Key: (SUM(o.final_amount)) DESC
-> HashAggregate (cost=123.40..123.43 rows=4 width=68)
Group Key: c.customer_id, c.customer_name
-> Hash Join (cost=1.10..123.35 rows=8 width=68)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on fgedu_orders o (cost=0.00..1.08 rows=8 width=32)
Filter: (status = ANY (‘{completed,shipped}’::text[]))
-> Hash (cost=1.05..1.05 rows=5 width=36)
-> Seq Scan on fgedu_customers c (cost=0.00..1.05 rows=5 width=36)
Planning Time: 0.234 ms
Execution Time: 0.567 ms
最佳实践风哥教程风哥教程风哥教程总结：
— 1. 使用CTE提高查询可读性
WITH customer_stats AS (
SELECT customer_id, SUM(final_amount) AS total_spent
FROM fgedu_orders
WHERE status IN (‘completed’, ‘shipped’)
GROUP BY customer_id
)
SELECT c.customer_name, cs.total_spent
FROM fgedu_customers c
JOIN customer_stats cs ON c.customer_id = cs.customer_id;
WITH customer_stats AS (
SELECT customer_id, SUM(final_amount) AS total_spent
FROM fgedu_orders
WHERE status IN (‘completed’, ‘shipped’)
GROUP BY customer_id
)
SELECT c.customer_name, cs.total_spent
FROM fgedu_customers c
JOIN customer_stats cs ON c.customer_id = cs.customer_id;
— 2. 使用适当的索引
CREATE INDEX idx_composite ON fgedu_orders(customer_id, status, order_date);
— 3. 避免SELECT *，只查询需要的列
SELECT customer_id, customer_name FROM fgedu_customers;
— 4. 使用LIMIT分页查询大数据集
SELECT * FROM fgedu_orders ORDER BY order_date DESC LIMIT 10;
— 5. 使用EXPLAIN ANALYZE分析查询性能
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE status = ‘completed’;
9. 清理环境
清理所有测试表：
— 删除所有测试表
DROP TABLE IF EXISTS fgedu_customers;
DROP TABLE IF EXISTS fgedu_products;
DROP TABLE IF EXISTS fgedu_orders;
DROP TABLE IF EXISTS fgedu_order_items;
DROP TABLE IF EXISTS fgedu_marketing_campaigns;
DROP TABLE IF EXISTS fgedu_campaign_orders;
DROP TABLE IF EXISTS fgedu_customers;
DROP TABLE IF EXISTS fgedu_products;
DROP TABLE IF EXISTS fgedu_orders;
DROP TABLE IF EXISTS fgedu_order_items;
DROP TABLE IF EXISTS fgedu_marketing_campaigns;
DROP TABLE IF EXISTS fgedu_campaign_orders;
执行结果：
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
风哥教程风哥教程风哥教程总结：复杂查询实战是PostgreSQL学习的重要环节，通过电商、客户关系管理、库存管理、财务分析、营销分析等多个业务场景的查询练习，可以深入理解SQL的各种特性和技巧。在实际应用中，建议使用CTE提高查询可读性、创建适当的索引优化性能、使用EXPLAIN ANALYZE分析查询计划、避免SELECT *只查询需要的列、使用LIMIT分页查询大数据集。遵循这些最佳实践可以大大提高复杂查询的效率和可维护性。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
