U0100068: GaussDB 中分析函数 FIRST_VALUE 不支持 IGNORE NULLS 语法

描述

GaussDB 中分析函数 FIRST_VALUE 不支持 IGNORE NULLS 语法。

数据库类型与版本

• 源库类型与版本：UGO 支持的 Oracle 版本。
• 目标库类型与版本： GaussDB V2.0-8.100 以下版本。

语法示例

-----------场景1：存在oder by  desc
SELECT a,b,FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a ORDER BY b desc) FROM first_value_t1;
-----------场景2：存在oder by 
SELECT a,b,FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a ORDER BY b ) FROM first_value_t1;
-----------场景3：不存在order by
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：存在oder by  desc
 ---GaussDB改写后：
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：存在oder by 
 ---GaussDB改写后：
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：不存在order by
 ---GaussDB改写后：
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;