у меня есть такая таблица
Тип 1 означает депозит, тип 0 означает снятие средств. Дело в том, что когда тип равен 1, тогда сумма является точной суммой, которую внес пользователь, поэтому мы можем просто суммировать это, но тип 0 означает снятие в процентах. Что я ищу, так это создать еще один столбец с текущей суммой депозита. Для приведенного выше примера это будет выглядеть так.
Я не могу понять, как сделать такую сумму, которая вычла бы процент от предыдущей суммы
Да, у него есть временная метка
Добавьте этот столбец к своим демонстрационным данным и ожидаемому результату.
У вас нет доступа к фактическим суммам снятия за эти дни?
@Rajat, вам не нужны фактические суммы снятия, чтобы решить эту проблему, используя только SQL.
Кто решил генерировать такие данные на стороне приложения... просто говоря??
@AdrianWhite Я знаю, но доступ к этим данным предотвратил бы излишне сложное решение (на случай, если ОП не предвидел этого)
@Rajat согласен, если это возможно, это уменьшит сложность -> я учусь находить баланс между прагматизмом и элегантным решением сложных проблем только с помощью SQL. К вашему сведению, использование рекурсивного представления, в котором раздел «начиная с» выполняет корректировку%, а объединение всей части делает совокупную сумму почти работает -> просто пытается сделать так, чтобы она выглядела элегантно :-) Таким образом, вы, по сути, разделяете совокупный результат от снятия% таким образом, вы обходите числовое переполнение, которое происходит с оператором case.
Примечание; в то время как рекурсивный подход легче понять (и поддерживать), если вы имеете дело с сериями более 100 записей, вы можете столкнуться с проблемами. Гораздо лучше использовать подход журнала/экспоненты, если у вас есть длинные серии или вопросы производительности.
Альтернативный подход с использованием 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.
технически Match_Recognize() является UDF или SQL?
@AdrianWhite это не функция, определяемая пользователем, это конструкция выражения. он же SQL. это не «стандартный SQL», но в основном это бессмысленно, это выражение намерений более высокого порядка. И почему для «новичка» проще вникнуть в код, чем вручную перекатывать одни и те же базовые конструкции SQL.
Решение без рекурсии и 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
Я думаю, такие расчеты лучше выполнять на стороне клиента или на среднем уровне. Последовательные вычисления сложно реализовать в Sql. В некоторых особых случаях можно использовать логарифмические выражения. Но это понятнее и проще реализовать с помощью рекурсии, как показал @Simeon.
Или делайте их во время вставки. Каждый раз, когда запись вставляется (или обновляется/удаляется), инициируется расчет. Какой бы подход ни был выбран, делать это во время «отчета», как правило, дорого.
Я играл с этим, чтобы правильно понять его, и / exp(totLog)
был гениальным шагом. Спасибо. При этом мне удалось удалить большее выражение CASE
, сместив окна на единицу; Демонстрация с обеими версиями кода и расширенным набором тестовых данных
Чтобы расширить ответ @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
Следующий трюк легче объяснить с помощью целых чисел, а не процентов. То есть иметь возможность разбить исходную проблему на ту, которую можно решить, используя «кумулятивную сумму» и «кумулятивный продукт»...
Текущая работа:
+
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) * 3
270
Перестроена работа:
+
10
2*3=6
(10*6 ) / 6
10
2
+
20
2*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) / 1
270
CUMPROD — это «кумулятивный продукт» всех будущих «значений умножения».
Тогда уравнение представляет собой «кумулятивную сумму» value * CUMPROD
, деленную на текущую CUMPROD
.
Так...
SUM(10*6 ) / 6
=> SUM(10 )
SUM(10*6, 20*6 ) / 6
=> SUM(10, 20)
SUM(10*6, 20*6 ) / 3
=> SUM(10, 20) * 2
SUM(10*6, 20*6, 30*3) / 3
=> SUM(10, 20) * 2 + SUM(30)
SUM(10*6, 20*6, 30*3) / 1
=> SUM(10, 20) * 2*3 + SUM(30) * 3
Единственное, чего следует опасаться, это:
Итак, я скопировал код @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) — это проблема, к счастью, имеющая хорошее решение.
yikes, это сгибает мою голову .. Я знал о части трюка с умножением -> логарифмической суммы, но мне еще многое предстоит узнать о том, как сплести их вместе .. требуется больше игры. Но на этом уровне трюков UDTF на самом деле становится настолько превосходным, имхо ... поскольку перенос такой крошечный и является плавающим продуктом ...
разнесенный девизор, аккумулируется в порядке десц row_id, да? ах, ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
так что да, но нет, но да.
@SimeonPilgrim - сомневаюсь, что смогу объяснить в комментарии то, что не смог объяснить в ответе, но я попробую... В строке 3: вы хотите (10 + 20) * 2
, но получаете (10*6 + 20*6) / 3
; что сделано, потому что 10*6
«легко» получить (совокупный продукт всех множимых, следующих за 10), а /3
«легко» получить (кумулятивный продукт всех множимых, следующих за строкой 3).
@SimeonPilgrim - проблема с рекурсивными запросами заключается в том, что они относительно медленные, особенно в длинных сериях. Особенно, если вы столкнулись с ограничением рекурсии РСУБД (переопределить его позволяет выполнять запрос, но он все еще становится все более медленным). Я ничего не знаю о Snowflake и его UDTF, но я вижу, что они определены с использованием SQL, и поэтому я предполагаю, что у них те же ограничения, что и у SQL без функциональной инкапсуляции? Запрос сложен, чтобы понять его в первый раз, но как только это будет достигнуто, это просто кумулятивные продукты и кумулятивные суммы.
Итак, как это должно быть решено, это 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;
поэтому для данных, которые мы использовали в примере, это дает:
да, результаты теперь в формате с плавающей запятой, так что вы должны удвоить округление, чтобы избежать проблем с представлением FP, например:
round(round(c.deposited, 6) , 2) as deposited
Есть ли в вашей таблице метка времени или столбец последовательности?