手把手教你21c新特性(2)：大数据与数据仓库新特性
【转载声明】本文为转载文章，原作者「Dr.Henry」，版权归原作者所有。 原文链接：https://mp.weixin.qq.com/s/Hhp7k17hEPaXYYCHAJrXBg
公众号：杨建荣的学习笔记 · 作者：Dr.Henry · 发布：2022-05-25 23:59:51 · 原文链接
在今天的内容中，将重点介绍Oracle Database 21c 中旨在提高大数据和数据仓库工作负载性能的增强功能。包括在组内记录按位聚合函数，对Window框架子句、GROUPS 和 EXCLUDE 以及table表达式中的 WINDOW 子句进行操作的增强分析函数，通过CHECKSUM 聚合函数来检测表，通过SKEWNESS_POP 和 SKEWNESS_SAMP 聚合函数来衡量数据的不对称性，以及通过 KURTOSIS_POP 和 KURTOSIS_SAMP 聚合函数来衡量数据。
实验1：使用按位聚合函数
按位聚合函数在SQL中支持直接按位类型进行处理。通过消除不必要的数据移动和充分利用其他数据库功能(如并行处理)，这些新函数可以提高整体查询性能。
在这个实验中，将为您介绍如何在组内记录的位级上使用新的BIT_AND_AGG、BIT_OR_AGG和BIT_XOR_AGG按位聚合函数。BIT_AND_AGG、BIT_OR_AGG和BIT_XOR_AGG分别返回按位和、按位或和按位异或的操作结果。这些聚合可以对单个数字列或表达式执行运算。按位聚合操作的返回类型总是一个数字。
按位AND函数
按位AND运算是一种二进制运算，它取两个相等长度的二进制数，并对每一对“对应的位”进行逻辑与（AND）运算。如果比较位置的两个值都是1，则二进制表示结果中的值是1，否则结果为0。对两个数字应用BIT_AND_AGG函数。下面示例中，数值2对应二进制10，数值3对应二进制11，于是对于2和3使用BIT_AND_AGG，得到的二进制结果为10，对应十进制数2，所以下面的结果为2。
SQL> WITH x AS (SELECT 2 c1 FROM dual UNION ALL SELECT 3 FROM dual)SELECT BIT_AND_AGG(c1) FROM x;
BIT_AND_AGG(C1)--------------- 2按位OR函数
如果您已经掌握了上面的按位AND函数，那么按位OR函数也很容易理解，如果一对“位值”中有一个值为1，结果就为1，否则为0。因此对于下面的例子，2的二进制值为10,3的二进制值为11，于是按位OR函数处理后的结果为二进制值11，对应十进制值3。如果您对上面的说明不能很好地理解，建议您拿出纸笔，在纸上写出上面的算式，就会一目了然。
SQL> WITH x AS (SELECT 2 c1 FROM dual UNION ALL SELECT 3 FROM dual)SELECT BIT_OR_AGG(c1) FROM x;
BIT_OR_AGG(C1)-------------- 3按位XOR函数
这个函数可以用来检查对应“位”上的值是否相同，如果相同返回0，否则返回1。对于十进制的2和3，使用XOR函数，首先将它们转换为二进制的10和11，因为第一位都是1，所以XOR函数计算的结果为0，第二位一个是0一个是1，所以XOR得到的计算结果为1，最终结果为二进制01，对应十进制1。
SQL> WITH x AS (SELECT 2 c1 FROM dual UNION ALL SELECT 3 FROM dual)SELECT BIT_XOR_AGG(c1) FROM x;
BIT_XOR_AGG(C1)--------------- 1实验2：使用增强分析函数
在这个实验中，将为您展示如何从window框架子句GROUPS和EXCLUDE的新选项以及table表达式中的window子句中获得增强功能。
Window框架的GROUPS子句
关于Window框架是一个很有趣的功能，就好像在打印记录的时候，有一个隐形的窗口，通过滑动窗口，仅对窗口内的数据进行计算与处理。让我们首先看看表中的原始数据。
SQL> select * from trades order by tday;
ACNO TID TDAY TTYP AMOUNT TICK---------- ---------- --------- ---- ---------- ----123 1 07-APR-22 buy 1000 CSCO123 1 07-APR-22 buy 400 JNPR123 3 09-APR-22 buy 2000 SYMC123 4 09-APR-22 buy 1200 CSCO123 5 09-APR-22 buy 500 JNPR123 6 11-APR-22 buy 200 CSCO123 7 11-APR-22 buy 100 CSCO123 9 12-APR-22 buy 400 JNPR123 10 12-APR-22 buy 200 GOOG123 11 12-APR-22 buy 1000 JNPR123 12 12-APR-22 buy 4000 JNPR123 13 15-APR-22 buy 2000 HPQ
12 rows selected.现在我们想做这样的统计，按照ACNO分组（当前数据表中，只有ACNO=123的一组信息，您可以添加ACNO为其他的记录来观察语句生成的不同结果），然后按照TDAY升序排序，统计以当前记录位置算起，前三行及当前行的AMOUNT总和。
SQL> SELECT acno, tday, SUM (amount) OVER W FROM tradesWINDOW W AS (PARTITION BY acno ORDER BY tday ROWS BETWEEN 3 PRECEDING AND CURRENT ROW);
ACNO TDAY SUM(AMOUNT)OVERW---------- --------- ----------------123 07-APR-22 1000123 07-APR-22 1400123 09-APR-22 3400123 09-APR-22 4600123 09-APR-22 4100123 11-APR-22 3900123 11-APR-22 2000123 12-APR-22 1200123 12-APR-22 900123 12-APR-22 1700123 12-APR-22 5600123 15-APR-22 7200
12 rows selected.现在我们解释一下上面的结果，让我们看看第四行第三列的值为4600，它是原始表有前三行：1000、400、2000和当前行1200相加得到的。让我们看看第五行的4100，原始表的第2、3、4、5行：400、2000、1200和500相加得到的。
上面的结果中TDAY列的值相同，但是对应后面的SUM值不同，这让人看起来很困扰，如果我们想按照日期进行分组，每个日期都显示相同的值，而之前的window逻辑不变。我们可以通过如下代码实现。需要注意的是，下面代码中的GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW是按组算的，并不是之前按单行记录来计算的。仔细想想，下面的5100和5400是怎么来的。
SQL> SELECT acno, tday, SUM(amount) OVER W, COUNT(ticker) OVER WFROM tradesWINDOW W AS (PARTITION BY acno ORDER BY tday GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW);
ACNO TDAY SUM(AMOUNT)OVERW COUNT(TICKER)OVERW---------- --------- ---------------- ------------------123 07-APR-22 1400 2123 07-APR-22 1400 2123 09-APR-22 5100 5123 09-APR-22 5100 5123 09-APR-22 5100 5123 11-APR-22 5400 7123 11-APR-22 5400 7123 12-APR-22 11000 11123 12-APR-22 11000 11123 12-APR-22 11000 11123 12-APR-22 11000 11123 15-APR-22 11600 10
12 rows selected.Window框架的EXCLUDE子句
从字面意思上看EXCLUDE是排除的意思，但我们这里要介绍的是如何将EXCLUDE与window框架一起使用。首先让我们看看用来做测试的简单表。
SQL> SELECT * FROM t;
V---------- 1 1 3 5 5 5 6
7 rows selected.接下来我们就观察一下EXCLUDE与window框架的各种组合：
SQL> SELECT v,sum(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row,sum(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS the_group,sum(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties,sum(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_othersFROM tWINDOW o AS (ORDER BY v);
V CURRENT_ROW THE_GROUP TIES NO_OTHERS---------- ----------- ---------- ---------- ----------1 1 1 21 4 3 4 53 6 6 9 95 8 3 8 135 10 5 155 11 6 11 166 5 5 11 11
7 rows selected.
上面结果中的第一列v是原始值，current_row是当前行的前一行加上当前行的后一行，但是不包含当前行，我们看看上面结果的第二行current_row对应的4，它是原始记录的第一行（值为1）加上第三行(值为3)，但是不包含当前行（值为1）的值，4=1+3。the_group表示：当前行的前一行加上后一行，但是不包含当前组中的数据。我们看看上面结果第二行the_group列的那个3，在原始表中，它的前一行是1，后一行是3，但是前一行的1和当前行的值相同，属于同一组，所以这个前一行的1，不加入。最后得到3。EXCLUDE NO OTHERS是默认值，表示不删除。EXCLUDE TIES很有趣，以上面的情况为例，前一行加上后一行，如果有数据与当前行是同组的，那么将删除除了当前行以外的所有同组数据。比如结果倒数第三行TIES对应的那个5，对于这一行来说，它的前一行是5，后一行也是5，当前行还是5，按照规则，它们是一组的，那么删除除了当前以外所有的数据，那么只剩下当前行的值，为5。如果您觉得还是不太理解，我将前后行的参数由1改为2，您自己好好思考一下。比如想想，下面倒数第三行TIES列的那个14是怎么来的。
SQL> SELECT v,sum(v) OVER (o ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) AS current_row,sum(v) OVER (o ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE GROUP) AS the_group,sum(v) OVER (o ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE TIES) AS ties,sum(v) OVER (o ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE NO OTHERS) AS no_othersFROM tWINDOW o AS (ORDER BY v);
V CURRENT_ROW THE_GROUP TIES NO_OTHERS---------- ----------- ---------- ---------- ----------1 4 3 4 51 9 8 9 103 12 12 15 155 14 4 9 195 19 9 14 245 16 6 11 216 10 10 16 16
7 rows selected.上面我们使用的是ROWS BETWEEN，接下来我们看看稍微复杂的RANGE BETWEEN，这个RANGE解释起来有些麻烦。我们以下面结果中第四行CURRENT ROW列的那个16为例，EXCLUDE CURRENT ROW是不包含当前行的意思，与之前一样。而这个RANGE 1 PRECEDING1 和 1 FOLLOWING表示大于当前这行的V值 1，及小于这个当前V值1的范围，这样的V值是5，那么这个范围就是4到6。我们看看这行上一行（也可以认为是当前范围的上一个范围）的值是3，不在4到6的范围内，所以不考虑加总，当前行及下两行都是5，这三行都是5算作当前的range，再往下一range是6，满足4到6的范围，所以要加总进来，于是就有了5+5+6=16。我们再看看倒数第二行的那个16，倒数第二行的V值是5，它的上一个range对应的值是单个的3，不在4到6之间，所以不计入加总，它的下一个range是6，正好下一行的值是6，算入加总，于是就有了倒数第一行的这个6，加上当前range 3个5，但不算当前行的5，得到5+5+6=16，您自己尝试计算，倒数第一行在CURRENT_ROW的那个15是怎么计算出来的。
SELECT v,sum(v) OVER (o RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row,sum(v) OVER (o RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS the_group, sum(v) OVER (o RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties,sum(v) OVER (o RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_othersFROM tWINDOW o AS (ORDER BY v);
V CURRENT_ROW THE_GROUP TIES NO_OTHERS---------- ----------- ---------- ---------- ---------- 1 1 1 2 1 1 1 2 3 3 3 5 16 6 11 21 5 16 6 11 21 5 16 6 11 21 6 15 15 21 21
7 rows selected.
接下来我们可以将 GROUPS和EXCLUDE一起使用，我在这里不做解释，看看您是否可以自己找到它们的计算方法，小小的提示，之前的ROWS是简单的按行计算，RANGE是上下的范围，而GROUPS是按照什么呢？比如结果中第四行第二列的那个19，它的上一组V值是3，下一组V值是6，当前组有3个5，计算的时候不算当前行的5,19=3+5+5+6。您自己算算，最后一行第二例的那个15是怎么算出来。
SQL> SELECT v,sum(v) OVER (o GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row,sum(v) OVER (o GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS the_group,sum(v) OVER (o GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties,sum(v) OVER (o GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_othersFROM tWINDOW o AS (ORDER BY v);
V CURRENT_ROW THE_GROUP TIES NO_OTHERS---------- ----------- ---------- ---------- ----------1 4 3 4 51 4 3 4 53 17 17 20 205 19 9 14 245 19 9 14 245 19 9 14 246 15 15 21 21
7 rows selected.
实验3：使用CHECKSUM函数检测数据篡改
在这个实验中，将为您介绍如何使用CHECKSUM聚合函数来检测表中的更改。该函数可以应用于列、常量、绑定变量或涉及它们的表达式。支持除 ADT 和 JSON 之外的所有数据类型。表中行的顺序不影响检测结果。
关于数据的审计和防篡改，我们可以通过数据库选件或者AVDF等软件来实现。但如果您想通过在数据库内部查询的方式知道数据是否被篡改，就可以使用CHECKSUM函数。
让我们首先看看原始数据的CHECKSUM值。
SQL> SELECT CHECKSUM(amount_sold) FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
CHECKSUM(AMOUNT_SOLD)---------------------793409
通过查询，我们得到满足查询条件的amount_sold字段值的CHECKSUM为793409。接下来，让我们在另外一个Terminal对这张表中的数据做更新，然后提交更新。之后再次执行上面的CHECKSUM检查。
Connected to:Oracle Database 21c EE High Perf Release 21.0.0.0.0 - ProductionVersion 21.5.0.0.0
525 rows updated.
Commit complete.SQL> SELECT CHECKSUM(amount_sold) FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
CHECKSUM(AMOUNT_SOLD)--------------------- 835564
通过观察，相同的SQL查询，得到的CHECKSUM值与之前不同，说明数据已经被篡改。
那么接下来，我们看看，如果在一个会话中，我们修改数据之后，回滚，那么CHECKSUM值是否发生变化。
SQL> UPDATE sh.sales SET amount_sold = amount_sold*2 WHERE time_id='30-NOV-98';
525 rows updated.
SQL> SELECT CHECKSUM(amount_sold) FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
CHECKSUM(AMOUNT_SOLD)---------------------366195
SQL> rollback;
Rollback complete.
SQL> SELECT CHECKSUM(amount_sold) FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
CHECKSUM(AMOUNT_SOLD)---------------------835564
通过观察，我们发现如果我们对数据进行修改，不提交，然后查询CHECKSUM的值，这个值会发生变化，但是如果我们rollback，那么CHECKSUM的值又恢复成原来的值。
接下来让我们做另外一个查询，这是一个特殊情况。这个查询的结果只有一条记录。也就是说quantity_sold的唯一值只有一个1。
SQL> SELECT DISTINCT quantity_sold FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
QUANTITY_SOLD------------- 1SQL> SELECT CHECKSUM(quantity_sold) FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
CHECKSUM(QUANTITY_SOLD)----------------------- 0 在上面的查询中，我们得到的校验值为0，而0这个值，让我们无法判断数据是否发生篡改。
那么让我们在查询中添加上distinct，看看结果如何。
SELECT CHECKSUM(DISTINCT quantity_sold) FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
CHECKSUM(DISTINCTQUANTITY_SOLD)-------------------------------863352
这回的CHECKSUM值不再是0，可以进行区分了。
在下面的代码中，我们将quantity_sold的值由1修改为2，并且提交我们的修改。然后查询CHECKSUM值。
SQL> UPDATE sh.sales SET quantity_sold = 2;
918843 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT CHECKSUM(quantity_sold) FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
CHECKSUM(QUANTITY_SOLD)-----------------------0
SQL> SELECT CHECKSUM(DISTINCT quantity_sold) FROM sh.sales sJOIN sh.times t ON (s.time_id = t.time_id)WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
CHECKSUM(DISTINCTQUANTITY_SOLD)------------------------------- 65515通过观察发现，如果我们还是对quantity_sold进行CHECKSUM查询，我们依旧得到0，即便我们已经修改并提交，这并不能通过CHECKSUM函数看出数据是否被修改。但是如果我们加上distinct，我们发现CHECKSUM的值与之前不同，说明数据已经被篡改。所以如果当您看到CHECKSUM值为0时，您应该使用其他手段对数据进行校验，比如我们上面用的distinct。
接下来，让我们看看，如果在更新的过程中，我们将数据更新成NULL，通过CHECKSUM是否可以发现数据被篡改。这次，我们对字符串型的数据进行修改，我们将客户的邮件地址改成NULL，然后检查修改前后的CHECKSUM值。
首先，我们看看满足特定条件的记录有多少条，稍后我们将这些记录的email都改成NULL。
SQL> select count(cust_email) FROM sh.customers WHERE cust_income_level ='L: 300,000 and above';
COUNT(CUST_EMAIL)----------------- 1684我们发现有1684条记录满足我们的条件，接下来我们先查询一下CHECKSUM值。
SQL> SELECT CHECKSUM(cust_email) FROM sh.customers;
CHECKSUM(CUST_EMAIL)-------------------- 107013
SQL> SELECT CHECKSUM(DISTINCT cust_email) FROM sh.customers;
CHECKSUM(DISTINCTCUST_EMAIL)---------------------------- 227092接下来我们将满足我们特定条件的1684条记录的email都修改为NULL并提交。然后检查是不是修改成功。通过最后一行的查询，我们发现，没有记录打印出来，其实系统打印的是NULL。
SQL> UPDATE sh.customers SET cust_email = NULL WHERE cust_income_level ='L: 300,000 and above'; 2
1684 rows updated.
SQL> commit;
Commit complete.
SQL> SELECT DISTINCT cust_email FROM sh.customers WHERE cust_income_level ='L: 300,000 and above';
CUST_EMAIL--------------------------------------------------
现在让我们看看CHECKSUM值。
SQL> SELECT CHECKSUM(cust_email) FROM sh.customers;
CHECKSUM(CUST_EMAIL)-------------------- 577487
SQL> SELECT CHECKSUM(DISTINCT cust_email) FROM sh.customers;
CHECKSUM(DISTINCTCUST_EMAIL)---------------------------- 141231通过观察，两个CHECKSUM值都发生了变化，说明数据已经被篡改。
实验4：使用SKEWNESS函数测量数据中的不对称性
如果您和我一样，经常要处理数据科学相关的工作，或者您经常完成统计学相关的工作，那么您一定不会对数据的偏度陌生。虽然我们可以通过均值和方差了解数据集的数据分布情况，当如果要详细了解数据的分布，我们经常使用直方图和手动评估曲线。在下面的实验中，将为您介绍如何通过Oracle的偏度函数了解数据的分布。我们将为您介绍SKEWNESS_POP 和 SKEWNESS_SAMP 聚合函数，第一个函数用来计算总体偏度，第二个函数用来计算样本偏度。
让我们首先查看一下我们的数据集，其中HOUSE列表示房屋的类型。
SQL> SELECT * FROM houses;
HOUSE PRICE_BIG_CITY PRICE_SMALL_CITY PRICE_DAT---------- -------------- ---------------- ---------1 100000 10000 09-APR-221 200000 15000 10-APR-221 300000 25000 10-APR-221 400000 28000 11-APR-221 500000 30000 12-APR-221 600000 32000 12-APR-221 700000 35000 13-APR-221 800000 38000 13-APR-221 900000 40000 14-APR-222 2000000 1000000 15-APR-222 200000 20000 09-APR-222 400000 35000 10-APR-222 600000 55000 10-APR-222 800000 48000 11-APR-223 400000 40000 12-APR-223 500000 42000 12-APR-223 600000 45000 13-APR-223 700000 48000 13-APR-223 800000 49000 14-APR-22
19 rows selected.接下来我们按房屋类型进行分组，然后看看每组中的房屋数量。
SQL> SELECT house, count(house) FROM houses GROUP BY house ORDER BY 1;
HOUSE COUNT(HOUSE)---------- ------------1 92 5 3 5接下来让我们看看每种房型的总体偏度和样本偏度。
SQL> SELECT house, SKEWNESS_POP(price_big_city), SKEWNESS_POP(price_small_city) FROM housesGROUP BY house;
HOUSE SKEWNESS_POP(PRICE_BIG_CITY) SKEWNESS_POP(PRICE_SMALL_CITY)---------- ---------------------------- ------------------------------1 0 -.668640122 1.13841996 1.49637083 3 0 -.12735442SQL> SELECT house, SKEWNESS_SAMP(price_big_city), SKEWNESS_SAMP(price_small_city) FROM housesGROUP BY house;
HOUSE SKEWNESS_SAMP(PRICE_BIG_CITY) SKEWNESS_SAMP(PRICE_SMALL_CITY)---------- ----------------------------- -------------------------------1 0 -.810514222 1.69705627 2.230657933 0 -.18984876 在结果中，我们看到偏度为0的记录，表示它符合正态分布。
接下来，我们向表中添加更多的重复记录，然后我们观察它的总体偏度和样本偏度，看看这些值是否有变化。
SQL> INSERT INTO houses SELECT * FROM houses;
19 rows created.
SQL> /
38 rows created.
SQL> /
76 rows created.
SQL> /
152 rows created.
SQL> commit;
Commit complete.SQL> SELECT house, SKEWNESS_POP(price_big_city), SKEWNESS_POP(price_small_city) FROM housesGROUP BY house ORDER BY 1;
HOUSE SKEWNESS_POP(PRICE_BIG_CITY) SKEWNESS_POP(PRICE_SMALL_CITY)---------- ---------------------------- ------------------------------ 1 0 -.66864012 2 1.13841996 1.49637083 3 0 -.12735442
SQL> SELECT house, SKEWNESS_SAMP(price_big_city), SKEWNESS_SAMP(price_small_city) FROM housesGROUP BY house ORDER BY 1;
HOUSE SKEWNESS_SAMP(PRICE_BIG_CITY) SKEWNESS_SAMP(PRICE_SMALL_CITY)---------- ----------------------------- ------------------------------- 1 0 -.67569912 2 1.1602897 1.52511703 3 0 -.12980098
因为我们插入的是重复的值，所以总体的偏度没有变化，而样本偏度发生了变化，由原来的1.697变为1.160。
接下来让我们看看PRICE_BIG_CITY 和 PRICE_SMALL_CITY 列中唯一值的偏度。
SQL> SELECT house,SKEWNESS_POP(DISTINCT price_big_city) pop_big_city,SKEWNESS_SAMP(DISTINCT price_big_city) samp_big_city,SKEWNESS_POP(DISTINCT price_small_city) pop_small_city,SKEWNESS_SAMP(DISTINCT price_small_city) samp_small_cityFROM housesGROUP BY house;
HOUSE POP_BIG_CITY SAMP_BIG_CITY POP_SMALL_CITY SAMP_SMALL_CITY---------- ------------ ------------- -------------- --------------- 1 0 0 -.66864012 -.81051422 2 1.13841996 1.69705627 1.49637083 2.23065793 3 0 0 -.12735442 -.18984876让我们与不适用distinct的查询结果进行比较，看看偏度是否有很大不同？
SQL> SELECT house,SKEWNESS_POP(price_big_city) pop_big_city,SKEWNESS_SAMP(price_big_city) samp_big_city,SKEWNESS_POP(price_small_city) pop_small_city,SKEWNESS_SAMP(price_small_city) samp_small_cityFROM housesGROUP BY house;
HOUSE POP_BIG_CITY SAMP_BIG_CITY POP_SMALL_CITY SAMP_SMALL_CITY---------- ------------ ------------- -------------- --------------- 1 0 0 -.66864012 -.67569912 2 1.13841996 1.1602897 1.49637083 1.52511703 3 0 0 -.12735442 -.12980098大家通过观察可以发现，总体偏度不变，但是样本偏度发生了比较大的改变，关于其中的原理，建议大家去网路上检索关于偏度的统计学解释。
我们这里的总体偏度不变，是因为刚才我们插入的数据都是来自于表本身的。下面让我们插入一下不同的数据，然后再次观察总体偏度和样本偏度的变化。
SQL> INSERT INTO houses (house, price_big_city, price_small_city)SELECT house, price_big_city*0.5, price_small_city*0.1FROM houses WHERE house=1;
144 rows created.
SQL> /
288 rows created.
SQL> /
576 rows created.
SQL> /
1152 rows created.
SQL> /
2304 rows created.
首先我们看看数据的分布情况。我们发现房屋类型1的数量明显比其他两种房屋类型要多很多。
SQL> SELECT house, count(house) FROM houses GROUP BY house ORDER BY 1;
HOUSE COUNT(HOUSE)---------- ------------1 46082 803 80
接下来让我们看看数据的总体偏度与样本偏度。通过观察，房屋类型1的总体偏度也不再符合正态分布。
SQL> SELECT house,SKEWNESS_POP(price_big_city) pop_big_city,SKEWNESS_SAMP(price_big_city) samp_big_city,SKEWNESS_POP(price_small_city) pop_small_city,SKEWNESS_SAMP(price_small_city) samp_small_cityFROM housesGROUP BY house;
HOUSE POP_BIG_CITY SAMP_BIG_CITY POP_SMALL_CITY SAMP_SMALL_CITY---------- ------------ ------------- -------------- --------------- 1 2.57050631 2.57134341 5.7418481 5.74371797 2 1.13841996 1.1602897 1.49637083 1.52511703 3 0 0 -.12735442 -.12980098实验5：使用KURTOSIS函数测量数据的峰度
与刚才介绍的数据偏度一样，峰度也是用来描述数据分布的指标。关于峰度的具体介绍，在网路上有很多非常详尽的介绍材料。
首先依旧是让我们看看数据的基本情况。
SQL> SELECT * FROM houses;
HOUSE PRICE_BIG_CITY PRICE_SMALL_CITY PRICE_DAT---------- -------------- ---------------- ---------1 100000 10000 09-APR-221 200000 15000 10-APR-221 300000 25000 10-APR-221 400000 28000 11-APR-221 500000 30000 12-APR-221 600000 32000 12-APR-221 700000 35000 13-APR-221 800000 38000 13-APR-221 900000 40000 14-APR-222 2000000 1000000 15-APR-222 200000 20000 09-APR-222 400000 35000 10-APR-222 600000 55000 10-APR-222 800000 48000 11-APR-223 400000 40000 12-APR-223 500000 42000 12-APR-223 600000 45000 13-APR-223 700000 48000 13-APR-223 800000 49000 14-APR-22
19 rows selected.接下来让我们通过KURTOSIS_POP检查总体峰度，并使用KURTOSIS_SAMP检查样本峰度。
SQL> SELECT house, kurtosis_pop(price_big_city), kurtosis_pop(price_small_city) FROM houses GROUP BY house;
HOUSE KURTOSIS_POP(PRICE_BIG_CITY) KURTOSIS_POP(PRICE_SMALL_CITY)---------- ---------------------------- ------------------------------ 1 -1.23 -.7058169 2 -.212 .245200191 3 -1.3 -1.5417881
SQL> SELECT house, kurtosis_samp(price_big_city), kurtosis_samp(price_small_city) FROM houses GROUP BY house;
HOUSE KURTOSIS_SAMP(PRICE_BIG_CITY) KURTOSIS_SAMP(PRICE_SMALL_CITY)---------- ----------------------------- ------------------------------- 1 -1.2 -.201556 2 3.152 4.98080076 3 -1.2 -2.1671526
按照实验4的做法，我们先向数据集中加入重复的记录，然后看看总体峰度和样本峰度的变化。
SQL> INSERT INTO houses SELECT * FROM houses;
19 rows created.
SQL> /
38 rows created.
SQL> /
76 rows created.
SQL> /
152 rows created.SQL> SELECT house, KURTOSIS_POP(price_big_city), KURTOSIS_POP(price_small_city) FROM houses GROUP BY house ORDER BY 1; 2
HOUSE KURTOSIS_POP(PRICE_BIG_CITY) KURTOSIS_POP(PRICE_SMALL_CITY)---------- ---------------------------- ------------------------------ 1 -1.23 -.7058169 2 -.212 .245200191 3 -1.3 -1.5417881
SQL> SELECT house, KURTOSIS_SAMP(price_big_city), KURTOSIS_SAMP(price_small_city) FROM houses GROUP BY house ORDER BY 1; 2
HOUSE KURTOSIS_SAMP(PRICE_BIG_CITY) KURTOSIS_SAMP(PRICE_SMALL_CITY)---------- ----------------------------- ------------------------------- 1 -1.2309485 -.68809876 2 -.14695105 .340165838 3 -1.3061439 -1.5637533通过观察，我们发现总体峰度没有发生变化，而样本峰度发生了变化，关于背后的统计学原理，大家可以去网路检索。
接下来我们观察distinct值的峰度。
SQL> SELECT house,KURTOSIS_POP(DISTINCT price_big_city) pop_big_city,KURTOSIS_SAMP(DISTINCT price_big_city) samp_big_city,KURTOSIS_POP(DISTINCT price_small_city) pop_small_city,KURTOSIS_SAMP(DISTINCT price_small_city) samp_small_cityFROM housesGROUP BY house;
HOUSE POP_BIG_CITY SAMP_BIG_CITY POP_SMALL_CITY SAMP_SMALL_CITY---------- ------------ ------------- -------------- --------------- 1 -1.23 -1.2 -.7058169 -.201556 2 -.212 3.152 .245200191 4.98080076 3 -1.3 -1.2 -1.5417881 -2.1671526然后与未使用distinct进行对比。
SQL> SELECT house, KURTOSIS_POP(price_big_city) pop_big_city, KURTOSIS_SAMP(price_big_city) samp_big_city, KURTOSIS_POP(price_small_city) pop_small_city, KURTOSIS_SAMP(price_small_city) samp_small_city FROM houses GROUP BY house;
HOUSE POP_BIG_CITY SAMP_BIG_CITY POP_SMALL_CITY SAMP_SMALL_CITY---------- ------------ ------------- -------------- --------------- 1 -1.23 -1.2309485 -.7058169 -.68809876 2 -.212 -.14695105 .245200191 .340165838 3 -1.3 -1.3061439 -1.5417881 -1.5637533
与上面一样，让我们向数据表中插入新数据，并且这个新数据将引起数据的分布不平衡。
SQL> INSERT INTO houses (house, price_big_city, price_small_city) SELECT house, price_big_city*0.5, price_small_city*0.1 FROM houses WHERE house=1;
144 rows created.
SQL> /
288 rows created.
SQL> /
576 rows created.
SQL> /
1152 rows created.
SQL> /
2304 rows created.SQL> SELECT house, count(house) FROM houses GROUP BY house ORDER BY 1;
HOUSE COUNT(HOUSE)---------- ------------ 1 4608 2 80 3 80
重新观察数据的总体峰度和样本峰度。
SQL> SELECT house, KURTOSIS_POP(price_big_city) pop_big_city, KURTOSIS_SAMP(price_big_city) samp_big_city, KURTOSIS_POP(price_small_city) pop_small_city, KURTOSIS_SAMP(price_small_city) samp_small_city FROM houses GROUP BY house;
HOUSE POP_BIG_CITY SAMP_BIG_CITY POP_SMALL_CITY SAMP_SMALL_CITY---------- ------------ ------------- -------------- --------------- 1 9.12746931 9.13868421 33.7452495 33.7831972 2 -.212 -.14695105 .245200191 .340165838 3 -1.3 -1.3061439 -1.5417881 -1.5637533与上面未插入数据之前的峰度相比，房屋类型1的峰度发生了巨大的变化。
其实上面的峰度和偏度，可以通过Python很容易地实现，但是在同等的硬件环境下，使用数据库查询技术所用的资源要比Python少得多，所以返回的速度也会快很多。
今天的内容就到这里，将在下次的文章中为大家介绍Oracle Database 21c性能与高可用方面的新特性，感谢您的阅读，谢谢。
手把手系列文章：
手把手教你应用Oracle Database 19c新特性-创建和填充外部表到In-Memory
手把手教你:使用Oracle Data Science分析纽约民宿数据
手把手教你:使用Oracle AutoML进行预测（实战教程）
手把手教你OCI机器视觉（1）：通过控制台使用OCI Vision
编辑：殷海英
