# U0100068: GaussDB 中分析函数 FIRST_VALUE 不支持 IGNORE NULLS 语法
#### 描述
GaussDB 中分析函数 FIRST_VALUE 不支持 IGNORE NULLS 语法。
**数据库类型与版本**
- 源库类型与版本：UGO 支持的 Oracle 版本。
- 目标库类型与版本： GaussDB V2.0-8.100 以下版本。
**语法示例**
```
--1
SELECT a,b,FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a ORDER BY b desc) FROM first_value_t1;
--2
SELECT a,b,FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a ORDER BY b ) FROM first_value_t1;
--3
SELECT a,b,FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a) FROM first_value_t1;
;
```
#### 改造建议
1、场景1和场景2：去掉FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a order by b (desc))，其他查询列加ROW_NUMBER() OVER (PARTITION BY a order by b (desc) ) AS rn 组成一个cte子句；
2、主表和cte关联：select查询列加case when t.b is not null then FIRST_VALUE(r.b) OVER(PARTITION BY r.a ORDER BY r.b) else t.b end AS first_value_not_null
(1)如果升序排序else t.b改写为else r.b
3、where条件加t.a = r.a AND r.rn = 1。
4、场景3：FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a)不带排序:直接转换为：FIRST_VALUE(b) OVER(PARTITION BY a ORDER BY CASE WHEN b IS NOT NULL THEN 0 ELSE 1 END) 。
GaussDB改写后的脚本：
```
--1                    
WITH cte AS (SELECT a, b,ROW_NUMBER() OVER (PARTITION BY a ORDER BY b desc ) AS rn FROM first_value_t1 WHERE  b IS NOT NULL)
SELECT t.a,t.b,case when t.b is not null 
then FIRST_VALUE(r.b) OVER(PARTITION BY r.a ORDER BY r.b)
else t.b end AS first_value_not_null
FROM first_value_t1 t , cte r where t.a = r.a AND r.rn = 1;
--2
WITH cte AS (SELECT a, b,ROW_NUMBER() OVER (PARTITION BY a ORDER BY b  ) AS rn FROM first_value_t1 WHERE  b IS NOT NULL)
SELECT t.a,t.b,case when t.b is not null 
then FIRST_VALUE(r.b) OVER(PARTITION BY r.a ORDER BY r.b )
else r.b end AS first_value_not_null
FROM first_value_t1 t , cte r where t.a = r.a AND r.rn = 1;
--3
SELECT a,b,FIRST_VALUE(b)  OVER(PARTITION BY a  ORDER BY CASE WHEN b IS NOT NULL THEN 0 ELSE 1 END)  FROM first_value_t1;
```