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;