programing

PostgreSQL 크로스탭 쿼리

stoneblock 2023. 4. 24. 21:10

PostgreSQL 크로스탭 쿼리

포스트그레스QL에서 크로스탭 쿼리를 작성하는 방법을 아는 사람이 있습니까?
예를 들어 다음과 같은 표가 있습니다.

Section    Status    Count
A          Active    1
A          Inactive  2
B          Active    4
B          Inactive  5

아래 크로스탭을 반환해 주셨으면 합니다.

Section    Active    Inactive
A          1         2
B          4         5

이게 가능합니까?

데이터베이스당 1회 추가 모듈을 설치합니다.이것에 의해, 기능이 제공됩니다.crosstab()Postgres 9.1 이후로는 다음을 사용할 수 있습니다.

CREATE EXTENSION IF NOT EXISTS tablefunc;

테스트 케이스의 개선

CREATE TABLE tbl (
   section   text
 , status    text
 , ct        integer  -- "count" is a reserved word in standard SQL
);

INSERT INTO tbl VALUES 
  ('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
                    , ('C', 'Inactive', 7);  -- ('C', 'Active') is missing

단순 형식 - 결측 속성에 적합하지 않음

crosstab(text)입력 파라미터가 1개일 경우:

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- needs to be "ORDER BY 1,2" here
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

반품:

섹션 | 액티브 | 비액티브---------+--------+----------A | 1 | 2B | 4 | 5C | 7 | --!!
  • 캐스팅 및 이름 변경 불필요.
  • 에 대한 잘못된 결과에 주의해 주세요.C값 : 값7첫 번째 열에 입력됩니다.경우에 따라서는 이 동작이 바람직할 수 있지만, 이 사용 예에서는 바람직하지 않습니다.
  • 또한 간단한 형식은 제공된 입력 쿼리에서 row_name, 범주, 값이라는 정확히 세 개의 열로 제한됩니다.아래 2-모수 대안과 같이 열을 추가할 공간이 없습니다.

세이프 폼

crosstab(text, text)2개의 입력 파라미터:

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- could also just be "ORDER BY 1" here

  , $$VALUES ('Active'::text), ('Inactive')$$
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

반품:

섹션 | 액티브 | 비액티브---------+--------+----------A | 1 | 2B | 4 | 5C | | 7 --!!

  • 해서 .C.

  • 두 번째 파라미터는 어트리뷰트당1개의 행을 반환하는 임의의 쿼리입니다.이러한 쿼리는 끝에 있는 컬럼 정의의 순서와 일치합니다.대부분의 경우 다음과 같이 기본 테이블에서 고유한 속성을 쿼리해야 합니다.

      'SELECT DISTINCT attribute FROM tbl ORDER BY 1'

그건 설명서에 나와 있어요.

컬럼을 입력해야 정의된 )crosstabN()일반적으로는 짧은 목록을 제공하는 것이 더 효율적입니다.VALUES뭇매를 맞다

    $$VALUES ('Active'::text), ('Inactive')$$)

또는 (매뉴얼에 기재되어 있지 않음)

    $$SELECT unnest('{Active,Inactive}'::text[])$$  -- short syntax for long lists

  • 나는 견적을 쉽게 하기 위해 달러 견적을 사용했다.

  • 데이터 유형이 다른 열을 다음과 같이 출력할 수도 있습니다.crosstab(text, text)- 값유형에 대해 - 값 열의 텍스트 표현이 유효하면 됩니다. 다른 출력의 수 있습니다.text,date,numeric등입니다.설명서의 장 끝에 코드 예가 있습니다.

db <>여기에 추가

초과 입력 행의 효과

은 다른한(" "category된 행이 처리됩니다.동일한("row_name "category") - _""" ( "row_name", "category") -(section, status)를 참조해 주세요.

1-모수 양식은 사용 가능한 값 열을 왼쪽에서 오른쪽으로 채웁니다.초과값은 폐기됩니다.
이전 입력 행이 이깁니다.

2-파라미터 양식은 각 입력값을 전용 열에 할당하고 이전 할당을 덮어씁니다.
이후 입력 행이 이깁니다.

일반적으로는 처음부터 중복 항목이 없습니다.다만, 그 경우는, 요건에 맞추어 소트 순서를 신중하게 조정해, 무슨 일이 일어나고 있는지를 기록해 주세요.
또는 필요에 따라 임의의 결과를 빠르게 얻을 수도 있습니다.그냥 그 효과를 알아두세요.

상세 예시


\crosstabview

Postgres 9.6은 이 메타 명령어를 기본 인터랙티브 단말기 psql에 추가하였습니다.처음에 사용할 쿼리를 실행할 수 있습니다.crosstab()하여 "에 합니다.\crosstabview(서양속담임)들면 다음과 같습니다.

db=> SELECT section, status, ct FROM tbl \crosstabview

위와 같은 결과가 나오지만, 클라이언트측만의 표현 기능입니다.입력 행은 약간 다르게 취급됩니다.ORDER BY는 필수가 아닙니다.상세한 것에 대하여는, 메뉴얼을 참조해 주세요.이 페이지 하단에 더 많은 코드 예가 있습니다.

dba 관련 답변입니다.SE by Daniel Vérité (psql 기능의 저자):

SELECT section,
       SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly
       SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status  value as a separate column explicitly

FROM t
GROUP BY section

.crosstab()추가 모듈의 기능 - 데이터베이스당 한 번 설치해야 합니다.포스트그레 이후SQL 9.1을 사용할 수 있습니다.

CREATE EXTENSION tablefunc;

당신의 경우, 다음과 같이 보일 것입니다.

CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);

INSERT INTO t VALUES ('A', 'Active',   1);
INSERT INTO t VALUES ('A', 'Inactive', 2);
INSERT INTO t VALUES ('B', 'Active',   4);
INSERT INTO t VALUES ('B', 'Inactive', 5);

SELECT row_name AS Section,
       category_1::integer AS Active,
       category_2::integer AS Inactive
FROM crosstab('select section::text, status, count::text from t',2)
            AS ct (row_name text, category_1 text, category_2 text);

DB Fidel:

  • 모든 기능: https://dbfiddle.uk/iKCW9Uhh
  • CREATE EXTENSION tablefunc;https://dbfiddle.uk/j8W1CMvI 에러가 표시됩니다. 
    ERROR:  function crosstab(unknown, integer) does not exist
LINE 4: FROM crosstab('select section::text, status, count::text fro...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

JSON 어그리게이션이 있는 솔루션:

CREATE TEMP TABLE t (
  section   text
, status    text
, ct        integer  -- don't use "count" as column name.
);

INSERT INTO t VALUES 
  ('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
                   , ('C', 'Inactive', 7); 


SELECT section,
       (obj ->> 'Active')::int AS active,
       (obj ->> 'Inactive')::int AS inactive
FROM (SELECT section, json_object_agg(status,ct) AS obj
      FROM t
      GROUP BY section
     )X

죄송하지만 여기서 테스트를 할 수 없기 때문에 완성되지 않았습니다만, 올바른 방향으로 내릴 수 있을지도 모릅니다.비슷한 질문을 하는 번역기를 사용하고 있습니다.

select mt.section, mt1.count as Active, mt2.count as Inactive
from mytable mt
left join (select section, count from mytable where status='Active')mt1
on mt.section = mt1.section
left join (select section, count from mytable where status='Inactive')mt2
on mt.section = mt2.section
group by mt.section,
         mt1.count,
         mt2.count
order by mt.section asc;

제가 작업하는 코드는 다음과 같습니다.

select m.typeID, m1.highBid, m2.lowAsk, m1.highBid - m2.lowAsk as diff, 100*(m1.highBid - m2.lowAsk)/m2.lowAsk as diffPercent
from mktTrades m
   left join (select typeID,MAX(price) as highBid from mktTrades where bid=1 group by typeID)m1
   on m.typeID = m1.typeID
   left join (select typeID,MIN(price) as lowAsk  from mktTrades where bid=0 group by typeID)m2
   on m1.typeID = m2.typeID
group by m.typeID, 
         m1.highBid, 
         m2.lowAsk
order by diffPercent desc;

타입을 반환한다.ID, 최고가 및 최저가, 그리고 둘 사이의 차이(양수적인 차이는 팔 수 있는 것보다 더 적은 금액으로 살 수 있다는 것을 의미합니다).

제가 고안한 다른 다이내믹한 방법이 있습니다.다이나믹한 레크 타입(임시 테이블, 익명 프로시저로 작성)과 JSON을 채용하고 있습니다.이 기능은 tablefunc/crosstab 확장을 설치할 수 없지만 임시 테이블을 만들거나 anon.pro를 실행할 수 있는 최종 사용자에게 유용할 수 있습니다.

이 예에서는 모든 xtab 열이 동일한 유형(INTEGER)이라고 가정하고 있지만 열 수는 데이터 중심 및 가변입니다.즉, JSON Aggregate 함수는 혼합 데이터 유형을 지원하므로 내장된 복합(혼합) 유형을 사용하여 혁신을 이룰 수 있습니다.

JSON 레코드 세트 함수 내에서(복합 유형을 방출하는 중첩된 SELECT를 통해) Rec. 유형을 정적으로 정의하려면 이 유형을 한 단계로 줄일 수 있습니다.

dbfiddle.uk

https://dbfiddle.uk/N1EzugHk

Crosstab은 '사용할 수 없습니다'에서할 수 있습니다.tablefunc는 데이터베이스에한번해야 합니다데이터베이스에 대해 이 확장을 한 번 작성해야 합니다.

EXTENSION create create create 。tablefunc;

다음 코드를 사용하여 십자 탭을 사용하여 피벗 테이블을 만들 수 있습니다.

create table test_Crosstab( section text,
status text,
count numeric)

insert into test_Crosstab values ( 'A','Active',1)
                ,( 'A','Inactive',2)
                ,( 'B','Active',4)
                ,( 'B','Inactive',5)

select * from crosstab(
    'select section
    ,status
    ,count
    from test_crosstab'
    )as ctab ("Section" text,"Active" numeric,"Inactive" numeric)

언급URL : https://stackoverflow.com/questions/3002499/postgresql-crosstab-query

'programing' 카테고리의 다른 글

UI WebView 배경이 [Clear Color](색상 지우기)로 설정되어 있지만 투명하지는 않습니다.  (0) 2023.04.24
사용자 제어 라이브러리와 커스텀 제어 라이브러리의 차이점은 무엇입니까?  (0) 2023.04.24
Eclipse에서 다른 폴더로 프로젝트 이동  (0) 2023.04.24
열을 삭제하지 않고 NOT NULL에서 NULL로 변경할 수 있습니까?  (0) 2023.04.24
서비스와 팩토리에 대한 혼란  (0) 2023.04.24

'programing'의 다른글

  • 현재글PostgreSQL 크로스탭 쿼리

관련글

댓글

티스토리툴바