복붙노트

[SQL] 인간화 또는 자연수 혼합 된 단어 및 번호 문자열의 정렬

SQL

인간화 또는 자연수 혼합 된 단어 및 번호 문자열의 정렬

"인간화"또는 - - Sivaram Chintalapudi에 의해이 질문에 위로 따라, 나는 그것이 PostgreSQL의 자연해야 할 실제 여부에 관심이없는거야 여러 자리의 숫자와 단어 / 문자의 혼합물을 포함하는 문자열의 "정렬에는이 고정됩니다. 문자열의 단어 수의 패턴과 문자열의 하나 이상의 여러 자리 번호가있을 수있다.

나는이 일상적으로 수행 본 적이있는 유일한 장소는 "20", "3"후가 아니라 그 전에 배치, 자연스럽게 혼합 숫자와 단어를 포함하는 파일 이름을 정렬 맥 OS의 파인더에 있습니다.

문자 - 숫자 경계에서 블록으로 각 문자열을 분할하는 알고리즘에 의해 생성 될 수 원하는 데이터 정렬 순서는 다음 정렬 목적을 위해 정수로 정상적인 정렬 및 번호 - 블록 레터 블록을 치료, 각 부분을 명령했다. 그래서:

'AAA2fred은'( 'AAA', 2 ', 프레드')와 'AAA10bob은'( 'AAA', 10 '밥')가 될 될 것이다. 원하는 이들은 다음 정렬 할 수 있습니다 :

regress=# WITH dat AS ( VALUES ('AAA',2,'fred'), ('AAA',10,'bob') )
regress-# SELECT dat FROM dat ORDER BY dat;
     dat      
--------------
 (AAA,2,fred)
 (AAA,10,bob)
(2 rows)

일반 문자열 정렬 순서에 비해 :

regress=# WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') )
regress-# SELECT dat FROM dat ORDER BY dat;
    dat     
------------
 (AAA10bob)
 (AAA2fred)
(2 rows)

대학원은 ROW (..) 구조 또는 항목이 동일하지 않은 수의 기록을 비교하지 않습니다 때문에, 기록 비교 방식은 일반화되지 않습니다.

이 SQLFiddle에 샘플 데이터를 감안할 때 기본 en_AU.UTF-8 데이터 정렬 순서를 생성합니다

1A, 10A, 2A, AAA10B, AAA11B, AAA1BB, AAA20B, AAA21B, X10C10, X10C2, X1C1, X1C10, X1C3, X1C30, X1C4, X2C1

하지만 나는 원한다:

1A, 2A, 10A, AAA1BB, AAA10B, AAA11B, AAA20B, AAA21B, X1C1, X1C3, X1C4, X1C10, X1C30, X2C1, X10C10, X10C2

나는 순간에 PostgreSQL을 9.1 함께 일하고 있어요,하지만 9.2 전용 제안 괜찮을 것입니다. 나는 효율적인 문자열 분할 방법을 달성하는 방법에 대한 조언에 관심이 있어요, 어떻게 다음 기술 교류 문자열 다음 번호의 조합의 결과 분할 데이터를 비교할 수 있습니다. 또는, 물론, 분할 문자열을 필요로하지 않는 완전히 다른 더 나은 접근합니다.

PostgreSQL의 비교기 기능을 지원하지 않는 것, 그렇지 않으면이 ORDER comparator_fn와 비교기를 사용 (텍스트, 텍스트) 함수와 같은 재귀 비교하고 뭔가 매우 쉽게 할 수 있습니다. 아아, 그 구문은 상상이다.

업데이트 : 주제에 대한 블로그 게시물.

해결법

  1. ==============================

    1.테스트 데이터를 구축하지만, 임의의 데이터이 작동합니다. 이 문자열의 요소의 수와 함께 작동합니다.

    테스트 데이터를 구축하지만, 임의의 데이터이 작동합니다. 이 문자열의 요소의 수와 함께 작동합니다.

    하나의 텍스트 및 데이터베이스 당 한 번에 하나 개의 정수 값으로 구성 복합 유형을 등록합니다. 나는 인공 지능 전화 :

    TYPE AI AS (텍스트, I INT)을 작성;

    트릭 열의 각 값에서 AI의 어레이를 형성하는 것이다.

    패턴과 regexp_matches () (\ D *) (\ D *) 문자와 숫자의 조합에 대한 모든 옵션 g 복귀 한 행. 게다가 두 개의 빈 문자열 '{ "", ""}'필터링하거나 억제 한 관련성이없는 매달려 행은 비용을 추가합니다. "(정수로 캐스팅 할 수 없습니다 '로) 정수 성분이 0 (') '빈 문자열을 교체 한 후, 배열로 집계.

    NULL 값은 일종의 첫째 - 또는 당신은 특별한 경우 그들에게이 - 또는 @Craig이 제안 같은 엄격한 함수에서 전체 오두막을 사용합니다.

    SELECT data
FROM   alnum
ORDER  BY ARRAY(SELECT ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai
                FROM regexp_matches(data, '(\D*)(\d*)', 'g') x)
        , data;

    DB <> 바이올린 여기

    REGEXP_REPLACE ()는 약간 다른 행동을했다 PostgreSQL을 9.1.5로 테스트.

    SELECT data
FROM  (
    SELECT ctid, data, regexp_matches(data, '(\D*)(\d*)', 'g') AS x
    FROM   alnum
    ) x
GROUP  BY ctid, data   -- ctid as stand-in for a missing pk
ORDER  BY regexp_replace (left(data, 1), '[0-9]', '0')
        , array_agg(ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai)
        , data         -- for special case of trailing 0

    추가 REGEXP_REPLACE (왼쪽 (데이터, 1), '[1-9]', '0') 선두 자리하고 빈 문자열을 돌볼 수있는 항목 BY 첫 번째 순서로.

    특수 문자는 {} () '', 발생할 수 있습니다 좋아한다면, 그에 따라 사람들을 탈출해야 할 것이다.   @ 행 표현식을 사용하는 크레이그의 제안은 처리한다.

    BTW,이 sqlfiddle에서 실행되지 않습니다,하지만 내 DB 클러스터 않습니다. JDBC는 최대 없습니다. sqlfiddle는 불평 :

  2. ==============================

    2.그것은 다른 사람들이 배열이나 일부 등으로 풀기 안쪽 말 때문에 대답을 추가. 과도한 듯.

    그것은 다른 사람들이 배열이나 일부 등으로 풀기 안쪽 말 때문에 대답을 추가. 과도한 듯.

    CREATE FUNCTION rr(text,int) RETURNS text AS $$
SELECT regexp_replace(
    regexp_replace($1, '[0-9]+', repeat('0',$2) || '\&', 'g'), 
    '[0-9]*([0-9]{' || $2 || '})', 
    '\1', 
    'g'
)
$$ LANGUAGE sql;

SELECT t,rr(t,9) FROM mixed ORDER BY t;
      t       |             rr              
--------------+-----------------------------
 AAA02free    | AAA000000002free
 AAA10bob     | AAA000000010bob
 AAA2bbb03boo | AAA000000002bbb000000003boo
 AAA2bbb3baa  | AAA000000002bbb000000003baa
 AAA2fred     | AAA000000002fred
(5 rows)

(reverse-i-search)`OD': SELECT crypt('richpass','$2$08$aJ9ko0uKa^C1krIbdValZ.dUH8D0R0dj8mqte0Xw2FjImP5B86ugC');
richardh=> 
richardh=> SELECT t,rr(t,9) FROM mixed ORDER BY rr(t,9);
      t       |             rr              
--------------+-----------------------------
 AAA2bbb3baa  | AAA000000002bbb000000003baa
 AAA2bbb03boo | AAA000000002bbb000000003boo
 AAA2fred     | AAA000000002fred
 AAA02free    | AAA000000002free
 AAA10bob     | AAA000000010bob
(5 rows)

    나는이 regexps '에이 작업을 수행하는 가장 효율적인 방법입니다 주장 아니지만, 당신이 인덱스 그것을 할 수 있도록 RR은 () (고정 길이) 변경할 수 없습니다. 오 -이 9.1입니다

    물론, plperl 당신은 / 패드 교체를 평가 단지 수 한 번에 그것을 트림. 그러나 펄과 함께 당신은 항상 다른 방법에 비해 단지 - 한 - 더 - 옵션 (TM)를 가지고 :-)

  3. ==============================

    3.나는이 같은 문제에 직면하고, 나는 쉽게 다시 사용할 수 있도록하는 기능의 솔루션을 포장하고 싶었다. 나는 포스트 그레스의 '인간의 스타일'정렬 순서를 달성하기 위해 다음과 같은 기능을 만들었습니다.

    나는이 같은 문제에 직면하고, 나는 쉽게 다시 사용할 수 있도록하는 기능의 솔루션을 포장하고 싶었다. 나는 포스트 그레스의 '인간의 스타일'정렬 순서를 달성하기 위해 다음과 같은 기능을 만들었습니다.

    CREATE OR REPLACE FUNCTION human_sort(text)
  RETURNS text[] AS
$BODY$   
  /* Split the input text into contiguous chunks where no numbers appear,
     and contiguous chunks of only numbers. For the numbers, add leading 
     zeros to 20 digits, so we can use one text array, but sort the 
     numbers as if they were big integers.

       For example, human_sort('Run 12 Miles') gives
            {'Run ', '00000000000000000012', ' Miles'}
  */
  select array_agg(
    case
      when a.match_array[1]::text is not null 
        then a.match_array[1]::text         
      else lpad(a.match_array[2]::text, 20::int, '0'::text)::text                                      
    end::text)
    from (
      select regexp_matches(
        case when $1 = '' then null else $1 end, E'(\\D+)|(\\d+)', 'g'
      ) AS match_array      
    ) AS a  
$BODY$
  LANGUAGE sql IMMUTABLE;

    포스트 그레스 8.3.18과 9.3.5에서 작동하도록 테스트

    다음은 사용 예는 다음과 같습니다

    select * from (values 
  ('Books 1', 9),
  ('Book 20 Chapter 1', 8),
  ('Book 3 Suffix 1', 7),
  ('Book 3 Chapter 20', 6),
  ('Book 3 Chapter 2', 5),
  ('Book 3 Chapter 1', 4),
  ('Book 1 Chapter 20', 3),
  ('Book 1 Chapter 3', 2),
  ('Book 1 Chapter 1', 1),
  ('', 0),
  (null::text, 0)
) as a(name, sort)
order by human_sort(a.name)
-----------------------------
|name               |  sort |
-----------------------------
|                   |   0   |
|                   |   0   |
|Book 1 Chapter 1   |   1   |
|Book 1 Chapter 3   |   2   |
|Book 1 Chapter 20  |   3   |
|Book 3 Chapter 1   |   4   |
|Book 3 Chapter 2   |   5   |
|Book 3 Chapter 20  |   6   |
|Book 3 Suffix 1    |   7   |
|Book 20 Chapter 1  |   8   |
|Books 1            |   9   |
-----------------------------
  4. ==============================

    4.다음 함수는 임의의 길이 (단어 수) 쌍 배열로 문자열을 분할한다. 문자열이 숫자로 시작하는 경우 첫 번째 항목은 NULL 단어를해야합니다.

    다음 함수는 임의의 길이 (단어 수) 쌍 배열로 문자열을 분할한다. 문자열이 숫자로 시작하는 경우 첫 번째 항목은 NULL 단어를해야합니다.

    CREATE TYPE alnumpair AS (wordpart text,numpart integer);

CREATE OR REPLACE FUNCTION regexp_split_numstring_depth_pairs(instr text)
RETURNS alnumpair[] AS $$
WITH x(match) AS (SELECT regexp_matches($1, '(\D*)(\d+)(.*)'))
SELECT
  ARRAY[(CASE WHEN match[1] = '' THEN '0' ELSE match[1] END, match[2])::alnumpair] || (CASE 
  WHEN match[3] = '' THEN
    ARRAY[]::alnumpair[]
  ELSE 
    regexp_split_numstring_depth_pairs(match[3]) 
  END)
FROM x;$$ LANGUAGE 'sql' IMMUTABLE;

    플레이에 와서 정렬의 PostgreSQL의 복합 형을 허용 :

    SELECT data FROM alnum ORDER BY regexp_split_numstring_depth_pairs(data);

    이 SQLFiddle에 따라, 예상되는 결과를 생성. 나는 일종의 먼저 수 있도록 숫자로 시작하는 모든 문자열의 빈 문자열 0의 어윈의 대체를 채택했습니다; 왼쪽 (데이터, 1), regexp_split_numstring_depth_pairs (데이터)에 의해 ORDER를 사용하는 것보다 그것의 청소기.

    함수가 있지만 아마 엄청 시리가 적어도 표현 인덱스에서 사용할 수 있습니다 천천히.

    그것은 재미 있었다!

  5. ==============================

    5.

    create table dat(val text)
insert into dat ( VALUES ('BBB0adam'), ('AAA10fred'), ('AAA2fred'), ('AAA2bob') );

select 
  array_agg( case when z.x[1] ~ E'\\d' then lpad(z.x[1],10,'0') else z.x[1] end ) alnum_key
from (
  SELECT ctid, regexp_matches(dat.val, E'(\\D+|\\d+)','g') as x
  from dat
) z
group by z.ctid
order by alnum_key;

       alnum_key       
-----------------------
 {AAA,0000000002,bob}
 {AAA,0000000002,fred}
 {AAA,0000000010,fred}
 {BBB,0000000000,adam}

    나는 어윈 비슷한 장소에 도착 참조 - 보지 않고 거의 한 시간 동안이 작업에 참여하고 기록했다. 같은 퉜다는 @Clodoaldo 등의 문제 "데이터 형식 텍스트 []의 배열 유형을 찾을 수 없습니다." 정말 부정 행위 같은 느낌 CTID (에 의해 그룹화 생각까지 모든 AGG 행으로 정리 운동을지고 문제를 많이했다 - 그리고 DAT AS (값으로 영업 이익의 예에서와 같은 사이비 테이블에 작업을하지 않습니다 ( 'AAA2fred'), ( 'AAA10bob')) ...). array_agg이 세트를 생산하는 부속 선택을 받아 들일 수 있다면 좋을 것입니다.

  6. ==============================

    6.나는 정규식 전문가 모르겠지만, 어느 정도를 작업 할 수 있습니다. 이 답변을 생산할 정도로.

    나는 정규식 전문가 모르겠지만, 어느 정도를 작업 할 수 있습니다. 이 답변을 생산할 정도로.

    이 내용에서이 개 수치를 처리합니다. 그것도 2를 처리하는 경우 나, OSX 그것보다 더 나아가 생각하지 않습니다.

    WITH parted AS (
  select data,
         substring(data from '([A-Za-z]+).*') part1,
         substring('a'||data from '[A-Za-z]+([0-9]+).*') part2,
         substring('a'||data from '[A-Za-z]+[0-9]+([A-Za-z]+).*') part3,
         substring('a'||data from '[A-Za-z]+[0-9]+[A-Za-z]+([0-9]+).*') part4
    from alnum
)
  select data
    from parted
order by part1,
         cast(part2 as int),
         part3,
         cast(part4 as int),
         data;

    SQLFiddle

    7. from https://stackoverflow.com/questions/12965463/humanized-or-natural-number-sorting-of-mixed-word-and-number-strings by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] DB2 쉼표 그룹에 의해 출력을 분리  (0) 2020.03.27
[SQL] 왜 MySQL은 집계 함수없이 쿼리 "에 의해 그룹을"할 수 있습니까?  (0) 2020.03.27
[SQL] MySQL의 - 피연산자 1 열 (들)을 포함해야  (0) 2020.03.27
[SQL] SQL CASE 표현식 구문?  (0) 2020.03.27
[SQL] 마이크로 소프트 SQL 서버 2000 에뮬레이션 MySQL의 LIMIT 절  (0) 2020.03.27
,

티스토리툴바