ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Рекурсивное распределение суммы по чеку
6 сообщений из 6, страница 1 из 1
Рекурсивное распределение суммы по чеку
    #39351562
PranT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PranT
Гость
Добрый день.

Есть выборка товаров с заданным порядком и общая сумма скидки, которую необходимо распределить по товарам в заданном порядке.
- ITEM_NUM - порядковый номер товара
- CODE - код товара
- AMOUNT - цена товара

Алгоритм распределения следующий.
авторСумма скидки текущего товара DISCOUNT_AMOUNT = FLOOR( AMOUNT / "Стоимость нераспределённых товаров" * "Сумма скидки за вычетом уже распределённых товаров")
Получается, что величины "Стоимость нераспределённых товаров" и "Сумма скидки за вычетом уже распределённых товаров" по мере обхода товаров уменьшаются (рекурсивно).

Пример
 

Код: plsql
1.
2.
3.
4.
5.
6.
with w_cheque as (
   select null item_num, null code, null amount from dual where null is not null
   union all select 1, 'PRD_1', 10 from dual
   union all select 2, 'PRD_2', 100 from dual
   union all select 3, 'PRD_3', 200 from dual
)



ITEM_NUMCODEAMOUNT1PRD_1102PRD_21003PRD_3200

Сумма скидки = 309.

Работа алгоритма при обходе товаров.
1. ITEM_NUM = 1
DISCOUNT_AMOUNT = FLOOR( 10 / (10+100+200) * 309 ) = 9
2. ITEM_NUM = 2
DISCOUNT_AMOUNT = FLOOR( 100 / (100+200) * (309-9) ) = 100
3. ITEM_NUM = 3
DISCOUNT_AMOUNT = FLOOR( 200 / (200) * (309-9-100) ) = 200

Посмотрел на форуме другие решения похожей задачи. Вариант, когда сумма распределяется по всем товарам с округлением вниз, а остаток - на товар с наибольшей стоимостью, не подходит, т.к. даёт другой результат. Решение на PL/SQL очевидно, ну нужна именно SQL-выборка ввиду специфики доработки.
...
Рейтинг: 0 / 0
22.11.2016, 07:21
| Ответить | Цитировать | Написать  
Рекурсивное распределение суммы по чеку
    #39351625
Glays
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glays
Участник
PranT, что-то не выходит
PranTDISCOUNT_AMOUNT = FLOOR( 100 / (100+200) * (309-9) ) = 100
выходит
Код: plsql
1.
SELECT FLOOR (100 / (100 + 200) * (309 - 9)) FROM DUAL


99


Может лучше ?
Код: plsql
1.
SELECT FLOOR (100* (309 - 9) / (100 + 200) ) FROM DUAL



а так оба варианта

 

по твоим цифрам
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
WITH w_cheque
     AS (SELECT NULL item_num, NULL code, NULL amount
           FROM DUAL
          WHERE NULL IS NOT NULL
         UNION ALL
         SELECT 1, 'PRD_1', 10 FROM DUAL
         UNION ALL
         SELECT 2, 'PRD_2', 100 FROM DUAL
         UNION ALL
         SELECT 3, 'PRD_3', 200 FROM DUAL),
     w_discount AS (SELECT 309 AS DISCOUNT FROM DUAL),
     w_sum
     AS (SELECT item_num,
                SUM (amount) OVER (ORDER BY item_num DESC) sum_by_it
           FROM w_cheque),
     w_rec (item_num,
            code,
            amount,
            DISCOUNT,
            DISCOUNT_AMOUNT)
     AS (SELECT w_c.item_num,
                code,
                amount,
                DISCOUNT,
                FLOOR ( (amount * DISCOUNT) / sum_by_it) AS DISCOUNT_AMOUNT
           FROM w_discount,
                w_cheque w_c
                JOIN w_sum w_s ON w_c.item_num = w_s.item_num
          WHERE w_c.item_num = 1
         UNION ALL
         SELECT w_c.item_num,
                w_c.code,
                w_c.amount,
                DISCOUNT - DISCOUNT_AMOUNT AS DISCOUNT,
                FLOOR (
                   (w_c.amount * (DISCOUNT - DISCOUNT_AMOUNT)) / sum_by_it)
                   AS DISCOUNT_AMOUNT
           FROM w_rec w_r
                JOIN w_cheque w_c ON w_r.item_num + 1 = w_c.item_num
                JOIN w_sum w_s ON w_s.item_num = w_c.item_num
          WHERE DISCOUNT > 0)
SELECT *
  FROM w_rec


по твоей формуле
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
WITH w_cheque
     AS (SELECT NULL item_num, NULL code, NULL amount
           FROM DUAL
          WHERE NULL IS NOT NULL
         UNION ALL
         SELECT 1, 'PRD_1', 10 FROM DUAL
         UNION ALL
         SELECT 2, 'PRD_2', 100 FROM DUAL
         UNION ALL
         SELECT 3, 'PRD_3', 200 FROM DUAL),
     w_discount AS (SELECT 309 AS DISCOUNT FROM DUAL),
     w_sum
     AS (SELECT item_num,
                SUM (amount) OVER (ORDER BY item_num DESC) sum_by_it
           FROM w_cheque),
     w_rec (item_num,
            code,
            amount,
            DISCOUNT,
            DISCOUNT_AMOUNT)
     AS (SELECT w_c.item_num,
                code,
                amount,
                DISCOUNT,
                FLOOR (amount / sum_by_it * DISCOUNT) AS DISCOUNT_AMOUNT
           FROM w_discount,
                w_cheque w_c
                JOIN w_sum w_s ON w_c.item_num = w_s.item_num
          WHERE w_c.item_num = 1
         UNION ALL
         SELECT w_c.item_num,
                w_c.code,
                w_c.amount,
                DISCOUNT - DISCOUNT_AMOUNT AS DISCOUNT,
                FLOOR (w_c.amount / sum_by_it * (DISCOUNT - DISCOUNT_AMOUNT))
                   AS DISCOUNT_AMOUNT
           FROM w_rec w_r
                JOIN w_cheque w_c ON w_r.item_num + 1 = w_c.item_num
                JOIN w_sum w_s ON w_s.item_num = w_c.item_num
          WHERE DISCOUNT > 0)
SELECT *
  FROM w_rec



...
Рейтинг: 0 / 0
22.11.2016, 10:19
| Ответить | Цитировать | Написать  
Рекурсивное распределение суммы по чеку
    #39351852
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
PranTРешение на PL/SQL очевидно, ну нужна именно SQL-выборка ввиду специфики доработки.Можно узнать про "специфику доработки"?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with w_cheque (item_num, code, amount, discount) as
(
   select 0, null, null, 309  from dual
   union all select 1, 'PRD_1', 10, null from dual
   union all select 2, 'PRD_2', 100, null from dual
   union all select 3, 'PRD_3', 200, null from dual
)
select *
from w_cheque
model
return updated rows
dimension by (item_num i)
measures (amount, discount)
rules
(discount[i > 0] order by i = floor(amount[cv()] * (discount[0] - nvl(sum(discount)[i > 0 and i < cv()], 0)) / sum(amount)[i >= cv()]))
/

         I     AMOUNT   DISCOUNT
---------- ---------- ----------
         1         10          9
         2        100        100
         3        200        200

От обоих агрегатов в правой части правила можно избавиться, если ввести пару вспомогательных столбцов.
Будет больше букв, зато быстрее.
...
Рейтинг: 0 / 0
22.11.2016, 14:07
| Ответить | Цитировать | Написать  
Рекурсивное распределение суммы по чеку
    #39352367
PranT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PranT
Гость
Спасибо, коллеги, за решения.

Век живи - век учись. :) Не знал ни о первой возможности (рекурсивные подзапросы), ни об операторе model. Пытался решить задачу через аналитические функции SQL. Буду разбираться и тестировать эти решения на реальных объёмах данных.

dbms_photoshopМожно узнать про "специфику доработки"?
Процесс ETL, когда требуется обработка большого количества строк из OLTP-системы. Подумалось, что построчная обработка через реализацию алгоритма на PL/SQL будет медленнее, чем использование выборки.
...
Рейтинг: 0 / 0
23.11.2016, 07:21
| Ответить | Цитировать | Написать  
Рекурсивное распределение суммы по чеку
    #39352707
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
PranTdbms_photoshopМожно узнать про "специфику доработки"?
Процесс ETL, когда требуется обработка большого количества строк из OLTP-системы. Подумалось, что построчная обработка через реализацию алгоритма на PL/SQL будет медленнее, чем использование выборки.Recursive subquery factoring вообще не жилец на больших объемах. Ситуация может быть несколько улучшена, если создать индекс по item_id, тогда в рекурсивном члене будет сканирование по индексу, а не полное сканирование.

Производительность model тоже будет все более удручающая, если более 1М строк. Ситуацию несколько может спасти, если можно сделать секционирование модели (partition by) и запустить в parallel.

В итоге, pipeline PL/SQL функция (или просто функция, возвращающая коллекцию) выглядит более производительным и сопровождаемым решением.
...
Рейтинг: 0 / 0
23.11.2016, 14:12
| Ответить | Цитировать | Написать  
Рекурсивное распределение суммы по чеку
    #39352871
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
PranT,
1. с таким подходом по любому потеряете когда нибудь на округлении и будете бегать кругами и искать почему скидка не равна сумме скидок.
2. чем не устраивает нарастающий итог?
3. не проще ли равновзвешенное распределение с закидыванием остатка на первую или последнюю запись?)
...
Рейтинг: 0 / 0
23.11.2016, 16:41
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Рекурсивное распределение суммы по чеку
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39352707&tid=1886964]:
 0ms
get settings:
 6ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 168ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 41ms
get tp. blocked users:
 1ms
others: 247ms
total:  496ms
созд. / загр.: 496ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]