ABAP 7.4 SQL Window Expression

1. Application scenarios

• In use OpenSQL When fetching data ; To see each date Number of seats in first class The data of , Also get all the dates Number of seats in first class Average value
  

2. Code

• SQL Window Expression Use key words OVER
• PARTITION BY amount to OpenSQL Medium 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. effect

• Here's the picture ：seatsocc_f For each date Number of seats in first class The number of avg_seatsocc_f For all dates Number of seats in first class Average value
  

• Be careful 1.3153846153846153E+01 For scientific counting ; stay Excel The corresponding value in is 13.1538461538461
  
  

• The following code can be used to convert the data of scientific counting

  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.