@Newageman

Как составить MySQL запрос для начисления процента?

Помогите, пожалуйста, правильно составить такой запрос: Вывести всех продавцов, у которых есть 5 пар клиентов и посчитать 10% от суммы покупок этих клиентов (для теста можно кол-во пар сократить). Вот какой код сейчас есть:
SELECT umr.af_id, 
    (FLOOR(COUNT(*) / 5) * (SELECT SUM(amount_value * 0.1) 
    FROM orders io WHERE io.status='Completed')) AS amount
FROM orders io
LEFT JOIN u_af ua on io.uid = ua.uid 
LEFT JOIN arur umr on umr.af_id = ua.id
GROUP BY af_id
HAVING COUNT(*) MOD 5 = 0;

Но он выводит что-то неадекватное.
Таблицы и код можно посмотреть по ссылке: ссылка.
Помогите, пожалуйста, составить этот запрос.
UPD1:
Как я вижу решение:
Взять af_id из таблицы arur
Вычислить все r_w_uid относящиеся к этому af_id из таблицы arur
Подсчитать их количество.
Как только общее количество r_w_uid достигнет, например, 6, то суммировать значения amount_value из таблицы orders относящиеся к uid из таблицы orders при условии status = Completed.
Но я могу с лёгкостью ошибаться.
Умножить полученное в предыдущем пункте значение на 0.1 и вывести значение.
UPD2:
Связи таблиц:
orders.uid = u_af.uid
u_af.id = arur.af_id

UPD3:
Описания таблиц:
1)
orders - таблица с информацией о платежах.
orders.uid - столбец с внутренним id пользователя.
orders.amount_value - столбец с суммой платежа.
orders.status - столбец со статусом платежа.
2)
u_af - таблица нужна для связи таблицы orders с таблицей arur.
3)
arur - таблица отношений между продавцами и клиентами.
arur.af_id - столбец с id продавца.
arur.r_w_uid - столбец с id клиента.

Пары формируются следующим образом:
Если посмотреть в таблицу arur, то можно увидеть что для, например, af_id = 52 есть несколько записей в столбце r_w_uid. Если взять все записи r_w_uid относящиеся af_id = 52 и поделить их на 2, то получим количество пар. Остаток не включается в пары.
  • Вопрос задан
  • 128 просмотров
Решения вопроса 1
@MaximaXXl
Вы уж извините, но то что Вы написали в запросе ... это даже приблизительно не то что Вы хотели получить на словах.
Да и на словах как то все поплыло "Вывести всех клиентов, у которых есть 5 пар клиентов" - кто на ком стоял?

Если в 2 словах по запросу, у Вас 2 LEFT JOIN, оба не нужны.
У Вас нет вообще связки между двумя экземплярами orders, да и не надо там 2 раза.
Ну в целом .... работает долго и непонятно, будет данных больше, будет работать днями ...

P.S. после долгих уточнений подходящий ответ такой, для MySQL 8+
select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (
        select 
        arur.af_id,
        arur.r_w_uid,
        lag(arur.r_w_uid,1,-1) over (partition by arur.af_id order by arur.r_w_uid) r_w_uid_1
        ,row_number() OVER (partition by arur.af_id order by arur.r_w_uid ) mod 2 pair_flag
        
        from arur, u_af 
        where u_af.id = arur.af_id ) t1  left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag = 0
group by af_id
having count(distinct r_w_uid) >  5

Для более младших версий
select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (SELECT arur.af_id,
       arur.r_w_uid, 
       @row_num :=  CASE WHEN @row_num_val = af_id                THEN @row_num+1
                         WHEN (@row_num_val := af_id) IS NOT NULL THEN 1
                    END   pair_flag,
       @lag_r_w_uid :=  CASE WHEN (@row_num_val = af_id) and @row_num mod 2 = 1 THEN r_w_uid
                             else @lag_r_w_uid
                        END r_w_uid_1     

FROM arur, u_af, (SELECT @row_num := null, @row_num_val := null, @lag_r_w_uid := null) AS x
where u_af.id = arur.af_id 
ORDER BY af_id, r_w_uid        ) t1  left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag mod 2 = 0
group by af_id
having count(distinct r_w_uid) >  5
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
@Newageman Автор вопроса
Максим, status = Completed нужен для отсеивания неоплаченных заказов. Спасибо за то что помогаете.
К сожалению, запрос что-то не то выдаёт. Не считает пары и какие-то странные суммы выводит.
Например (все действия и цифры из fiddle по ссылке): для af_id = 52 sum_ = 900 000 Т.е. его клиенты должны в сумме купить на 9 000 000, чтобы, при условии 10% от их суммы, получилось 900 000. Такой суммы в тестовых таблицах нет.
Посчитав вручную всех относящихся к af_id = 52 (o.uid = 60), я получил 9 клиентов и общую сумму 1 550 000, т.е. 10% от этой суммы должно быть 155 000. Но если учитывать, что надо получить проценты за 4 пары, то должно получиться 145 000, т.к. последний 9-й клиент в пару не попадает.
Ещё раз спасибо за помощь.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы