SAP HANA SQL для сравнения позиций

ВОПРОС: Как мне найти материалы в номерах документов, которые соответствуют хотя бы X-процентному соотношению (например,> = 50%), в других номерах документов?

СОЗДАТЬ ТАБЛИЦУ:

CREATE COLUMN TABLE "SCHEMA"."MYTABLE" 
(
     "DOCUMENT" NVARCHAR(10) DEFAULT '' NOT NULL ,
     "POSNR" NVARCHAR(6) DEFAULT '000000' NOT NULL ,
     "MATERIAL" NVARCHAR(40) DEFAULT '' NOT NULL,
PRIMARY KEY (
     "DOCUMENT",
     "POSNR")
     ) UNLOAD PRIORITY 5 AUTO MERGE 
;

ВСТАВИТЬ ДАННЫЕ:

INSERT INTO MYTABLE VALUES (100, '10', 'R3');
INSERT INTO MYTABLE VALUES (100, '20', '7000000');
INSERT INTO MYTABLE VALUES (100, '30', '7000010');
INSERT INTO MYTABLE VALUES (100, '40', '7000011');
INSERT INTO MYTABLE VALUES (100, '50', '7000160');

INSERT INTO MYTABLE VALUES (200, '10', 'SW');
INSERT INTO MYTABLE VALUES (200, '20', '7000000');
INSERT INTO MYTABLE VALUES (200, '30', '7000010');
INSERT INTO MYTABLE VALUES (200, '40', '7000011');
INSERT INTO MYTABLE VALUES (200, '50', '7000160');
INSERT INTO MYTABLE VALUES (200, '60', '7000036');
INSERT INTO MYTABLE VALUES (200, '70', '7000040');
INSERT INTO MYTABLE VALUES (200, '80', '7000066');
INSERT INTO MYTABLE VALUES (200, '90', '7000068');

INSERT INTO MYTABLE VALUES (300, '01', '7000160');
INSERT INTO MYTABLE VALUES (300, '11', '7000011');

INSERT INTO MYTABLE VALUES (400, '10', '7000033');
INSERT INTO MYTABLE VALUES (400, '20', '7000034');
INSERT INTO MYTABLE VALUES (400, '50', '7000068');
INSERT INTO MYTABLE VALUES (400, '60', '7000079');

sql hana hana-sql-script
person Robin P.    schedule 26.02.2020    source источник
comment
Предоставьте команды CREATE TABLE и INSERT, не указывайте, что в структуре данных нет двусмысленности.   -  person Lars Br.    schedule 26.02.2020
comment
@LarsBr. Я обновил свою первоначальную публикацию этой информацией. Спасибо   -  person Robin P.    schedule 26.02.2020
comment
Спасибо за определение таблицы и данных. Теперь вопрос не совсем ясен. Вы хотите найти документы, из которых X-процент от количества материалов используется совместно с другими документами? Пожалуйста, покажите также желаемый результат. Также: является ли какой-либо материал уникальным в одном документе? например Может ли материал 700000 встречаться в документе 100 только один раз?   -  person Lars Br.    schedule 27.02.2020
comment
@LarsBr. извините за путаницу. Материалы не уникальны, например документ 100 может содержать POSNR 60, материал 700000. В приведенном выше примере я хочу найти следующее: документ 100 имеет материал 700000, 700010, 700011, 700160, которые все 4x также находятся в документе 200 с 4x совпадениями ((100/9 ) * 4 = 44,4% совпадения), в документе 300 я нахожу 2x совпадения из 2x материала ((100/2) * 2 = 100% совпадение), в документе 400 я нахожу только 1x совпадение (7000068), что должно дайте мне (100/4) * 1 = 25% совпадения   -  person Robin P.    schedule 27.02.2020
comment
Добавленное описание проясняет требования намного лучше, но также означает, что мне нужно скорректировать свое решение. Отправлю, когда доберусь до него (в ближайшие пару дней). Между тем, было бы хорошо, если бы вы объяснили, следует ли здесь учитывать записи R3 и SW или каково правило включения материалов.   -  person Lars Br.    schedule 28.02.2020
comment
@LarsBr. Я отсутствовал, поэтому пока не мог это проверить. Но большое вам спасибо! Попробую на этой неделе :-)   -  person Robin P.    schedule 16.03.2020

Ответы (1)


arrow_upward
0
arrow_downward

Это действительно может быть решено без использования курсоров.

with doc_elements 
(document, material, material_cnt)  
as  (select distinct
          document
        , material
        , count( *) OVER
            (PARTITION BY document) as MATERIAL_CNT
    from
        mytable
    )  
, matched_materials 
(document_a, material, material_b_cnt, document_b, match_cnt)  
as  (select
         side_a.document as document_a
       , side_a.material
       , side_b.material_cnt as material_a_cnt
       , side_b.document doc_b
       , count(*) OVER
            (PARTITION BY side_a.document, side_b.document) as match_cnt
    from 
                        doc_elements side_a
        left outer join doc_elements side_b
                on   side_a.material = side_b.material
                and side_a.document != side_b.document
     where 
            side_b.document IS NOT NULL
    )      
select distinct
    document_a
  --, material
  , document_b
  , material_b_cnt
  , match_cnt
  , round((100/material_b_cnt)*match_cnt, 2) as match_pct
from 
    matched_materials
order by
    document_a
  , document_b;

Этот оператор возвращает:

DOCUMENT_A|DOCUMENT_B|MATERIAL_B_CNT|MATCH_CNT|MATCH_PCT|
----------|----------|--------------|---------|---------|
100       |200       |             9|        4|    44.44|
100       |300       |             2|        2|      100|

200       |100       |             6|        4|    66.67|
200       |300       |             2|        2|      100|
200       |400       |             4|        1|       25|

300       |100       |             6|        2|    33.33|
300       |200       |             9|        2|    22.22|

400       |200       |             9|        1|    11.11|

Для простоты я рассматривал R3 и SW как обычные материалы.
Вывод содержит только сопоставления документов, у которых есть хотя бы одно соответствие материала (см. side_b.document IS NOT NULL условие в matched_material общем табличном выражении).

Обратите внимание, что спецификация результата в комментариях содержала ошибку:
Документ 400 не имеет соответствия, поскольку материал 7000068 не является частью материалов для документа 100.

Я воспринял этот вопрос как подсказку, чтобы подробнее рассказать об этом решении, а также включил обзор производительности запросов и параметров настройки.

См. https://lbreddemann.org/matchmaker/ и https://lbreddemann.org/matchmaker-quick-quick/
для этого.

person Lars Br.    schedule 02.03.2020