SQL Server 内置函数RAND()、CEILING()的应用

SQL Server 数学/统计函数使用

[TOC]

## 1.1. RAND 函数

### 1.1.1. 定义

**RAND()可以随机产生一个0至1之间的随机数**

示例：没有参数

```SQL
-- 直接执行3次
SELECT RAND()
--  结果1:0.0088524383111658
--  结果2:0.343443392823765
--  结果3:0.210903950196124
```

**RAND()函数传入一个一数值，那么这个数值将作为随机数生成器的种子，它随机的值将固定，并且会影响整个RAND()的执行**

示例：有参数

```SQL
-- 执行多次
SELECT RAND()
SELECT RAND()
SELECT RAND(1)
-- 结果:0.472241415009636/0.943996112912269/0.713591993212924

-- 执行多次
SELECT RAND()
SELECT RAND(1)
SELECT RAND()
-- 结果:0.943996112912269/0.713591993212924/0.472241415009636

-- 执行多次
SELECT RAND(1)
SELECT RAND()
SELECT RAND()
-- 结果:0.713591993212924/0.472241415009636/0.943996112912269
```

> 说明：上面的执行结果分析，当我们给RAND()函数一个种子之后，RAND(1)将不再是个随机数了，它固定了，并且它前后也有RAND()执行也被固定了、RAND(1) = 0.713591993212924，RAND(1)前一个RAND() = 0.943996112912269，RAND(1)后一个RAND() = 0.472241415009636

### 1.1.2. 应用

案例一：随机获取一个小于100保留两位小数的数值

```SQL
SELECT ROUND(RAND()*100,2)
```

案例二：随机排序

```SQL
SELECT 学生编号,姓名,性别 FROM 学生表 ORDER BY RAND()
```

## 1.2. CEILING 函数

### 1.2.1. 定义

**适用于：**SQL Server、Azure SQL 数据库、Azure SQL 托管实例、Azure SynapseAnalytics、Analytics Platform System (PDW)、Microsoft Fabric 中的 SQL 分析终结点、Microsoft Fabric 中的仓库

**作用：**此函数返回大于或等于指定数值表达式的最小整数。

**语法：**<mark>CEILING ( numeric_expression )</mark>

+ 参数：numeric_expression 精确数值或近似数值数据类型类别的表达式，可以是各类的数值类型，int、float、money、decimal等，也可以是数字格式的字符串（例如'3.14'）

+ 返回：结果类型与 numeric_expression 对应，对应关系如下表

> 备注：numeric_expression 是字符串类型的数值，CEILING 会先将字符串转成对应的数值类型，如果字符串不能转成数值则会报错

**示例：**

```SQL
DECLARE @num INT, @num1 INT, @num2 DECIMAL(18, 2), @num3 FLOAT, @num4 BIGINT, @num5 MONEY
SELECT @num = CEILING('4.5'),
       @num1 = CEILING('3'),
       @num2 = CEILING(18.88),
       @num3 = CEILING(18.8888888888888),
       @num4 = CEILING(16.6666666666666666666666),
       @num5 = CEILING(19.99999999999999999)
SELECT @num num,
       @num1 num1,
       @num2 num2,
       @num3 num3,
       @num4 num4,
       @num5 num5,
       CEILING($123.45) num6,
       CEILING($123.45) num7,
       CEILING($-123.45) num8,
       CEILING($0.0) num9
```

**示例结果：**

|num|num1|num2|num3|num4|num5|num6|num7|num8|num9|
|--|--|--|--|--|--|--|--|--|--|
|5|3|19.00|19|17|20.00|124.00|124.00|-123.00|0.00|

**错误示例：**

```SQL
SELECT CEILING('曹操')
-- Error：从数据类型 varchar 转换为 float 时出错。
------------------------------------------------------------------------------------
SELECT CEILING(GETDATE())
-- Error：不允许从数据类型 datetime 到 float 的隐式转换。请使用 CONVERT 函数来运行此查询。
```

## 1.3. COUNT 函数

```SQL
--
```

### 1.3.2. 遇到的问题

#### 1.3.2.1. COUNT() 函数中NULL参数说明

为什么在COUNT函数种直接传NULL会报错，而将NULL放在一个变量中传递不会报错，如下代码示例

```SQL
DECLARE @I INT = NULL
SELECT COUNT(0), COUNT(NULL), COUNT(@I)
```

> 说明：上面的代码在执行COUNT(NULL)时会提示如下信息
<span style="color:red">消息 8117，级别 16，状态 1，第 2 行
操作数数据类型 NULL 对于 count 运算符无效。</span>

这段代码之所以会有不同的表现，是因为在SQL里`COUNT`函数的`参数必须是列名或者表达式`，直接用`NULL`作为参数是不符合语法规则的，所以会报错。而变量`@I`在本质上属于表达式，尽管它的值为`NULL`，但语法层面是合规的，因此能够正常运行。

**下面为你详细分析：**

1. **直接用`COUNT(NULL)`会报错**：
   `COUNT`函数的作用是统计某列或者表达式里非空值的数量。`NULL`代表着没有值，它既不是列名，也不是合法的表达式，所以直接把`NULL`作为参数传递给`COUNT`函数，就会引发语法错误。

2. **通过变量赋值的方式能正常运行**：
   变量`@I`属于合法的表达式。即便它的值为`NULL`，`COUNT(@I)`实际上执行的操作是统计数据集中该变量对应的值不为`NULL`的行数。在这个例子中，由于`@I`的值是`NULL`，所以统计结果为0，这是符合预期的。

下面通过一个简单的例子来帮助你理解：

```sql
DECLARE @I INT = NULL;
DECLARE @J INT = 1;

-- 错误：直接使用NULL作为参数不符合语法规则
SELECT COUNT(NULL);

-- 正确：变量属于合法表达式，结果为0
SELECT COUNT(@I);

-- 正确：结果为1（因为@J的值为1，属于非空值）
SELECT COUNT(@J);
```

总结一下，`COUNT`函数的参数需要是列名或者表达式，直接使用`NULL`违反了这一语法要求，而使用变量则是符合语法的。