paulf

Далее опять вызывается TRANSLATE; на этот раз, чтобы заменить в исходной строке все нечисловые символы (возвращенные предыдущим запросом) экземпляром # :

select translate(paul123f321,

replace(translate( paul123f321,

0123456789,

rpad(#,10,#)),#, ), rpad(#,length(paul123f321),#))

from t1

TRANSLATE(

####123#321

Здесь остановимся и рассмотрим самый внешний вызов TRANSLATE. Второй параметр функции RPAD (или второй параметр функции REPEAT для DB2) - длина исходной строки. Этой величиной удобно пользоваться, потому что ни один символ не может повторяться количество раз, превышающее длину строки, частью которой он является. Теперь, когда мы заменили все нечисловые символы экземпляром # , необходимо удалить все # с помощью функции REPLACE. В итоге остаются одни числа.

Изменение значений в текущей сумме Задача

Требуется изменять значения текущей суммы в зависимости от значений другого столбца. Рассмотрим сценарий, в котором после каждой транзакции должны быть представлены история транзакций кредитной карты и текущий баланс. В этом примере будет использоваться следующее представление V:

create view V (id,amt,trx) as

select 1, 100, PR from t1 union all select 2, 100, PR from t1 union all

Итак, исходный подход иной: вместо замены символом # всех нечисловых символов заменяем им все числовые символы. Следующий шаг - удаляем все экземпляры # , таким образом оставляя только нечисловые символы:

select replace(translate( paul123f321,

0123456789, rpad(#,10,#)),#,

from t1

REPLA

select 3, 50, PY from t1 union all

select 4, 100, PR from t1 union all

select 5, 200, PY from t1 union all

select 6, 50, PY from t1

Значения столбца ID уникально идентифицируют каждую транзакцию. Столбец AMT представляет размер денежных средств, участвующих в каждой транзакции (будь то покупка или платеж). Столбец TRX содержит информацию о типе транзакции: платеж обозначается PY , покупка - PR . Если в столбце TRX находится значение PY, текущее значение столбца AMT должно вычитаться из накопленной суммы. Если значение столбца - PR, текущее значение AMT добавляется к текущей сумме. В конечном счете требуется получить следующее результирующее множество:

Решение

DB2 и Oracle

Для вычисления текущей суммы используйте оконную функцию SUM OVER вместе с выражением CASE для определения типа транзакции:

1 select case when trx = PY

2 then PAYMENT

3 else PURCHASE

4 end trx type,

5 amt,

6 sum(

7 case when trx = PY

8 then -amt else amt

9 end

10 ) over (order by id,amt) as balance

11 from V

MySQL, PostgreSQL и SQL Server

Для вычисления текущей суммы используйте скалярный подзапрос вместе с выражением CASE для определения типа транзакции:

1 select case when v1.trx = PY

2 then PAYMENT

3 else PURCHASE

4 end as trx type,

5 v1.amt,

6 (select sum(

7 case when v2.trx = PY

8 then -v2.amt else v2.amt

109 )end

11 from V v2

12 where v2.id <= v1.id) as balance

13 from V v1

Обсуждение

Выражение CASE определяет, как следует поступить с текущим значением AMT: добавить его или вычесть из текущей суммы. Если транзакция является платежом, то значение AMT преобразуется в отрицательное, таким образом, при суммировании текущая сумма уменьшается. Результат выполнения выражения CASE представлен ниже:

select case when trx = PY

then PAYMENT

else PURCHASE end trx type, case when trx = PY

then -amt else amt end as amt

Когда тип транзакции известен, значения AMT добавляются или вычитаются из текущей суммы. О том, как оконная функция SUM OVER или скалярный подзапрос формируют текущую сумму, смотрите в рецепте Вычисление текущей суммы .