视图与综合实战

视图是什么

视图(View)是一段保存在数据库中的命名查询.它不存储数据,每次查询视图时实际执行的是底层 SQL.

类比:视图像是给一段复杂的 SQL 取了个"快捷方式". 可以像查普通表一样 SELECT * FROM view_name,背后自动展开为完整查询.

使用视图的场景:

  • 简化复杂查询:把多表 JOIN + 聚合封装起来,对调用者隐藏复杂度
  • 统一业务口径:报表用的"月活用户","有效订单"等定义写在视图中,团队共用
  • 权限隔离:只暴露部分列给特定角色,隐藏敏感字段

创建与使用普通视图

-- 创建:封装"用户消费汇总"
CREATE VIEW v_user_spending AS
SELECT
  u.id AS user_id,
  u.username,
  u.city,
  u.is_vip,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent,
  ROUND(COALESCE(AVG(o.total), 0), 2) AS avg_order
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'done'
GROUP BY u.id, u.username, u.city, u.is_vip;

-- 使用:像普通表一样查
SELECT * FROM v_user_spending WHERE total_spent > 5000 ORDER BY total_spent DESC;

-- 视图上还可以继续 JOIN / WHERE / 聚合
SELECT city, SUM(total_spent) AS city_revenue
FROM v_user_spending
GROUP BY city
ORDER BY city_revenue DESC;
-- 创建:商品销售排行视图
CREATE VIEW v_product_sales AS
SELECT
  p.id AS product_id,
  p.name AS product_name,
  c.name AS category,
  SUM(oi.quantity) AS total_sold,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  COUNT(DISTINCT o.user_id) AS buyer_count
FROM products p
JOIN categories c ON c.id = p.category_id
LEFT JOIN order_items oi ON oi.product_id = p.id
LEFT JOIN orders o ON o.id = oi.order_id
GROUP BY p.id, p.name, c.name;

-- 查询:最畅销的 5 个商品
SELECT * FROM v_product_sales ORDER BY total_sold DESC NULLS LAST LIMIT 5;
-- 修改视图定义(CREATE OR REPLACE)
CREATE OR REPLACE VIEW v_user_spending AS
SELECT
  u.id AS user_id,
  u.username,
  u.city,
  u.is_vip,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent,
  ROUND(COALESCE(AVG(o.total), 0), 2) AS avg_order,
  MAX(o.created_at) AS last_order_at  -- 新增列
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'done'
GROUP BY u.id, u.username, u.city, u.is_vip;

-- 删除视图
DROP VIEW IF EXISTS v_user_spending;

CREATE OR REPLACE 的限制

只能新增列,不能删除或改变已有列的类型/名称. 如果需要修改已有列,必须先 DROP 再 CREATE.

可更新视图

如果视图基于单表,无聚合,无 DISTINCT / GROUP BY / UNION,则默认可以对视图执行 INSERT / UPDATE / DELETE,操作会穿透到底层表:

-- 简单视图:活跃 VIP 用户
CREATE VIEW v_active_vips AS
SELECT id, username, email, balance, city
FROM users
WHERE is_vip = true AND balance > 0;

-- 可以直接通过视图更新
UPDATE v_active_vips SET balance = balance + 100 WHERE username = 'alice';

-- 验证
SELECT * FROM users WHERE username = 'alice';  -- balance 已增加

WITH CHECK OPTION

加上 WITH CHECK OPTION 后,通过视图的 INSERT/UPDATE 必须满足视图的 WHERE 条件,否则拒绝操作. 防止通过视图插入"视图看不到"的数据.

CREATE VIEW v_active_vips AS
SELECT id, username, email, balance, city
FROM users
WHERE is_vip = true AND balance > 0
WITH CHECK OPTION;

-- 试图把余额设为 0 → 被拒绝(违反 balance > 0)
UPDATE v_active_vips SET balance = 0 WHERE username = 'alice';
-- ERROR: new row violates check option for view "v_active_vips"

物化视图

物化视图(Materialized View)与普通视图的关键区别:它真实存储数据.查询时直接读取已计算好的结果,不再实时执行底层 SQL.

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_monthly_report AS
SELECT
  TO_CHAR(o.created_at, 'YYYY-MM') AS month,
  COUNT(*) AS order_count,
  SUM(o.total) AS revenue,
  COUNT(DISTINCT o.user_id) AS active_users
FROM orders o
WHERE o.status = 'done'
GROUP BY TO_CHAR(o.created_at, 'YYYY-MM')
ORDER BY month;

-- 查询(直接读存储的数据,极快)
SELECT * FROM mv_monthly_report;

-- 刷新(底层数据变化后需要手动刷新)
REFRESH MATERIALIZED VIEW mv_monthly_report;

-- 并发刷新(不阻塞正在查询的连接,需要先创建唯一索引)
CREATE UNIQUE INDEX ON mv_monthly_report (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_report;

物化视图的数据一致性

物化视图是快照--底层表数据变化后,物化视图不会自动更新. 需要定期 REFRESH,或通过触发器/定时任务(pg_cron)来保持新鲜度. 适合"允许几分钟或几小时延迟"的报表场景.

-- 实用示例:商品评分汇总(物化视图 + 索引)
CREATE MATERIALIZED VIEW mv_product_ratings AS
SELECT
  p.id AS product_id,
  p.name AS product_name,
  c.name AS category,
  COUNT(r.id) AS review_count,
  ROUND(AVG(r.rating), 2) AS avg_rating,
  MIN(r.rating) AS min_rating,
  MAX(r.rating) AS max_rating
FROM products p
JOIN categories c ON c.id = p.category_id
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name, c.name;

CREATE INDEX ON mv_product_ratings (avg_rating DESC);
CREATE INDEX ON mv_product_ratings (category);

-- 使用
SELECT * FROM mv_product_ratings WHERE category = '电子产品' ORDER BY avg_rating DESC;

普通视图 vs 物化视图

特性 普通视图(View) 物化视图(Materialized View)
数据存储 不存储,每次查询实时执行 存储查询结果的快照
查询性能 取决于底层查询复杂度 极快(等价于查普通表)
数据新鲜度 实时 REFRESH 后才更新
可以建索引 不可以 可以
可更新 简单视图可以 不可以
占用磁盘 不占(只存定义) 占用(存数据)
适用场景 简化查询,权限隔离,业务口径统一 报表缓存,复杂聚合加速,数据仓库

选择建议

  • 查询频率高 + 底层数据变化慢 → 物化视图
  • 需要实时数据 + 查询复杂度可接受 → 普通视图
  • 需要通过视图写数据 → 只能用普通视图

视图实战模式

模式 1:业务口径标准化

-- "有效订单"的定义:状态不是 cancelled 且金额大于 0
CREATE VIEW v_valid_orders AS
SELECT * FROM orders WHERE status != 'cancelled' AND total > 0;

-- 所有报表统一基于 v_valid_orders 查询,不再各自 WHERE
SELECT user_id, COUNT(*) FROM v_valid_orders GROUP BY user_id;

模式 2:宽表视图(打平多表)

-- 订单明细宽表:一行包含订单,用户,商品的全部关键信息
CREATE VIEW v_order_detail AS
SELECT
  o.id AS order_id,
  o.created_at AS order_date,
  o.status,
  u.username,
  u.city,
  u.is_vip,
  p.name AS product_name,
  c.name AS category,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id;

-- 使用:直接基于宽表做各种维度的分析
SELECT category, SUM(line_total) AS revenue
FROM v_order_detail
WHERE status = 'done'
GROUP BY category
ORDER BY revenue DESC;

模式 3:分层视图

-- 第一层:基础宽表
-- (已有 v_order_detail)

-- 第二层:基于宽表的月度 KPI
CREATE VIEW v_monthly_kpi AS
SELECT
  TO_CHAR(order_date, 'YYYY-MM') AS month,
  COUNT(DISTINCT order_id) AS orders,
  COUNT(DISTINCT username) AS buyers,
  SUM(line_total) AS revenue,
  ROUND(SUM(line_total) / COUNT(DISTINCT order_id), 2) AS avg_order_value
FROM v_order_detail
WHERE status = 'done'
GROUP BY TO_CHAR(order_date, 'YYYY-MM');

SELECT * FROM v_monthly_kpi ORDER BY month;

综合练习

  1. 创建用户画像视图 -- 创建 v_user_profile,包含:用户名,城市,是否 VIP,订单数(含所有状态),总消费金额
  2. 视图查询 -- 基于上述视图,查询北京地区消费最多的用户
  3. 物化视图 -- 创建 mv_category_stats,显示每个分类的商品数,平均价格,总库存
  4. 城市消费冠军 -- 查询每个城市中总消费最高的用户,使用窗口函数实现
  5. 复购率统计 -- 在所有购买过电子产品的用户中,有多少人同分类下单 >= 2 次. 给出人数和占比
  6. RFM 视图 -- 创建 v_rfm 实现简化 RFM 模型:R = 最近一次下单距今天数,F = 订单总数,M = 总消费金额
  7. 消费等级分布 -- 按总消费分为钻石(>=10000)/黄金(>=5000)/白银(>=1000)/青铜,显示每级用户数和占比
  8. 协同过滤推荐 -- 对于指定用户(alice),找出"买过相同商品的其他用户还买了什么",排除已购商品
  9. 用户生命周期 -- 用 CTE + 窗口函数构建每月新增/活跃/流失用户趋势
参考答案
-- 1. 用户画像视图
CREATE VIEW v_user_profile AS
SELECT
  u.username,
  u.city,
  u.is_vip,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username, u.city, u.is_vip;

-- 2. 北京消费最多
SELECT * FROM v_user_profile
WHERE city = '北京'
ORDER BY total_spent DESC
LIMIT 1;
-- carol: 15138.00

-- 3. 分类统计物化视图
CREATE MATERIALIZED VIEW mv_category_stats AS
SELECT
  c.name AS category,
  COUNT(p.id) AS product_count,
  ROUND(AVG(p.price), 2) AS avg_price,
  SUM(p.stock) AS total_stock
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
GROUP BY c.id, c.name;

-- 4. 每个城市的消费冠军
SELECT city, username, total_spent FROM (
  SELECT u.city, u.username,
         SUM(o.total) AS total_spent,
         RANK() OVER (PARTITION BY u.city ORDER BY SUM(o.total) DESC) AS rnk
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status != 'cancelled'
  GROUP BY u.id, u.city, u.username
) ranked
WHERE rnk = 1;

-- 5. 电子产品复购率
WITH elec_buyers AS (
  SELECT o.user_id, COUNT(DISTINCT o.id) AS elec_order_count
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE p.category_id = 1
  GROUP BY o.user_id
)
SELECT
  COUNT(*) AS total_elec_buyers,
  COUNT(*) FILTER (WHERE elec_order_count >= 2) AS repeat_buyers,
  ROUND(
    COUNT(*) FILTER (WHERE elec_order_count >= 2)::numeric / COUNT(*) * 100, 1
  ) AS repeat_rate_pct
FROM elec_buyers;

-- 6. RFM 视图
CREATE VIEW v_rfm AS
SELECT
  u.username,
  u.city,
  CURRENT_DATE - MAX(o.created_at)::date AS recency_days,
  COUNT(o.id) AS frequency,
  COALESCE(SUM(o.total), 0) AS monetary
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status != 'cancelled'
GROUP BY u.id, u.username, u.city;

SELECT * FROM v_rfm ORDER BY monetary DESC;

-- 7. 消费等级分布
WITH user_level AS (
  SELECT u.username,
         COALESCE(SUM(o.total), 0) AS total_spent,
         CASE
           WHEN COALESCE(SUM(o.total), 0) >= 10000 THEN '钻石'
           WHEN COALESCE(SUM(o.total), 0) >= 5000  THEN '黄金'
           WHEN COALESCE(SUM(o.total), 0) >= 1000  THEN '白银'
           ELSE '青铜'
         END AS level
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id AND o.status != 'cancelled'
  GROUP BY u.id, u.username
)
SELECT level,
       COUNT(*) AS user_count,
       ROUND(COUNT(*)::numeric / (SELECT COUNT(*) FROM users) * 100, 1) AS pct
FROM user_level
GROUP BY level
ORDER BY
  CASE level WHEN '钻石' THEN 1 WHEN '黄金' THEN 2 WHEN '白银' THEN 3 ELSE 4 END;

-- 8. 协同过滤推荐(简化版)
WITH alice_products AS (
  SELECT DISTINCT oi.product_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.user_id = 1
),
similar_users AS (
  SELECT DISTINCT o.user_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE oi.product_id IN (SELECT product_id FROM alice_products)
    AND o.user_id != 1
)
SELECT p.name AS recommended_product,
       COUNT(DISTINCT o.user_id) AS bought_by_n_users
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.user_id IN (SELECT user_id FROM similar_users)
  AND oi.product_id NOT IN (SELECT product_id FROM alice_products)
GROUP BY p.id, p.name
ORDER BY bought_by_n_users DESC;

-- 9. 用户价值变化趋势
WITH months AS (
  SELECT DISTINCT TO_CHAR(created_at, 'YYYY-MM') AS month FROM orders
),
user_months AS (
  SELECT user_id, TO_CHAR(created_at, 'YYYY-MM') AS month
  FROM orders WHERE status != 'cancelled'
  GROUP BY user_id, TO_CHAR(created_at, 'YYYY-MM')
),
first_order AS (
  SELECT user_id, MIN(TO_CHAR(created_at, 'YYYY-MM')) AS first_month
  FROM orders WHERE status != 'cancelled'
  GROUP BY user_id
)
SELECT
  m.month,
  COUNT(DISTINCT um.user_id) AS active_users,
  COUNT(DISTINCT CASE WHEN fo.first_month = m.month THEN fo.user_id END) AS new_users,
  (
    SELECT COUNT(DISTINCT prev.user_id)
    FROM user_months prev
    WHERE prev.month = TO_CHAR(TO_DATE(m.month, 'YYYY-MM') - INTERVAL '1 month', 'YYYY-MM')
      AND prev.user_id NOT IN (
        SELECT cur.user_id FROM user_months cur WHERE cur.month = m.month
      )
  ) AS churned_users
FROM months m
LEFT JOIN user_months um ON um.month = m.month
LEFT JOIN first_order fo ON fo.user_id = um.user_id
GROUP BY m.month
ORDER BY m.month;

第 4 题 GROUP BY + RANK 是组合技--先聚合出每用户的总消费,再在城市窗口内排名. 第 5 题用 CTE 先算出每个用户的电子产品订单数,再用 FILTER 统计复购人数. 第 6 题 CURRENT_DATE - MAX(created_at)::date 得到天数差. 第 8 题是经典的协同过滤思路:找相似用户 → 取他们买过的商品 → 排除已购 → 按热度排序. 第 9 题涉及 CTE 链,子查询,聚合和日期运算的综合运用.