SQL Server 幾十萬條減肥記錄表設計：主鍵選擇、分表策略與平均用電量計算

業務場景

記錄用戶購電信息：繳費時間、用戶 ID、用戶名、購電量、購電時間。需計算「兩次購電間隔期間的日均用電量」並存入新表。

表結構設計建議

主表：當前購電記錄表 (PurchaseCurrent)

CREATE TABLE PurchaseCurrent (
    Id          UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), -- UUID 主鍵，避免熱點
    UserId      BIGINT        NOT NULL,
    UserName    NVARCHAR(50)  NOT NULL,
    Amount      DECIMAL(10,2) NOT NULL,   -- 購電量
    PurchaseTime DATETIME2     NOT NULL,  -- 購電時間
    CreateTime  DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
    
    CONSTRAINT UX_UserId UNIQUE (UserId)  -- 單用戶僅保留最新一條
);

✅ UUID 主鍵避免自增熱點
✅ 用戶 ID 唯一索引保證單用戶最新記錄

歷史表：購電履歷表 (PurchaseHistory)

CREATE TABLE PurchaseHistory (
    Id          BIGINT IDENTITY(1,1) PRIMARY KEY,  -- 自增主鍵，寫入快
    UserId      BIGINT        NOT NULL,
    Amount      DECIMAL(10,2) NOT NULL,
    PurchaseTime DATETIME2     NOT NULL,
    PrevPurchaseTime DATETIME2 NULL,               -- 上次購電時間（冗余便於查詢）
    DailyAvg    DECIMAL(10,4) NULL,                -- 計算出的日均用電量
    CreateTime  DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
    
    INDEX IX_UserId_PurchaseTime (UserId, PurchaseTime)
);

業務流程與計算邏輯

1. 用戶新購電 → 寫入 `PurchaseCurrent`（UPSERT：已存在則更新）
2. 觸發器 / 應用層邏輯：
• 查詢該用戶上一條 `PurchaseHistory` 記錄獲取 `PrevPurchaseTime`
• 計算 `DailyAvg = Amount / DATEDIFF(DAY, PrevPurchaseTime, PurchaseTime)`
• 將上一條當前記錄歸檔至 `PurchaseHistory`（含計算出的 DailyAvg）
• 更新 `PurchaseCurrent` 為最新記錄
3. 歷史表保留完整鏈路，支援審計、報表、趨勢分析

性能優化要點

• 分區表：`PurchaseHistory` 按 `PurchaseTime` 月份分區，歷史數據冷熱分離
• 索引策略：`(UserId, PurchaseTime)` 複合索引覆蓋「用戶歷史查詢」
• 讀寫分離：報表查詢走只讀副本
• 批量歸檔：定時任務將超 30 天未更新的當前記錄批量移至歷史表

日均用電量計算 SQL 示例

WITH Ranked AS (
  SELECT 
    UserId,
    Amount,
    PurchaseTime,
    LAG(PurchaseTime) OVER (PARTITION BY UserId ORDER BY PurchaseTime) AS PrevTime,
    LAG(Amount) OVER (PARTITION BY UserId ORDER BY PurchaseTime) AS PrevAmount
  FROM PurchaseHistory
)
SELECT 
  UserId,
  PurchaseTime,
  PrevAmount * 1.0 / NULLIF(DATEDIFF(DAY, PrevTime, PurchaseTime), 0) AS DailyAvg
FROM Ranked
WHERE PrevTime IS NOT NULL;

總結

採用當前表 + 歷史表雙表模式，主鍵用 UUID 避免熱點，歷史表用自增主鍵提升寫入性能。通過觸發器或應用層在購電瞬間完成「歸檔舊記錄 + 計算日均 + 更新當前」原子操作，滿足幾十萬量級下的高併發寫入與靈活查詢需求。