Sales Analytics Dashboard 查询
你将构建什么
集合的 SQL 查询那个 power sales analytics dashboard: 总计 revenue, 顶部 products, sales 通过分类, and best customers.
学习目标
- Use GROUP BY 到 aggregate sales 通过 product and 分类
- Apply HAVING 到筛选分组通过 aggregate 值
- Compute revenue, 平均, and rankings
- 组合连接使用 aggregations for rich reports
前置要求
- SELECT and WHERE 基础
- JOIN 基本
- Aggregate 函数概念
步骤
- 1
创建 Schema
创建 products and sales 表. Products 有分类; sales 记录每个 purchase 使用数量 and price.
代码CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL ); CREATE TABLE sales ( id SERIAL PRIMARY KEY, product_id INTEGER REFERENCES products(id), quantity INTEGER NOT NULL, price NUMERIC(10,2) NOT NULL, sold_at TIMESTAMP DEFAULT NOW() );预期结果
Two 表是 创建使用外键关系.
- 2
Insert sample 数据
Insert products in 不同分类 and 几个 sales 记录.
代码INSERT INTO products (name, category) VALUES ('Widget', 'Tools'), ('Gadget', 'Tools'), ('Book', 'Media'), ('Pen', 'Office'); INSERT INTO sales (product_id, quantity, price) VALUES (1, 5, 9.99), (1, 3, 9.99), (2, 2, 19.99), (3, 10, 12.50), (4, 8, 1.99), (4, 4, 1.99);预期结果
Four products and six sales 记录是 inserted.
- 3
Revenue 通过分类
编写查询那个 computes 总计 revenue 每 product 分类 using GROUP BY and SUM.
代码SELECT p.category, COUNT(*) AS num_sales, SUM(s.quantity * s.price) AS revenue FROM sales s INNER JOIN products p ON s.product_id = p.id GROUP BY p.category ORDER BY revenue DESC;预期结果
summary showing 每个分类's sales 数量 and 总计 revenue.
- 4
顶部 products 使用 HAVING
查找 products 那个有 sold 更多 than 5 总计单元, using HAVING 到筛选之后 aggregation.
代码SELECT p.name, SUM(s.quantity) AS total_units, SUM(s.quantity * s.price) AS revenue FROM sales s INNER JOIN products p ON s.product_id = p.id GROUP BY p.name HAVING SUM(s.quantity) > 5 ORDER BY revenue DESC;预期结果
仅 products 使用更多 than 5 单元 sold 是 returned.
- 5
添加索引优化查询
为频繁查询的列创建索引,提升查询性能。索引是数据库性能优化的首要手段,但需注意写操作的开销。
代码-- Create indexes on frequently filtered columns CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at DESC); -- Verify the index is used EXPLAIN SELECT * FROM orders WHERE user_id = 42;预期结果
EXPLAIN 输出显示使用 idx_orders_user_id 索引扫描而非全表扫描。
自己动手试试
你学到了什么
你构建真实 analytics 查询组合连接, GROUP BY, HAVING, and 多个 aggregate 函数 — 相同模式 used in business intelligence dashboards.
相关资源
相关课程
- GROUP BY
学习分组 and aggregation.
- Aggregations
学习 aggregate 函数.
相关术语
- Aggregation
Summarizing 数据.
- GROUP BY
分组行.
- HAVING
筛选分组.
相关术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
- Aggregation
Summarizing 数据.
- GROUP BY
分组行.
- HAVING
筛选分组.
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。