Oracle 11g 构建测试数据环境并执行多维度复杂查询
表结构设计
创建用于模拟电商系统的四张核心表，涵盖客户、产品、订单及明细信息。
-- 客户信息表
CREATE TABLE client_info (
client_id NUMBER PRIMARY KEY,
given_name VARCHAR2(50),
family_name VARCHAR2(50),
contact_email VARCHAR2(100),
phone_number VARCHAR2(20),
join_time DATE,
residence_city VARCHAR2(50),
credit_rating NUMBER
);
-- 产品目录表
CREATE TABLE product_catalog (
item_id NUMBER PRIMARY KEY,
item_title VARCHAR2(100),
type_category VARCHAR2(50),
unit_price NUMBER(10,2),
inventory_count NUMBER
);
-- 订单主表
CREATE TABLE order_master (
order_ref NUMBER PRIMARY KEY,
client_id NUMBER,
creation_date DATE,
order_total NUMBER(10,2),
status_code VARCHAR2(20),
CONSTRAINT fk_client FOREIGN KEY (client_id) REFERENCES client_info(client_id)
);
-- 订单详情表
CREATE TABLE order_items (
record_id NUMBER PRIMARY KEY,
order_ref NUMBER,
item_id NUMBER,
quantity_purchased NUMBER,
price_per_unit NUMBER(10,2),
discount_rate NUMBER(5,2),
CONSTRAINT fk_order FOREIGN KEY (order_ref) REFERENCES order_master(order_ref),
CONSTRAINT fk_product FOREIGN KEY (item_id) REFERENCES product_catalog(item_id)
);
批量生成测试数据（共10万条记录）
通过PL/SQL过程实现高效数据填充，覆盖客户、商品、订单与明细。
-- 插入1万个客户数据
BEGIN
FOR idx IN 1..10000 LOOP
INSERT INTO client_info VALUES (
idx,
'First' || MOD(idx, 100),
'Last' || MOD(idx, 200),
'user' || idx || '@test.com',
'1-' || LPAD(MOD(idx, 999), 3, '0') || '-' || LPAD(MOD(idx*7, 9999), 4, '0'),
SYSDATE - MOD(idx, 3650),
'City' || MOD(idx, 50),
300 + MOD(idx, 500)
);
END LOOP;
COMMIT;
END;
/
-- 插入100种商品数据
BEGIN
FOR prod_idx IN 1..100 LOOP
INSERT INTO product_catalog VALUES (
prod_idx,
'Item ' || prod_idx,
CASE
WHEN MOD(prod_idx, 4) = 0 THEN 'Electronics'
WHEN MOD(prod_idx, 4) = 1 THEN 'Apparel'
WHEN MOD(prod_idx, 4) = 2 THEN 'Books'
ELSE 'Household'
END,
10 + MOD(prod_idx, 990),
100 + MOD(prod_idx, 900)
);
END LOOP;
COMMIT;
END;
/
-- 生成3万笔订单
BEGIN
FOR ord_idx IN 1..30000 LOOP
INSERT INTO order_master VALUES (
ord_idx,
MOD(ord_idx, 10000) + 1,
SYSDATE - MOD(ord_idx, 730),
ROUND(DBMS_RANDOM.VALUE(10, 2000), 2),
CASE MOD(ord_idx, 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
ord_key NUMBER;
prod_key NUMBER;
qty_val NUMBER;
unit_cost NUMBER(10,2);
BEGIN
FOR rec_idx IN 1..100000 LOOP
ord_key := MOD(rec_idx, 30000) + 1;
SELECT item_id, unit_price INTO prod_key, unit_cost
FROM product_catalog
WHERE item_id = MOD(rec_idx, 100) + 1;
qty_val := MOD(rec_idx, 10) + 1;
INSERT INTO order_items VALUES (
rec_idx,
ord_key,
prod_key,
qty_val,
unit_cost,
CASE WHEN MOD(rec_idx, 5) = 0 THEN ROUND(DBMS_RANDOM.VALUE(0, 0.3), 2) ELSE 0 END
);
END LOOP;
COMMIT;
END;
/
性能优化：建立索引提升查询效率
针对高频查询字段添加索引以加速数据检索。
CREATE INDEX idx_city ON client_info(residence_city);
CREATE INDEX idx_join_date ON client_info(join_time);
CREATE INDEX idx_ord_client ON order_master(client_id);
CREATE INDEX idx_ord_date ON order_master(creation_date);
CREATE INDEX idx_item_order ON order_items(order_ref);
CREATE INDEX idx_item_product ON order_items(item_id);
CREATE INDEX idx_prod_type ON product_catalog(type_category);
复杂分析查询实战
查询一：按城市统计活跃客户与营收表现
SELECT
c.residence_city AS region,
COUNT(DISTINCT c.client_id) AS active_users,
COUNT(o.order_ref) AS total_orders,
SUM(o.order_total) AS gross_revenue,
ROUND(AVG(o.order_total), 2) AS avg_order_size,
MAX(o.order_total) AS highest_single_order
FROM client_info c
LEFT JOIN order_master o ON c.client_id = o.client_id
WHERE o.creation_date >= ADD_MONTHS(SYSDATE, -12)
GROUP BY c.residence_city
HAVING COUNT(o.order_ref) > 10
ORDER BY gross_revenue DESC;
查询二：品类内产品销售排名与占比分析
SELECT
p.type_category AS category,
p.item_title AS product_name,
SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate)) AS revenue_contribution,
RANK() OVER (PARTITION BY p.type_category ORDER BY SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate)) DESC) AS ranking,
ROUND(SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate)) * 100.0 /
SUM(SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate))) OVER (PARTITION BY p.type_category), 2) AS share_pct
FROM product_catalog p
JOIN order_items i ON p.item_id = i.item_id
JOIN order_master o ON i.order_ref = o.order_ref
WHERE o.creation_date >= ADD_MONTHS(SYSDATE, -6)
GROUP BY p.type_category, p.item_title
HAVING SUM(i.quantity_purchased) > 100
ORDER BY p.type_category, ranking;
查询三：高价值客户识别（基于CTE与条件判断）
WITH user_summary AS (
SELECT
c.client_id,
c.given_name || ' ' || c.family_name AS full_name,
c.residence_city,
c.credit_rating,
COUNT(o.order_ref) AS order_frequency,
SUM(o.order_total) AS lifetime_value,
MAX(o.creation_date) AS last_purchase
FROM client_info c
JOIN order_master o ON c.client_id = o.client_id
GROUP BY c.client_id, c.given_name, c.family_name, c.residence_city, c.credit_rating
),
avg_metrics AS (
SELECT
AVG(order_frequency) AS avg_orders,
AVG(lifetime_value) AS avg_ltv
FROM user_summary
)
SELECT
us.client_id,
us.full_name,
us.residence_city,
us.credit_rating,
us.order_frequency,
us.lifetime_value,
us.last_purchase,
CASE
WHEN us.order_frequency > avg.avg_orders AND us.lifetime_value > avg.avg_ltv
THEN 'Premium'
WHEN us.lifetime_value > avg.avg_ltv * 1.5
THEN 'High-Value'
ELSE 'Standard'
END AS customer_level
FROM user_summary us, avg_metrics avg
WHERE us.lifetime_value > 1000
ORDER BY us.lifetime_value DESC;
查询四：分品类月度销售趋势与增长率
SELECT
TO_CHAR(o.creation_date, 'YYYY-MM') AS month_period,
p.type_category AS category,
COUNT(DISTINCT o.order_ref) AS order_volume,
SUM(i.quantity_purchased) AS units_sold,
SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate)) AS monthly_sales,
LAG(SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate)))
OVER (PARTITION BY p.type_category ORDER BY TO_CHAR(o.creation_date, 'YYYY-MM')) AS prev_month_sales,
ROUND((SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate)) -
LAG(SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate)))
OVER (PARTITION BY p.type_category ORDER BY TO_CHAR(o.creation_date, 'YYYY-MM'))) * 100.0 /
LAG(SUM(i.quantity_purchased * i.price_per_unit * (1 - i.discount_rate)))
OVER (PARTITION BY p.type_category ORDER BY TO_CHAR(o.creation_date, 'YYYY-MM')), 2) AS growth_rate_percent
FROM order_master o
JOIN order_items i ON o.order_ref = i.order_ref
JOIN product_catalog p ON i.item_id = p.item_id
WHERE o.creation_date >= ADD_MONTHS(SYSDATE, -24)
GROUP BY TO_CHAR(o.creation_date, 'YYYY-MM'), p.type_category
ORDER BY month_period DESC, monthly_sales DESC;
执行性能调优建议
确保数据库统计信息准确，便于优化器选择最优执行路径。
-- 更新表统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'CLIENT_INFO');
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ORDER_MASTER');
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ORDER_ITEMS');
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'PRODUCT_CATALOG');
END;
/
-- 查看执行计划
EXPLAIN PLAN FOR
SELECT /* 输入你的复杂查询 */;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
