74.Oracle数据库SQL开发之高级查询——处理空值和缺失值

来源：互联网发布：如何免费开淘宝网店编辑：程序博客网时间：2024/05/16 01:56

74.Oracle数据库SQL开发之高级查询——处理空值和缺失值

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49847113

1. 使用IS PRESENT

当数据单元指定的记录再MODEL子句执行之前存在，则ISPRESENT返回true.

store@PDB1> selectprd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1and 2 and emp_id = 21

model

partition by (prd_type_id)

dimension by (month,year)

measures (amount sales_amount) (

sales_amount[for month from 1 to 3increment 1,2004]=

case when sales_amount[currentv(),2003] ispresent then

round(sales_amount[currentv(),2003]*1.25,2)

else

end

)

order by prd_type_id,year,month;

PRD_TYPE_ID YEAR MONTH SALES_AMOUNT

--------------------- ---------- ------------

1 2003 1 10034.84

1 2003 2 15144.65

1 2003 3 20137.83

1 2003 4 25057.45

1 2003 5 17214.56

1 2003 6 15564.64

1 2003 7 12654.84

1 2003 8 17434.82

1 2003 9 19854.57

1 2003 10 21754.19

1 2003 11 13029.73

1 2003 12 10034.84

1 2004 1 12543.55

1 2004 2 18930.81

1 2004 3 25172.29

2 2003 1 1034.84

2 2003 2 1544.65

2 2003 3 2037.83

2 2003 4 2557.45

2 2003 5 1714.56

2 2003 6 1564.64

2 2003 7 1264.84

2 2003 8 1734.82

2 2003 9 1854.57

2 2003 10 2754.19

2 2003 11 1329.73

2 2003 12 1034.84

2 2004 1 1293.55

2 2004 2 1930.81

2 2004 3 2547.29

30 rowsselected.

2. 使用PRESENTV

如果cell引用的记录再MODEL子句执行之前就存在，那么PRESENTV(cell,expr1,expr2)返回表达式expr1。

store@PDB1> selectprd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1and 2 and emp_id = 21

model

partitionby (prd_type_id) dimension by (month,year)

measures(amount sales_amount) (

sales_amount[for month from 1 to 3 increment 1,2004] =presentv(sales_amount[currentv(),2003],

round(sales_amount[currentv(),2003]*1.25,2),0))

order byprd_type_id,year,month;

PRD_TYPE_ID YEAR MONTH SALES_AMOUNT

----------- ---------- ----------------------

1 2003 1 10034.84

1 2003 2 15144.65

1 2003 3 20137.83

1 2003 4 25057.45

1 2003 5 17214.56

1 2003 6 15564.64

1 2003 7 12654.84

1 2003 8 17434.82

1 2003 9 19854.57

1 2003 10 21754.19

1 2003 11 13029.73

1 2003 12 10034.84

1 2004 1 12543.55

1 2004 2 18930.81

1 2004 3 25172.29

2 2003 1 1034.84

2 2003 2 1544.65

2 2003 3 2037.83

2 2003 4 2557.45

2 2003 5 1714.56

2 2003 6 1564.64

2 2003 7 1264.84

2 2003 8 1734.82

2 2003 9 1854.57

2 2003 10 2754.19

2 2003 11 1329.73

2 2003 12 1034.84

2 2004 1 1293.55

2 2004 2 1930.81

2 2004 3 2547.29

30 rows selected.

3. 使用PRESENTNNV

如果cell引用的单元MODEL子句执行之前已经存在，并且该单元的值不为空，则PRESENTNNV(cell,expr1,expr2)返回表达式expr1.如果记录不存在，或单元值为空值，则返回表达式expr2.

4. 使用IGNORE NAV和KEEP NAV

IGNORE NAV返回值如下：

l 空值或缺失数字值时返回0

l 空值或缺失字符串值时返回空字符串

l 空值或缺失日期值时返回01-JAN-2000

l 其他所有数据库类型时返回空值

store@PDB1> selectprd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1and 2 and emp_id = 21

modelignore nav

partitionby (prd_type_id)

dimensionby (month,year)

measures(amount sales_amount) (

sales_amount[for month from 1 to 3 increment 1,2004]=

round(sales_amount[currentv(),2003]*1.25,2)

)

order byprd_type_id,year,month;

PRD_TYPE_ID YEAR MONTH SALES_AMOUNT

----------- ---------- ----------------------

1 2003 1 10034.84

1 2003 2 15144.65

1 2003 3 20137.83

1 2003 4 25057.45

1 2003 5 17214.56

1 2003 6 15564.64

1 2003 7 12654.84

1 2003 8 17434.82

1 2003 9 19854.57

1 2003 10 21754.19

1 2003 11 13029.73

1 2003 12 10034.84

1 2004 1 12543.55

1 2004 2 18930.81

1 2004 3 25172.29

2 2003 1 1034.84

2 2003 2 1544.65

2 2003 3 2037.83

2 2003 4 2557.45

2 2003 5 1714.56

2 2003 6 1564.64

2 2003 7 1264.84

2 2003 8 1734.82

2 2003 9 1854.57

2 2003 10 2754.19

2 2003 11 1329.73

2 2003 12 1034.84

2 2004 1 1293.55

2 2004 2 1930.81

2 2004 3 2547.29

30 rows selected.

0 0

74.Oracle数据库SQL开发之 高级查询——处理空值和缺失值

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49847113

1. 使用IS PRESENT

2. 使用PRESENTV

3. 使用PRESENTNNV

4. 使用IGNORE NAV和KEEP NAV

74.Oracle数据库SQL开发之高级查询——处理空值和缺失值