时间戳

首页 > 解决方案 > 如何通过相同的开始查找订单号的排名

sql - 如何通过相同的开始查找订单号的排名

问题描述

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

标签： sqlpostgresqlwindow-functionsrankingranking-functions

解决方案

如果我正确理解了您的用例，查询将始终考虑前 11 个字符的固定前缀 on tasudok，因此为了加快速度，您可以告诉索引排序tasudok已经截断为前 11 个字符（部分索引），所以你不需要像这样使用。

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

推荐阅读