PostgreSQL教程FG019-PG入门实战：简单数据统计与查询

本文档风哥主要介绍PostgreSQL教程019相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

内容简介：本文介绍PostgreSQL数据库的简单数据统计与查询方法，通过电商系统的实际案例演示如何使用PostgreSQL进行数据统计分析。风哥教程参考PostgreSQL官方文档Queries、Functions and Operators等内容，学习基本的统计函数、分组查询和聚合分析方法，帮助读者掌握PostgreSQL数据统计分析的基础知识和实战技能。

Part01-基础概念与理论知识

1.1 数据统计与分析概念

数据统计与分析是指对数据进行收集、整理、计算和解释，以提取有用信息和得出结论的过程。在数据库中，数据统计与分析通常通过SQL查询实现，使用聚合函数、分组查询等技术对数据进行汇总和分析。更多视频教程www.fgedu.net.cn

1.2 PostgreSQL常用统计函数

PostgreSQL提供了丰富的统计函数，用于对数据进行各种统计计算，主要包括：

COUNT()：计算记录数
SUM()：计算数值总和
AVG()：计算数值平均值
MIN()：计算最小值
MAX()：计算最大值
STDDEV()：计算标准差
VARIANCE()：计算方差

1.3 简单数据分析方法

简单数据分析方法主要包括：

描述性统计：对数据的基本特征进行描述，如平均值、中位数、标准差等
分组分析：按照某个或多个维度对数据进行分组，然后对每组数据进行统计
趋势分析：分析数据随时间的变化趋势
关联分析：分析不同数据之间的关联关系

Part02-生产环境规划与建议

2.1 数据建模建议

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 设计合理的数据模型，确保数据的完整性和一致性
– 使用适当的数据类型，提高查询性能和存储效率
– 为经常用于分组和过滤的字段创建索引
– 考虑数据的增长趋势，预留足够的存储空间
– 定期清理不需要的历史数据，保持数据库性能

2.2 查询性能优化

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 尽量减少查询返回的数据量，只查询需要的字段
– 使用适当的过滤条件，减少需要处理的数据量
– 合理使用索引，提高查询速度
– 避免在WHERE子句中使用函数，影响索引使用
– 考虑使用物化视图，提高复杂查询的性能

2.3 数据分析最佳实践

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 明确分析目标，确定需要分析的指标
– 选择合适的分析方法，确保分析结果的准确性
– 验证分析结果的合理性，避免得出错误结论
– 定期更新分析模型，适应业务需求的变化
– 可视化分析结果，提高数据的可读性和理解性

Part03-生产环境项目实施方案

3.1 数据准备

在进行数据分析之前，需要准备合适的测试数据。我们将创建一个简单的电商系统数据集，包括产品表、订单表和订单明细表。

$ psql -d fgedudb -U fgedu
Password for fgedu fgedu:
psql (18.3)
Type “help” for help.

fgedudb=>

— 创建产品表
CREATE TABLE fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price NUMERIC(10, 2) NOT NULL,
stock INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fgedu_– 创建订单表
CREATE TABLE fgedu_orders (
order_id SERIAL PRIMARY KEY,
fgedu_id INTEGER NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount NUMERIC(10, 2) NOT NULL,
order_status VARCHAR(20) DEFAULT ‘pending’
);

CREATE TABLE fgedu_– 创建订单明细表
CREATE TABLE fgedu_order_items (
item_id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES fgedu_orders(order_id),
product_id INTEGER NOT NULL REFERENCES fgedu_products(product_id),
quantity INTEGER NOT NULL,
unit_price NUMERIC(10, 2) NOT NULL
);

CREATE TABLE fgedu_– 插入产品数据
INSERT INTO fgedu_products (product_name, category, price, stock)
VALUES (‘PostgreSQL教程’, ‘书籍’, 99.99, 100),
(‘Python编程’, ‘书籍’, 89.99, 150),
(‘Java开发’, ‘书籍’, 109.99, 200),
(‘Linux运维’, ‘书籍’, 119.99, 120),
(‘MySQL数据库’, ‘书籍’, 94.99, 180),
(‘PostgreSQL高级开发’, ‘书籍’, 129.99, 80),
(‘Python数据分析’, ‘书籍’, 104.99, 130),
(‘Java架构设计’, ‘书籍’, 139.99, 90),
(‘Linux性能调优’, ‘书籍’, 124.99, 110),
(‘MySQL优化’, ‘书籍’, 114.99, 160);

INSERT 0 10

— 插入订单数据
INSERT INTO fgedu_orders (fgedu_id, order_date, total_amount, order_status)
VALUES (1, ‘2026-03-01 10:30:00’, 199.98, ‘completed’),
(2, ‘2026-03-02 11:45:00’, 209.98, ‘completed’),
(3, ‘2026-03-03 14:20:00’, 189.98, ‘completed’),
(1, ‘2026-03-04 09:15:00’, 249.97, ‘completed’),
(4, ‘2026-03-05 16:50:00’, 99.99, ‘completed’),
(2, ‘2026-03-06 13:30:00’, 329.96, ‘completed’),
(5, ‘2026-03-07 10:00:00’, 109.99, ‘completed’),
(3, ‘2026-03-08 15:40:00’, 229.98, ‘completed’),
(6, ‘2026-03-09 11:10:00’, 119.99, ‘completed’),
(4, ‘2026-03-10 14:50:00’, 199.98, ‘completed’),
(7, ‘2026-03-11 09:30:00’, 139.99, ‘completed’),
(8, ‘2026-03-12 16:20:00’, 124.99, ‘completed’),
(5, ‘2026-03-13 10:45:00’, 114.99, ‘completed’),
(6, ‘2026-03-14 13:15:00’, 259.97, ‘completed’),
(7, ‘2026-03-15 15:30:00’, 94.99, ‘completed’);

INSERT 0 15

— 插入订单明细数据
INSERT INTO fgedu_order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 1, 99.99),
(1, 2, 1, 89.99),
(2, 3, 1, 109.99),
(2, 5, 1, 94.99),
(3, 2, 1, 89.99),
(3, 5, 1, 94.99),
(4, 1, 1, 99.99),
(4, 3, 1, 109.99),
(4, 6, 1, 129.99),
(5, 1, 1, 99.99),
(6, 3, 1, 109.99),
(6, 7, 1, 104.99),
(6, 8, 1, 139.99),
(7, 3, 1, 109.99),
(8, 4, 1, 119.99),
(8, 9, 1, 124.99),
(9, 4, 1, 119.99),
(10, 1, 1, 99.99),
(10, 2, 1, 89.99),
(11, 8, 1, 139.99),
(12, 9, 1, 124.99),
(13, 10, 1, 114.99),
(14, 6, 1, 129.99),
(14, 7, 1, 104.99),
(14, 2, 1, 89.99),
(15, 5, 1, 94.99);

INSERT 0 26

3.2 基础统计查询

基础统计查询是指对数据进行基本的统计计算，如计数、求和、平均值等。学习交流加群风哥微信: itpux-com

— 计算产品总数
SELECT COUNT(*) AS total_products FROM fgedu_products;
total_products
—————-
10
(1 row)

— 计算产品平均价格
SELECT AVG(price) AS avg_price FROM fgedu_products;
avg_price
——————–
112.9900000000000000
(1 row)

— 计算产品最高价格和最低价格
SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM fgedu_products;
max_price | min_price
———–+———–
139.99 | 89.99
(1 row)

— 计算产品总库存
SELECT SUM(stock) AS total_stock FROM fgedu_products;
total_stock
————-+
1320
(1 row)

— 计算订单总数和销售总额
SELECT COUNT(*) AS total_orders, SUM(total_amount) AS total_fgfgfgfgsales FROM fgedu_orders;
total_orders | total_fgfgfgfgsales
————–+————–+
15 | 2654.83
(1 row)

— 计算平均订单金额
SELECT AVG(total_amount) AS avg_order_amount FROM fgedu_orders;
avg_order_amount
———————–+
176.9886666666666667
(1 row)

3.3 分组分析

分组分析是指按照某个或多个维度对数据进行分组，然后对每组数据进行统计计算。

— 按产品类别分组，计算每个类别的产品数量和平均价格
SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM fgedu_products
GROUP BY category;
category | product_count | avg_price
———-+—————+——————–
书籍 | 10 | 112.9900000000000000
(1 row)

— 按订单状态分组，计算每个状态的订单数量和销售总额
SELECT order_status, COUNT(*) AS order_count, SUM(total_amount) AS total_fgfgfgfgsales
FROM fgedu_orders
GROUP BY order_status;
order_status | order_count | total_fgfgfgfgsales
————–+————-+————–+
completed | 15 | 2654.83
(1 row)

— 按用户分组，计算每个用户的订单数量和消费总额
SELECT fgedu_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
FROM fgedu_orders
GROUP BY fgedu_id
ORDER BY total_spent DESC;
fgedu_id | order_count | total_spent
———+————-+————-+
2 | 2 | 539.94
6 | 2 | 379.96
1 | 2 | 449.95
3 | 2 | 419.96
4 | 2 | 299.97
7 | 2 | 234.98
5 | 2 | 224.98
8 | 1 | 124.99
(8 rows)

— 按产品分组，计算每个产品的销售数量和销售总额
SELECT p.product_id, p.product_name, COUNT(oi.quantity) AS fgfgfgfgsales_count,
SUM(oi.quantity * oi.unit_price) AS fgfgfgfgsales_amount
FROM fgedu_products p
LEFT JOIN fgedu_order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY fgfgfgfgsales_amount DESC;
product_id | product_name | fgfgfgfgsales_count | fgfgfgfgsales_amount
————+——————+————-+————–
3 | Java开发 | 4 | 439.96
1 | PostgreSQL教程 | 4 | 399.96
2 | Python编程 | 4 | 359.96
8 | Java架构设计 | 2 | 279.98
4 | Linux运维 | 2 | 239.98
6 | PostgreSQL高级开发 | 2 | 259.98
7 | Python数据分析 | 2 | 209.98
9 | Linux性能调优 | 2 | 249.98
5 | MySQL数据库 | 3 | 284.97
10 | MySQL优化 | 1 | 114.99
(10 rows)

Part04-生产案例与实战讲解

4.1 产品销售分析

产品销售分析是电商系统中常见的数据分析需求，可以帮助了解产品的销售情况和市场表现。

— 分析每个产品的销售情况
SELECT
p.product_id,
p.product_name,
p.price AS unit_price,
COALESCE(SUM(oi.quantity), 0) AS total_sold,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_fgfgfgfgsales,
p.stock,
p.stock – COALESCE(SUM(oi.quantity), 0) AS current_stock
FROM fgedu_products p
LEFT JOIN fgedu_order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.price, p.stock
ORDER BY total_fgfgfgfgsales DESC;
product_id | product_name | unit_price | total_sold | total_fgfgfgfgsales | stock | current_stock
————+——————+————+————+————–+——-+—————+
3 | Java开发 | 109.99 | 4 | 439.96 | 200 | 196
5 | MySQL数据库 | 94.99 | 3 | 284.97 | 180 | 177
6 | PostgreSQL高级开发 | 129.99 | 2 | 259.98 | 80 | 78
9 | Linux性能调优 | 124.99 | 2 | 249.98 | 110 | 108
4 | Linux运维 | 119.99 | 2 | 239.98 | 120 | 118
8 | Java架构设计 | 139.99 | 2 | 279.98 | 90 | 88
7 | Python数据分析 | 104.99 | 2 | 209.98 | 130 | 128
1 | PostgreSQL教程 | 99.99 | 4 | 399.96 | 100 | 96
2 | Python编程 | 89.99 | 4 | 359.96 | 150 | 146
10 | MySQL优化 | 114.99 | 1 | 114.99 | 160 | 159
(10 rows)

4.2 订单数据分析

订单数据分析可以帮助了解订单的分布情况、销售趋势和用户消费行为。学习交流加群风哥QQ113257174

— 按日期分析订单数量和销售总额
SELECT
DATE(order_date) AS order_day,
COUNT(*) AS order_count,
SUM(total_amount) AS daily_fgfgfgfgsales
FROM fgedu_orders
GROUP BY DATE(order_date)
ORDER BY order_day;
order_day | order_count | daily_fgfgfgfgsales
————+————-+————–
2026-03-01 | 1 | 199.98
2026-03-02 | 1 | 209.98
2026-03-03 | 1 | 189.98
2026-03-04 | 1 | 249.97
2026-03-05 | 1 | 99.99
2026-03-06 | 1 | 329.96
2026-03-07 | 1 | 109.99
2026-03-08 | 1 | 229.98
2026-03-09 | 1 | 119.99
2026-03-10 | 1 | 199.98
2026-03-11 | 1 | 139.99
2026-03-12 | 1 | 124.99
2026-03-13 | 1 | 114.99
2026-03-14 | 1 | 259.97
2026-03-15 | 1 | 94.99
(15 rows)

— 分析订单金额分布
SELECT
CASE
WHEN total_amount < 100 THEN '0-100' WHEN total_amount < 200 THEN '100-200' WHEN total_amount < 300 THEN '200-300' ELSE '300+' END AS amount_range, COUNT(*) AS order_count, SUM(total_amount) AS total_fgfgfgfgsales FROM fgedu_orders GROUP BY amount_range ORDER BY amount_range; amount_range | order_count | total_fgfgfgfgsales --------------+-------------+-------------- 0-100 | 2 | 194.98 100-200 | 7 | 1144.91 200-300 | 5 | 1044.91 300+ | 1 | 329.96 (4 rows) -- 分析订单平均商品数量 SELECT AVG(item_count) AS avg_items_per_order FROM ( SELECT order_id, COUNT(*) AS item_count FROM fgedu_order_items GROUP BY order_id ) AS order_item_counts; avg_items_per_order -------------------- 1.7333333333333333 (1 row)

4.3 用户行为分析

用户行为分析可以帮助了解用户的消费习惯和行为特征，为营销策略提供数据支持。

— 分析用户的首次购买时间和消费总额
SELECT
fgedu_id,
MIN(order_date) AS first_purchase_date,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_spent,
AVG(total_amount) AS avg_order_amount
FROM fgedu_orders
GROUP BY fgedu_id
ORDER BY first_purchase_date;
fgedu_id | first_purchase_date | total_orders | total_spent | avg_order_amount
———+———————+————-+————-+——————–+
1 | 2026-03-01 10:30:00 | 2 | 449.95 | 224.9750000000000000
2 | 2026-03-02 11:45:00 | 2 | 539.94 | 269.9700000000000000
3 | 2026-03-03 14:20:00 | 2 | 419.96 | 209.9800000000000000
4 | 2026-03-05 16:50:00 | 2 | 299.97 | 149.9850000000000000
5 | 2026-03-07 10:00:00 | 2 | 224.98 | 112.4900000000000000
6 | 2026-03-09 11:10:00 | 2 | 379.96 | 189.9800000000000000
7 | 2026-03-11 09:30:00 | 2 | 234.98 | 117.4900000000000000
8 | 2026-03-12 16:20:00 | 1 | 124.99 | 124.9900000000000000
(8 rows)

— 分析用户的购买频率
SELECT
purchase_interval,
COUNT(*) AS fgedu_count
FROM (
SELECT
fgedu_id,
CASE
WHEN MAX(order_date) – MIN(order_date) < interval '7 days' THEN '1周内' WHEN MAX(order_date) - MIN(order_date) < interval '30 days' THEN '1个月内' ELSE '1个月以上' END AS purchase_interval FROM fgedu_orders GROUP BY fgedu_id HAVING COUNT(*) >= 2
) AS fgedu_intervals
GROUP BY purchase_interval;
purchase_interval | fgedu_count
——————-+————+
1个月内 | 7
(1 row)

— 分析用户的购买偏好（最常购买的产品类别）
SELECT
o.fgedu_id,
p.category,
COUNT(oi.item_id) AS purchase_count
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
GROUP BY o.fgedu_id, p.category
ORDER BY o.fgedu_id, purchase_count DESC;
fgedu_id | category | purchase_count
———+———-+—————-+
1 | 书籍 | 4
2 | 书籍 | 4
3 | 书籍 | 4
4 | 书籍 | 3
5 | 书籍 | 2
6 | 书籍 | 4
7 | 书籍 | 2
8 | 书籍 | 1
(8 rows)

Part05-风哥经验总结与分享

风哥提示：数据统计与分析是数据库应用的重要组成部分，通过对数据的分析可以提取有价值的信息，为业务决策提供支持。PostgreSQL提供了丰富的统计函数和查询功能，可以满足各种数据分析需求。

1. 数据准备是数据分析的基础，需要确保数据的完整性、一致性和准确性。

2. 选择合适的统计函数和查询方法，根据分析目标进行灵活组合。

3. 分组分析是数据分析的常用方法，可以从不同维度了解数据的分布情况。

4. 合理使用聚合函数和窗口函数，可以实现复杂的数据分析需求。

5. 对于大数据量的分析，需要考虑查询性能，合理使用索引和优化查询语句。

6. 数据分析结果需要结合业务实际进行解释和应用，避免单纯的数据堆砌。

7. 定期进行数据分析，跟踪业务变化趋势，及时调整业务策略。

8. 可视化分析结果，提高数据的可读性和沟通效率。更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

9. 学习使用高级分析功能，如窗口函数、CTE（公共表表达式）等，可以提高数据分析的效率和灵活性。

10. 结合业务知识进行数据分析，才能得出有价值的结论和建议。from PostgreSQL:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html