Skip to content

Sales Analytics Dashboard 查询

中等 约 25 分钟

你将构建什么

集合的 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. 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. 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. 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. 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. 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.

相关资源

相关课程

相关术语

相关术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误

← 返回主题