Блог

Главная - Вопросы по программированию - Как найти ранг номера заказа по тому же началу

Как найти ранг номера заказа по тому же началу

#sql #postgresql #window-functions #ranking #ranking-functions

Вопрос:

Таблица Dok содержит номера заказов :

 create table dok ( doktyyp char(1),
     tasudok char(25) );
CREATE INDEX dok_tasudok_idx ON dok (tasudok);
CREATE UNIQUE INDEX dok_tasudok_unique_idx ON dok (doktyyp,tasudok)
WHERE doktyyp IN ( 'T', 'U') ;

Номера заказов содержат одну и ту же начальную часть и достаточно разных, таких как

 91000465663        
91000465663-1
91000465663-2
91000465663-T
91000465663-T-1

Как создать запрос, который возвращает порядковый номер заказа по заданному префиксу. Для столбца doktyyp значение всегда является постоянным «T».

Для приведенных ниже цифр результаты должны быть

 91000465663        returns 1
91000465663-1      returns 2
91000465663-2      returns 3
91000465663-T      returns 4
91000465663-T-1    returns 5

Запрос

 with koik as (
select rank() over (order by tasudok), tasudok
from dok 
where doktyyp='T' and tasudok  like '91000465663%'
)

select rank 
from koik 
where tasudok='91000465663-1'

Кажется, работает правильно, но выглядит слишком долго для этой простой задачи. Как создать более короткий и качественный запрос ?

Объясните вывод:

 "Subquery Scan on koik  (cost=685.04..685.07 rows=1 width=8)"
"  Filter: (koik.tasudok = '91000465663-1'::bpchar)"
"  ->  WindowAgg  (cost=685.04..685.06 rows=1 width=34)"
"        ->  Sort  (cost=685.04..685.05 rows=1 width=26)"
"              Sort Key: dok.tasudok"
"              ->  Bitmap Heap Scan on dok  (cost=23.55..685.03 rows=1 width=26)"
"                    Recheck Cond: (doktyyp = 'T'::bpchar)"
"                    Filter: (tasudok ~~ '91000465663%'::text)"
"                    ->  Bitmap Index Scan on dok_tasudok_unique_idx  (cost=0.00..23.55 rows=437 width=0)"
"                          Index Cond: (doktyyp = 'T'::bpchar)"

Использование postgres 11

Комментарии:

1. есть ли индекс по столбцу тасудок? Что говорит ОБЪЯСНЕНИЕ?

2. Я обновил вопрос и добавил индексы и объяснил вывод

Ответ №1:

Если я правильно понял ваш вариант использования, в запросе всегда будет учитываться фиксированный префикс первых 11 символов tasudok , поэтому для ускорения работы вы можете указать индексу, чтобы tasudok он уже был усечен до первых 11 символов (частичный индекс), чтобы вам не нужно было использовать like.

 CREATE INDEX dok_doktyyp_tasudok_idx ON dok (doktyyp, SUBSTRING(tasudok FROM 1 FOR 11));

И запрос должен выглядеть так

 WITH koik AS (
  SELECT RANK() OVER (ORDER BY tasudok), tasudok
  FROM dok 
  WHERE doktyyp='T' AND SUBSTRING(tasudok FROM 1 FOR 11) = '91000465663'
)
SELECT tasudok,rank FROM koik 
WHERE tasudok = '91000465663-1';

Конечно, это не имело бы смысла, если бы размер префикса был переменным.

ДЕМОНСТРАЦИЯ: db<>fiddle

Метки: