sql: SQL Data Sampling and Approximation Techniques using sql server 2025

 

 

/* ==================================================================================================
   SQL Server 2025  ·  数据采样与近似聚合（Data Sampling & Approximation Techniques）
   国际化珠宝集团  ERP / CRM / HR  全场景演示
   --------------------------------------------------------------------------------------------------
   目标：在"全表精确计算太贵、但快速估计够用"的场景下，用采样与近似函数把查询成本降下来。

   覆盖内容：
     · TABLESAMPLE  (SYSTEM / ROWS)   + REPEATABLE        —— 物理页 / 行级采样
     · TOP (n) PERCENT / TOP (n) WITH TIES                —— 语法级"抽样"（其实是截断）
     · APPROX_COUNT_DISTINCT                               —— 近似基数（HyperLogLog）
     · APPROX_PERCENTILE_CONT / APPROX_PERCENTILE_DISC    —— 近似分位数（t-digest）
     · 基数估计（Cardinality Estimation）与采样误差、置信区间
     · 分层采样（Stratified by 国家/品类）、聚类采样与"伯努利 vs 系统"采样
     · 万亿级 / 高并发场景下的近似查询设计（三层架构 + 结果集缓存 + 双轨制）
     · 精确 vs 近似的成本对比（STATISTICS IO / TIME）、误差度量

   数据库：JewelryAnalytics（已存在的示例库，兼容级别 170 = SQL Server 2025）
   说明：脚本**只读**既有业务表（不改结构），自建对象统一放在 dbo.Sample* / dbo.usp_Approx* 下，
         并在第 00 节做幂等清理，可反复执行。

   测试环境：SQL Server 2025 Enterprise (17.0.1135.8)，库兼容级别 170。

   ★ 本脚本最容易踩的坑（正文有实测）：
     1) TABLESAMPLE 的语法是 TABLESAMPLE SYSTEM (n PERCENT) 或 TABLESAMPLE SYSTEM (n ROWS)。
        ★★ 写成 TABLESAMPLE ROWS (n PERCENT) 会报 102 —— ROWS/PERCENT 是括号内的单位后缀，
           不是 TABLESAMPLE 后面的关键字。这个错很多人犯。
     2) SYSTEM 是 SQL Server 唯一可用的采样方法，且**始终是页级采样**；
        不存在"行级伯努利采样"。要按行数采样用 TABLESAMPLE SYSTEM (n ROWS)。
     3) TABLESAMPLE 只支持字面量，不支持变量：TABLESAMPLE SYSTEM (@pct PERCENT) 报语法错误。
        -> 要按运行期参数采样，只能动态 SQL。
     4) TABLESAMPLE 不支持 WITH (NOLOCK) 提示；两者写一起报语法错误。
     5) ★★ 最反直觉的一条：TABLESAMPLE 对**聚集列存(CCI)表基本不起作用**。
        计划里不会出现 Table Sample 算子，实际是一次完整 Clustered Index Scan，
        IO 与全表查询几乎相同 —— 即"采样不省 IO"。第 06.2 节有 STATISTICS IO 实证。
        列存要少读，应该用「列裁剪 + 行组消除 + 预聚合」，而不是采样。
     6) 对结果做 COUNT(*) 得到的是"采样行数"，不是"估计总量"：
        要用 SUM(样本值 × 权重) 或 AVG(样本值) × 总行数。直接 COUNT(*) 是最经典的错误。
     7) APPROX_PERCENTILE_DISC 对 DECIMAL 列会报 402「decimal 和 decimal 不兼容」，
        必须把 ORDER BY 列 CAST 成 FLOAT；APPROX_PERCENTILE_CONT 没这个限制。
     8) APPROX_COUNT_DISTINCT 在**极低基数**（几个取值）时相对误差很难看；
        高基数时反而准。枚举型维度去重不要用它。
     9) 采样会破坏"精确对账"：财务口径绝不能采样，必须走预聚合表（第 09.2 节）。
    10) 小表（<10 万行）采样会严重失真甚至静默返回 0 行（第 03.6 / 11-V6 节实测）。
    11) 变量名**大小写不敏感**：DECLARE @ci_n_pool 和 @ci_N_pool 会被判为"已声明"（错误 134）。
    12) 列别名**不能以数字开头**：`95%下限 = ...` 会报 102「'=' 附近有语法错误」；
        改成 CI95_下限 之类即可。这个坑在写置信区间列时特别容易踩。
    13) 变量不能跨 GO 生存：DECLARE 之后若有 GO，变量在后一个批里就失效（错误 137）。
        第 08.1 节因此把所有相关语句保持在同一批内。
    14) 「聚合或其他 SET 操作消除了 Null 值」这类**提示**（非错误）的两种典型来源：
        (a) COUNT(DISTINCT 可空列)：本库 erp.SalesOrder.CustomerId 有 666 笔 NULL（散客单），
            "客户数"口径不含散客；加 IS NOT NULL 过滤即可消除提示并明确口径。
        (b) 多变量赋值 + 可产生 NULL 的聚合实参，例如
            SELECT @n = COUNT(*), @avg = AVG(col) FROM T TABLESAMPLE ...;   -- 空样本时 AVG 得 NULL
            SELECT @n = COUNT(*), @s = SUM(CASE WHEN cond THEN v END) FROM T; -- 无匹配行时 SUM 得 NULL
            最小复现：SELECT @c = COUNT(*), @d = SUM(CASE WHEN 1 = 0 THEN 1 END) FROM sys.objects;
            消除办法：拆成两条 SELECT，或给实参加 ISNULL/COALESCE。
        两种情形结果都正确，提示可安全忽略；脚本第 04.3 / 08.1 节已就地注释说明。
    15) ★★ 分层估计的权重最容易算错（本脚本第一版就写错了，实测差 23 倍）：
        按"每层每 100 行取 1 行"抽样时，权重**不是** 100，
        而是「层内总行数 ÷ 层内实际样本行数」。
        写 SUM(金额 × 层内总行数) / 100 会得到 43 万亿（真值只有 1830 亿）。
        正确写法： SUM(金额 × 层内总行数 / 层内实际样本行数)。
        详见 07.1（同时给出了错误写法的对照，直观看到 23 倍偏差）。
    16) 采样/聚合的多变量赋值若可能命中空集，AVG/SUM 会返回 NULL 并触发提示：
        空样本时 AVG 得 NULL；无匹配行时 SUM(CASE ...) 得 NULL。
        消除办法：拆成多条 SELECT，或给实参加 ISNULL/COALESCE。
   ================================================================================================== */

SET NOCOUNT ON;
SET XACT_ABORT OFF;
GO

/* ==================================================================================================
   00  环境确认与幂等清理
   ================================================================================================== */

PRINT N'';
PRINT N'################################################################################';
PRINT N'#  SQL Server 2025 · 数据采样与近似聚合 · 国际化珠宝集团 ERP/CRM/HR 演示';
PRINT N'################################################################################';
GO

PRINT N'';
PRINT N'===== 00 环境确认 =====';
GO

SELECT
    项目        = N'实例版本',
    值          = CAST(SERVERPROPERTY('ProductVersion')       AS NVARCHAR(50))
UNION ALL SELECT N'版本类型',   CAST(SERVERPROPERTY('Edition')              AS NVARCHAR(60))
UNION ALL SELECT N'当前数据库', CAST(DB_NAME()                             AS NVARCHAR(60))
UNION ALL SELECT N'兼容级别',   CAST((SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID()) AS NVARCHAR(10))
UNION ALL SELECT N'RCSI 已开启', CAST((SELECT is_read_committed_snapshot_on FROM sys.databases WHERE database_id = DB_ID()) AS NVARCHAR(10));
GO

-- 前提检查：本脚本依赖的既有表必须存在
IF OBJECT_ID(N'rpt.OrgSalesFact')   IS NULL THROW 50001, N'缺少 rpt.OrgSalesFact，请先准备 JewelryAnalytics 示例库。', 1;
IF OBJECT_ID(N'erp.SalesOrder')     IS NULL THROW 50002, N'缺少 erp.SalesOrder。', 1;
IF OBJECT_ID(N'crm.Interaction')    IS NULL THROW 50003, N'缺少 crm.Interaction。', 1;
IF OBJECT_ID(N'hr.Employee')        IS NULL THROW 50004, N'缺少 hr.Employee。', 1;
GO

-- 记录基线（后面所有偏差都以这里为"真值"）
PRINT N'[00-A] 精确基线（后面所有近似结果都以此为真值对比）';
SELECT
     事实表行数          = COUNT(*)
    , 精确总金额USD       = CAST(SUM(AmountUsd)          AS DECIMAL(38,2))
    , 精确_unique_OrgUnit = COUNT(DISTINCT OrgUnitId)
    , 精确_unique_Category= COUNT(DISTINCT CategoryId)
FROM rpt.OrgSalesFact;
GO
SELECT
     销售订单行数     = COUNT(*)
    , 精确_unique_customer = COUNT(DISTINCT CustomerId)   -- 注意：CustomerId 可空，COUNT(DISTINCT) 会忽略 NULL 散客单
    , 精确_unique_store    = COUNT(DISTINCT StoreCode)
    , 精确_unique_employee = COUNT(DISTINCT SalesEmployeeId)
FROM erp.SalesOrder;
GO
SELECT
     CRM互动行数        = COUNT(*)
   , 精确_unique_customer = COUNT(DISTINCT CustomerId)   -- 同上，忽略 NULL
   , 精确_unique_channel  = COUNT(DISTINCT Channel)
FROM crm.Interaction;
GO

-- 00-B 幂等清理（可反复执行）
PRINT N'[00-B] 清理本脚本自建对象（可反复执行）';
GO
DROP TABLE IF EXISTS dbo.SampleResultLog;
DROP TABLE IF EXISTS dbo.SampleEstimates;
DROP TABLE IF EXISTS dbo.SampleAccessLog;
DROP TABLE IF EXISTS dbo.SampleInteractionFlat;
DROP TABLE IF EXISTS dbo.SampleTmpOrders;
DROP PROCEDURE IF EXISTS dbo.usp_ApproxSalesByCountry;
DROP PROCEDURE IF EXISTS dbo.usp_SampleSalesOrder;
DROP PROCEDURE IF EXISTS dbo.usp_ApproxDashboard;
GO
PRINT N'  清理完成';
GO


/* ==================================================================================================
   01  为什么需要"采样 / 近似"：先量化"精确"到底有多贵
   --------------------------------------------------------------------------------------------------
   先把问题摆清楚：在 500 万行（本地叫"中量级"，生产是万亿级）上做一次全表精确聚合，
   代价是多少？下面用真实执行统计把它量出来，作为后面所有"省钱"对比的分母。
   ================================================================================================== */

PRINT N'';
PRINT N'===== 01 精确聚合的成本基线 =====';
GO

PRINT N'----- 01.1 精确聚合一次 500 万行（冷/热各一次，看逻辑读）-----';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT
     精确_总金额      = CAST(SUM(AmountUsd) AS DECIMAL(38,2))
    , 精确_总件数      = SUM(Qty)
    , 精确_订单数      = COUNT(*)
    , 精确_独立门店数  = COUNT(DISTINCT OrgUnitId)
    , 精确_独立品类数  = COUNT(DISTINCT CategoryId)
FROM rpt.OrgSalesFact;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

PRINT N'  观察点：这条语句的 logical reads 就是"精确"的价格标签。';
PRINT N'           后面每种采样/近似手段，都要和它比"误差换来了多少 IO 下降"。';
GO

PRINT N'----- 01.2 同一份数据的"结果集大小"其实很小（这是采样的立足点）-----';
SELECT
     维度组合总数 = COUNT(*)
   , 说明        = N'国家 x 品类 x 月 只有这么点组合 —— 结论可以很小，但算它很贵'
FROM (
    SELECT CountryCode, Category, SalesMonth FROM rpt.MonthlySalesPreAgg GROUP BY CountryCode, Category, SalesMonth
) x;
GO


/* ==================================================================================================
   02  语法级"抽样"：TOP n PERCENT / TOP n WITH TIES
   --------------------------------------------------------------------------------------------------
   最容易和"采样"混淆的一类写法。要点只有一个：
     ★ TOP 不是采样！它不带统计意义，只是"取前 N 行"，
       而且取哪些行取决于 ORDER BY（或无序时的存储物理顺序）。
   ================================================================================================== */

PRINT N'';
PRINT N'===== 02 TOP n PERCENT：方便但不是采样 =====';
GO

PRINT N'----- 02.1 TOP 1 PERCENT 无序取：取到的是"簇头"，不是随机样本 -----';
SELECT
     方式   = N'TOP 1 PERCENT (无 ORDER BY)'
   , 行数   = COUNT(*)
   , 平均金额 = CAST(AVG(AmountUsd) AS DECIMAL(38,2))
   , 最小FactId = MIN(FactId)
   , 最大FactId = MAX(FactId)
FROM (SELECT TOP (1) PERCENT FactId, AmountUsd FROM rpt.OrgSalesFact) s;
GO

PRINT N'  观察点：最小/最大 FactId 会告诉你它取的是开头一段连续区间 —— 完全不是均匀抽样。';
PRINT N'           在聚集列存上这种"取开头"尤其明显，因为它按行组顺序读。';
GO

PRINT N'----- 02.2 TOP 1 PERCENT + ORDER BY NEWID()：算"真随机"，但代价惨烈 -----';
PRINT N'  说明：NEWID() 会对全表每一行求值并排序，等于把"省"变成了"更贵"。';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT
     方式   = N'TOP 1 PERCENT (ORDER BY NEWID())'
   , 行数   = COUNT(*)
   , 平均金额 = CAST(AVG(AmountUsd) AS DECIMAL(38,2))
FROM (SELECT TOP (1) PERCENT AmountUsd FROM rpt.OrgSalesFact ORDER BY NEWID()) s;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

PRINT N'  结论：随机是真的随机了，但 IO 通常比 CLUSTERED COLUMNSTORE 全表扫描还差。';
PRINT N'        —— 第 03 节的 TABLESAMPLE 就是为了解决"随机 + 便宜"这个矛盾。';
GO


/* ==================================================================================================
   03  TABLESAMPLE：采样子句
   --------------------------------------------------------------------------------------------------
   ★★ 语法要点（很多人写错，本机实测确认）：
       正确： TABLESAMPLE SYSTEM (n PERCENT)
       正确： TABLESAMPLE SYSTEM (n ROWS)
       正确： TABLESAMPLE (n PERCENT)              -- SYSTEM 可省略，默认就是 SYSTEM
       错误： TABLESAMPLE ROWS (n PERCENT)         -- 报 102「'ROWS' 附近有语法错误」

       ★ 关键区别：PERCENT 和 ROWS 是"写在括号里的单位后缀"，不是写在 TABLESAMPLE 后面的关键字。
         表示"取 5 万行"写 TABLESAMPLE SYSTEM (50000 ROWS)，
         而不是 TABLESAMPLE ROWS (50000 PERCENT) —— 后者语法根本不存在。

   ★★ 另一个必须知道的事实（本机实测，与很多博客说法相反）：
       SQL Server 的 SYSTEM 是**唯一**可用的采样方法，且它**始终是页级采样**，
       不存在"行级伯努利采样"的实现。
         实测：TABLESAMPLE SYSTEM (0.5 PERCENT) 在 500 万行表上返回 24449 行（≈0.49%），
               TABLESAMPLE SYSTEM (0.01 PERCENT) 返回 531 行 —— 并没有返回空集。
       换句话说：
         · 采样率**不会**因为页取整而归零（只要表足够大）；
         · 但小表上会明显偏离标称比例（见 03.5）；
         · "行级采样"要靠 TABLESAMPLE SYSTEM (n ROWS) 或窗口函数取模自行实现。
   ================================================================================================== */

PRINT N'';
PRINT N'===== 03 TABLESAMPLE：PERCENT 与 ROWS 两种写法 =====';
GO

PRINT N'----- 03.1 SYSTEM (1 PERCENT)：按百分比采样 -----';
SELECT
     方式     = N'SYSTEM (1 PERCENT)'
   , 采样行数 = COUNT(*)
   , 约占全表 = CAST(100.0 * COUNT(*) / (SELECT COUNT(*) FROM rpt.OrgSalesFact) AS DECIMAL(10,4))
   , 平均金额 = CAST(AVG(AmountUsd) AS DECIMAL(38,2))
FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT);
GO

PRINT N'----- 03.2 SYSTEM (n ROWS)：按"行数"采样（注意 ROWS 在括号内！）-----';
SELECT
     方式     = N'SYSTEM (50000 ROWS)'
   , 采样行数 = COUNT(*)
   , 约占全表 = CAST(100.0 * COUNT(*) / (SELECT COUNT(*) FROM rpt.OrgSalesFact) AS DECIMAL(10,4))
   , 平均金额 = CAST(AVG(AmountUsd) AS DECIMAL(38,2))
FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (50000 ROWS);
GO

PRINT N'  说明：即使是 ROWS 写法，底层依然是"选若干整页"，所以实际行数是近似值，';
PRINT N'        不会精确等于 50000。实测在 5 万上下浮动。';
GO

PRINT N'----- 03.3 PERCENT 可省略 SYSTEM 关键字（默认即 SYSTEM）-----';
SELECT 写法 = N'TABLESAMPLE (1 PERCENT)', 采样行数 = COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE (1 PERCENT)
UNION ALL
SELECT N'TABLESAMPLE SYSTEM (1 PERCENT)', COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT);
GO

PRINT N'----- 03.4 采样率不是精确百分比：多跑几次看波动 -----';
PRINT N'  说明：TABLESAMPLE 是概率采样，每次结果行数都不同；1% 只是期望值。';
SELECT 轮次 = 1, 采样行数 = COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT)
UNION ALL SELECT 2, COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT)
UNION ALL SELECT 3, COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT)
UNION ALL SELECT 4, COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT)
UNION ALL SELECT 5, COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT);
GO

PRINT N'----- 03.5 REPEATABLE：让同一个种子产生同一个样本（可复现）-----';
PRINT N'  用途：回归测试、结果对账、把"某一次抽样"存档后复盘。seed 相同 -> 样本相同。';
SELECT
     种子相同_第一次 = (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT) REPEATABLE (20250101))
   , 种子相同_第二次 = (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT) REPEATABLE (20250101))
   , 种子不同_第三次 = (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT) REPEATABLE (20250202));
GO

PRINT N'----- 03.6 ★ 陷阱：小表上采样率严重失真（甚至可能取到 0 行）-----';
PRINT N'  erp.SalesOrder 只有 6000 行（约几十页），0.1% 期望只有 6 行左右；';
PRINT N'  一旦抽样落到"一页都没选中"，就会**静默返回 0 行**。';
PRINT N'  多跑几次能看出这种抖动：';
SELECT 轮次 = 1, 行数 = COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT)
UNION ALL SELECT 2, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT)
UNION ALL SELECT 3, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT)
UNION ALL SELECT 4, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT)
UNION ALL SELECT 5, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT)
UNION ALL SELECT 6, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT);
GO
PRINT N'  对照：同一张小表上 TABLESAMPLE SYSTEM (5 PERCENT) 就稳定得多：';
SELECT 轮次 = 1, 行数 = COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (5 PERCENT)
UNION ALL SELECT 2, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (5 PERCENT)
UNION ALL SELECT 3, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (5 PERCENT);
GO
PRINT N'  ★ 工程结论：小表（十万行以下）不要采样，直接精确算；';
PRINT N'             采样比例要有下限保护，且必须对"0 行"结果做兜底。';
GO

PRINT N'----- 03.7 ★ 陷阱演示：TABLESAMPLE 只吃字面量，变量会报语法错误 -----';
PRINT N'  下面 4 行是"错误示范"，此行起注释掉，不实际执行：';
PRINT N'      DECLARE @pct FLOAT = 1.0;';
PRINT N'      SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (@pct PERCENT);';
PRINT N'      -- 消息 102：''@pct'' 附近有语法错误。';
PRINT N'  要按运行期参数采样，必须走动态 SQL（第 07.3 节给出完整封装）。';
GO

PRINT N'----- 03.8 ★ 陷阱演示：TABLESAMPLE 不能和表提示一起写 -----';
PRINT N'      SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT) WITH (NOLOCK);';
PRINT N'      -- 消息 156：关键字 ''WITH'' 附近有语法错误。';
PRINT N'  采样本身已经是"近似读取"（SYSTEM 采样可能按 READ UNCOMMITTED 读），';
PRINT N'  不需要也不允许再加隔离级别提示。';
GO

PRINT N'----- 03.9 ★ 陷阱演示：TABLESAMPLE ROWS (...) 是错误写法 -----';
PRINT N'      SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE ROWS (1 PERCENT);';
PRINT N'      -- 消息 102：''ROWS'' 附近有语法错误。';
PRINT N'  正确写法是把 ROWS 放进括号： TABLESAMPLE SYSTEM (50000 ROWS)。';
GO


/* ==================================================================================================
   04  采样的估计量：怎么从样本反推总体
   --------------------------------------------------------------------------------------------------
   ★ 这是采样最容易出错的地方。
     在采样结果上直接 COUNT(*)，得到的是「样本行数」，不是「总体行数」。
     正确做法：用样本均值 × 总体行数（Horvitz-Thompson / 扩展估计量）。
   ================================================================================================== */

PRINT N'';
PRINT N'===== 04 从样本反推总体：正确的估计量 =====';
GO

PRINT N'----- 04.1 反例：把样本行数当总量（经典错误）-----';
SELECT
     错误做法        = N'直接把采样行数当结果'
   , 采样行数        = (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT))
   , 真实总行数      = (SELECT COUNT(*) FROM rpt.OrgSalesFact)
   , 结论            = N'差两个数量级 —— 这个数字不能直接给业务';
GO

PRINT N'----- 04.2 正例：样本均值 × 总体行数 = 总量估计 -----';
DECLARE @n_total     BIGINT;
DECLARE @n_sample    BIGINT;
DECLARE @avg_sample  DECIMAL(38,4);
DECLARE @true_total  DECIMAL(38,4);
DECLARE @est_total   DECIMAL(38,4);

SELECT @n_total = COUNT(*) FROM rpt.OrgSalesFact;
SELECT @true_total = CAST(SUM(AmountUsd) AS DECIMAL(38,4)) FROM rpt.OrgSalesFact;

SELECT
    @n_sample   = COUNT(*),
    @avg_sample = AVG(AmountUsd)
FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT);

SET @est_total = @avg_sample * @n_total;

SELECT
     总体行数N         = @n_total
   , 样本行数n         = @n_sample
   , 样本单行均值       = CAST(@avg_sample AS DECIMAL(38,2))
   , 总量估计_采样法    = CAST(@est_total  AS DECIMAL(38,2))
   , 真值              = CAST(@true_total AS DECIMAL(38,2))
   , 相对误差百分比     = CAST(100.0 * ABS(@est_total - @true_total) / NULLIF(@true_total, 0) AS DECIMAL(10,4));
GO

PRINT N'  要点：采样估"总量"时，永远是「先算样本的均值/比例，再乘以 N」。';
PRINT N'        SUM 这类加法型指标不能直接拿样本 SUM 交差。';
GO

PRINT N'----- 04.3 采样比例的置信区间（正态近似，95%）-----';
PRINT N'  业务定义：把单笔 >= 5 万美元的订单视为"大额/VIP 单"，要估它**有多少笔**。';
PRINT N'  公式：p_hat ± 1.96 * SQRT(p_hat*(1-p_hat)/n)    再乘 N 得到总体计数的区间。';
PRINT N'  说明：为降低抽样波动带来的观测噪声，这里把多个独立样本**合并**成一个大样本再算区间。';
GO
DECLARE @ci_p_hat      FLOAT;
DECLARE @ci_samp_rows  BIGINT;
DECLARE @ci_pop_rows   BIGINT;
DECLARE @ci_se         FLOAT;
DECLARE @ci_total_hit  BIGINT;

SELECT @ci_pop_rows  = COUNT(*) FROM rpt.OrgSalesFact;
SELECT @ci_total_hit = COUNT(*) FROM rpt.OrgSalesFact WHERE AmountUsd >= 50000;

-- 把 5 次 2% 样本 UNION ALL 起来，形成一个约 10% 的大样本（仅用于演示公式）
-- ★ 说明：下面这句在 SSMS/sqlcmd 里可能输出「警告: 聚合或其他 SET 操作消除了 Null 值。」，
--    这是 SQL Server 的**信息性提示，不是错误**，结果完全正确、可以忽略。
--    成因：同一条 SELECT 里既做多变量赋值（COUNT + AVG），聚合的实参又是"可产生 NULL 的表达式"。
--    用最小复现证明：SELECT @c = COUNT(*), @d = SUM(CASE WHEN 1=0 THEN 1 END) FROM T;  → 必出该提示。
--    消除办法（二选一）：① 把两个聚合拆成两条 SELECT；② 给实参加 ISNULL/COALESCE 兜底。
SELECT
    @ci_samp_rows = COUNT(*),
    @ci_p_hat     = AVG(CASE WHEN AmountUsd >= 50000 THEN 1.0 ELSE 0.0 END)
FROM (
    SELECT AmountUsd FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT)
    UNION ALL SELECT AmountUsd FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT)
    UNION ALL SELECT AmountUsd FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT)
    UNION ALL SELECT AmountUsd FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT)
    UNION ALL SELECT AmountUsd FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT)
) pooled;

SET @ci_se = SQRT(@ci_p_hat * (1 - @ci_p_hat) / NULLIF(@ci_samp_rows, 0));

-- ★ 列别名不能以数字开头：'95%下限 = ...' 会报 102，所以这里用 CI95_ 前缀
SELECT
     合并样本量n            = @ci_samp_rows
   , 样本命中比例p_hat       = CAST(@ci_p_hat AS DECIMAL(10,6))
   , 标准误SE                = CAST(@ci_se AS DECIMAL(10,6))
   , 估计大额单笔数          = CAST(@ci_p_hat * @ci_pop_rows AS DECIMAL(38,0))
   , CI95_下限               = CAST((@ci_p_hat - 1.96 * @ci_se) * @ci_pop_rows AS DECIMAL(38,0))
   , CI95_上限               = CAST((@ci_p_hat + 1.96 * @ci_se) * @ci_pop_rows AS DECIMAL(38,0))
   , 真实大额单笔数          = @ci_total_hit
   , 真值是否落在区间内      = CASE WHEN @ci_total_hit BETWEEN (@ci_p_hat - 1.96 * @ci_se) * @ci_pop_rows
                                                      AND (@ci_p_hat + 1.96 * @ci_se) * @ci_pop_rows
                                 THEN N'是 —— 区间覆盖成功' ELSE N'否 —— 此次抽样偏离（可重跑，属正常概率事件）' END;
GO

PRINT N'  公式使用提醒：1.96 对应 95% 置信度（双侧）。若业务要求 99.9%，系数要换成 3.29。';
PRINT N'                样本量越小，区间越宽；这就是"小表不要采样"的统计学原因。';
GO


/* ==================================================================================================
   05  近似聚合函数：APPROX_COUNT_DISTINCT / APPROX_PERCENTILE_*
   --------------------------------------------------------------------------------------------------
   这才是"官方认可的近似"：有确定的误差上界，单遍扫描、内存可控，
   在万亿级表上比精确算法便宜一个数量级。
   ================================================================================================== */

PRINT N'';
PRINT N'===== 05 近似聚合函数 =====';
GO

PRINT N'----- 05.1 APPROX_COUNT_DISTINCT vs COUNT(DISTINCT)：精度与成本 -----';
PRINT N'  ★ 先注意一个业务事实：erp.SalesOrder.CustomerId 是**可空**的（散客/匿名单）。';
PRINT N'    实测 6000 笔订单里有 666 笔 CustomerId IS NULL。';
PRINT N'    COUNT(DISTINCT col) 与 APPROX_COUNT_DISTINCT(col) 都会忽略 NULL，';
PRINT N'    此时 SQL Server 会打印警告「聚合或其他 SET 操作消除了 Null 值」——';
PRINT N'    这不是错误，但提醒你：统计口径里"客户数"是**有客户号的订单**的客户数，不含散客。';
PRINT N'    要显示这个口径差异，把散客单独算一行更清楚。';
GO
SELECT
     口径                       = N'散客（CustomerId IS NULL）订单数'
   , 订单数                     = SUM(CASE WHEN CustomerId IS NULL THEN 1 ELSE 0 END)
   , 占总订单百分比             = CAST(100.0 * SUM(CASE WHEN CustomerId IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(10,4))
FROM erp.SalesOrder;
GO
SELECT 口径 = N'有客户号的订单数', 订单数 = COUNT(*) FROM erp.SalesOrder WHERE CustomerId IS NOT NULL;
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- 显式加 CustomerId IS NOT NULL，既避免警告，也让"客户数"的口径一目了然
SELECT 方式 = N'精确 COUNT(DISTINCT CustomerId)', 结果 = COUNT(DISTINCT CustomerId)
FROM erp.SalesOrder WHERE CustomerId IS NOT NULL;
GO
SELECT 方式 = N'近似 APPROX_COUNT_DISTINCT(CustomerId)', 结果 = APPROX_COUNT_DISTINCT(CustomerId)
FROM erp.SalesOrder WHERE CustomerId IS NOT NULL;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

PRINT N'  基数越大，APPROX_COUNT_DISTINCT 的误差率越稳定（通常 < 2%）。';
PRINT N'  在 500 万行事实表上再对比一次（低基数 + 高基数各一组）：';
GO
SELECT
     对象        = N'rpt.OrgSalesFact.OrgUnitId (低基数, 只有10个值)'
   , 精确值      = (SELECT COUNT(DISTINCT OrgUnitId) FROM rpt.OrgSalesFact)
   , 近似值      = (SELECT APPROX_COUNT_DISTINCT(OrgUnitId) FROM rpt.OrgSalesFact);
GO
SELECT
     对象        = N'erp.SalesOrder.OrderNo (高基数, 几乎全不同)'
   , 精确值      = (SELECT COUNT(DISTINCT OrderNo) FROM erp.SalesOrder)
   , 近似值      = (SELECT APPROX_COUNT_DISTINCT(OrderNo) FROM erp.SalesOrder);
GO

PRINT N'----- 05.2 ★ 陷阱：APPROX_COUNT_DISTINCT 在"极低基数"上反而更不准 -----';
PRINT N'  HyperLogLog 的误差是"相对误差"，基数=3 时 ±2% 的相对误差在计数上很难看，';
PRINT N'  而且它对小基数做了特殊估计，结果可能与真值差 1~2。所以：';
PRINT N'    - 枚举型维度（渠道/币种/状态，值 < 1000）→ 老实用 COUNT(DISTINCT)；';
PRINT N'    - 高基数（客户数/订单号/SKU）→ 用 APPROX_COUNT_DISTINCT。';
SELECT
     Channel_精确 = (SELECT COUNT(DISTINCT Channel) FROM crm.Interaction)
   , Channel_近似 = (SELECT APPROX_COUNT_DISTINCT(Channel) FROM crm.Interaction)
   , Tier_精确    = (SELECT COUNT(DISTINCT Tier) FROM crm.Customer)
   , Tier_近似    = (SELECT APPROX_COUNT_DISTINCT(Tier) FROM crm.Customer);
GO

PRINT N'----- 05.3 APPROX_PERCENTILE_CONT / DISC：珠宝客单价的分位数 -----';
PRINT N'  CONT = 连续插值（返回值可能是表里不存在的值），DISC = 离散取值（一定来自数据）。';
PRINT N'  场景：运营要看"客单价 P50 / P90 / P99"，精确版 PERCENTILE_CONT 要在全表上做';
PRINT N'        排序/聚合，万亿级非常贵；近似版是 t-digest 草图，单遍即可。';
PRINT N'';
PRINT N'  ★★ 陷阱（本机实测，值得单独记住）：APPROX_PERCENTILE_DISC 对 DECIMAL 列会报 402';
PRINT N'     「数据类型 decimal 和 decimal 在 approx_percentile_disc 运算符中不兼容」。';
PRINT N'     原因：DISC 要求**分位参数与 ORDER BY 列必须是同一浮点族类型**，';
PRINT N'           而 AmountUsd 是 DECIMAL —— 传十进制字面量、DECIMAL(n,m)、NUMERIC 全都报错。';
PRINT N'     实测结果：';
PRINT N'       APPROX_PERCENTILE_DISC(0.5)            + ORDER BY AmountUsd(DECIMAL) -> 402   失败';
PRINT N'       APPROX_PERCENTILE_DISC(CAST(0.5 AS FLOAT)) + ORDER BY AmountUsd(DECIMAL) -> 402   失败';
PRINT N'       APPROX_PERCENTILE_DISC(CAST(0.5 AS FLOAT)) + ORDER BY CAST(AmountUsd AS FLOAT) -> 成功';
PRINT N'       APPROX_PERCENTILE_DISC(0.5)            + ORDER BY Qty(INT)      -> 成功';
PRINT N'     结论：用 DECIMAL 列跑 DISC 时必须显式 CAST 列（以及参数）为 FLOAT；';
PRINT N'           而 APPROX_PERCENTILE_CONT **没有**这个限制，可以直接吃 DECIMAL。';
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT
     分位数      = N'P50 (中位客单价)'
   , 精确_CONT   = (SELECT DISTINCT CAST(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY AmountUsd) OVER () AS DECIMAL(38,2)) FROM rpt.OrgSalesFact)
   , 近似_CONT   = (SELECT CAST(APPROX_PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY AmountUsd) AS DECIMAL(38,2)) FROM rpt.OrgSalesFact)
   , 近似_DISC   = (SELECT CAST(APPROX_PERCENTILE_DISC(CAST(0.50 AS FLOAT))
                                  WITHIN GROUP (ORDER BY CAST(AmountUsd AS FLOAT)) AS DECIMAL(38,2))
                     FROM rpt.OrgSalesFact);
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

PRINT N'  补充：DISC 与 CONT 的差别直观展示（P50 附近）';
SELECT
     版本    = N'CONT（连续插值）'
   , 值      = CAST((SELECT APPROX_PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY AmountUsd) FROM rpt.OrgSalesFact) AS DECIMAL(38,2))
UNION ALL
SELECT N'DISC（离散取值，一定来自真实行）'
   , CAST((SELECT APPROX_PERCENTILE_DISC(CAST(0.50 AS FLOAT)) WITHIN GROUP (ORDER BY CAST(AmountUsd AS FLOAT)) FROM rpt.OrgSalesFact) AS DECIMAL(38,2));
GO

PRINT N'----- 05.4 一次拿到多个分位数（P50 / P90 / P99），看尾部偏差 -----';
SELECT
     分位   = N'P50'
   , 精确   = (SELECT DISTINCT PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY AmountUsd) OVER () FROM rpt.OrgSalesFact)
   , 近似   = (SELECT APPROX_PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY AmountUsd) FROM rpt.OrgSalesFact)
UNION ALL SELECT N'P90'
   , (SELECT DISTINCT PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY AmountUsd) OVER () FROM rpt.OrgSalesFact)
   , (SELECT APPROX_PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY AmountUsd) FROM rpt.OrgSalesFact)
UNION ALL SELECT N'P99'
   , (SELECT DISTINCT PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY AmountUsd) OVER () FROM rpt.OrgSalesFact)
   , (SELECT APPROX_PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY AmountUsd) FROM rpt.OrgSalesFact)
ORDER BY 分位;
GO

PRINT N'----- 05.5 分组近似：按品类看客单价中位数（GROUP BY + 近似函数）-----';
PRINT N'  说明：OrgSalesFact 没有国家列，所以这里按 CategoryId 分组，再 JOIN 品类维表。';
PRINT N'        金额跨币种已折算为 AmountUsd，所以分组近似是可比口径。';
GO
SELECT
     品类              = pc.CategoryName
   , 行数              = COUNT(*)
   , 近似中位单价USD   = CAST(APPROX_PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY f.AmountUsd) AS DECIMAL(38,2))
   , 近似P95单价USD    = CAST(APPROX_PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY f.AmountUsd) AS DECIMAL(38,2))
FROM rpt.OrgSalesFact f
JOIN erp.ProductCategory pc ON pc.CategoryId = f.CategoryId
GROUP BY pc.CategoryName
ORDER BY 行数 DESC;
GO

PRINT N'  对照：只抽 5% 再用近似函数分组 —— 精度够不够？';
GO
SELECT
     品类              = pc.CategoryName
   , 样本行数          = COUNT(*)
   , 近似中位单价USD   = CAST(APPROX_PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY f.AmountUsd) AS DECIMAL(38,2))
FROM rpt.OrgSalesFact f TABLESAMPLE SYSTEM (5 PERCENT)
JOIN erp.ProductCategory pc ON pc.CategoryId = f.CategoryId
GROUP BY pc.CategoryName
ORDER BY 样本行数 DESC;
GO


/* ==================================================================================================
   06  聚集列存(CCI)上的采样：★★ 本脚本最重要的性能结论
   --------------------------------------------------------------------------------------------------
   rpt.OrgSalesFact 上只有一个 CLUSTERED COLUMNSTORE 索引（CCI）。
   列存的物理单位是**行组(rowgroup)**，不是 8KB 页 —— 这带来两个必须知道的后果：

     (1) TABLESAMPLE 对列存表**基本不起作用**（实测，见 06.2）。
         查询计划里根本不会出现 "Table Sample" 算子，
         实际就是一次完整的 Clustered Index Scan，只是内部丢掉了少量行。
         —— 也就是说：你以为在采样省 IO，其实 IO 一点没省。

     (2) 列存要想"少读"，正确手段不是随机采样，而是**列裁剪 + 行组消除**：
         SELECT 只取需要的列，WHERE 用可下推的谓词（日期/分区/字典编码列），
         让存储引擎跳过整行组。这是精确的，比采样既快又准。

   ★ 结论：万亿级列存表上，采样是最后手段，不是第一手段。
           先做列裁剪和行组消除，再做预聚合，实在不行才考虑采样。
   ================================================================================================== */

PRINT N'';
PRINT N'===== 06 聚集列存上的采样（关键结论）=====';
GO

PRINT N'----- 06.1 事实表的物理结构：只有 CCI，没有行存 B 树 -----';
SELECT
     索引名     = i.name
   , 索引类型   = i.type_desc
   , 行组数     = rg.rowgroup_cnt
   , 总行数     = rg.total_rows
   , 说明       = N'列存按行组组织（本表 7 个 COMPRESSED 行组，约 71 万行/组）'
FROM sys.indexes i
CROSS APPLY (
    SELECT rowgroup_cnt = COUNT(*), total_rows = SUM(rg.total_rows)
    FROM sys.column_store_row_groups rg
    WHERE rg.object_id = i.object_id AND rg.state_description = N'COMPRESSED'
) rg
WHERE i.object_id = OBJECT_ID(N'rpt.OrgSalesFact') AND i.type > 0;
GO

PRINT N'----- 06.2 ★ 关键实测：TABLESAMPLE 在 CCI 表上不会真正减少 IO -----';
PRINT N'  先看行数：它确实"像"在采样（因为底层丢了一些行）：';
SELECT
     N'rpt.OrgSalesFact (CCI)' AS 表
   , N'10 PERCENT'             AS 采样率
   , 总行数 = (SELECT COUNT(*) FROM rpt.OrgSalesFact)
   , 采样行数 = (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (10 PERCENT));
GO
PRINT N'  再看真实读放大：采样查询与全表查询的 STATISTICS IO 几乎一样 —— 这才是问题所在。';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
PRINT N'  [a] 全表 COUNT(*)';
SELECT 全表行数 = COUNT(*) FROM rpt.OrgSalesFact;
GO
PRINT N'  [b] 采样 10% 的 COUNT(*)';
SELECT 采样行数 = COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (10 PERCENT);
GO
PRINT N'  [c] 采样 0.1% 的 COUNT(*) —— 采样率降到 1/100，IO 依然不变';
SELECT 采样行数 = COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (0.1 PERCENT);
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
PRINT N'  ★ 观察点：a / b / c 三条语句的 LOB 逻辑读取次数（列存的 "reads"）几乎相同。';
PRINT N'           这就是"列存上采样不省 IO"的直接证据：页级采样在行组粒度的存储上失效了。';
PRINT N'  执行计划佐证：CCI 表的采样计划里**没有** Table Sample 算子，只有 Clustered Index Scan。';
GO

PRINT N'----- 06.3 列存上真正有效的"少读"手段：列裁剪 + 行组消除 -----';
PRINT N'  [1] 列裁剪：只 SELECT 需要的列，列存按列读，不碰的列一点 IO 都不产生。';
SET STATISTICS IO ON;
GO
PRINT N'  [1-a] 只读 Qty 一列（宽表 1 列）';
SELECT 总件数 = SUM(Qty) FROM rpt.OrgSalesFact;
GO
PRINT N'  [1-b] 读 Qty + AmountUsd + OrderDate 三列';
SELECT 总件数 = SUM(Qty), 总金额 = CAST(SUM(AmountUsd) AS DECIMAL(38,2))
FROM rpt.OrgSalesFact WHERE OrderDate >= '2025-01-01';
GO
SET STATISTICS IO OFF;
GO
PRINT N'  [2] 行组消除：用可下推的谓词（日期范围）让存储引擎跳过整行组，精度 100%。';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT
     限定方式   = N'只取 2025 年（时间范围消除行组，精确无误差）'
   , 总金额USD  = CAST(SUM(AmountUsd) AS DECIMAL(38,2))
   , 行数       = COUNT(*)
FROM rpt.OrgSalesFact
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';
GO
PRINT N'  对比：同一口径走全年（不消除行组）';
SELECT
     限定方式   = N'不限定时间（全表行组）'
   , 总金额USD  = CAST(SUM(AmountUsd) AS DECIMAL(38,2))
   , 行数       = COUNT(*)
FROM rpt.OrgSalesFact;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
PRINT N'  结论：① 列裁剪 ② 行组消除 都是"精确的省钱"，应优先于采样。';
PRINT N'        ③ 采样在列存上甚至省不了 IO（06.2 已证），只适合"行数多到连列裁剪都扛不住"的极端场景。';
PRINT N'        ④ 万亿级生产环境的真正答案是：分区 + 列存 + 预聚合，采样只是补充手段。';
GO


/* ==================================================================================================
   07  分层采样（Stratified Sampling）：珠宝行业最重要的采样技巧
   --------------------------------------------------------------------------------------------------
   为什么必须分层：珠宝销售金额高度右偏（大头来自少数高单价品类/高净值客户）。
   不分层直接采，方差大、估计抖动；按"国家/品类"分层后，每个层内各自采样再汇总，
   同样的样本量能拿到更稳的估计。
   ================================================================================================== */

PRINT N'';
PRINT N'===== 07 分层采样 =====';
GO

PRINT N'----- 07.1 不分层 vs 分层：同样 1% 样本量，估计精度对比 -----';
PRINT N'  ★ 分层估计的正确公式（Horvitz-Thompson 扩展估计量）：';
PRINT N'      总量估计 = Σ_样本 ( 该行金额 × 该行的抽样权重 )';
PRINT N'      其中 权重 = 层内总行数 / 层内样本行数';
PRINT N'';
PRINT N'    ★★ 这里有个极易写错的点（本脚本第一版就写错了，实测差 23 倍）：';
PRINT N'       按"每层每 100 行取 1 行"抽样时，权重不是 100，而是 cnt / 层内实际样本行数。';
PRINT N'       本表每层 238095 行，实际取到 2381 行，所以权重 = 238095/2381 ≈ 99.9975，';
PRINT N'       而不是写死的 100。';
PRINT N'       错误写法： SUM(金额 × cnt) / 100           -> 差 23 倍（分母用了 100 而不是样本数）';
PRINT N'       正确写法： SUM(金额 × cnt / 层内样本行数)    -> 立刻贴近真值';
PRINT N'    下面两段都用同一个 1% 抽样比例，唯一差别是"是否按品类分层"。';
GO

PRINT N'  [A] 分层 1%（按 CategoryId 分层，权重 = 层内总行数 / 层内样本行数）';
WITH Ranked AS (
    SELECT
        f.CategoryId,
        f.AmountUsd,
        rn  = ROW_NUMBER() OVER (PARTITION BY f.CategoryId ORDER BY f.FactId),
        cnt = COUNT(*)     OVER (PARTITION BY f.CategoryId)
    FROM rpt.OrgSalesFact f
), Strat AS (
    SELECT CategoryId, AmountUsd, cnt
    FROM Ranked
    WHERE (rn % 100) = 0                    -- 每层每 100 行取 1 行
), Layer AS (
    SELECT
        CategoryId,
        samp_rows = COUNT(*),
        weight    = MAX(cnt) * 1.0 / COUNT(*)
    FROM Strat
    GROUP BY CategoryId
), Joined AS (
    SELECT s.AmountUsd, L.weight
    FROM Strat s JOIN Layer L ON L.CategoryId = s.CategoryId
)
SELECT
     方法         = N'分层 1%（Σ 样本金额 × 层权重）'
   , 样本行数     = COUNT(*)
   , 总量估计USD  = CAST(SUM(AmountUsd * weight) AS DECIMAL(38,2))
   , 真值USD      = CAST((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact) AS DECIMAL(38,2))
   , 误差百分比   = CAST(100.0 * ABS(SUM(AmountUsd * weight)
                        - (SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact))
                        / NULLIF((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact), 0) AS DECIMAL(10,4))
FROM Joined;
GO

PRINT N'  [A-反面] 展示写错权重的后果（与本脚本第一版一致）：';
WITH Ranked AS (
    SELECT f.CategoryId, f.AmountUsd,
           rn  = ROW_NUMBER() OVER (PARTITION BY f.CategoryId ORDER BY f.FactId),
           cnt = COUNT(*)     OVER (PARTITION BY f.CategoryId)
    FROM rpt.OrgSalesFact f
)
SELECT
     方法         = N'错误示例：SUM(金额 × cnt) / 100（把采样率当权重分母）'
   , 总量估计USD  = CAST(SUM(AmountUsd * cnt) / 100.0 AS DECIMAL(38,2))
   , 真值USD      = CAST((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact) AS DECIMAL(38,2))
   , 误差倍数     = CAST((SUM(AmountUsd * cnt) / 100.0)
                         / NULLIF((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact), 0) AS DECIMAL(18,2))
FROM Ranked WHERE (rn % 100) = 0;
GO

PRINT N'  [B] 不分层 1%（整表系统抽样 + 均值外推）';
WITH Sys AS (
    SELECT f.AmountUsd,
           rn = ROW_NUMBER() OVER (ORDER BY f.FactId)
    FROM rpt.OrgSalesFact f
)
SELECT
     方法         = N'系统采样 1%（不按品类分层，均值×N）'
   , 样本行数     = COUNT(*)
   , 总量估计USD  = CAST(AVG(AmountUsd) * 5000000.0 AS DECIMAL(38,2))
   , 真值USD      = CAST((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact) AS DECIMAL(38,2))
   , 误差百分比   = CAST(100.0 * ABS(AVG(AmountUsd) * 5000000.0
                        - (SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact))
                        / NULLIF((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact), 0) AS DECIMAL(10,4))
FROM Sys WHERE (rn % 100) = 0;
GO

PRINT N'  要点：分层法把 SUM 拆成"层内均值 x 层内行数"再相加，天然消掉了';
PRINT N'        "某些层被抽多、某些层被抽少"带来的偏差 —— 在偏态分布的数据上差别会更明显。';
PRINT N'  注意：本演示数据是**均匀合成**的（每个品类 23.8 万行、金额区间相同），';
PRINT N'        所以两种方法误差都很小。真实珠宝数据高度右偏，分层的优势会放大。';
GO

PRINT N'----- 07.2 按品类分层明细：看分层后各层的采样比是否一致 -----';
WITH Ranked AS (
    SELECT
        f.CategoryId,
        f.AmountUsd,
        rn  = ROW_NUMBER() OVER (PARTITION BY f.CategoryId ORDER BY f.FactId),
        cnt = COUNT(*)     OVER (PARTITION BY f.CategoryId)
    FROM rpt.OrgSalesFact f
)
SELECT
     品类             = pc.CategoryName
   , 层内行数          = MAX(r.cnt)
   , 样本行数          = COUNT(*)
   , 层内采样比百分比  = CAST(100.0 * COUNT(*) / NULLIF(MAX(r.cnt), 0) AS DECIMAL(10,4))
   , 层内样本均值USD   = CAST(AVG(r.AmountUsd) AS DECIMAL(38,2))
   , 层权重            = CAST(MAX(r.cnt) * 1.0 / COUNT(*) AS DECIMAL(18,4))
   , 层估计总量USD     = CAST(AVG(r.AmountUsd) * MAX(r.cnt) AS DECIMAL(38,2))   -- 等价于 均值 × 层内总行数
FROM Ranked r
JOIN erp.ProductCategory pc ON pc.CategoryId = r.CategoryId
WHERE (r.rn % 100) = 0
GROUP BY pc.CategoryName
ORDER BY 层内行数 DESC;
GO

PRINT N'----- 07.3 参数化采样：用动态 SQL 绕开"TABLESAMPLE 只吃字面量" -----';
GO
CREATE OR ALTER PROCEDURE dbo.usp_SampleSalesOrder
    @Percent FLOAT = 1.0,          -- 采样百分比（字面量限制的绕行方案）
    @MinRows BIGINT = 1000         -- 采样率下限保护：采样行数低于这个值就改全表精确算
AS
BEGIN
    SET NOCOUNT ON;
    IF @Percent IS NULL OR @Percent <= 0 OR @Percent > 100 SET @Percent = 1.0;

    DECLARE @sql NVARCHAR(400) =
        N'SELECT 采样方式 = N''TABLESAMPLE SYSTEM (''
                    + CAST(' + CAST(@Percent AS NVARCHAR(20)) + N' AS NVARCHAR(20)) + N'' PERCENT)'';';

    EXEC sys.sp_executesql @sql;

    DECLARE @pctLiteral NVARCHAR(20) = CAST(@Percent AS NVARCHAR(20));

    -- ★ 关键点：采样百分比必须是编译期字面量 -> 动态 SQL 拼进去
    SET @sql = N'SELECT 采样行数 = COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (' + @pctLiteral + N' PERCENT);';
    EXEC sys.sp_executesql @sql;

    SET @sql = N'SELECT 近似总金额USD = CAST(SUM(AmountUsd) AS DECIMAL(38,2)) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (' + @pctLiteral + N' PERCENT);';
    EXEC sys.sp_executesql @sql;
END
GO
PRINT N'  测试：0.5% 采样';
EXEC dbo.usp_SampleSalesOrder @Percent = 0.5;
GO
PRINT N'  测试：5% 采样';
EXEC dbo.usp_SampleSalesOrder @Percent = 5;
GO


/* ==================================================================================================
   08  业务场景实战：ERP / CRM / HR 三条线的"近似问答"
   ================================================================================================== */

PRINT N'';
PRINT N'===== 08 三业务场景 =====';
GO

/* -------------------------------------------------------------------------------------------------
   08.1  ERP：全球门店销售大盘（近似版本，用于实时大屏）
         大屏要求"秒级刷新"，允许 1~2% 误差 -> 采样 + 近似基数完全够用。
   ------------------------------------------------------------------------------------------------- */
PRINT N'----- 08.1 ERP 大盘：精确 vs 近似（同一份数据两种口径）-----';
PRINT N'  口径固定为 2% 采样；总量用「样本均值 × N」外推，N 从系统目录取实际行数，不写死。';
PRINT N'  ★ 注意：下面的变量声明、采样、精确值、误差对照必须在**同一个批**里（不能被 GO 切开），';
PRINT N'          否则变量出了批就失效，会报 137「必须声明标量变量」。';
GO
DECLARE @N_fact BIGINT = (SELECT SUM(p.rows) FROM sys.partitions p
                          WHERE p.object_id = OBJECT_ID(N'rpt.OrgSalesFact') AND p.index_id IN (0,1));
DECLARE @exact_sum DECIMAL(38,2) = (SELECT CAST(SUM(AmountUsd) AS DECIMAL(38,2)) FROM rpt.OrgSalesFact);
DECLARE @samp_rows BIGINT, @samp_avg DECIMAL(38,4);
-- ★ 同上：多变量赋值 + 可空实参的聚合，会输出「聚合或其他 SET 操作消除了 Null 值」提示（非错误）。
--    这里 AVG 作用在 2% 样本上；若某次采样恰好抽到 0 行，AVG 返回 NULL，提示即由此而来。
SELECT @samp_rows = COUNT(*), @samp_avg = AVG(ISNULL(AmountUsd, 0))
FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT);

PRINT N'  [精确口径]';
SELECT
     口径          = N'精确'
   , 销售额USD      = @exact_sum
   , 订单数         = COUNT(*)
   , 独立品类数     = COUNT(DISTINCT CategoryId)
   , 独立组织数     = COUNT(DISTINCT OrgUnitId)
   , 客单价P90      = CAST((SELECT DISTINCT PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY AmountUsd) OVER () FROM rpt.OrgSalesFact) AS DECIMAL(38,2))
FROM rpt.OrgSalesFact;

PRINT N'  [近似口径]：采 2% + APPROX_*';
SELECT
     口径          = N'近似'
   , 销售额USD      = CAST(@samp_avg * @N_fact AS DECIMAL(38,2))
   , 订单数         = CAST(1.0 * @samp_rows / 0.02 AS DECIMAL(38,0))
   , 独立品类数     = (SELECT APPROX_COUNT_DISTINCT(CategoryId) FROM rpt.OrgSalesFact)
   , 独立组织数     = (SELECT APPROX_COUNT_DISTINCT(OrgUnitId)  FROM rpt.OrgSalesFact)
   , 客单价P90      = (SELECT CAST(APPROX_PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY AmountUsd) AS DECIMAL(38,2)) FROM rpt.OrgSalesFact);

PRINT N'  [误差对照]：把两种口径的差异量化出来';
SELECT
     指标            = N'销售额USD'
   , 精确值          = @exact_sum
   , 近似值          = CAST(@samp_avg * @N_fact AS DECIMAL(38,2))
   , 误差百分比      = CAST(100.0 * ABS(@samp_avg * @N_fact - @exact_sum) / NULLIF(@exact_sum, 0) AS DECIMAL(10,4))
   , 采样行数        = @samp_rows
   , 采样比例实际值  = CAST(100.0 * @samp_rows / @N_fact AS DECIMAL(10,4));
GO

PRINT N'  结论：采样把 IO 降下来，但代价是 ~0.x% 的金额误差 —— 所以大屏可以，对账不行。';
GO

PRINT N'  [附] 用 STATISTICS IO 观察采样扫描与全表扫描的读差异：';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT 全表扫描行数 = COUNT(*) FROM rpt.OrgSalesFact;
GO
SELECT 采样扫描行数 = COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT);
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

/* -------------------------------------------------------------------------------------------------
   08.2  CRM：客户互动质量抽查
         场景：客服质检只抽 5% 工单看满意度分布 —— 典型的分层抽样（按渠道分层）。
   ------------------------------------------------------------------------------------------------- */
PRINT N'----- 08.2 CRM 质检抽样：按渠道分层的 5% 抽样 -----';
GO
WITH Ranked AS (
    SELECT
        i.Channel,
        i.Satisfaction,
        i.DurationMinutes,
        rn  = ROW_NUMBER() OVER (PARTITION BY i.Channel ORDER BY i.InteractionId),
        cnt = COUNT(*)     OVER (PARTITION BY i.Channel)
    FROM crm.Interaction i
)
SELECT
     渠道            = Channel
   , 该渠道总量      = MAX(cnt)
   , 抽中工单数      = COUNT(*)
   , 抽样比          = CAST(100.0 * COUNT(*) / NULLIF(MAX(cnt), 0) AS DECIMAL(10,4))
   , 平均满意度      = CAST(AVG(1.0 * Satisfaction) AS DECIMAL(10,4))
   , 平均时长分钟    = CAST(AVG(1.0 * DurationMinutes) AS DECIMAL(10,2))
   , 满意度_近似总量 = CAST(AVG(1.0 * Satisfaction) * MAX(cnt) AS DECIMAL(38,2))
FROM Ranked
WHERE (rn % 20) = 0                 -- 每层每 20 条取 1 条 = 5%
GROUP BY Channel
ORDER BY 该渠道总量 DESC;
GO

PRINT N'  对照：全量精确口径（CRM 只有 12000 行，精确很便宜；这里是为了看偏差）';
GO
SELECT
     渠道         = Channel
   , 全量工单数   = COUNT(*)
   , 平均满意度   = CAST(AVG(1.0 * Satisfaction) AS DECIMAL(10,4))
FROM crm.Interaction
GROUP BY Channel
ORDER BY 全量工单数 DESC;
GO

/* -------------------------------------------------------------------------------------------------
   08.3  HR：全球薪酬的近似分位数（合规视角下的快速体检）
         场景：跨 7 国看薪酬分布，精确版要按国别做多次排序；
               近似版一次扫描即可，用于"薪酬健康度"看板。
   ------------------------------------------------------------------------------------------------- */
PRINT N'----- 08.3 HR 薪酬近似体检：按国别的薪酬中位数与 P90 -----';
GO
SELECT
     国别          = e.CountryCode
   , 人数           = COUNT(DISTINCT e.EmployeeId)
   , 薪酬记录数      = COUNT(*)
   , 近似中位薪酬    = CAST(APPROX_PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY s.BaseSalary) AS DECIMAL(38,2))
   , 近似P90薪酬     = CAST(APPROX_PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY s.BaseSalary) AS DECIMAL(38,2))
FROM hr.SalaryHistory s
JOIN hr.Employee e ON e.EmployeeId = s.EmployeeId
GROUP BY e.CountryCode
ORDER BY 薪酬记录数 DESC;
GO

PRINT N'  注意：薪酬基数太小（几百行）时不要用近似，直接精确更快也更准：';
GO
SELECT
     国别   = e.CountryCode
   , 精确中位薪酬 = CAST((SELECT DISTINCT PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY s2.BaseSalary) OVER (PARTITION BY e2.CountryCode)
                           FROM hr.SalaryHistory s2 JOIN hr.Employee e2 ON e2.EmployeeId = s2.EmployeeId
                           WHERE e2.CountryCode = e.CountryCode) AS DECIMAL(38,2))
FROM hr.Employee e
GROUP BY e.CountryCode
ORDER BY e.CountryCode;
GO

PRINT N'  判据：行数 < 10 万时，"精确 + 合适索引"通常比"近似"更快且无误差。';
PRINT N'       近似函数的收益在行数上了千万以后才显现。';
GO


/* ==================================================================================================
   09  万亿级 / 高并发场景的近似查询设计
   ================================================================================================== */

PRINT N'';
PRINT N'===== 09 万亿级与高并发下的设计 =====';
GO

/* -------------------------------------------------------------------------------------------------
   09.1 大表规模推演：采样在"万亿"级别上的可行性
   ------------------------------------------------------------------------------------------------- */
PRINT N'----- 09.1 规模推演表（本地 500 万 -> 生产 1 万亿）-----';
SELECT * FROM (VALUES
     (1, N'500 万  (本地演示)',      CAST(5000000 AS BIGINT),    CAST(2.5  AS DECIMAL(10,2)),   N'精确聚合 ~0.1 秒，采样无必要')
   , (2, N'5 亿   (单区域生产)',     CAST(500000000 AS BIGINT),  CAST(250.0 AS DECIMAL(10,2)),  N'精确聚合 ~10 秒，大屏需采样或预聚合')
   , (3, N'1 万亿 (全球全量)',       CAST(1000000000000 AS BIGINT), CAST(500000.0 AS DECIMAL(10,2)), N'精确聚合不可接受；必须列存+分区+近似/预聚合')
) AS t(序号, 规模, 行数, 无索引列存精确聚合估算秒, 设计建议)
ORDER BY 序号;
GO

PRINT N'  说明：这里的秒数是基于"列存 ~500 万行/秒/核"的量级估算，仅用于说明量级差异。';
GO

/* -------------------------------------------------------------------------------------------------
   09.2 三层架构：把"精确"和"近似"放在正确的层
   ------------------------------------------------------------------------------------------------- */
PRINT N'----- 09.2 三层分离架构 -----';
SELECT * FROM (VALUES
     (1, N'① 预聚合层', N'rpt.MonthlySalesPreAgg / rpt.CategorySalesPreAgg',
        N'分钟~小时级刷新，覆盖 90% 常规报表', N'精确，不允许采样'),
     (2, N'② 近似层',   N'APPROX_COUNT_DISTINCT + APPROX_PERCENTILE_*（直接打事实表）',
        N'高基数去重、分位数，实时看板', N'近似，误差 <2%，标注在 UI 上'),
     (3, N'③ 采样层',   N'TABLESAMPLE / 分层抽样',
        N'探索性分析、质检抽查、超大表快速体检', N'近似，必须给出置信区间'),
     (4, N'④ 精确层',   N'全表扫描（列存）',
        N'财务对账、合规审计、最终口径', N'精确，可接受慢')
) AS t(层, 名称, 手段, 适用场景, 精度承诺)
ORDER BY 层;
GO

PRINT N'  ★ 铁律：财务口径（收入、税、存货计价）永远走第 ① / ④ 层；';
PRINT N'         采样与近似只服务于"探索、监控、预警"这类可以容忍偏差的场景。';
GO

/* -------------------------------------------------------------------------------------------------
   09.3 预聚合表：比采样更好的"省钱"方式（答案已经在表里）
   ------------------------------------------------------------------------------------------------- */
PRINT N'----- 09.3 走预聚合 vs 走事实表：同一个问题的两种成本 -----';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
PRINT N'  [贵] 直接打 500 万行事实表按月汇总';
SELECT
     SalesMonth = DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
   , SalesUsd   = CAST(SUM(AmountUsd) AS DECIMAL(38,2))
FROM rpt.OrgSalesFact
GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
ORDER BY SalesMonth;
GO
PRINT N'  [便宜] 打预聚合表（1175 行）';
SELECT
     SalesMonth
   , SalesUsd   = CAST(SUM(SalesUsd) AS DECIMAL(38,2))
   , OrderCnt   = SUM(OrderCnt)
FROM rpt.MonthlySalesPreAgg
GROUP BY SalesMonth
ORDER BY SalesMonth;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

/* -------------------------------------------------------------------------------------------------
   09.4 高并发：近似查询的并发友好性
   ------------------------------------------------------------------------------------------------- */
PRINT N'----- 09.4 高并发下的关键差异 -----';
SELECT * FROM (VALUES
     (1, N'内存占用', N'精确 COUNT(DISTINCT) 需要维护大哈希表（随基数线性增长）', N'APPROX_* 固定大小草图（HLL 约几 KB，t-digest 随分位数个数）')
   , (2, N'并发扩展性', N'高基数去重在并发下会争抢 tempdb 与内存授予', N'固定内存 -> 并发计划稳定，不易触发 RESOURCE_SEMAPHORE')
   , (3, N'执行时间', N'随行数线性增长，长跑查询占住 worker', N'单遍扫描，时间可控，快速释放 worker')
   , (4, N'计划缓存', N'字面量差异会撑爆计划缓存', N'参数化 + OPTION(RECOMPILE) 或 sp_executesql')
   , (5, N'隔离级别', N'长事务在 RCSI 下 still 要读一致性快照', N'近似读可接受 READ UNCOMMITTED 语义（配合 RCSI 更佳）')
) AS t(序号, 维度, 精确聚合, 近似聚合)
ORDER BY 序号;
GO

PRINT N'----- 09.5 高并发防雪崩：同一秒 1000 个看板请求打同一个近似查询 -----';
PRINT N'  模式：请求缓存（把近似结果写进一张小结果表，带 TTL），而不是让 1000 个会话各算一遍。';
GO
CREATE TABLE dbo.SampleResultLog (
    CacheKey     VARCHAR(120)  NOT NULL PRIMARY KEY,
    ResultValue  DECIMAL(38,2) NULL,
    ResultJson   NVARCHAR(1000) NULL,
    IsApprox     BIT           NOT NULL DEFAULT (1),
    ErrorPct     DECIMAL(10,4) NULL,
    ComputedAt   DATETIME2(0)  NOT NULL DEFAULT (SYSDATETIME()),
    TtlSeconds   INT           NOT NULL DEFAULT (300)
);
GO
PRINT N'  建好缓存表 dbo.SampleResultLog。生产写法示意（本脚本不真的起后台刷新）：';
PRINT N'      -- 伪代码';
PRINT N'      -- 1) 先查缓存： SELECT ... FROM dbo.SampleResultLog WHERE CacheKey=@k AND ComputedAt > DATEADD(SECOND,-TtlSeconds,SYSDATETIME());';
PRINT N'      -- 2) 未命中： 跑近似/采样查询 -> 写回缓存 -> 返回';
PRINT N'      -- 3) 用 sp_getapplock 保证"同一个 key 只有一个会话在刷新"，其余等结果';
PRINT N'  关键点：缓存的是"近似结果"，所以 TTL 可以很短（1~5 分钟），过期重算也很便宜。';
GO

/* -------------------------------------------------------------------------------------------------
   09.6 采样误差的记录与暴露（工程化要求）
   ------------------------------------------------------------------------------------------------- */
PRINT N'----- 09.6 把"这是近似值"显式记录下来（不要让近似冒充精确）-----';
GO
CREATE TABLE dbo.SampleEstimates (
    EstimateId   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    MetricName   NVARCHAR(100) NOT NULL,
    ExactValue   DECIMAL(38,4) NULL,
    ApproxValue  DECIMAL(38,4) NULL,
    AbsError     DECIMAL(38,4) NULL,
    ErrorPct     DECIMAL(18,6) NULL,
    Method       NVARCHAR(100) NOT NULL,
    SampleRate   DECIMAL(10,4) NULL,
    MeasuredAt   DATETIME2(0)  NOT NULL DEFAULT (SYSDATETIME())
);
GO

-- 用变量分步算，避免在一条 SELECT 里塞太多嵌套子查询（可读性 + 避免隐式转换问题）
DECLARE @ex_total  DECIMAL(38,4), @ap_total  DECIMAL(38,4);
DECLARE @ex_cust   DECIMAL(38,4), @ap_cust   DECIMAL(38,4);
DECLARE @ex_p90    DECIMAL(38,4), @ap_p90    DECIMAL(38,4);

SELECT @ex_total = CAST(SUM(AmountUsd) AS DECIMAL(38,4)) FROM rpt.OrgSalesFact;
SELECT @ap_total = CAST(AVG(AmountUsd) * 5000000.0 AS DECIMAL(38,4))
FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (2 PERCENT);

SELECT @ex_cust = CAST(COUNT(DISTINCT CustomerId) AS DECIMAL(38,4)) FROM erp.SalesOrder WHERE CustomerId IS NOT NULL;
SELECT @ap_cust = CAST(APPROX_COUNT_DISTINCT(CustomerId) AS DECIMAL(38,4)) FROM erp.SalesOrder WHERE CustomerId IS NOT NULL;

SELECT @ex_p90 = CAST(v AS DECIMAL(38,4))
FROM (SELECT DISTINCT v = PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY AmountUsd) OVER () FROM rpt.OrgSalesFact) x;
SELECT @ap_p90 = CAST(APPROX_PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY AmountUsd) AS DECIMAL(38,4)) FROM rpt.OrgSalesFact;

INSERT dbo.SampleEstimates (MetricName, ExactValue, ApproxValue, AbsError, ErrorPct, Method, SampleRate) VALUES
 (N'rpt.OrgSalesFact 总金额USD', @ex_total, @ap_total,
     ABS(@ex_total - @ap_total),
     CAST(100.0 * ABS(@ex_total - @ap_total) / NULLIF(@ex_total, 0) AS DECIMAL(18,6)),
     N'TABLESAMPLE SYSTEM 2% + 均值×N 外推', 2.0),
 (N'erp.SalesOrder 独立客户数', @ex_cust, @ap_cust,
     ABS(@ex_cust - @ap_cust),
     CAST(100.0 * ABS(@ex_cust - @ap_cust) / NULLIF(@ex_cust, 0) AS DECIMAL(18,6)),
     N'APPROX_COUNT_DISTINCT', 100.0),
 (N'rpt.OrgSalesFact 客单价P90', @ex_p90, @ap_p90,
     ABS(@ex_p90 - @ap_p90),
     CAST(100.0 * ABS(@ex_p90 - @ap_p90) / NULLIF(@ex_p90, 0) AS DECIMAL(18,6)),
     N'APPROX_PERCENTILE_CONT', 100.0);
GO

SELECT
     指标          = MetricName
   , 精确值        = ExactValue
   , 近似值        = ApproxValue
   , 绝对误差      = AbsError
   , 误差百分比    = ErrorPct
   , 方法          = Method
FROM dbo.SampleEstimates
ORDER BY EstimateId;
GO

PRINT N'  ★ 误差百分比这一列就是给产品和业务看的"免责声明"：';
PRINT N'     误差 <2%  -> 可以直接展示';
PRINT N'     误差 2~5% -> 展示时标注"约为"';
PRINT N'     误差 >5%  -> 提高采样率或退回精确计算。';
GO


/* ==================================================================================================
   10  高并发压测：同一个近似查询并发跑的稳定性观察
   --------------------------------------------------------------------------------------------------
   说明：本机是开发环境，这里不做真正的 1000 并发压测，
         而是把"近似查询的内存/时间稳定性"用可复现的方式演示出来。
   ================================================================================================== */

PRINT N'';
PRINT N'===== 10 并发友好性观察 =====';
GO

PRINT N'----- 10.1 近似查询的内存授予需求（比精确小得多）-----';
SELECT TOP 10
     QueryText      = SUBSTRING(t.text, 1, 90)
   , RequestedMB    = CAST(g.requested_memory_kb / 1024.0 AS DECIMAL(18,2))
   , GrantedMB      = CAST(g.granted_memory_kb   / 1024.0 AS DECIMAL(18,2))
   , WaitType       = r.wait_type
FROM sys.dm_exec_query_memory_grants g
LEFT JOIN sys.dm_exec_requests r ON r.session_id = g.session_id
OUTER APPLY sys.dm_exec_sql_text(g.sql_handle) t;
GO
PRINT N'  （若当前没有正在执行的查询，这里会是空集；跑了长期查询后再看。）';
GO

PRINT N'----- 10.2 当前是否存在内存授予等待（近似查询的主要并发风险）-----';
SELECT
     PoolType        = pool_id
   , TargetMB        = CAST(target_memory_kb    / 1024.0 AS DECIMAL(18,2))
   , AvailableMB     = CAST(available_memory_kb / 1024.0 AS DECIMAL(18,2))
   , GranteeCount    = grantee_count
   , WaiterCount     = waiter_count
   , TimeoutErrorCnt = timeout_error_count
FROM sys.dm_exec_query_resource_semaphores
WHERE pool_id IN (2);
GO
PRINT N'  判据：WaiterCount 长期 > 0 说明内存授予不足，此时精确聚合会排队甚至超时；';
PRINT N'       近似聚合因为授予需求小，受影响明显更小 —— 这是它在高并发下的核心价值。';
GO

PRINT N'----- 10.3 参数化：让近似查询的计划缓存不被字面量撑爆 -----';
PRINT N'  反面：每次都换采样率的字符串拼接 -> 计划缓存里堆积无数变体。';
PRINT N'  正面：把采样率归一化到有限档位（0.1 / 0.5 / 1 / 2 / 5），缓存命中率极高。';
SELECT * FROM (VALUES
     (N'0.1%', N'超大表首次体检 / 极速预览'),
     (N'0.5%', N'实时大屏（秒级刷新）'),
     (N'1%',   N'常规探索分析'),
     (N'2%',   N'看板默认档'),
     (N'5%',   N'需要较稳估计的抽样报告')
) AS t(采样率档位, 推荐用途);
GO


/* ==================================================================================================
   11  诊断与验证
   ================================================================================================== */

PRINT N'';
PRINT N'===== 11 验证 =====';
GO

PRINT N'  [V1] 采样行数应随采样率单调上升';
SELECT 档位 = N'1%',  行数 = COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (1 PERCENT)
UNION ALL SELECT N'5%',  COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (5 PERCENT)
UNION ALL SELECT N'20%', COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (20 PERCENT);
GO

PRINT N'  [V2] REPEATABLE 必须可复现（同种子两次结果必须相等）';
SELECT
     第一次 = (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (3 PERCENT) REPEATABLE (8888))
   , 第二次 = (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (3 PERCENT) REPEATABLE (8888))
   , 判定   = CASE WHEN (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (3 PERCENT) REPEATABLE (8888))
                     = (SELECT COUNT(*) FROM rpt.OrgSalesFact TABLESAMPLE SYSTEM (3 PERCENT) REPEATABLE (8888))
                  THEN N'通过' ELSE N'失败' END;
GO

PRINT N'  [V3] APPROX_COUNT_DISTINCT 在高基数上的误差应 < 5%';
PRINT N'       （加 WHERE CustomerId IS NOT NULL，明确"仅统计有客户号的订单"口径）';
SELECT
     指标       = N'erp.SalesOrder.独立客户数'
   , 精确值    = COUNT(DISTINCT CustomerId)
   , 近似值    = APPROX_COUNT_DISTINCT(CustomerId)
   , 误差百分比 = CAST(100.0 * ABS(COUNT(DISTINCT CustomerId) - APPROX_COUNT_DISTINCT(CustomerId))
                        / NULLIF(COUNT(DISTINCT CustomerId), 0) AS DECIMAL(10,4))
   , 判定      = CASE WHEN 100.0 * ABS(COUNT(DISTINCT CustomerId) - APPROX_COUNT_DISTINCT(CustomerId))
                          / NULLIF(COUNT(DISTINCT CustomerId), 0) <= 5 THEN N'通过' ELSE N'超差' END
FROM erp.SalesOrder
WHERE CustomerId IS NOT NULL;
GO

PRINT N'  [V4] APPROX_PERCENTILE_CONT 与精确 PERCENTILE_CONT 的偏差应 < 5%';
SELECT
     分位       = N'P90'
   , 精确值    = e.v
   , 近似值    = a.v
   , 误差百分比 = CAST(100.0 * ABS(e.v - a.v) / NULLIF(e.v, 0) AS DECIMAL(10,4))
   , 判定      = CASE WHEN 100.0 * ABS(e.v - a.v) / NULLIF(e.v, 0) <= 5 THEN N'通过' ELSE N'超差' END
FROM (SELECT v = (SELECT DISTINCT PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY AmountUsd) OVER () FROM rpt.OrgSalesFact)) e
CROSS JOIN (SELECT v = (SELECT APPROX_PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY AmountUsd) FROM rpt.OrgSalesFact)) a;
GO

PRINT N'  [V5] 分层采样估计应接近真值（误差 < 5%）';
PRINT N'       权重 = 层内总行数 / 层内样本行数（详见 07.1 的公式说明）';
SELECT
     方法         = N'分层 1% 估计（Σ 样本金额 × 层权重）'
   , 估计值USD    = CAST(SUM(AmountUsd * weight) AS DECIMAL(38,2))
   , 真值USD      = CAST((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact) AS DECIMAL(38,2))
   , 误差百分比   = CAST(100.0 * ABS(SUM(AmountUsd * weight)
                        - (SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact))
                        / NULLIF((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact), 0) AS DECIMAL(10,4))
   , 判定         = CASE WHEN 100.0 * ABS(SUM(AmountUsd * weight)
                        - (SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact))
                        / NULLIF((SELECT SUM(AmountUsd) FROM rpt.OrgSalesFact), 0) <= 5
                       THEN N'通过' ELSE N'超差' END
FROM (
    SELECT s.AmountUsd, L.weight
    FROM (
        SELECT f.CategoryId, f.AmountUsd,
               rn  = ROW_NUMBER() OVER (PARTITION BY f.CategoryId ORDER BY f.FactId),
               cnt = COUNT(*)     OVER (PARTITION BY f.CategoryId)
        FROM rpt.OrgSalesFact f
    ) s
    JOIN (
        SELECT CategoryId, weight = MAX(cnt) * 1.0 / COUNT(*)
        FROM (
            SELECT f.CategoryId,
                   rn  = ROW_NUMBER() OVER (PARTITION BY f.CategoryId ORDER BY f.FactId),
                   cnt = COUNT(*)     OVER (PARTITION BY f.CategoryId)
            FROM rpt.OrgSalesFact f
        ) r
        WHERE (r.rn % 100) = 0
        GROUP BY CategoryId
    ) L ON L.CategoryId = s.CategoryId
    WHERE (s.rn % 100) = 0
) q;
GO

PRINT N'  [V6] 小表上的低采样率应表现为"行数严重失真或无行"（验证采样在小表上不可靠）';
SELECT 轮次 = 1, 行数 = COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT)
UNION ALL SELECT 2, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT)
UNION ALL SELECT 3, COUNT(*) FROM erp.SalesOrder TABLESAMPLE SYSTEM (0.1 PERCENT);
PRINT N'       （期望：行数在个位数上下抖动，甚至出现 0 —— 这正是小表不能采样的原因）';
GO

PRINT N'  [V7] 估计误差登记表应已建立且有记录';
SELECT 记录数 = COUNT(*) FROM dbo.SampleEstimates;
GO

PRINT N'  [V8] 缓存结果表应已建立';
SELECT
     表名   = s.name + N'.' + t.name
   , 列数   = COUNT(c.column_id)
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
WHERE t.name = N'SampleResultLog'
GROUP BY s.name, t.name;
GO

PRINT N'  [V9] 事实表 -> 品类维表的连接链完整性（采样结果必须能回表到维表）';
SELECT
     检查项 = N'rpt.OrgSalesFact.CategoryId 关联 erp.ProductCategory 的悬空行数'
   , 悬空行 = COUNT(*)
FROM rpt.OrgSalesFact f
LEFT JOIN erp.ProductCategory pc ON pc.CategoryId = f.CategoryId
WHERE pc.CategoryId IS NULL;
GO

PRINT N'  [V10] TABLESAMPLE SYSTEM (n ROWS) 写法必须可用（正确的 ROWS 语法）';
SELECT 按行数采样_实际行数 = COUNT(*) FROM erp.SalesOrderLine TABLESAMPLE SYSTEM (2000 ROWS);
PRINT N'       （期望接近 2000，但因为按页抽取会有偏差）';
GO

PRINT N'  [V11] 维表永远精确，不参与采样';
SELECT
     国家数 = COUNT(*) FROM dbo.Country;
SELECT 门店数 = COUNT(*) FROM erp.Store;
SELECT 品类数 = COUNT(*) FROM erp.ProductCategory;
GO

PRINT N'  [V12] 大表体检';
SELECT
     表名      = s.name + N'.' + t.name
   , 行数      = SUM(p.rows)
   , 索引数    = (SELECT COUNT(*) FROM sys.indexes i WHERE i.object_id = t.object_id AND i.type > 0)
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
WHERE s.name + N'.' + t.name IN (N'rpt.OrgSalesFact', N'erp.SalesOrder', N'erp.SalesOrderLine',
                                 N'crm.Interaction', N'crm.Customer', N'hr.Employee', N'hr.SalaryHistory')
GROUP BY s.name, t.name, t.object_id
ORDER BY 行数 DESC;
GO


/* ==================================================================================================
   12  决策清单：什么时候采样 / 什么时候近似 / 什么时候必须精确
   ================================================================================================== */

PRINT N'';
PRINT N'===== 12 决策清单 =====';
GO

PRINT N'  ── 用「近似聚合 APPROX_*」的场景 ──';
PRINT N'    · 高基数去重（唯一客户数、唯一订单数、UV）计数：APPROX_COUNT_DISTINCT';
PRINT N'    · 分位数看板（P50/P90/P99 客单价、响应时长）：APPROX_PERCENTILE_*';
PRINT N'    · 结果用于监控、预警、趋势判断，允许 <2% 误差';
PRINT N'    · 并发高、内存授予紧张的执行环境';
GO

PRINT N'  ── 用「采样 TABLESAMPLE / 分层抽样」的场景 ──';
PRINT N'    · 探索性分析：先看看数据长什么样，不需要精确';
PRINT N'    · 超大表的"第一眼体检"（分布、异常值、空值率）';
PRINT N'    · 客服质检、门店巡检等"本来就只查一部分"的业务';
PRINT N'    · 需要给出置信区间、并接受"每次结果略有不同"';
GO

PRINT N'  ── 必须「精确」的场景（禁止近似）──';
PRINT N'    · 财务对账：收入、税额、应收账款、存货计价';
PRINT N'    · 合规与审计：监管报送、内控抽样以外的全量核对';
PRINT N'    · 薪酬与结算：员工工资、奖金、佣金';
PRINT N'    · 库存与成本：任何会影响账实相符的金额';
PRINT N'    · 小表（<10 万行）：精确比近似更快，没有理由近似';
GO

PRINT N'  ── 用「预聚合表」替代采样的场景 ──';
PRINT N'    · 报表口径固定、可提前算好（rpt.MonthlySalesPreAgg 这类）';
PRINT N'    · 要求精确、又要求快 —— 唯一正解是"提前算好"而不是"少算一点"';
PRINT N'    · 高并发大屏：查 1175 行的预聚合表 vs 打 500 万行事实表';
GO

PRINT N'  ── 工程化铁律 ──';
PRINT N'    ① 近似结果必须在 UI / API 上显式标注"近似"，并给出误差量级；';
PRINT N'    ② 任何采样都要写 REPEATABLE 种子，保证可复现、可对账；';
PRINT N'    ③ 采样率要有下限保护（低于 1000 行直接退回精确），避免"静默 0 行"；';
PRINT N'    ④ 把误差登记到 dbo.SampleEstimates 这类表里，长期监控偏差是否漂移。';
GO

PRINT N'';
PRINT N'################################################################################';
PRINT N'#  脚本执行完成';
PRINT N'#  核心结论：近似的价值 = 用可量化、可接受的误差，换取一个数量级的成本下降。';
PRINT N'#  前提是：误差被度量、被记录、被显式暴露。否则"近似"就是"不可信的精确"。';
PRINT N'################################################################################';
GO