ABAP 7.4 SQL Window Expression

1. 应用场景

• 在用OpenSQL取数时；既要看到每个日期占据头等舱的座位数量的数据，也要取得所有日期占据头等舱的座位数量的平均值
  

2. 代码

• SQL Window Expression 使用关键词 OVER
• PARTITION BY 相当于OpenSQL中的Group By

  SELECT
      carrid,
      connid,
      fldate,
      seatsocc_f,
      SUM( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS sum_seatsocc_f,
      COUNT( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS count_seatsocc_f,
      MIN( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS min_seatsocc_f,
      MAX( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS max_seatsocc_f,
      AVG( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS avg_seatsocc_f
  FROM sflight
  WHERE carrid = 'AA'
   AND connid = '0064'
  ORDER BY carrid, connid
  INTO TABLE @DATA(lt_tab).
  
  cl_demo_output=>display( lt_tab ).
  

3. 效果

• 如下图：seatsocc_f 为每个日期占据头等舱的座位数量的数量 avg_seatsocc_f 为所有日期占据头等舱的座位数量的平均值
  

• 注意 1.3153846153846153E+01 为科学计数法；在Excel中对应的值为 13.1538461538461
  
  

• 可使用下面代码转换科学计数法的数据

  SELECT
      carrid,
      connid,
      fldate,
      seatsocc_f,
      SUM( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS sum_seatsocc_f,
      COUNT( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS count_seatsocc_f,
      MIN( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS min_seatsocc_f,
      MAX( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS max_seatsocc_f,
      AVG( seatsocc_f ) OVER( PARTITION BY carrid, connid ) AS avg_seatsocc_f
  FROM sflight
  WHERE carrid = 'AA'
   AND connid = '0064'
  ORDER BY carrid, connid
  INTO TABLE @DATA(lt_tab).
  
  LOOP AT lt_tab ASSIGNING FIELD-SYMBOL(<fs_tab>).
    DATA l_char             TYPE cha_class_view-sollwert.
  
    CALL FUNCTION 'QSS0_FLTP_TO_CHAR_CONVERSION'
      EXPORTING
        i_number_of_digits       = 8
        i_fltp_value             = <fs_tab>-avg_seatsocc_f
        i_value_not_initial_flag = 'X'
        i_screen_fieldlength     = 16
      IMPORTING
        e_char_field             = l_char.
  
    WRITE: / l_char.
  ENDLOOP.