大表场景下，如何基于在线数据创建高效无卡顿的透视表？

一、用原生透视语法替代多视图关联 #

不要创建4个独立的Sales视图再关联Customer，这种方式会导致数据库多次扫描Sales表，且多表关联易产生冗余计算。直接用条件聚合（或数据库原生PIVOT语法），仅扫描Sales和Customer各一次即可生成透视结构：

CREATE VIEW PivotView AS
SELECT
    c.customerCode,
    c.customerName,
    -- 产品1对应销售信息
    MAX(CASE WHEN s.productID = 'P1' THEN s.productID END) AS p1_productID,
    MAX(CASE WHEN s.productID = 'P1' THEN s.agentID END) AS p1_agentID,
    MAX(CASE WHEN s.productID = 'P1' THEN s.saleID END) AS p1_saleID,
    -- 产品2对应销售信息
    MAX(CASE WHEN s.productID = 'P2' THEN s.productID END) AS p2_productID,
    MAX(CASE WHEN s.productID = 'P2' THEN s.agentID END) AS p2_agentID,
    MAX(CASE WHEN s.productID = 'P2' THEN s.saleID END) AS p2_saleID,
    -- 产品3、4同理
    MAX(CASE WHEN s.productID = 'P3' THEN s.productID END) AS p3_productID,
    MAX(CASE WHEN s.productID = 'P3' THEN s.agentID END) AS p3_agentID,
    MAX(CASE WHEN s.productID = 'P3' THEN s.saleID END) AS p3_saleID,
    MAX(CASE WHEN s.productID = 'P4' THEN s.productID END) AS p4_productID,
    MAX(CASE WHEN s.productID = 'P4' THEN s.agentID END) AS p4_agentID,
    MAX(CASE WHEN s.productID = 'P4' THEN s.saleID END) AS p4_saleID
FROM Customer c
LEFT JOIN Sales s ON c.customerID = s.customerID
GROUP BY c.customerCode, c.customerName;

该方式避免了多视图关联带来的重复扫描和关联开销，大幅降低内存占用。

二、添加针对性联合索引 #

针对业务查询场景创建索引，减少全表扫描和排序操作：

• 在Sales表创建(customerID, productID)联合索引，包含agentID, saleID字段：

  CREATE INDEX idx_sales_cust_prod ON Sales (customerID, productID) INCLUDE (agentID, saleID);
  

  可让数据库快速定位某客户某产品的销售记录，无需扫描全表。
• 在Customer表创建(city, customerID)联合索引：

  CREATE INDEX idx_customer_city_cust ON Customer (city, customerID);
  

  针对“洛杉矶客户”这类过滤查询，能直接筛选目标客户，减少后续关联的数据量。

三、用物化视图替代普通视图 #

若数据库支持物化视图（如PostgreSQL、Oracle、SQL Server），可将透视视图改为物化视图：

-- PostgreSQL示例
CREATE MATERIALIZED VIEW PivotMaterializedView AS
-- 此处为上述条件聚合的SQL语句
WITH DATA;

-- 设置增量刷新索引
CREATE UNIQUE INDEX idx_mv_cust_code ON PivotMaterializedView (customerCode);

物化视图会预计算并存储透视结果，查询时直接读取预存数据，性能接近实体表。同时可设置定时刷新或增量刷新策略（如每日凌晨刷新、基于Sales表变更触发刷新），兼顾数据实时性和查询性能。

四、优化统计查询执行逻辑 #

针对“洛杉矶客户代理商一致性统计”这类复杂查询，不要先查完整透视视图再过滤，而是先缩小数据范围再做透视：

-- 先筛选洛杉矶客户，再关联Sales做统计
SELECT
    c.customerCode,
    COUNT(DISTINCT s.agentID) AS agent_count,
    CASE WHEN COUNT(DISTINCT s.agentID) = 1 THEN '一致' ELSE '不一致' END AS agent_consistency
FROM Customer c
JOIN Sales s ON c.customerID = s.customerID
WHERE c.city = '洛杉矶'
GROUP BY c.customerCode;

该方式避免了先处理全量数据再过滤的冗余操作，大幅降低内存消耗。

五、精简透视表字段 #

仅保留业务必需字段，不要将Customer或Sales表的冗余字段加入透视视图。比如无需客户历史地址、备注等字段时，就不要在视图中包含，减少数据传输和内存占用。

内容的提问来源于stack exchange，提问作者Luca Giammattei