SQL: поиск квинтилей с помощью Partition By

ProgramBox

SQL: поиск квинтилей с помощью Partition By

Post author:admin
Запись опубликована:4 февраля, 2023
Post category:Вопросы по программированию

#sql #hive

#sql #улей

Вопрос:

У меня есть таблица транзакций t1 , которая выглядит следующим образом:

 store_id.   cust_id.   amount
100         1000        1.00
100         1000        2.05
100         1000        3.15
100         2000        5.00
100         2000        6.00
200         3000       16.00
200         3000       60.00
200         1000        6.00
300         1000        8.05
300         1000       15.15

У меня также есть справочная таблица t2 , которая связывает хранилища с одним или несколькими отраслевыми узлами:

 store_id.   peer_id
100         200
200         100
300         300

Вот код для создания таблиц выше:

 DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
    store_id int,
    cust_id int,
    amount float,
);
INSERT INTO t1 VALUES(100,1000,1.00);
INSERT INTO t1 VALUES(100,1000,2.05);
INSERT INTO t1 VALUES(100,1000,3.15);
INSERT INTO t1 VALUES(100,2000,5.00);
INSERT INTO t1 VALUES(100,2000,6.00);
INSERT INTO t1 VALUES(200,3000,16.00);
INSERT INTO t1 VALUES(200,3000,60.00);
INSERT INTO t1 VALUES(200,1000,6.00);
INSERT INTO t1 VALUES(300,1000,8.05);
INSERT INTO t1 VALUES(300,1000,15.15);

DROP TABLE IF EXISTS t2;
CREATE TABLE t2(
    store_id int,
    peer_id int
);

INSERT INTO t2 VALUES(100,200);
INSERT INTO t2 VALUES(200,100);
INSERT INTO t2 VALUES(300,300);

Я хотел бы присвоить каждому customer_id квинтилю на основе их общих расходов в определенном магазине и одноранговых узлах этого магазина. Что-то вроде этого:

 SELECT
    a.cust_id
    , b.store_id
    , b.peer_id
    , NTILE(5) OVER(PARTITION BY b.store_id, b.peer_id ORDER BY sum(a.amount) DESC) as quintile 
    ,sum(a.amount) as total
FROM t1 a
LEFT JOIN t2 b
ON a.store_id=b.store_id
GROUP BY a.cust_id, b.store_id, b.peer_id;

Однако этот код не работает, поскольку он присваивает каждому клиенту несколько квинтилей. Каков наилучший способ сделать это?

1. Почему вы разделяете (и группируете) по хранилищам и одноранговым узлам? Как вы думаете, что это дает? (Я считаю, что это ошибка, но я пытаюсь избежать предположений.) Кроме того, вам нужен один результат для каждого клиента или один результат для каждой комбинации клиент / магазин?

2. @MatBailie: да, вероятно, ошибка. То, что я пытаюсь сделать, это разделить сначала по store_id, а затем по peer_id, чтобы идентифицировать покупки либо в конкретном магазине, либо в его одноранговых сетях. Предположим, я делаю покупки в магазинах 100 и 200. Предположим, что хранилище 200 является одноранговым хранилищем 100 (но не обязательно наоборот). Затем, когда я вычисляю квинтили для магазина 100, я хотел бы включить обе мои покупки в магазин 100 и магазин 200.

3. Разделение по обоим разделам отделяет каждое хранилище / узел от других (предоставляя вам отдельные ntiles для каждой комбинации хранилища / узла). Вы хотите объединить результаты, а не разделять их. Приведенный ниже ответ не объединяет их, не разделяя их. (среди других изменений)

Ответ №1:

Я думаю, вам нужна агрегация:

 select t2.store_id, t1.customer_id, sum(t1.amount),
       ntile(5) over (partition by t1.store_id order by sum(t1.amount))
from t1 join
     t2
     on t1.store_id = t2.peer_id
group by t1.customer_id, t2.store_id;

Примечание: При этом предполагается, что хранилище является собственным узлом. Вам нужна дополнительная логика, если это не так.

Редактировать:

Наиболее эффективной модификацией было бы сделать хранилище своим собственным узлом. Вы также можете использовать эту логику:

 select t2.store_id, t1.customer_id, sum(t1.amount),
       ntile(5) over (partition by t1.store_id order by sum(t1.amount))
from t1 join
     t2
     on t1.store_id = t2.peer_id or t2.store_id = t1.store_id
group by t1.customer_id, t2.store_id;

Но or это приведет к снижению производительности.

Итак:

 select t1.store_id, t1.customer_id, sum(t1.amount),
       ntile(5) over (partition by t1.store_id order by sum(t1.amount))
from t1 join
     (select store_id, peer_id
      from t2
      union all
      select distinct store_id, store_id
      from t2
     ) t2
     on t1.store_id = t2.peer_id or t2.store_id = t1.store_id
group by t1.customer_id, t2.store_id;

Примечание: Предполагается, что все хранилища находятся внутри t2 . Второй подзапрос просто должен получить дополнительные строки, которые также могут использовать t1 или другую таблицу.

1. Спасибо. Как бы вы изменили его, если хранилище не является его собственным одноранговым узлом?

2. @caerus либо измените данные в t2, чтобы хранилище было его собственным одноранговым узлом, либо подделайте его, используя (select * from t2 UNION ALL select id, id FROM store_table) as t2 вместо t2 .

3. @Gordon Linoff спасибо, но не будут ли эти запросы выдавать ошибку, если t1.store_id ее нет в предложении GROUP BY?

4. @caerus Он должен быть t2.store_id в SELECT предложении -и- в PARTITION BY . Кроме того, последний запрос не должен OR содержать условие в предикате соединения.