SSAS - 生成模拟测试数据

在分析系统中，因为数据量太大，无法直观看出统计结果的正确性。在使用新技术时，需要使用简单的数据，来验证语句的结果。本文介绍SQL Server生成模拟数据的方法。

使用技术

一、静态数据：VALUES

1. 使用说明

• 格式：VALUES (列1值, 列2值, 列3值), (列1值2, 列2值2, 列3值2)…
• 命名：AS 表名(列1名, 列2名, 列3名)，列的数量和顺序必须和 VALUES 里的一致。
• 类型：SQL Server 会自动推断列的数据类型（如数字、字符串、小数），也可手动指定（如 CAST(‘产品A’ AS VARCHAR(20))）。

2. 示例

SELECT*FROM(VALUES(1,'张三','销售部',8000.00),(2,'李四','技术部',9500.50),(3,'王五','财务部',7800.80))ASEmployees(Id,Name,Department,Salary);

3. 结果

二、序列数据：master.dbo.spt_values

1. 使用说明
   利用 spt_values 生成时间序列：master.dbo.spt_values 中 type = ‘P’ 的记录包含连续的数字（0-2047），可以用它来生成 N 个月份的序列（对应 2024-2025 年）。

2. 示例

SELECTnumberASId,DATEADD(MONTH,number,'2024-01-01')AS[Date]FROMmaster.dbo.spt_valuesWHEREtype='P'ANDnumberBETWEEN0AND23

3. 结果

三、随机数字：ABS(CHECKSUM(NEWID()))

1. 使用说明

• ABS(CHECKSUM(NEWID()))：核心作用是生成一个随机的 32 位整数（非负数），常用来快速生成无规律的随机数，是 SQL Server 中生成随机整数的经典写法。
• NEWID()：创建一个全球唯一的UNIQUEIDENTIFIER类型值（GUID/UUID），格式如E2B5D86C-7B9F-4A8D-9876-1234567890AB。每次执行都会生成完全不同的值。
• CHECKSUM()：计算哈希值，对输入的任意值计算一个 32 位的整数校验和（哈希值），返回值是INT类型，范围为-2147483648到2147483647（可能是正数或负数）。
• ABS()：取绝对值

2. 示例

SELECTABS(CHECKSUM(NEWID())),ABS(CHECKSUM(NEWID())),ABS(CHECKSUM(NEWID()))

3. 结果

实际应用

一、生成2025年目标数据

1. 语句

SELECTDATEADD(MONTH,sv.number,'2025-01-01')ASDate,p.ProductId,ABS(CHECKSUM(NEWID()))%1000+100ASQuantity,(ABS(CHECKSUM(NEWID()))%1000+100)*10ASTotalFROMmaster.dbo.spt_values svCROSSJOIN(VALUES(101),(102),(103))ASp(ProductId)WHEREsv.type='P'ANDsv.numberBETWEEN0AND11ORDERBYDate,ProductId;

2. 结果

3. 说明

• 月份采用日期表示，以与实际完成数据维度对齐
• 数量通常改为固定值，以方便在测试时判断统计是否正确
• 用于SSAS命名查询，需去掉order by。