[SQL] PostgreSQL의 ORDER BY 문제 - 자연 종류

PostgreSQL의 ORDER BY 문제 - 자연 종류

나는 다음 표와 포스트 그레스 ORDER BY의 문제가 발생했습니다 :

em_code  name
EM001    AAA
EM999    BBB
EM1000   CCC

테이블에 새 레코드를 삽입하려면,

em_code EM1000가 도달하면, 상기 알고리즘은 실패한다.

첫 번째 단계는 EM999 대신 EM1000를 반환하고 그것을 다시 고유 키 제약을 깨는 새로운 em_code으로 EM1000를 생성합니다.

모든 아이디어를 어떻게 EM1000를 선택하려면?

해결법

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

   1.그 이유는 (당신이 그것을 원하는 것처럼 대신 숫자) 9 전 1 종류의 문자열이 알파벳 순으로 정렬하는 것입니다. 이처럼 해결할 수 :

   그 이유는 (당신이 그것을 원하는 것처럼 대신 숫자) 9 전 1 종류의 문자열이 알파벳 순으로 정렬하는 것입니다. 이처럼 해결할 수 :

   SELECT * FROM employees ORDER BY substring(em_code, 3)::int DESC
   

   당신이 할 수있는 경우 - -와 함께 시작하는 정수를 저장 당신의 em_code에서 중복 'EM'을 (를) 삭제할 것이 더 효율적이 될 것입니다.

   문자열에서 일체의 비 숫자를 제거하려면 :

   SELECT regexp_replace(em_code, E'\\D','','g')
   FROM employees
   

   \ D는 "비 숫자"에 대한 정규 표현식 클래스 속기이다. 'g'넷째 파라미터 첫번째뿐만 아니라 문자열에서 모든 경우에 여분을 적용하는 "글로벌"스위치가있다.

   나는 빈 문자열 증류하여 모든 비 자리를 대체 그래서 전적으로 문자열에서 숫자.

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

   2.당신이 취할 수있는 한 가지 방법은 이것에 대한 naturalsort 함수를 만드는 것입니다. 여기에 포스트 그레스 전설 RhodiumToad에 의해 작성된 예, 있습니다.

   당신이 취할 수있는 한 가지 방법은 이것에 대한 naturalsort 함수를 만드는 것입니다. 여기에 포스트 그레스 전설 RhodiumToad에 의해 작성된 예, 있습니다.

   create or replace function naturalsort(text)
       returns bytea language sql immutable strict as $f$
       select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'),'\x00')
       from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r;
   $f$;
   

   출처 : http://www.rhodiumtoad.org.uk/junk/naturalsort.sql

   단순히하여 순서대로 함수를 호출 사용하려면 :

   SELECT * FROM employees ORDER BY naturalsort(em_code) DESC
   

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

   3.이것은 항상 질문에 내 자신의 개발에 와서 마침내이 일의 교묘 한 방법으로 피곤. 드디어 고장 및 PostgreSQL의 확장으로 구현 :

   이것은 항상 질문에 내 자신의 개발에 와서 마침내이 일의 교묘 한 방법으로 피곤. 드디어 고장 및 PostgreSQL의 확장으로 구현 :

   https://github.com/Bjond/pg_natural_sort_order

   그것은 MIT 라이센스를 사용하는 무료입니다.

   기본적으로 그것은 당신이 노소 자연 그대로를 정렬 최대 속도에 대한 인덱스 컬럼을 만들 수 있도록 문자열 내의 수치 (제로 사전중인 수치)를 정규화. 추가 정보는 설명한다.

   장점은 트리거가 작동하지 응용 프로그램 코드를 할 수있다. 그것은 PostgreSQL 서버에 기계 속도로 계산 열을 추가 마이그레이션 빠르고, 간단하고이 될 것입니다.

4. ==============================

   4.당신은이 라인을 사용할 수 있습니다 "길이 ORDER BY는 em_code ( '[0-9] +') (FROM em_code 하위 문자열)"

   당신은이 라인을 사용할 수 있습니다 "길이 ORDER BY는 em_code ( '[0-9] +') (FROM em_code 하위 문자열)"

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

   5.나는이 관련 질문에 자세히 이것에 대해 쓴 :

   나는이 관련 질문에 자세히 이것에 대해 쓴 :

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

   (난 단지 유용한 상호 참조, 그래서 그것의 커뮤니티 위키로이 답변을 게시하도록하겠습니다).

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

   6.나는 패딩에 비해 사용 덜 DB를 저장하고 즉석에서 계산하는 것보다 시간을 절약 할 수 있음이 일을 다른 방법에 대해 생각했다.

   나는 패딩에 비해 사용 덜 DB를 저장하고 즉석에서 계산하는 것보다 시간을 절약 할 수 있음이 일을 다른 방법에 대해 생각했다.

   https://stackoverflow.com/a/47522040/935122

   나는 또한 GitHub의에 넣어했습니다

   https://github.com/ccsalway/dbNaturalSort

7. ==============================

   7.나는 약간 다른 무언가를 내놓았다.

   나는 약간 다른 무언가를 내놓았다.

   기본적인 아이디어는 이들에 의해 튜플의 배열 (정수, 문자열) 다음 순서를 만드는 것입니다. 마법 번호 2147483647는 문자열을 숫자 후 분류되어 너무 사용 int32_max입니다.

     ORDER BY ARRAY(
       SELECT ROW(
         CAST(COALESCE(NULLIF(match[1], ''), '2147483647') AS INTEGER),
         match[2]
       )
       FROM REGEXP_MATCHES(col_to_sort_by, '(\d*)|(\D*)', 'g')
       AS match
     )
   

from https://stackoverflow.com/questions/9173558/postgresql-order-by-issue-natural-sort by cc-by-sa and MIT license