복붙노트

[SQL] Tablefunc를 사용하여 여러 열에서 피벗

SQL

Tablefunc를 사용하여 여러 열에서 피벗

만 열 이름을 사용하는 것이 아니라 사람이 여러 변수에 피벗에 tablefunc을 사용 했습니까? 문서 참고 사항 :

나는 확실하지 나는 (내가보기 엔 의심의 여지 나에게 속도 I의 필요성을 줄 것이다)에 피벗으로 원하는 열을 결합하지 않고이 작업을 수행하는 방법입니다. 이 작업을 수행하는 한 가지 방법은 개체의 숫자를 만들어 밀리 세컨드로 localt에 추가하는 것입니다,하지만이 진행 흔들리는 방법처럼 보인다.

PostgreSQL의 크로스 탭 쿼리 :이 질문에 대한 응답에 사용 된 데이터를 편집했습니다.

 CREATE TEMP TABLE t4 (
  timeof   timestamp
 ,entity    character
 ,status    integer
 ,ct        integer);

 INSERT INTO t4 VALUES 
  ('2012-01-01', 'a', 1, 1)
 ,('2012-01-01', 'a', 0, 2)
 ,('2012-01-02', 'b', 1, 3)
 ,('2012-01-02', 'c', 0, 4);

 SELECT * FROM crosstab(
     'SELECT timeof, entity, status, ct
      FROM   t4
      ORDER  BY 1,2,3'
     ,$$VALUES (1::text), (0::text)$$)
 AS ct ("Section" timestamp, "Attribute" character, "1" int, "0" int);

보고:

 Section                   | Attribute | 1 | 0
---------------------------+-----------+---+---
 2012-01-01 00:00:00       |     a     | 1 | 2
 2012-01-02 00:00:00       |     b     | 3 | 4

그래서 문서 상태로, '특성'일명 여분의 열은 각 행 일명 이름 '섹션'에 대한 동일한 것으로 간주됩니다. 따라서, "엔티티"또한 "timeof '값은'C '값을 가지는 경우에도 두번째 행 (B)을보고한다.

원하는 출력 :

Section                   | Attribute | 1 | 0
--------------------------+-----------+---+---
2012-01-01 00:00:00       |     a     | 1 | 2
2012-01-02 00:00:00       |     b     | 3 |  
2012-01-02 00:00:00       |     c     |   | 4

어떤 생각 또는 참조?

조금 더 배경 : 나는 잠재적으로 수십억 개의 행이 작업을 수행 할 필요가 나는 길고 넓은 형식으로이 데이터를 저장 밖으로 테스트하고 나는 정기적으로 집계 함수보다 더 효율적으로 너비 형식에서 갈 tablefunc 사용할 수 있는지보고 있어요 . 나는 100에 대한 측정은 약 300 개체에 대한 모든 분을 만들었해야합니다. 우리가 자주 와이드 포맷에 갈 필요가 너무 자주, 우리는 주어진 개체의 지정된 초 동안 만든 다른 측정 값을 비교해야합니다. 또한, 특정 단체에 만든 측정은 매우 다양하다.

편집 : http://www.postgresonline.com/journal/categories/24-tablefunc :이의 자원을 발견했다.

해결법

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

    1.쿼리의 문제는 나하고 같은 타임 스탬프 2012-01-02 0시 0분 0초을 공유 C 및 그렇게, 쿼리에 timeof 먼저 타임 스탬프 열이 - 굵게 강조를 추가하더라도 - b와 c는 같은 그룹 2012-01-02 00:00:00에 빠지게 단지 여분의 열입니다. 첫 번째 (b)는 (설명서를 인용) 이후 반환됩니다

    쿼리의 문제는 나하고 같은 타임 스탬프 2012-01-02 0시 0분 0초을 공유 C 및 그렇게, 쿼리에 timeof 먼저 타임 스탬프 열이 - 굵게 강조를 추가하더라도 - b와 c는 같은 그룹 2012-01-02 00:00:00에 빠지게 단지 여분의 열입니다. 첫 번째 (b)는 (설명서를 인용) 이후 반환됩니다

    굵게 강조 광산. 그냥 엔티티 행 이름을 만들 수있는 첫 번째 두 열의 순서를 되돌리려는 원하는대로 작동합니다 :

    SELECT * FROM crosstab(
      'SELECT entity, timeof, status, ct
       FROM   t4
       ORDER  BY 1'
      ,'VALUES (1), (0)')
 AS ct (
    "Attribute" character
   ,"Section" timestamp
   ,"status_1" int
   ,"status_0" int);

    기업은 물론, 고유해야합니다.

    추가 열은 각 ROW_NAME 파티션에서 첫 번째 행에서 채워집니다. 다른 행에서 값 채우기에 ROW_NAME 당 하나의 열이 무시됩니다. 일반적으로 사람들은 하나 ROW_NAME의 모든 행에 대해 동일 할 것이다, 그러나 그것은 당신에게 달려 있습니다.

    SELECT localt, entity
     , msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05  -- , more?
FROM   crosstab(
        'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name
              , localt, entity -- additional columns
              , msrmnt, val
         FROM   test
         -- WHERE  ???   -- instead of LIMIT at the end
         ORDER  BY localt, entity, msrmnt
         -- LIMIT ???'   -- instead of LIMIT at the end
     , $$SELECT generate_series(1,5)$$)  -- more?
     AS ct (row_name int, localt timestamp, entity int
          , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
            )
LIMIT 1000  -- ??!!

    의심 할 여지가 테스트의 쿼리는 몹시 수행하지 않습니다. 귀하의 테스트 설정은 14M 행을 가지고 있으며, 당신은 감소 된 결과 세트 추가를 들어 LIMIT 1000 멀리 그것의 대부분을 던지기 전에 그들 모두를 처리 WHERE 조건이나 원본 쿼리에 LIMIT!

    게다가, 당신은 작업 배열은 그 위에 불필요하게 비싸다. 나는 () 대신 DENSE_RANK와 대리 행 이름을 생성합니다.

    DB <> 바이올린 여기 - 간단한 테스트 설정과 적은 수의 행.

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

    2.내 원래의 질문에 나는 내 샘플 데이터에 대한이를 사용했다합니다 :

    내 원래의 질문에 나는 내 샘플 데이터에 대한이를 사용했다합니다 :

    CREATE TEMP TABLE t4 (
 timeof    date
,entity    integer
,status    integer
,ct        integer);
INSERT INTO t4 VALUES 
 ('2012-01-01', 1, 1, 1)
,('2012-01-01', 1, 0, 2)
,('2012-01-01', 3, 0, 3)
,('2012-01-02', 2, 1, 4)
,('2012-01-02', 3, 1, 5)
,('2012-01-02', 3, 0, 6);

    이것으로 나는 timeof와 기업 모두에 피벗에 있습니다. tablefunc 만 회전에 대해 하나의 열을 사용하기 때문에, 당신은 열 두 차원을 물건 수있는 방법을 찾을 필요가있다. (http://www.postgresonline.com/journal/categories/24-tablefunc). 그 링크에 단지 예처럼 배열했다.

    SELECT (timestamp 'epoch' + row_name[1] * INTERVAL '1 second')::date 
           as localt, 
           row_name[2] As entity, status1, status0
FROM crosstab('SELECT ARRAY[extract(epoch from timeof), entity] as row_name,
                    status, ct
               FROM t4 
               ORDER BY timeof, entity, status'
     ,$$VALUES (1::text), (0::text)$$) 
          as ct (row_name integer[], status1 int, status0 int)

    FWIW, 내가 문자 배열을 사용하여 시도하고 지금까지이 빠르게 내 설정을위한처럼 보이는; 9.2.3 PostgreSQL을.

    이 결과 원하는 출력된다.

    localt           | entity | status1 | status0
--------------------------+---------+--------
2012-01-01       |   1    |    1    |   2
2012-01-01       |   3    |         |   3
2012-01-02       |   2    |    4    |  
2012-01-02       |   3    |    5    |   6

    나는 궁금 얼마나 훨씬 더 큰 데이터 세트에이 수행하고 나중에 다시보고합니다.

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

    3.좋아, 그래서 더 가까이 내 사용 사례에 테이블에이 달렸다. 어느 내가 잘못을하고 있어요 또는 크로스 탭 내 사용에 적합하지 않습니다.

    좋아, 그래서 더 가까이 내 사용 사례에 테이블에이 달렸다. 어느 내가 잘못을하고 있어요 또는 크로스 탭 내 사용에 적합하지 않습니다.

    우선 몇 가지 유사한 데이터를했다 :

    CREATE TABLE public.test (
    id serial primary key,
    msrmnt integer,
    entity integer,
    localt timestamp,
    val    double precision
);
CREATE INDEX ix_test_msrmnt
   ON public.test (msrmnt);
 CREATE INDEX ix_public_test_201201_entity
   ON public.test (entity);
CREATE INDEX ix_public_test_201201_localt
  ON public.test (localt);
insert into public.test (msrmnt, entity, localt, val)
select *
from(
SELECT msrmnt, entity, localt, random() as val 
FROM generate_series('2012-01-01'::timestamp, '2012-01-01 23:59:00'::timestamp, interval '1 minutes') as localt
join 
(select *
FROM generate_series(1, 50, 1) as msrmnt) as msrmnt
on 1=1
join 
(select *
FROM generate_series(1, 200, 1) as entity) as entity
on 1=1) as data;

    그럼 난 크로스 탭 코드 서너 시간을 실행 :

    explain analyze
SELECT (timestamp 'epoch' + row_name[1] * INTERVAL '1 second')::date As localt, row_name[2] as entity
    ,msrmnt01,msrmnt02,msrmnt03,msrmnt04,msrmnt05,msrmnt06,msrmnt07,msrmnt08,msrmnt09,msrmnt10
    ,msrmnt11,msrmnt12,msrmnt13,msrmnt14,msrmnt15,msrmnt16,msrmnt17,msrmnt18,msrmnt19,msrmnt20
    ,msrmnt21,msrmnt22,msrmnt23,msrmnt24,msrmnt25,msrmnt26,msrmnt27,msrmnt28,msrmnt29,msrmnt30
    ,msrmnt31,msrmnt32,msrmnt33,msrmnt34,msrmnt35,msrmnt36,msrmnt37,msrmnt38,msrmnt39,msrmnt40
    ,msrmnt41,msrmnt42,msrmnt43,msrmnt44,msrmnt45,msrmnt46,msrmnt47,msrmnt48,msrmnt49,msrmnt50
    FROM crosstab('SELECT ARRAY[extract(epoch from localt), entity] as row_name, msrmnt, val
               FROM public.test
               ORDER BY localt, entity, msrmnt',$$VALUES  ( 1::text),( 2::text),( 3::text),( 4::text),( 5::text),( 6::text),( 7::text),( 8::text),( 9::text),(10::text)
                                                         ,(11::text),(12::text),(13::text),(14::text),(15::text),(16::text),(17::text),(18::text),(19::text),(20::text)
                                                         ,(21::text),(22::text),(23::text),(24::text),(25::text),(26::text),(27::text),(28::text),(29::text),(30::text)
                                                         ,(31::text),(32::text),(33::text),(34::text),(35::text),(36::text),(37::text),(38::text),(39::text),(40::text)
                                                         ,(41::text),(42::text),(43::text),(44::text),(45::text),(46::text),(47::text),(48::text),(49::text),(50::text)$$)
        as ct (row_name integer[],msrmnt01 double precision, msrmnt02 double precision,msrmnt03 double precision, msrmnt04 double precision,msrmnt05 double precision, 
                    msrmnt06 double precision,msrmnt07 double precision, msrmnt08 double precision,msrmnt09 double precision, msrmnt10 double precision
                 ,msrmnt11 double precision, msrmnt12 double precision,msrmnt13 double precision, msrmnt14 double precision,msrmnt15 double precision, 
                    msrmnt16 double precision,msrmnt17 double precision, msrmnt18 double precision,msrmnt19 double precision, msrmnt20 double precision
                 ,msrmnt21 double precision, msrmnt22 double precision,msrmnt23 double precision, msrmnt24 double precision,msrmnt25 double precision, 
                    msrmnt26 double precision,msrmnt27 double precision, msrmnt28 double precision,msrmnt29 double precision, msrmnt30 double precision
                 ,msrmnt31 double precision, msrmnt32 double precision,msrmnt33 double precision, msrmnt34 double precision,msrmnt35 double precision, 
                    msrmnt36 double precision,msrmnt37 double precision, msrmnt38 double precision,msrmnt39 double precision, msrmnt40 double precision
                 ,msrmnt41 double precision, msrmnt42 double precision,msrmnt43 double precision, msrmnt44 double precision,msrmnt45 double precision, 
                    msrmnt46 double precision,msrmnt47 double precision, msrmnt48 double precision,msrmnt49 double precision, msrmnt50 double precision)
limit 1000

    세 번째 시도에서이 입수

    QUERY PLAN
Limit  (cost=0.00..20.00 rows=1000 width=432) (actual time=110236.673..110237.667 rows=1000 loops=1)
  ->  Function Scan on crosstab ct  (cost=0.00..20.00 rows=1000 width=432) (actual time=110236.672..110237.598 rows=1000 loops=1)
Total runtime: 110699.598 ms

    그럼 난 표준 용액 A의 몇 시간을 실행 :

    explain analyze
select localt, entity, 
 max(case when msrmnt =  1 then val else null end) as msrmnt01
,max(case when msrmnt =  2 then val else null end) as msrmnt02
,max(case when msrmnt =  3 then val else null end) as msrmnt03
,max(case when msrmnt =  4 then val else null end) as msrmnt04
,max(case when msrmnt =  5 then val else null end) as msrmnt05
,max(case when msrmnt =  6 then val else null end) as msrmnt06
,max(case when msrmnt =  7 then val else null end) as msrmnt07
,max(case when msrmnt =  8 then val else null end) as msrmnt08
,max(case when msrmnt =  9 then val else null end) as msrmnt09
,max(case when msrmnt = 10 then val else null end) as msrmnt10
,max(case when msrmnt = 11 then val else null end) as msrmnt11
,max(case when msrmnt = 12 then val else null end) as msrmnt12
,max(case when msrmnt = 13 then val else null end) as msrmnt13
,max(case when msrmnt = 14 then val else null end) as msrmnt14
,max(case when msrmnt = 15 then val else null end) as msrmnt15
,max(case when msrmnt = 16 then val else null end) as msrmnt16
,max(case when msrmnt = 17 then val else null end) as msrmnt17
,max(case when msrmnt = 18 then val else null end) as msrmnt18
,max(case when msrmnt = 19 then val else null end) as msrmnt19
,max(case when msrmnt = 20 then val else null end) as msrmnt20
,max(case when msrmnt = 21 then val else null end) as msrmnt21
,max(case when msrmnt = 22 then val else null end) as msrmnt22
,max(case when msrmnt = 23 then val else null end) as msrmnt23
,max(case when msrmnt = 24 then val else null end) as msrmnt24
,max(case when msrmnt = 25 then val else null end) as msrmnt25
,max(case when msrmnt = 26 then val else null end) as msrmnt26
,max(case when msrmnt = 27 then val else null end) as msrmnt27
,max(case when msrmnt = 28 then val else null end) as msrmnt28
,max(case when msrmnt = 29 then val else null end) as msrmnt29
,max(case when msrmnt = 30 then val else null end) as msrmnt30
,max(case when msrmnt = 31 then val else null end) as msrmnt31
,max(case when msrmnt = 32 then val else null end) as msrmnt32
,max(case when msrmnt = 33 then val else null end) as msrmnt33
,max(case when msrmnt = 34 then val else null end) as msrmnt34
,max(case when msrmnt = 35 then val else null end) as msrmnt35
,max(case when msrmnt = 36 then val else null end) as msrmnt36
,max(case when msrmnt = 37 then val else null end) as msrmnt37
,max(case when msrmnt = 38 then val else null end) as msrmnt38
,max(case when msrmnt = 39 then val else null end) as msrmnt39
,max(case when msrmnt = 40 then val else null end) as msrmnt40
,max(case when msrmnt = 41 then val else null end) as msrmnt41
,max(case when msrmnt = 42 then val else null end) as msrmnt42
,max(case when msrmnt = 43 then val else null end) as msrmnt43
,max(case when msrmnt = 44 then val else null end) as msrmnt44
,max(case when msrmnt = 45 then val else null end) as msrmnt45
,max(case when msrmnt = 46 then val else null end) as msrmnt46
,max(case when msrmnt = 47 then val else null end) as msrmnt47
,max(case when msrmnt = 48 then val else null end) as msrmnt48
,max(case when msrmnt = 49 then val else null end) as msrmnt49
,max(case when msrmnt = 50 then val else null end) as msrmnt50
from sample
group by localt, entity
limit 1000

    세 번째 시도에서이 입수

    QUERY PLAN
Limit  (cost=2257339.69..2270224.77 rows=1000 width=24) (actual time=19795.984..20090.626 rows=1000 loops=1)
  ->  GroupAggregate  (cost=2257339.69..5968242.35 rows=288000 width=24) (actual time=19795.983..20090.496 rows=1000 loops=1)
        ->  Sort  (cost=2257339.69..2293339.91 rows=14400088 width=24) (actual time=19795.626..19808.820 rows=50001 loops=1)
              Sort Key: localt
              Sort Method: external merge  Disk: 478568kB
              ->  Seq Scan on sample  (cost=0.00..249883.88 rows=14400088 width=24) (actual time=0.013..2245.247 rows=14400000 loops=1)
Total runtime: 20197.565 ms

    그래서, 내 경우에, 그것은 지금까지 크로스 탭 해결책하지 않은 것 같습니다. 그리고 이것은 내가 여러 년을해야 한 날이다. 변수와 새로 도입되는 것입니다 사실, 나는 아마 측정 엔티티 만들어지는 사실에도 불구하고, 와이드 포맷 (하지 정규화) 테이블로 이동해야합니다,하지만 난 여기에 들어갈 수 없습니다.

    여기에 포스트 그레스 9.2.3를 사용하여 내 설정 중 일부는 다음과 같습니다

    name                    setting
max_connections             100
shared_buffers          2097152
effective_cache_size    6291456
maintenance_work_mem    1048576
work_mem                 262144

    4. from https://stackoverflow.com/questions/15415446/pivot-on-multiple-columns-using-tablefunc by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] PostgreSQL을 / NodeJS와 결과의 배열로 테이블을 가입하세요  (0) 2020.03.23
[SQL] SQL Server의 날짜 범위를 생성하는 방법  (0) 2020.03.23
[SQL] 목록에있는 모든 항목과 일치하는 행의 선택 그룹  (0) 2020.03.23
[SQL] "절에"내 항목의 MySQL의 수  (0) 2020.03.23
[SQL] MySQL의 : 그룹 함수의 잘못된 사용  (0) 2020.03.23
,

티스토리툴바