Блог

Допустим, у меня есть таблица транзакций

 CREATE TABLE IF NOT EXISTS txn_raw (
transaction_id VARCHAR(60),
sport_label VARCHAR(300),
family_label VARCHAR(150),
item_label VARCHAR(150)
)
DISTKEY (the_transaction_id)
SORTKEY (the_transaction_id, sport_label, family_label, item_label)
;
COMMIT;
  

И я хочу оптимизировать следующий запрос для вычисления корреляции между элементами.

 SELECT 
a.sport_label as sport_label_a, 
a.family_label as family_label_a, 
a.dsm_label as dsm_label_a, 
b.sport_label as sport_label_b, 
b.family_label as family_label_b, 
b.dsm_label as dsm_label_b, 
count(distinct a.the_transaction_id) as txn_ab
FROM txn_raw a
JOIN txn_raw b 
on a.the_transaction_id=b.the_transaction_id
and a.sport_label != b.sport_label
and a.family_label != b.family_label
and a.item_label != b.item_label
group by 1,2,3,4,5,6
  

Я думаю создать временную таблицу, хранящую данные после объединения txn_raw с самим собой.
Затем запросите временную таблицу и выполните group by.

Есть ли лучший способ оптимизировать такого рода запросы?

Я бы предложил извлекать отдельные значения перед объединением, а не после:

 WITH r as (
      SELECT DISTINCT the_transaction_id, sport_label, family_label, item_label
      FROM txn_raw
     )
SELECT a.sport_label as sport_label_a, 
       a.family_label as family_label_a, 
       a.dsm_label as dsm_label_a, 
       b.sport_label as sport_label_b, 
       b.family_label as family_label_b, 
       b.dsm_label as dsm_label_b, 
       COUNT(*) as txn_ab
FROM r a JOIN 
     r b 
     ON a.the_transaction_id = b.the_transaction_id AND
        a.sport_label <> b.sport_label AND
        a.family_label <> b.family_label AND
        a.item_label <> b.item_label
GROUP BY 1,2,3,4,5,6;
  

Я также ожидаю, что фактическое JOIN условие, которое вы хотите, это:

 FROM r a JOIN 
     r b 
     ON a.the_transaction_id = b.the_transaction_id AND
        NOT (a.sport_label = b.sport_label AND
             a.family_label = b.family_label AND
             a.item_label = b.item_label
            )
  

То есть … любой из столбцов отличается, а не все из них.