oracle 11g 创建几个表，插入10w数据，写一个复杂查询【测试专用】

Posted on 2025-09-01 11:02 大势趋007 阅读(6) 评论(0) 收藏举报

1. 创建表结构

-- 创建客户表
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    phone VARCHAR2(20),
    registration_date DATE,
    city VARCHAR2(50),
    credit_score NUMBER
);

-- 创建产品表
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    category VARCHAR2(50),
    price NUMBER(10,2),
    stock_quantity NUMBER
);

-- 创建订单表
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    total_amount NUMBER(10,2),
    status VARCHAR2(20),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 创建订单明细表
CREATE TABLE order_details (
    detail_id NUMBER PRIMARY KEY,
    order_id NUMBER,
    product_id NUMBER,
    quantity NUMBER,
    unit_price NUMBER(10,2),
    discount NUMBER(5,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

2. 插入10万条测试数据

-- 插入客户数据 (1万客户)
BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO customers VALUES (
            i,
            'FirstName' || MOD(i, 100),
            'LastName' || MOD(i, 200),
            'email' || i || '@example.com',
            '1-' || LPAD(MOD(i, 999), 3, '0') || '-' || LPAD(MOD(i*7, 9999), 4, '0'),
            SYSDATE - MOD(i, 3650),
            'City' || MOD(i, 50),
            300 + MOD(i, 500)
        );
    END LOOP;
    COMMIT;
END;
/

-- 插入产品数据 (100种产品)
BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO products VALUES (
            i,
            'Product ' || i,
            CASE 
                WHEN MOD(i, 4) = 0 THEN 'Electronics'
                WHEN MOD(i, 4) = 1 THEN 'Clothing'
                WHEN MOD(i, 4) = 2 THEN 'Books'
                ELSE 'Home'
            END,
            10 + MOD(i, 990),
            100 + MOD(i, 900)
        );
    END LOOP;
    COMMIT;
END;
/

-- 插入订单数据 (3万订单)
BEGIN
    FOR i IN 1..30000 LOOP
        INSERT INTO orders VALUES (
            i,
            MOD(i, 10000) + 1,
            SYSDATE - MOD(i, 730),
            ROUND(DBMS_RANDOM.VALUE(10, 2000), 2),
            CASE MOD(i, 10)
                WHEN 0 THEN 'Pending'
                WHEN 1 THEN 'Processing'
                WHEN 2 THEN 'Shipped'
                WHEN 3 THEN 'Delivered'
                WHEN 4 THEN 'Cancelled'
                ELSE 'Completed'
            END
        );
    END LOOP;
    COMMIT;
END;
/

-- 插入订单明细数据 (10万条明细)
DECLARE
    v_order_id NUMBER;
    v_product_id NUMBER;
    v_quantity NUMBER;
    v_unit_price NUMBER(10,2);
BEGIN
    FOR i IN 1..100000 LOOP
        v_order_id := MOD(i, 30000) + 1;
        
        SELECT product_id, price INTO v_product_id, v_unit_price
        FROM products 
        WHERE product_id = MOD(i, 100) + 1;
        
        v_quantity := MOD(i, 10) + 1;
        
        INSERT INTO order_details VALUES (
            i,
            v_order_id,
            v_product_id,
            v_quantity,
            v_unit_price,
            CASE WHEN MOD(i, 5) = 0 THEN ROUND(DBMS_RANDOM.VALUE(0, 0.3), 2) ELSE 0 END
        );
    END LOOP;
    COMMIT;
END;
/

3. 创建索引优化查询性能

-- 创建索引
CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_regdate ON customers(registration_date);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_order_details_order ON order_details(order_id);
CREATE INDEX idx_order_details_product ON order_details(product_id);
CREATE INDEX idx_products_category ON products(category);

4. 复杂查询示例

查询1: 分析各城市客户消费情况

SELECT 
    c.city,
    COUNT(DISTINCT c.customer_id) as total_customers,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_revenue,
    ROUND(AVG(o.total_amount), 2) as avg_order_value,
    MAX(o.total_amount) as max_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= ADD_MONTHS(SYSDATE, -12)
GROUP BY c.city
HAVING COUNT(o.order_id) > 10
ORDER BY total_revenue DESC;

查询2: 产品类别销售分析（使用窗口函数）

SELECT 
    p.category,
    p.product_name,
    SUM(od.quantity * od.unit_price * (1 - od.discount)) as total_sales,
    RANK() OVER (PARTITION BY p.category ORDER BY SUM(od.quantity * od.unit_price * (1 - od.discount)) DESC) as sales_rank,
    ROUND(SUM(od.quantity * od.unit_price * (1 - od.discount)) * 100.0 / 
          SUM(SUM(od.quantity * od.unit_price * (1 - od.discount))) OVER (PARTITION BY p.category), 2) as category_percentage
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
WHERE o.order_date >= ADD_MONTHS(SYSDATE, -6)
GROUP BY p.category, p.product_name
HAVING SUM(od.quantity) > 100
ORDER BY p.category, sales_rank;

查询3: 高价值客户识别（使用子查询和CTE）

WITH customer_stats AS (
    SELECT 
        c.customer_id,
        c.first_name || ' ' || c.last_name as customer_name,
        c.city,
        c.credit_score,
        COUNT(o.order_id) as order_count,
        SUM(o.total_amount) as lifetime_value,
        MAX(o.order_date) as last_order_date
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name, c.city, c.credit_score
),
avg_stats AS (
    SELECT 
        AVG(order_count) as avg_orders,
        AVG(lifetime_value) as avg_lifetime_value
    FROM customer_stats
)
SELECT 
    cs.customer_id,
    cs.customer_name,
    cs.city,
    cs.credit_score,
    cs.order_count,
    cs.lifetime_value,
    cs.last_order_date,
    CASE 
        WHEN cs.order_count > avg.avg_orders AND cs.lifetime_value > avg.avg_lifetime_value 
        THEN 'VIP Customer'
        WHEN cs.lifetime_value > avg.avg_lifetime_value * 1.5 
        THEN 'High Value'
        ELSE 'Regular'
    END as customer_segment
FROM customer_stats cs, avg_stats avg
WHERE cs.lifetime_value > 1000
ORDER BY cs.lifetime_value DESC;

查询4: 月度销售趋势分析

SELECT 
    TO_CHAR(o.order_date, 'YYYY-MM') as sales_month,
    p.category,
    COUNT(DISTINCT o.order_id) as order_count,
    SUM(od.quantity) as total_quantity,
    SUM(od.quantity * od.unit_price * (1 - od.discount)) as total_sales,
    LAG(SUM(od.quantity * od.unit_price * (1 - od.discount))) 
        OVER (PARTITION BY p.category ORDER BY TO_CHAR(o.order_date, 'YYYY-MM')) as prev_month_sales,
    ROUND((SUM(od.quantity * od.unit_price * (1 - od.discount)) - 
          LAG(SUM(od.quantity * od.unit_price * (1 - od.discount))) 
          OVER (PARTITION BY p.category ORDER BY TO_CHAR(o.order_date, 'YYYY-MM'))) * 100.0 /
          LAG(SUM(od.quantity * od.unit_price * (1 - od.discount))) 
          OVER (PARTITION BY p.category ORDER BY TO_CHAR(o.order_date, 'YYYY-MM')), 2) as growth_percentage
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE o.order_date >= ADD_MONTHS(SYSDATE, -24)
GROUP BY TO_CHAR(o.order_date, 'YYYY-MM'), p.category
ORDER BY sales_month DESC, total_sales DESC;

5. 性能优化建议

-- 收集统计信息
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'CUSTOMERS');
    DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ORDERS');
    DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ORDER_DETAILS');
    DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'PRODUCTS');
END;
/

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT /* 你的复杂查询 */;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

刷新页面返回顶部