GBase 8a Mpp Cluster集群产品性能优化篇之MAX OVER函数的改写

例1：

select

min(kpi_value) over (partition by a.kpi_id,a.brand_id,a.city_id order by floor( days(a.kpi_date) ) range between 1 preceding and 1 preceding ) as last_value

from a;

改写为：

select

(select min(kpi_value) from kpi_values aa where aa.kpi_id = a.kpi_id and aa.brand_id = a.brand_id and aa.city_id = a.city_id and aa.kpi_date = date_sub(a.kpi_date, INTERVAL 1 DAY)) as last_value from a order by a.kpi_id,a.brand_id,a.city_id,a.kpi_date

例2

select max(rn) over (PARTITION by product_no,imei order by first_time) rn from a;

改写为：

select

(select max(rn) from a aa where aa.product_no = a.product_no and aa.imei=a.imei and aa.first_time <= a.first_time)

from a order by a.product_no,a.imei,a.first_time;