SAP HANA |具有子句性能

Question

我们使用的是SAP HANA 1.0 SPS12。

我们有像下面这样的日度表-

从table_1中选择trans_date、文章、measure1、measure2

表的容量约为500万行

我们需要看到这样的数据-

select 'day-1',sum(measure1),sum(meaure2) from table1 where trans_date=add_days(current_date,-1) group by 'day-1'
union all
select 'day-2',sum(measure1),sum(meaure2) from table1 where trans_date>=add_days(current_date,-2) group by 'day-2' 
union all
select 'WTD',sum(measure1),sum(meaure2) from table1 where trans_date>=add_days(current_date,-7) group by 'WTD'
union all
select 'WTD-1',sum(measure1),sum(meaure2) from table1 where trans_date>=add_days(current_date,-15) and trans_Date <= add_days(current_date,-7) group by 'WTD-1'

MTD、MTD-1、MTD-2、YTD依此类推。

在性能方面，使用WITH子句并保存数据一年，然后根据时间范围拆分是不是更好？或者，对每个时间段使用单独的聚合更好，如上所示。

据我所知，在Oracle这样的RDBMS中，WITH子句实现结果并从内存中使用它。SAP HANA位于内存数据库本身中。在SAP HANA中使用WITH子句是否具有独特的性能优势？

使用WITH子句查询-

WITH t1 as
(
select trans_date,sum(measure1),sum(meaure2) from table1 where trans_date>=add_days(current_date,-365)
)
select 'day-1',sum(measure1),sum(meaure2) from t1 where trans_date=add_days(current_date,-1) group by 'day-1'
union all
select 'day-2',sum(measure1),sum(meaure2) from t1 where trans_date>=add_days(current_date,-2) group by 'day-2' 
union all
select 'WTD',sum(measure1),sum(meaure2) from t1 where trans_date>=add_days(current_date,-7) group by 'WTD'
union all
select 'WTD-1',sum(measure1),sum(meaure2) from t1 where trans_date>=add_days(current_date,-15) 
                                                  and trans_Date <= add_days(current_date,-7) 
                                                  group by 'WTD-1'

Answer 1

如果您关心性能，那么将数据放在一行中应该会更好：

select sum(case when trans_date = add_days(current_date, -1) then measure1 end) as measure1_day1,
       sum(case when trans_date = add_days(current_date, -1) then measure2 end) as measure2_day1,
       sum(case when trans_date = add_days(current_date, -2) then measure1 end) as measure1_day2,
       sum(case when trans_date = add_days(current_date, -2) then measure2 end) as measure2_day2,
       . . .       
from table1
where trans_date >= add_days(current_date, -15);

如果您确实需要单独行中的值，则可以在以后取消透视结果。

或者，您可以执行以下操作：

select days, sum(measure1), sum(measure2)
from (select 1 as days from dummy union all
      select 2 from dummy union all
      select 7 from dummy union all
      select 15 from dummy
     ) d left join
     table1 t
     on t.trans_date = add_days(current_date, - d.days)
group by days
order by days;