如何以有效的方式计算查询中的重复行? [英] 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'.

记录处理选项卡

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屋！