postgresql - 如何按标题的字母顺序排序(忽略 The、An 等)并使用索引

Question

我有一个带有 title 字段的 PostgreSQL 表，但这些标题通常在前面包含“The”或“An”，我需要一种方法来按字母顺序对这些记录进行排序，就像图书馆那样，在进行排序时忽略这些文章。

两个问题

1. 在 SQL 中编写此 ORDER BY 表达式的最佳方法是什么？

2. 如何在不将标题字段值的子字符串复制到“alphabetical_title”字段中并为其编制索引的情况下，在标题字段上构建和使用适当的索引？

我正在寻找为 PostgreSQL 量身定制的解决方案。谢谢。

Answer 1

您可以在表达式上添加索引:

create index on yourtable (natural_sort(title));

Postgres 将在适当的时候使用索引，并且不会实际计算 natural_sort(title) -- 除非你也选择它。

也就是说(和 tsvector 字段非常相似)如果出于性能原因实际存储预先计算的结果，您将获得更高的性能。如果在上述情况下，Postgres 出于任何原因决定不使用该索引，则需要为考虑的每一行实际计算它，这将大大拖累您的查询。

无论哪种情况，都不要忘记数字:

http://www.codinghorror.com/blog/2007/12/sorting-for-humans-natural-sort-order.html

---

这里有两个函数可以帮助您开始自然排序:

/**
 * @param text _str The input string.
 * @return text The output string for consumption in natural sorting.
 */
CREATE OR REPLACE FUNCTION natsort(text)
    RETURNS text
AS $$
DECLARE
    _str    text := $1;
    _pad    int := 15; -- Maximum precision for PostgreSQL floats
BEGIN
    -- Bail if the string is empty
    IF  trim(_str) = ''
    THEN
        RETURN '';
    END IF;

    -- Strip accents and lower the case
    _str := lower(unaccent(_str));

    -- Replace nonsensical characters
    _str := regexp_replace(_str, E'[^a-z0-9$¢£¥₤€@&%\\(\\)\\[\\]\\{\\}_:;,\\.\\?!\\+\\-]+', ' ', 'g');

    -- Trim the result
    _str := trim(_str);

    -- @todo we'd ideally want to strip leading articles/prepositions ('a', 'the') at this stage,
    --       but to_tsvector()'s default dictionary also strips stop words (e.g. 'all').

    -- We're done if the string contains no numbers
    IF  _str !~ '[0-9]'
    THEN
        RETURN _str;
    END IF;

    -- Force spaces between numbers, so we can use regexp_split_to_table()
    _str := regexp_replace(_str, E'((?:[0-9]+|[0-9]*\\.[0-9]+)(?:e[+-]?[0-9]+\\M)?)', E' \\1 ', 'g');

    -- Pad zeros to obtain a reasonably natural looking sort order
    RETURN array_to_string(ARRAY(
    SELECT  CASE
            WHEN val !~ E'^\\.?[0-9]'
            -- Not a number; return as is
            THEN val
            -- Do our best after expanding the number...
            ELSE COALESCE(lpad(substring(val::numeric::text from '^[0-9]+'), _pad, '0'), '') ||
                COALESCE(rpad(substring(val::numeric::text from E'\\.[0-9]+'), _pad, '0'), '')
            END
    FROM    regexp_split_to_table(_str, E'\\s+') as val
    WHERE   val <> ''
    ), ' ');
END;
$$ IMMUTABLE STRICT LANGUAGE plpgsql COST 1;

COMMENT ON FUNCTION natsort(text) IS
'Rewrites a string so it can be used in natural sorting.

It''s by no means bullet proof, but it works properly for positive integers,
reasonably well for positive floats, and it''s fast enough to be used in a
trigger that populates an indexed column, or in an index directly.';

/**
 * @param text[] _values The potential values to use.
 * @return text The output string for consumption in natural sorting.
 */
CREATE OR REPLACE FUNCTION sort(text[])
    RETURNS text
AS $$
DECLARE
    _values     alias for $1;
    _sort       text;
BEGIN
    SELECT  natsort(value)
    INTO    _sort
    FROM    unnest(_values) as value
    WHERE   value IS NOT NULL
    AND     value <> ''
    AND     natsort(value) <> ''
    LIMIT 1;

    RETURN COALESCE(_sort, '');
END;
$$ IMMUTABLE STRICT LANGUAGE plpgsql COST 1;

COMMENT ON FUNCTION sort(text[]) IS
'Returns natsort() of the first significant input argument.';

第一个函数的单元测试的示例输出:

public function testNatsort()
{
    $this->checkInOut('natsort', array(
        '<NULL>'                => null,
        ''                      => '',
        'ABCde'                 => 'abcde',
        '12345 12345'           => '000000000012345 000000000012345',
        '12345.12345'           => '000000000012345.123450000000000',
        '12345e5'               => '000001234500000',
        '.12345e5'              => '000000000012345',
        '1e10'                  => '000010000000000',
        '1.2e20'                => '120000000000000',
        '-12345e5'              => '- 000001234500000',
        '-.12345e5'             => '- 000000000012345',
        '-1e10'                 => '- 000010000000000',
        '-1.2e20'               => '- 120000000000000',
        '+-$¢£¥₤€@&%'           => '+-$¢£¥₤€@&%',
        'ÀÁÂÃÄÅĀĄĂÆ'            => 'aaaaaeaaaaaae',
        'ÈÉÊËĒĘĚĔĖÐ'            => 'eeeeeeeeee',
        'ÌÍÎÏĪĨĬĮİIJ'            => 'iiiiiiiiiij',
        'ÒÓÔÕÖØŌŐŎŒ'            => 'oooooeoooooe',
        'ÙÚÛÜŪŮŰŬŨŲ'            => 'uuuueuuuuuu',
        'ÝŶŸ'                   => 'yyy',
        'àáâãäåāąăæ'            => 'aaaaaeaaaaaae',
        'èéêëēęěĕėð'            => 'eeeeeeeeee',
        'ìíîïīĩĭįıij'            => 'iiiiiiiiiij',
        'òóôõöøōőŏœ'            => 'oooooeoooooe',
        'ùúûüūůűŭũų'            => 'uuuueuuuuuu',
        'ýÿŷ'                   => 'yyy',
        'ÇĆČĈĊ'                 => 'ccccc',
        'ĎĐ'                    => 'dd',
        'Ƒ'                     => 'f',
        'ĜĞĠĢ'                  => 'gggg',
        'ĤĦ'                    => 'hh',
        'Ĵ'                     => 'j',
        'Ķ'                     => 'k',
        'ŁĽĹĻĿ'                 => 'lllll',
        'ÑŃŇŅŊ'                 => 'nnnnn',
        'ŔŘŖ'                   => 'rrr',
        'ŚŠŞŜȘſ'                => 'sssssss',
        'ŤŢŦȚÞ'                 => 'ttttt',
        'Ŵ'                     => 'w',
        'ŹŽŻ'                   => 'zzz',
        'çćčĉċ'                 => 'ccccc',
        'ďđ'                    => 'dd',
        'ƒ'                     => 'f',
        'ĝğġģ'                  => 'gggg',
        'ĥħ'                    => 'hh',
        'ĵ'                     => 'j',
        'ĸķ'                    => 'kk',
        'łľĺļŀ'                 => 'lllll',
        'ñńňņʼnŋ'                => 'nnnnnn',
        'ŕřŗ'                   => 'rrr',
        'śšşŝșß'                => 'sssssss',
        'ťţŧțþ'                 => 'ttttt',
        'ŵ'                     => 'w',
        'žżź'                   => 'zzz',
        '-_aaa--zzz--'          => '-_aaa--zzz--',
        '-:àáâ;-žżź--'          => '-:aaa;-zzz--',
        '-.à$â,-ž%ź--'          => '-.a$a,-z%z--',
        '--à$â--ž%ź--'          => '--a$a--z%z--',
        '-$à(â--ž)ź%-'          => '-$a(a--z)z%-',
        '#-à$â--ž?!ź-'          => '-a$a--z?!z-',
    ));