Сумма уменьшения SQL на процент

у меня есть такая таблица

отметка времени тип ценить 08.01.2023 1 5 07.01.2023 0 20 06.01.2023 1 1 05.01.2023 0 50 04.01.2023 0 50 03.01.2023 1 1 02.01.2023 1 1 01.01.2023 1 1

Тип 1 означает депозит, тип 0 означает снятие средств. Дело в том, что когда тип равен 1, тогда сумма является точной суммой, которую внес пользователь, поэтому мы можем просто суммировать это, но тип 0 означает снятие в процентах. Что я ищу, так это создать еще один столбец с текущей суммой депозита. Для приведенного выше примера это будет выглядеть так.

отметка времени тип ценить депонированный 08.01.2023 1 5 5.4 07.01.2023 0 20 1,4 06.01.2023 1 1 1,75 05.01.2023 0 50 0,75 04.01.2023 0 50 1,5 03.01.2023 1 1 3 02.01.2023 1 1 2 01.01.2023 1 1 1

Я не могу понять, как сделать такую ​​​​сумму, которая вычла бы процент от предыдущей суммы

Есть ли в вашей таблице метка времени или столбец последовательности?

jarlh 03.02.2023 13:11

Да, у него есть временная метка

jane 03.02.2023 13:12

Добавьте этот столбец к своим демонстрационным данным и ожидаемому результату.

jarlh 03.02.2023 13:13

У вас нет доступа к фактическим суммам снятия за эти дни?

Radagast 04.02.2023 01:55

@Rajat, вам не нужны фактические суммы снятия, чтобы решить эту проблему, используя только SQL.

Adrian White 04.02.2023 02:02

Кто решил генерировать такие данные на стороне приложения... просто говоря??

Mike Walton 04.02.2023 19:47

@AdrianWhite Я знаю, но доступ к этим данным предотвратил бы излишне сложное решение (на случай, если ОП не предвидел этого)

Radagast 05.02.2023 22:48

@Rajat согласен, если это возможно, это уменьшит сложность -> я учусь находить баланс между прагматизмом и элегантным решением сложных проблем только с помощью SQL. К вашему сведению, использование рекурсивного представления, в котором раздел «начиная с» выполняет корректировку%, а объединение всей части делает совокупную сумму почти работает -> просто пытается сделать так, чтобы она выглядела элегантно :-) Таким образом, вы, по сути, разделяете совокупный результат от снятия% таким образом, вы обходите числовое переполнение, которое происходит с оператором case.

Adrian White 06.02.2023 02:03

Примечание; в то время как рекурсивный подход легче понять (и поддерживать), если вы имеете дело с сериями более 100 записей, вы можете столкнуться с проблемами. Гораздо лучше использовать подход журнала/экспоненты, если у вас есть длинные серии или вопросы производительности.

MatBailie 06.02.2023 13:48
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
4
9
189
5
Перейти к ответу Данный вопрос помечен как решенный

Ответы 5

Альтернативный подход с использованием Match_Recognize(), POW(), SUM().

Я бы не рекомендовал использовать Match_Recognize(), если у вас тоже нет, это неудобно и может тратить время, но выглядит элегантно.

with data(transaction_date, type, value) as (
select
    to_date(column1, 'dd.mm.yyyy'),
    column2,
    column3
from
values
    ('08.01.2023', 1, 5),
    ('07.01.2023', 0, 20),
    ('06.01.2023', 1, 1),
    ('05.01.2023', 0, 50),
    ('04.01.2023', 0, 50),
    ('03.01.2023', 1, 1),
    ('02.01.2023', 1, 1),
    ('01.01.2023', 1, 1)
)  
select
    *
from
data match_recognize(
    order by
        transaction_date measures  
        sum(iff(CLASSIFIER() = 'ROW_WITH_DEPOSIT', value, 0)) DEPOSITS,
        pow(iff(CLASSIFIER() = 'ROW_WITH_WITHDRAWL', value / 100, 1) ,count(row_with_withdrawl.*))   DISCOUNT_FROM_WITHDRAWL,
        CLASSIFIER() TRANS_TYPE, 
        first(transaction_date) as start_date,
        last(transaction_date) as end_date,
        count(*) as rows_in_sequence,
        count(row_with_deposit.*) as num_deposits,
        count(row_with_withdrawl.*) as num_withdrawls 
    after
        match skip PAST LAST ROW pattern((row_with_deposit + | row_with_withdrawl +)) define row_with_deposit as type = 1,
        row_with_withdrawl as type = 0
);
Ответ принят как подходящий

Вы пытаетесь перенести состояние с течением времени, поэтому эфиру необходимо использовать UDTF для выполнения переноса за вас. Или используйте рекурсивный CTE

with data(transaction_date, type, value) as (
    select to_date(column1, 'dd.mm.yyyy'), column2, column3
    from values
        ('08.01.2023', 1, 5),
        ('07.01.2023', 0, 20),
        ('06.01.2023', 1, 1),
        ('05.01.2023', 0, 50),
        ('04.01.2023', 0, 50),
        ('03.01.2023', 1, 1),
        ('02.01.2023', 1, 1),
        ('01.01.2023', 1, 1)
), pre_process_data as (
    select *
        ,iff(type = 0, 0, value)::number as add
        ,iff(type = 0, value, 0)::number as per
        ,row_number()over(order by transaction_date asc) as rn
    from data 
), rec_cte_block as (
    with recursive rec_sub_cte as (
        select 
            p.*,
            p.add::number(20,4) as deposited
        from pre_process_data as p
        where p.rn = 1
        
        union all 
        
        select 
            p.*,
            round(div0((r.deposited + p.add)*(100-p.per), 100), 2)  as deposited
        from rec_sub_cte as r
        left join pre_process_data as p
        where p.rn = r.rn+1
    )
    select * 
    from rec_sub_cte
)
select * exclude(add, per, rn)
from rec_cte_block
order by 1;

Я написал рекурсивное CTE таким образом, так как в настоящее время есть инцидент, если внутри CTE используется IFF или CASE.

ДАТА СДЕЛКИ ТИП ЦЕНИТЬ ДЕПОНИРОВАННЫЙ 2023-01-01 1 1 1 2023-01-02 1 1 2 2023-01-03 1 1 3 2023-01-04 0 50 1,5 2023-01-05 0 50 0,75 2023-01-06 1 1 1,75 2023-01-07 0 20 1,4 2023-01-08 1 5 6.4

технически Match_Recognize() является UDF или SQL?

Adrian White 07.02.2023 11:47

@AdrianWhite это не функция, определяемая пользователем, это конструкция выражения. он же SQL. это не «стандартный SQL», но в основном это бессмысленно, это выражение намерений более высокого порядка. И почему для «новичка» проще вникнуть в код, чем вручную перекатывать одни и те же базовые конструкции SQL.

Simeon Pilgrim 07.02.2023 20:26

Решение без рекурсии и UDTF

create table depo (timestamp date,type int, value float);
insert into depo values
 (cast('01.01.2023' as date),1, 1.0)
,(cast('02.01.2023' as date),1, 1.0)
,(cast('03.01.2023' as date),1, 1.0)
,(cast('04.01.2023' as date),0, 50.0)
,(cast('05.01.2023' as date),0, 50.0)
,(cast('06.01.2023' as date),1, 1.0)
,(cast('07.01.2023' as date),0, 20.0)
,(cast('08.01.2023' as date),1, 5.0)
;
with t0 as(
select *
  ,sum(case when type=0 and value>=100 then 1 else 0 end)over(order by timestamp) gr
from depo
)
,t1 as (select timestamp as dt,type,gr
  ,case when type=1 then value else 0 end depo
  ,case when type=0 then ((100.0-value)/100.0) else 0.0 end pct
  ,sum(case when type=0 and value<100  then log((100.0-value)/100.0,2.0) 
            when type=0 and value>=100 then null
       else 0.0 
       end)
     over(partition by gr order by timestamp ROWS BETWEEN CURRENT ROW 
         AND UNBOUNDED FOLLOWING) totLog
from t0
)
,t2 as(
select * 
  ,case when type=1 then
      isnull(sum(depo*power(cast(2.0 as float),totLog))
            over(partition by gr order by dt rows between unbounded preceding and 1 preceding)
            ,0)/power(cast(2.0 as float),totLog)
      +depo
   else
      isnull(sum(depo*power(cast(2.0 as float),totLog))
           over(partition by gr order by dt rows between unbounded preceding and 1 preceding)
         ,0)/power(cast(2.0 as float),totLog)*pct
   end rest
from t1
)
select dt,type,depo,pct*100 pct
  ,rest-lag(rest,1,0)over(order by dt) movement
  ,rest
from t2
order by dt
дт тип депо процент движение отдых 2023-01-01 1 1 0 1 1 2023-02-01 1 1 0 1 2 2023-03-01 1 1 0 1 3 2023-04-01 0 0 50 -1,5 1,5 2023-05-01 0 0 50 -0,75 0,75 2023-06-01 1 1 0 1 1,75 2023-07-01 0 0 80 -0,35 1,4 2023-08-01 1 5 0 5 6.4

Я думаю, такие расчеты лучше выполнять на стороне клиента или на среднем уровне. Последовательные вычисления сложно реализовать в Sql. В некоторых особых случаях можно использовать логарифмические выражения. Но это понятнее и проще реализовать с помощью рекурсии, как показал @Simeon.

Или делайте их во время вставки. Каждый раз, когда запись вставляется (или обновляется/удаляется), инициируется расчет. Какой бы подход ни был выбран, делать это во время «отчета», как правило, дорого.

MatBailie 06.02.2023 12:41

Я играл с этим, чтобы правильно понять его, и / exp(totLog) был гениальным шагом. Спасибо. При этом мне удалось удалить большее выражение CASE, сместив окна на единицу; Демонстрация с обеими версиями кода и расширенным набором тестовых данных

MatBailie 06.02.2023 13:57

Чтобы расширить ответ @ValNik

Первый простой шаг — изменить «вычесть 20%, затем вычесть 50%, затем вычесть 30%» на умножение...

  X - 20% - 50% - 30%
  =>
  x * 0.8 * 0.5 * 0.7
  => 
  x * 0.28

Второй трюк — понять, как рассчитать кумулятивную PRODUCT(), когда у вас есть только кумулятивная сумма; SUM() OVER (), используя свойства логарифмов...

  a * b == exp( log(a) + log(b) )

  0.8 * 0.5 * 0.7
  =>
  exp( log(0.8) + log(0.5) + log(0.7) )
  =>
  exp( -0.2231 + -0.6931 + -0.3567 )
  =>
  exp( -1.2730 )
  =>
  0.28

Следующий трюк легче объяснить с помощью целых чисел, а не процентов. То есть иметь возможность разбить исходную проблему на ту, которую можно решить, используя «кумулятивную сумму» и «кумулятивный продукт»...

Текущая работа:

row_id тип ценить уравнение результат 1 + 10 0 + 10 10 2 + 20 (0 + 10 + 20) 30 3 * 2 (0 + 10 + 20) * 2 60 4 + 30 (0 + 10 + 20) * 2 + 30 90 5 * 3((0 + 10 + 20) * 2 + 30) * 3270

Перестроена работа:

row_id тип ценить КУМПРОД новое уравнение результат 1 + 102*3=6(10*6 ) / 6 10 2 + 202*3=6(10*6 + 20*6 ) / 6 30 3 * 2 3=3(10*6 + 20*6 ) / 3 60 4 + 30 3=3(10*6 + 20*6 + 30*3) / 3 90 5 * 3 =1(10*6 + 20*6 + 30*3) / 1270

CUMPROD — это «кумулятивный продукт» всех будущих «значений умножения».

Тогда уравнение представляет собой «кумулятивную сумму» value * CUMPROD, деленную на текущую CUMPROD.

Так...

  • 1 ряд: SUM(10*6 ) / 6 => SUM(10 )
  • 2 ряд: SUM(10*6, 20*6 ) / 6 => SUM(10, 20)
  • 3 ряд: SUM(10*6, 20*6 ) / 3 => SUM(10, 20) * 2
  • 4 ряд: SUM(10*6, 20*6, 30*3) / 3 => SUM(10, 20) * 2 + SUM(30)
  • ряд 5: SUM(10*6, 20*6, 30*3) / 1 => SUM(10, 20) * 2*3 + SUM(30) * 3

Единственное, чего следует опасаться, это:

  • LOG(0) = бесконечность (что произойдет при вычитании 100%)
  • Вычитать больше 100% нет смысла

Итак, я скопировал код @ValNik, который создает новый раздел каждый раз, когда вычитается 100% или более (заставляя все в следующем разделе снова начинать с нуля).

Это дает следующий SQL (перестроенная версия кода @ValNik):

WITH
  partition_when_deduct_everything AS
(
  SELECT
    *,
    SUM(
      CASE WHEN type = 0 AND value >= 100 THEN 1 ELSE 0 END
    )
    OVER (
      ORDER BY timestamp
    )
      AS deduct_everything_id,
    CASE WHEN type   = 1   THEN value
                           ELSE 0
    END
      AS deposit,
    CASE WHEN type   = 1   THEN 1.0                 -- Deposits == Deduct 0%
         WHEN value >= 100 THEN 1.0                 -- Treat "deduct everything" as a special case
                           ELSE (100.0-value)/100.0 -- Change "deduct 20%" to "multiply by 0.8"
    END
      AS multiplier
  FROM
    your_table
)
,
  cumulative_product_of_multipliers as
(
  SELECT
    *,
    EXP(
      ISNULL(
        SUM(
          LOG(multiplier)
        )
        OVER (
          PARTITION BY deduct_everything_id
              ORDER BY timestamp
          ROWS BETWEEN 1 FOLLOWING
                   AND UNBOUNDED FOLLOWING
        )
        , 0
      )
    )
      AS future_multiplier
  FROM
    partition_when_deduct_everything
)
SELECT
  *,
  ISNULL(
    SUM(
      deposit * future_multiplier
    )
    OVER (
      PARTITION BY deduct_everything_id
          ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING
               AND CURRENT ROW
    ),
    0
  )
  /
  future_multiplier
    AS rest
FROM
  cumulative_product_of_multipliers 

Демо: https://dbfiddle.uk/mrioIMiB

спасибо за подробное рассуждение и объяснение. Log(0) — это проблема, к счастью, имеющая хорошее решение.

ValNik 06.02.2023 16:23

yikes, это сгибает мою голову .. Я знал о части трюка с умножением -> логарифмической суммы, но мне еще многое предстоит узнать о том, как сплести их вместе .. требуется больше игры. Но на этом уровне трюков UDTF на самом деле становится настолько превосходным, имхо ... поскольку перенос такой крошечный и является плавающим продуктом ...

Simeon Pilgrim 06.02.2023 22:07

разнесенный девизор, аккумулируется в порядке десц row_id, да? ах, ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING так что да, но нет, но да.

Simeon Pilgrim 06.02.2023 22:09

@SimeonPilgrim - сомневаюсь, что смогу объяснить в комментарии то, что не смог объяснить в ответе, но я попробую... В строке 3: вы хотите (10 + 20) * 2, но получаете (10*6 + 20*6) / 3; что сделано, потому что 10*6 «легко» получить (совокупный продукт всех множимых, следующих за 10), а /3 «легко» получить (кумулятивный продукт всех множимых, следующих за строкой 3).

MatBailie 07.02.2023 00:39

@SimeonPilgrim - проблема с рекурсивными запросами заключается в том, что они относительно медленные, особенно в длинных сериях. Особенно, если вы столкнулись с ограничением рекурсии РСУБД (переопределить его позволяет выполнять запрос, но он все еще становится все более медленным). Я ничего не знаю о Snowflake и его UDTF, но я вижу, что они определены с использованием SQL, и поэтому я предполагаю, что у них те же ограничения, что и у SQL без функциональной инкапсуляции? Запрос сложен, чтобы понять его в первый раз, но как только это будет достигнуто, это просто кумулятивные продукты и кумулятивные суммы.

MatBailie 07.02.2023 00:49

Итак, как это должно быть решено, это UDTF, потому что он требует «сортировки данных один раз» и «обхода данных только один раз», и если у вас есть разные РАЗДЕЛЫ, такие как user_id и т. д. и т. д., вы можете работать параллельно):

create or replace function carry_value_state(_TYPE float, _VALUE float)
returns table (DEPOSITED float)
language javascript
as
$$
{
    initialize: function(argumentInfo, context) {
     this.carried_value = 0.0;
    },
    processRow: function (row, rowWriter, context){
        if (row._TYPE === 1) {
            this.carried_value += row._VALUE;  
        } else {
            let limited = Math.max(Math.min(row._VALUE, 100.0), 0.0);
            this.carried_value -= (this.carried_value * limited) / 100;          
        }
        rowWriter.writeRow({DEPOSITED: this.carried_value});
    }
}
$$;

который затем используется как:

select d.*,
    c.*
from data as d
    ,table(carry_value_state(d.type::float, d.value::float) over (order by transaction_date)) as c
order by 1;

поэтому для данных, которые мы использовали в примере, это дает:

ДАТА СДЕЛКИ ТИП ЦЕНИТЬ ДЕПОНИРОВАННЫЙ 2023-01-01 1 1 1 2023-01-02 1 1 2 2023-01-03 1 1 3 2023-01-04 0 50 1,5 2023-01-05 0 50 0,75 2023-01-06 1 1 1,75 2023-01-07 0 20 1,4 2023-01-08 1 5 6.4

да, результаты теперь в формате с плавающей запятой, так что вы должны удвоить округление, чтобы избежать проблем с представлением FP, например:

round(round(c.deposited, 6) , 2) as deposited

Другие вопросы по теме