如何有效地计算查询中的重复行？ [英] How to count duplicate rows in a query in efficient way?

问题描述

我正在SAP SQ01 / SQ02中创建一个报告，其目的是显示在指定时间范围内选择产品的次数。

I am creating a report in SAP SQ01/SQ02 whose purpose is to show the number of times a product was picked in the specified time frame.

数据为主要来自表LTAP。我只需要能够计算重复材料XYZ的行数并将其输出到额外的字段中并显示该值。另外，我希望此计数在指定的时间范围内发生，例如在过去30天内，这是为了确保它是最新的并且相关。

The data is mainly sourced from table LTAP. I just need to be able to count the number of rows Material XYZ is duplicated in and output the number into an extra field and show the value. Also I want this count to happen in a specified time frame, let's say in the last 30 days, this is to make sure it's up to date and relevant.

以下代码可以工作，但是速度太慢，只需要几秒钟就可以输出1种材料的计数，我需要报告根据计数输出前1000名。有没有更有效的方法来完成同一件事？

The below code works, but it's so slow, it takes a couple of seconds to output the count for just 1 material and I need the report to output top 1000 based on the count. Is there a more efficient way to accomplish the same thing?

数据标签

DATA : YEAR(4) TYPE N,
       MTH(2) TYPE N,
       DAY(2) TYPE N,
       YEAR1(4) TYPE N,
       MTH1(2) TYPE N,
       DAY1(2) TYPE N,
       FROM_DATE LIKE SY-DATUM,
       count1 like LTAP-UMREZ.
FIELD-SYMBOLS <fs_dtab> TYPE STANDARD TABLE.
DATA: sort_f1 TYPE fieldname.

初始化标签

sort_f1 = 'ltap-matnr'.

记录处理TAB

YEAR = SY-DATUM(4).
MTH = SY-DATUM+4(2).
DAY = SY-DATUM+6(2).
IF MTH eq 1.
    MTH1 = MTH + 11.
   ELSE.
    MTH1 = MTH - 1.
 ENDIF.
   IF MTH eq 1.
      YEAR1 = YEAR - 1.
        ELSE.
        YEAR1 = YEAR.
   ENDIF.
FROM_DATE(4) = YEAR1.
FROM_DATE+4(2) = MTH1.
FROM_DATE+6(2) = DAY.

选项卡后选择结束

ASSIGN ('%G00[]') TO <fs_dtab>.
IF <fs_dtab> IS ASSIGNED.
  SORT <fs_dtab> BY (sort_f1)
                 DESCENDING.
  DELETE ADJACENT DUPLICATES FROM <fs_dtab>
  COMPARING (sort_f1).
ENDIF.

附加字段代码

在LTAP中，字段UMREZ填充了数字 1，因此我在这里使用它来计算重复项。

In LTAP, field UMREZ is populated with number "1", so I am using it here to count the duplicates.

clear count.
Select sum( UMREZ ) as UMREZ
  from *LTAP into COUNT
  where *LTAP~MATNR eq LTAP-MATNR
  and *LTAP~QDATU GE from_date.

我希望报告能以几秒钟而不是几分钟的时间发布几个实质性代码。可以对替换代码或对当前代码的改进可以实现这一点非常感激。

I expect the report to come out in seconds rather than minutes for a couple of material codes. Alternative code or an improvement to the current one which could accomplish this will be very much appreciated.

推荐答案

DATA materials TYPE RANGE OF matnr.

SELECT
    matnr AS material,
    COUNT(*) AS count
  FROM ltap
  INTO TABLE @DATA(result)
  WHERE matnr IN materials
    AND qdatu >= @from_date
  GROUP BY matnr.

请确保 MATNR 。在当前的S / 4HANA中，有一个适合的 HW6 ，但它只能部署在SAP HANA上，并且可能在较早的版本中不可用。

Make sure there is an index on the column MATNR. In the current S/4HANA, there is one HW6 that fits, but it deploys on SAP HANA only and may not be available on older releases.

这篇关于如何有效地计算查询中的重复行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！