SQL笔记-正态分布函数

妖狐艹你老母 2023-10-12 14:02 23阅读 0赞

### 1. 数据库正态分布函数的说明 ###

#### Oracle ####

Oracle数据库中的正态分布函数为`NORMALDIST`，其函数原型为：

NORMALDIST(x, mean, std [, cumulative])

其中，`x`为随机变量，`mean`为均值，`std`为标准差，`cumulative`为可选参数，表示是否计算累积分布函数（默认为`TRUE`）。

#### MySQL ####

MySQL数据库中的正态分布函数为`NORMDIST`，其函数原型为：

NORMDIST(x, mean, std)

其中，`x`为随机变量，`mean`为均值，`std`为标准差。

#### SQL Server ####

SQL Server数据库中的正态分布函数为`NORMDIST`，其函数原型为：

NORMDIST(x, mean, std)

其中，`x`为随机变量，`mean`为均值，`std`为标准差。

#### PostgreSQL ####

PostgreSQL数据库中的正态分布函数为`normal_cdf`，其函数原型为：

normal_cdf(x, mean, std)

其中，`x`为随机变量，`mean`为均值，`std`为标准差。

### 2. 多表正态分布函数的写法 ###

#### Oracle ####

-- 建表语句
    CREATE TABLE table1 (
      id NUMBER(10) PRIMARY KEY,
      value NUMBER(10)
    );
    
    CREATE TABLE table2 (
      id NUMBER(10) PRIMARY KEY,
      value NUMBER(10)
    );
    
    -- 插入数据
    INSERT INTO table1 VALUES (1, 10);
    INSERT INTO table1 VALUES (2, 20);
    INSERT INTO table1 VALUES (3, 30);
    
    INSERT INTO table2 VALUES (1, 5);
    INSERT INTO table2 VALUES (2, 15);
    INSERT INTO table2 VALUES (3, 25);
    
    -- 计算正态分布函数
    SELECT NORMALDIST(t1.value - t2.value, MEAN, STDDEV) AS result
    FROM table1 t1, table2 t2
    CROSS JOIN (
      SELECT AVG(value) AS MEAN, STDDEV(value) AS STDDEV
      FROM (
        SELECT value FROM table1
        UNION ALL
        SELECT value FROM table2
      ) t
    ) s;

#### MySQL ####

-- 建表语句
    CREATE TABLE table1 (
      id INT PRIMARY KEY,
      value INT
    );
    
    CREATE TABLE table2 (
      id INT PRIMARY KEY,
      value INT
    );
    
    -- 插入数据
    INSERT INTO table1 VALUES (1, 10);
    INSERT INTO table1 VALUES (2, 20);
    INSERT INTO table1 VALUES (3, 30);
    
    INSERT INTO table2 VALUES (1, 5);
    INSERT INTO table2 VALUES (2, 15);
    INSERT INTO table2 VALUES (3, 25);
    
    -- 计算正态分布函数
    SELECT NORMDIST(t1.value - t2.value, MEAN, STDDEV) AS result
    FROM table1 t1, table2 t2
    CROSS JOIN (
      SELECT AVG(value) AS MEAN, STDDEV(value) AS STDDEV
      FROM (
        SELECT value FROM table1
        UNION ALL
        SELECT value FROM table2
      ) t
    ) s;

#### SQL Server ####

-- 建表语句
    CREATE TABLE table1 (
      id INT PRIMARY KEY,
      value INT
    );
    
    CREATE TABLE table2 (
      id INT PRIMARY KEY,
      value INT
    );
    
    -- 插入数据
    INSERT INTO table1 VALUES (1, 10);
    INSERT INTO table1 VALUES (2, 20);
    INSERT INTO table1 VALUES (3, 30);
    
    INSERT INTO table2 VALUES (1, 5);
    INSERT INTO table2 VALUES (2, 15);
    INSERT INTO table2 VALUES (3, 25);
    
    -- 计算正态分布函数
    SELECT NORMDIST(t1.value - t2.value, MEAN, STDDEV) AS result
    FROM table1 t1, table2 t2
    CROSS JOIN (
      SELECT AVG(value) AS MEAN, STDEV(value) AS STDDEV
      FROM (
        SELECT value FROM table1
        UNION ALL
        SELECT value FROM table2
      ) t
    ) s;

#### PostgreSQL ####

-- 建表语句
    CREATE TABLE table1 (
      id SERIAL PRIMARY KEY,
      value INT
    );
    
    CREATE TABLE table2 (
      id SERIAL PRIMARY KEY,
      value INT
    );
    
    -- 插入数据
    INSERT INTO table1 (value) VALUES (10);
    INSERT INTO table1 (value) VALUES (20);
    INSERT INTO table1 (value) VALUES (30);
    
    INSERT INTO table2 (value) VALUES (5);
    INSERT INTO table2 (value) VALUES (15);
    INSERT INTO table2 (value) VALUES (25);
    
    -- 计算正态分布函数
    SELECT normal_cdf(t1.value - t2.value, MEAN, STDDEV) AS result
    FROM table1 t1, table2 t2
    CROSS JOIN (
      SELECT AVG(value) AS MEAN, STDDEV(value) AS STDDEV
      FROM (
        SELECT value FROM table1
        UNION ALL
        SELECT value FROM table2
      ) t
    ) s;