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

Question

[Newageman]

Помогите, пожалуйста, правильно составить такой запрос: Вывести всех продавцов, у которых есть 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, то получим количество пар. Остаток не включается в пары.

Answer 1

[Максим]

Вы уж извините, но то что Вы написали в запросе ... это даже приблизительно не то что Вы хотели получить на словах.
Да и на словах как то все поплыло "Вывести всех клиентов, у которых есть 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

Comments

[Newageman]

Я немного добавил в описание. Может быть это поможет?

[Максим]

select
arur.af_id,
sum(o.amount_value) * 0.1 sum_
from arur, u_af left join orders o on (o.uid = u_af.uid and o.status = 'Completed')
where u_af.id = arur.af_id
group by arur.af_id
having count(arur.r_w_uid) > 5

Я не очень понял по поводу status = Completed, просто вписал что считать только их

[Максим]

Пары формируются следующим образом:
Если посмотреть в таблицу arur, то можно увидеть что для, например, af_id = 52 есть несколько записей в столбце r_w_uid. Если взять все записи r_w_uid относящиеся af_id = 52 и поделить их на 2, то получим количество пар. Остаток не включается в пары.

Вы, уважаемый, странные вещи говорите. т.к. Вы ищете кол-во эфимерных пар (вам просто надо делить на 2, нет зависимости в данных отвечающих за парность) то не морочте голову ни себе ни людям. Умножте на 2 вашу константу с которой сверяете (5 пар = 10 значений) и опишите это условие в having указав ">" или ">=" вашего сокрального значения "то получим количество пар. Остаток не включается в пары."

select
arur.af_id,
sum(o.amount_value) * 0.1 sum_10p
from arur, u_af left join orders o on (o.uid = u_af.uid and o.status = 'Completed')
where u_af.id = arur.af_id
group by arur.af_id
having count(arur.r_w_uid) > 5*2

Answer 2

[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-й клиент в пару не попадает.
Ещё раз спасибо за помощь.

Comments

[Максим]

Почему получилось 9 000 000?

UPD2:
Связи таблиц:
orders.uid = u_af.uid
u_af.id = arur.af_id

как Вы сами написали o.uid = 60, а это 9*1 000 000 = 9 000 000

Разберитесь со связями в таблицах, я первые селекты писал не загружая данные.

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

select af_id, sum(Sum_pair)*0.1 sum_pair_10
from (
        select 
        arur.af_id,
        o.amount_value + lag(o.amount_value,1,0) over (partition by arur.af_id order by o.id) Sum_pair,
        row_number() OVER (partition by arur.af_id order by o.id) mod 2 pair_flag

        from arur, u_af left join orders o on (o.uid = u_af.uid and o.status = 'Completed')
        where u_af.id = arur.af_id) t1
where pair_flag = 0
group by af_id

[Максим]

так, я немного запутался с парами, я написал вверху парные операции, а Вам нужны парные клиенты ...

Напишите селект который выводит пары для af_id = 52 и условие сортировки (кто неудачник, станет 9-м)

И напишите связку клиентов с orders, а то я не понимаю откуда 1 550 000 для af_id = 52

[Newageman]

Максим, Запрос на получение отношений продавцов и клиентов.

-- Запрос на получение отношений продавцов с клиентами + сколько каждый клиент заплатил
WITH 
cte1 AS (
SELECT t3.amount_value,
       t2.id prodavec, 
       t1.r_w_uid klient
FROM arur t1
JOIN u_af t2 ON t1.af_id = t2.id
JOIN orders t3 ON t1.r_w_uid = t3.uid
)
SELECT prodavec, klient, amount_value
FROM cte1
WHERE prodavec = 52
ORDER BY prodavec;

-- Запрос на сумму и проценты для u_af.id = 52
WITH 
cte1 AS (
SELECT t3.amount_value,
       t2.id prodavec, 
       t1.r_w_uid klient
FROM arur t1
JOIN u_af t2 ON t1.af_id = t2.id
JOIN orders t3 ON t1.r_w_uid = t3.uid
)
SELECT prodavec, SUM(amount_value) AS summa, SUM(amount_value * 0.1) AS 10_procentov
FROM cte1
WHERE prodavec = 52
ORDER BY prodavec;

Неудачником станет тот у которого больший arur.r_w_uid. В данном запросе arur.r_w_uid = 91

[Максим]

Т.к. появились нормальные связи, я накидал селект

select af_id, sum(amount_value)*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(pair_flag) >  5

Пока оставил но совсем непонятно зачем нам таблица u_af .
оставил left join ... т.е. если у продавца есть 5 пар и больше клиентов но нет операций, строка будет выведена, насколько это нужно - не знаю

P.S. можно и красивее написать, но боюсь вы запутаетесь в логике работы

[Newageman]

Максим, спасибо большое за помощь. Подскажите, пожалуйста, а для чего используется lag(arur.r_w_uid,1,-1)?
На -1 ругается: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1)

[Максим]

можете написать просто lag(arur.r_w_uid), в Вашем конкретном случае это не играет роли. lag - показывает значение столбца со смещением ... почитайте лучше в инете, они с примерами покажут.

[Newageman]

Максим, Спасибо Вам большое. Остался, надеюсь, последний вопрос: Запрос сейчас выводит значения и за 4 пары и за 5 пар, но 5-ти пар пока нет. Почему он выводит значения?

[Максим]

Задача до ужаса странная, но вот:

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

[Newageman]

Максим, Спасибо. Так всё работает. Да уж, задача, действительно, нестандартная. Напишите этот код отдельным ответом, чтобы я отметил его решением.

[Newageman]

Максим, Странно в fiddle всё работает, а в реальной БД MySQL нет. Ругается: #1064 - У вас ошибка в запросе. Изучите документацию по используемой версии MySQL на предмет корректного синтаксиса около '(partition by arur.af_id order by arur.r_w_uid) r_w_uid_1,
' на строке 6

[Максим]

какая версия MySQL?

[Newageman]

Максим, 5.5.67-MariaDB

[Максим]

как же я не люблю синтаксис до MySQL 8, вот Вам что-то в старом стиле:

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

[Newageman]

Максим, Простите, пожалуйста, за то что так озадачил Вас и потратил Ваше время. Спасибо Вам огромное. Всё работает.

[Newageman]

Максим, Извините, пожалуйста. Обнаружилась одна проблема: В результат должны попадать только те продавцы нужное кол-во пар клиентов которых оплатили заказ (orders.status = 'Completed'), а попадают, видимо, все у кого есть просто нужное кол-во пар (arur.r_w_uid). Например: если изменить кол-во пар на 3, то мы получим две записи.
1) af_id = 12, хотя у него только 4 клиента с платежами со status = 'Completed' - с ним проблема
2) af_id = 52 у него 9 клиентов с платежами со status = 'Completed' - нет проблем.
Можно ли это как-то исправить?
fiddle

[Максим]

Я про эту ситуацию писал выше, если использовать left join их будет выводить, если просто join не будет, я же не знаю как Вам надо было.

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

[Newageman]

Максим, Уже выяснил. Это моя ошибка, извините.