Startseite Beiträge getaggt mit "GROUP BY"

Schlagwort: GROUP BY

[ABAP] Interne Tabellen: Schleifen mit FOR, THEN, WHILE, GROUPS, IN GROUP

Variante 1 (FOR … WHILE)

DATA(it_strings) = VALUE stringtab( ).

it_strings = VALUE #(
* For i = 1 To 10
                      FOR i = 1 WHILE i < 11
                      (
                        |{ i }|
                      )
                    ).

cl_demo_output=>display( it_strings ).

Variante 2 (FOR … WHILE mit Schrittweite) 

                        
DATA(it_strings) = VALUE stringtab( ).

it_strings = VALUE #(
* For i = 1 To 10 Step 2
                      FOR i = 1 THEN i + 2 WHILE i < 11
                      (
                        |{ i }|
                      )
                    ).

cl_demo_output=>display( it_strings ).

Variante 3 (NESTED FOR)

                        
SELECT * FROM sflight INTO TABLE @DATA(it_sflight).
SELECT * FROM spfli INTO TABLE @DATA(it_spfli).

DATA(it_strings) = VALUE stringtab(
* alle Einträge aus sflight ab 01.01.2013
                                    FOR <f> IN it_sflight INDEX INTO idxf WHERE ( fldate >= '20130101' )
* alle Einträge aus spfli mit den Schlüsseln wie in der Ergebnismenge des vorherigen FOR
                                      FOR <c> IN it_spfli INDEX INTO idxc WHERE ( carrid = <f>-carrid AND connid = <f>-connid )
                                      (
* Ausgabe als Stringtab
                                        |{ idxf } \| { idxc } \| { <c>-carrid } \| { <c>-connid } \| { <c>-airpfrom }|
                                      )
                                  ).

cl_demo_output=>display( it_strings ).

Variante 4 (FOR GROUPS, FOR … IN GROUP)

TYPES: BEGIN OF ty_mat,
         matnr TYPE matnr,
         mtart TYPE mtart,
         price TYPE kbetr,
       END OF ty_mat.

TYPES: ty_it_mat TYPE HASHED TABLE OF ty_mat WITH UNIQUE KEY matnr
                                             WITH NON-UNIQUE SORTED KEY key_mtart COMPONENTS mtart.

TYPES: BEGIN OF ty_mat_sum,
         mtart TYPE mtart,
         count TYPE i,
         price TYPE kbetr,
       END OF ty_mat_sum.

TYPES: ty_it_mat_sum TYPE HASHED TABLE OF ty_mat_sum WITH UNIQUE KEY mtart.

* Tabelle mit Materialien
DATA(it_mat) = VALUE ty_it_mat(
                                ( matnr = '1' mtart = 'ROH'  price = '1.56' )
                                ( matnr = '2' mtart = 'ROH'  price = '2.00' )
                                ( matnr = '3' mtart = 'NLAG' price = '3.10' )
                                ( matnr = '4' mtart = 'NLAG' price = '0.40' )
                                ( matnr = '5' mtart = 'NLAG' price = '4.10' )
                                ( matnr = '6' mtart = 'HALB' price = '1.00' )
                                ( matnr = '7' mtart = 'HALB' price = '0.10' )
                              ).

* Tabelle gruppiert nach Materialarten ohne 'HALB' und summierten Preisen
DATA(it_mat_sum) = VALUE ty_it_mat_sum(
                                        FOR GROUPS grp OF <mtart> IN it_mat WHERE ( mtart NE 'HALB' ) GROUP BY ( mtart = <mtart>-mtart size = GROUP SIZE )
                                        (
                                          mtart = grp-mtart " Materialart der Gruppe
                                          count = grp-size  " Anz. Elemente der Gruppe
                                          price = REDUCE #( " Summe über die Elemente der akt. Gruppe bilden
                                                            INIT p = '0.00'
                                                            FOR <m> IN GROUP grp WHERE ( mtart = grp-mtart )
                                                            NEXT p = p + <m>-price
                                                          )
                                        )
                                      ).

cl_demo_output=>display( it_mat_sum ).

Links

[ABAP] Daten effizient zu internen Tabellen hinzufügen

Variante 1 (HASHED TABLE)

TYPES: BEGIN OF ty_msgnr,
         msgnr TYPE t100-msgnr,
       END OF ty_msgnr.

DATA: it_t100 TYPE HASHED TABLE OF t100 WITH UNIQUE KEY sprsl arbgb msgnr.

SELECT * FROM t100
  INTO TABLE @it_t100
  UP TO 100000 ROWS.

DATA: it_msgnr TYPE HASHED TABLE OF ty_msgnr WITH UNIQUE KEY msgnr.

DATA(o_timer) = cl_abap_runtime=>create_hr_timer( ).
DATA(usec_start) = o_timer->get_runtime( ).

LOOP AT it_t100 ASSIGNING FIELD-SYMBOL(<s>).
* jedes Element auf Vorhandensein Prüfen und ggf. zur Liste hinzufügen
  IF NOT line_exists( it_msgnr[ msgnr = <s>-msgnr ] ).
    INSERT VALUE ty_msgnr( msgnr = <s>-msgnr ) INTO TABLE it_msgnr.
  ENDIF.
ENDLOOP.

DATA(usec_end) = o_timer->get_runtime( ).
DATA(usec) = CONV decfloat16( usec_end - usec_start ).
DATA(sec) = usec / 1000000.

WRITE: / 'Laufzeit:', sec, 's'.

Variante 2 (GROUP BY)

TYPES: BEGIN OF ty_msgnr,
         msgnr TYPE t100-msgnr,
       END OF ty_msgnr.

*DATA: it_t100 TYPE HASHED TABLE OF t100 WITH UNIQUE KEY sprsl arbgb msgnr.
DATA: it_t100 TYPE STANDARD TABLE OF t100 WITH DEFAULT KEY.

SELECT * FROM t100
  INTO TABLE @it_t100
  UP TO 100000 ROWS.

DATA: it_msgnr TYPE STANDARD TABLE OF ty_msgnr WITH DEFAULT KEY.

DATA(o_timer) = cl_abap_runtime=>create_hr_timer( ).
DATA(usec_start) = o_timer->get_runtime( ).

LOOP AT it_t100 ASSIGNING FIELD-SYMBOL(<s>) GROUP BY <s>-msgnr ASCENDING.
* jedes Element auf Vorhandensein Prüfen und ggf. zur Liste hinzufügen
  APPEND VALUE ty_msgnr( msgnr = <s>-msgnr ) TO it_msgnr.
ENDLOOP.

DATA(usec_end) = o_timer->get_runtime( ).
DATA(usec) = CONV decfloat16( usec_end - usec_start ).
DATA(sec) = usec / 1000000.

WRITE: / 'Laufzeit:', sec, 's'.

Variante 3 (am schnellsten: COLLECT)

TYPES: BEGIN OF ty_msgnr,
         msgnr TYPE t100-msgnr,
       END OF ty_msgnr.

* Messages lesen
SELECT * FROM t100
  INTO TABLE @DATA(it_t100)
  UP TO 100000 ROWS.

DATA: it_msgnr TYPE STANDARD TABLE OF ty_msgnr WITH DEFAULT KEY.

DATA(o_timer) = cl_abap_runtime=>create_hr_timer( ).
DATA(usec_start) = o_timer->get_runtime( ).

LOOP AT it_t100 ASSIGNING FIELD-SYMBOL(<s>).
* jedes Element auf Vorhandensein Prüfen und ggf. zur Liste hinzufügen
* COLLECT verwendet intern HASHED TABLES
  COLLECT VALUE ty_msgnr( msgnr = <s>-msgnr ) INTO it_msgnr.
ENDLOOP.

DATA(usec_end) = o_timer->get_runtime( ).
DATA(usec) = CONV decfloat16( usec_end - usec_start ).
DATA(sec) = usec / 1000000.

WRITE: / 'Laufzeit:', sec, 's'.

[ABAP] Gruppierung von LOOP-Elementen (LOOP, GROUP BY), Group-Member

Beispiel 1 (einfach)

TYPES: BEGIN OF ty_person,
         person_id TYPE char3,
         name      TYPE char10,
         city      TYPE char3,
       END OF ty_person.

TYPES: ty_it_persons TYPE STANDARD TABLE OF ty_person WITH DEFAULT KEY.

DATA(it_persons) = VALUE ty_it_persons( ( person_id = 'P01' name = 'Horst' city = 'HAM' )
                                        ( person_id = 'P02' name = 'Udo'   city = 'BER' )
                                        ( person_id = 'P03' name = 'Inge'  city = 'HAM' )
                                        ( person_id = 'P04' name = 'Edit'  city = 'BER' )
                                        ( person_id = 'P05' name = 'Agnes' city = 'HAM' )
                                        ( person_id = 'P06' name = 'Ede'   city = 'LEI' ) ).

* Gruppierung nach City
* Aufsteigend sortiert
* mit Members (Unterelemente)
LOOP AT it_persons ASSIGNING FIELD-SYMBOL(<c>) GROUP BY <c>-city ASCENDING.

  LOOP AT GROUP <c> ASSIGNING FIELD-SYMBOL(<p>).
    WRITE: / '  ', <p>-person_id, <p>-name, <p>-city.
  ENDLOOP.

* neue interne Tabelle aus der Gruppe generieren
  DATA(it_cp) = VALUE ty_it_persons( FOR <cp> IN GROUP <c> ( <cp> ) ).

  ...

  ULINE.
ENDLOOP.

Beispiel 2 (WHERE, ASCENDING, ASSIGNING FIELD-SYMBOL)

TYPES: BEGIN OF ty_person,
         person_id TYPE char3,
         name      TYPE char10,
         city      TYPE char3,
         street    TYPE char20,
       END OF ty_person.

TYPES: ty_it_persons TYPE STANDARD TABLE OF ty_person WITH DEFAULT KEY.

* Testdaten mit Städten und Personen
DATA(it_persons) = VALUE ty_it_persons( ( person_id = 'P01' name = 'Horst' city = 'HAM' street = 'Allee' )
                                        ( person_id = 'P02' name = 'Udo'   city = 'BER' street = 'Kudamm' )
                                        ( person_id = 'P03' name = 'Inge'  city = 'HAM' street = 'Allee' )
                                        ( person_id = 'P04' name = 'Edit'  city = 'BER' street = 'Müllerstr.' )
                                        ( person_id = 'P05' name = 'Agnes' city = 'HAM' street = 'Hauptstraße' )
                                        ( person_id = 'P06' name = 'Ede'   city = 'LEI' street = 'Zentralplatz' )
                                        ( person_id = 'P07' name = 'Ida'   city = 'LEI' street = 'Zentralplatz' )
                                        ( person_id = 'P08' name = 'Ulf'   city = 'LEI' street = 'Poststraße' )
                                        ( person_id = 'P09' name = 'Ingo'  city = 'BER' street = 'Uferweg' )
                                        ( person_id = 'P10' name = 'Heinz' city = 'LEI' street = 'Postplatz' )
                                        ( person_id = 'P11' name = 'Heini' city = 'HAM' street = 'Postweg' )
                                      ).

* Suchkriterien für die Liste
DATA(rg_city) = VALUE rseloption( ( sign = 'I' option = 'EQ' low = 'HAM' high = '' )
                                  ( sign = 'I' option = 'EQ' low = 'LEI' high = '' ) ).

* Personenliste anhand der Suchkriterien für city durchloopen
LOOP AT it_persons ASSIGNING FIELD-SYMBOL(<g>) WHERE city IN rg_city
  GROUP BY ( city  = <g>-city       " Gruppierung nach feld 'city'
             size  = GROUP SIZE     " Anz. Elemente der akt. Gruppe in <gp>
             index = GROUP INDEX )  " Index der Gruppe <gp>
  ASCENDING AS TEXT                 " Gruppen aufsteigend sortieren
  ASSIGNING FIELD-SYMBOL(<gp>).     " Zugriff auf die Gruppe über <gp>

  WRITE: / |       City: { <gp>-city  }|.
  WRITE: / |GROUP INDEX: { <gp>-index }|.
  WRITE: / | GROUP SIZE: { <gp>-size }|.
  WRITE: / |   sy-tabix: { sy-tabix }|.

* Unterelemente der Gruppe durchloopen
  LOOP AT GROUP <gp> ASSIGNING FIELD-SYMBOL(<pi>).
    WRITE: / |     sy-tabix: { sy-tabix }|.
    WRITE: / |         data: { <pi>-city } { <pi>-person_id } { <pi>-name } { <pi>-street } |.
  ENDLOOP.

  ULINE.

ENDLOOP.

Beispiel 3 (Hierarchie)

TYPES: BEGIN OF ty_person,
         person_id TYPE char3,
         name      TYPE char10,
         city      TYPE char3,
         street    TYPE char20,
       END OF ty_person.

TYPES: ty_it_persons TYPE STANDARD TABLE OF ty_person WITH DEFAULT KEY.

* Testdaten mit Städten und Personen
DATA(it_persons) = VALUE ty_it_persons( ( person_id = 'P01' name = 'Horst' city = 'HAM' street = 'Allee' )
                                        ( person_id = 'P02' name = 'Udo'   city = 'BER' street = 'Kudamm' )
                                        ( person_id = 'P03' name = 'Inge'  city = 'HAM' street = 'Allee' )
                                        ( person_id = 'P04' name = 'Edit'  city = 'BER' street = 'Müllerstr.' )
                                        ( person_id = 'P05' name = 'Agnes' city = 'HAM' street = 'Hauptstraße' )
                                        ( person_id = 'P06' name = 'Ede'   city = 'LEI' street = 'Zentralplatz' )
                                        ( person_id = 'P07' name = 'Ida'   city = 'LEI' street = 'Zentralplatz' )
                                        ( person_id = 'P08' name = 'Ulf'   city = 'LEI' street = 'Poststraße' )
                                        ( person_id = 'P09' name = 'Ingo'  city = 'BER' street = 'Uferweg' )
                                        ( person_id = 'P10' name = 'Heinz' city = 'LEI' street = 'Postplatz' )
                                        ( person_id = 'P11' name = 'Heini' city = 'HAM' street = 'Postweg' )
                                      ).

* Gruppierung nach City
* Aufsteigend sortiert
* mit Members (Unterelemente)
LOOP AT it_persons ASSIGNING FIELD-SYMBOL(<c>) GROUP BY <c>-city ASCENDING.
* Stadt ausgeben
  WRITE: / <c>-city.

* Gruppierung nach City->Straße
* Aufsteigend sortiert
* mit Members (Unterelemente)
  LOOP AT GROUP <c> ASSIGNING FIELD-SYMBOL(<s>) GROUP BY <s>-street ASCENDING.
    WRITE: / '  ', <s>-street.

    LOOP AT GROUP <s> ASSIGNING FIELD-SYMBOL(<p>).
      WRITE: / '    ', <p>-person_id, <p>-name.
    ENDLOOP.
  ENDLOOP.

  ULINE.
ENDLOOP.

Beispiel 4 (REFERENCE INTO mit und ohne MEMBERS)

TYPES: BEGIN OF ty_person,
         person_id TYPE char3,
         name      TYPE char10,
         city      TYPE char3,
       END OF ty_person.

* Sortierte Tabelle
TYPES: ty_it_persons TYPE SORTED TABLE OF ty_person WITH UNIQUE KEY person_id.

DATA(it_persons) = VALUE ty_it_persons( ( person_id = 'P01' name = 'Horst' city = 'HAM' )
                                        ( person_id = 'P02' name = 'Udo'   city = 'BER' )
                                        ( person_id = 'P03' name = 'Inge'  city = 'HAM' )
                                        ( person_id = 'P04' name = 'Edit'  city = 'BER' )
                                        ( person_id = 'P05' name = 'Agnes' city = 'HAM' )
                                        ( person_id = 'P06' name = 'Ede'   city = 'LEI' ) ).

* Gruppierung nach City
* Aufsteigend sortiert
* WITHOUT MEMBERS -> bessere Performance, aber kein Zugriff auf Unterelemente
LOOP AT it_persons ASSIGNING FIELD-SYMBOL(<c>)
  GROUP BY ( city  = <c>-city
             size  = GROUP SIZE
             index = GROUP INDEX )
  ASCENDING
  WITHOUT MEMBERS
  REFERENCE INTO DATA(o_city).

  WRITE: / |City: { o_city->city }, Size: { o_city->size }, Index: { o_city->index }|.
ENDLOOP.

ULINE.

* Gruppierung nach City
* Aufsteigend sortiert
* mit Members (Unterelemente)
LOOP AT it_persons ASSIGNING FIELD-SYMBOL(<c2>)
  GROUP BY ( city  = <c2>-city
             size  = GROUP SIZE
             index = GROUP INDEX )
  ASCENDING
  REFERENCE INTO DATA(o_city2).

  WRITE: / |City: { o_city2->city }, Size: { o_city2->size }, Index: { o_city2->index }|.

  LOOP AT GROUP o_city2 ASSIGNING FIELD-SYMBOL(<p>).
    WRITE: / '  ', <p>-person_id, <p>-name, <p>-city.
  ENDLOOP.
ENDLOOP.

Weiterführende Infos: Link und Link

[ABAP] OpenSQL: Verknüpfung von Tabelleninhalten über INNER JOIN, Zählung einzelner Datensatzgruppen (COUNT) 

TYPES: BEGIN OF ty_ttxid,
         tdobject TYPE ttxid-tdobject,
         tdid     TYPE ttxid-tdid,
         tdtext   TYPE ttxit-tdtext,
         count    TYPE i,
       END OF ty_ttxid.
       
DATA: it_ttxid TYPE STANDARD TABLE OF ty_ttxid WITH DEFAULT KEY.

* count ist die Zählvariable für die Elemente der Gruppe, welche unter GROUP BY definiert wurde
* es werden nur Datensätze gezählt, die auch vorhanden sind, d.h. es gibt keine Werte mit count = 0
SELECT ttxid~tdobject, ttxid~tdid, ttxit~tdtext, COUNT( * ) AS count
  INTO CORRESPONDING FIELDS OF TABLE @it_ttxid
  FROM ttxid
  INNER JOIN ttxit ON ( ttxid~tdobject = ttxit~tdobject AND ttxid~tdid = ttxit~tdid )
  INNER JOIN stxh ON ( stxh~tdobject = ttxid~tdobject AND stxh~tdid = ttxid~tdid )
  WHERE ttxid~tdobject LIKE @p_obj
    AND ttxid~tdid LIKE @p_tdid
    AND stxh~tdfdate IN @so_date
    AND ttxit~tdspras = @p_lang
  GROUP BY ttxid~tdobject, ttxid~tdid, ttxit~tdtext.