Возврат структуры, подобной TABLE, в SELECT
Я работаю с SQR, чтобы составить отчет. Я не могу изменить структуру базы данных и не могу использовать PL/SQL для выполнения этой задачи.
Поскольку отчет может быть запущен из удаленных мест, я не хочу делать несколько вызовов в базу данных из SQR. Моя цель состоит в том, чтобы вернуть все в 1 SQL, который включает только записи, которые мне нужно сообщить, чтобы увеличить время выполнения при медленном соединении.
У меня это работает прямо сейчас, но я обеспокоен производительностью базы данных.
Таблица "транзакции" имеет следующие поля, которые можно использовать для этой цели:
account_num number(10) -- the account number
seq_num number(10) -- not a real sequence, it is unique to account_num
check_num number(10) -- the number on the check
postdate date
Первичный ключ (account_num, seq_num)
Пример данных выглядит так:
account_num seq_num check_num postdate
----------- ---------- ---------- ----------
1 11 200 2014-07-13
1 16 201 2014-07-14
1 23 205 2014-07-15
2 52 282 2014-07-13
2 66 284 2014-07-14
2 72 231 2014-07-15
3 11 201 2014-07-13
3 12 202 2014-07-14
3 15 203 2014-07-15
Примечание: в таблице есть много других типов транзакций, но мы фильтруем список по типу транзакции, что не очень важно для этого вопроса, поэтому я пропустил это. Объем транзакций в среднем составляет около 750000 в месяц (для всех транзакций, а не только для чеков), и из этого в среднем сообщается о около 10000 чековых транзакций.
Критерием выбора является возврат всех чековых транзакций, произошедших между двумя датами (включительно - обычно первый день месяца и последний день месяца), где разница между любыми отсортированными номерами чеков для учетной записи больше X (мы будем использовать 10 в этом случае).
Используя приведенные выше примеры данных, результаты выглядят так:
account_num seq_num check_num postdate
----------- ---------- ---------- ----------
2 52 282 2014-07-13
2 66 284 2014-07-14
2 72 231 2014-07-15
Все чеки из account_num 2 возвращаются, потому что разница между check_num 282 и 231 больше 10.
Я построил следующий SQL, чтобы вернуть результаты выше:
select
t1.*
from
transactions t1
join (
select
t3.account_num,
t3.min_postdate,
t3.max_postdate,
max(t3.check_diff)
from (
select distinct
t4.account_num,
lead(t4.check_num, 1, t4.check_num) over (partition by t4.account_num order by t4.check_num) - t4.check_num as check_diff,
min(t4.postdate) over (partition by t4.account_num) min_postdate,
max(t4.postdate) over (partition by t4.account_num) max_postdate
from
transactions t4
where
t4.postdate between trunc(sysdate,'mm') and last_day(trunc(sysdate))) t3
group by
t3.account_num,
t3.min_postdate,
t3.max_postdate
having max(t3.check_diff) > 10) t2
on t1.account_num = t2.account_num
and t1.postdate between t2.min_postdate and t2.max_postdate
;
Я хотел бы вернуть seq_num всех проверок из t4, поэтому я использую первичный ключ в t1. Я попытался использовать LISTAGG, который работает, чтобы собрать числа.
listagg(t4.seq_num,',') within group (order by seq_num) over (partition by account_num) sqe_nums
Но здесь я застрял... используя строку с запятой. Я могу заставить его работать, используя INSTR, но он не может использовать первичный ключ и производительность ужасна.
instr(t1.seq_num || ',', t2.seq_nbrs || ',') > 0
И я попытался присоединиться к этому так:
join (
select
t2.account_num,
regexp_substr(t2.seq_nums,'[^,]+{1}',1,level) seq_num
from
dual
connect by
level <= length(regexp_replace(t2.seq_nums,'[^,]*')) + 1) t5
on t1.account_num = t5. accout_num
and t1.sqe_num = t5.seq_num
Но я должен был знать лучше (ORA-00904) - t2 никогда не будет виден внутри выбора соединения.
У кого-нибудь есть какие-нибудь умные идеи?
1 ответ
Я бы вообще избежал объединений, используя подзапросы и более аналитические функции:
select
account_num, seq_num, check_num, postdate
from
(
select account_num,
seq_num,
check_num,
postdate,
max(check_gap) over (partition by account_num) as max_check_gap
from
(
select account_num,
seq_num,
check_num,
postdate,
lead(check_num) over (partition by account_num order by check_num)
- check_num as check_gap
from
transactions
where postdate between trunc(sysdate,'mm') and last_day(trunc(sysdate))
)
)
where
max_check_gap > 10
order by account_num, check_num;
SQL Fiddle с вашим исходным запросом, промежуточная попытка, которая неправильно прочитала правило 10 проверок, и эта версия. Все дают одинаковый результат для этих данных.
Это не решает конкретный вопрос, который вы задали, но, надеюсь, решит основную проблему производительности по-другому.
Если вы хотите придерживаться объединений - которые попадают в таблицу несколько раз, поэтому будут менее эффективными - вы можете использовать collect, Это грубый путь, table доступ может быть улучшен:
select
t1.*
from
transactions t1
join (
select
t3.account_num,
collect(t3.seq_num) as seq_nums,
t3.min_postdate,
t3.max_postdate,
max(t3.check_diff)
from (
select distinct
t4.account_num,
t4.seq_num,
lead(t4.check_num, 1, t4.check_num) over (partition by t4.account_num order by t4.check_num) - t4.check_num as check_diff,
min(t4.postdate) over (partition by t4.account_num) min_postdate,
max(t4.postdate) over (partition by t4.account_num) max_postdate
from
transactions t4
where
t4.postdate between trunc(sysdate,'mm') and last_day(trunc(sysdate))) t3
group by
t3.account_num,
t3.min_postdate,
t3.max_postdate
having max(t3.check_diff) > 10) t2
on t1.account_num = t2.account_num
and t1.seq_num in (select * from table(t2.seq_nums))
;