[SQL] 어떻게 BigQuery에서 피벗을 확장 할 수?

어떻게 BigQuery에서 피벗을 확장 할 수?

하자 말, 나는 뮤직 비디오 플레이 통계 주어진 일에 대한 테이블 mydataset.stats (3B 행, 100 만 사용자, 6K 작가)가 있습니다. 단순화 된 스키마는 다음과 같습니다 UserGUID 문자열, ArtistGUID 문자열

스키마가 될 것입니다, 그래서 나는 열에 행에서 피벗 / 트랜스 아티스트가 필요합니다 : UserGUID 문자열, Artist1 INT, Artist2 INT, ... Artist8000 지능 아티스트와 함께 각각의 사용자 수를 재생

BigQuery에 / SQL에서 데이터 다량의 열과 행을 바꾸어하는 방법에 제안 접근이 있었다? 그리고 어떻게 Google BigQuery에서 범주의 수천 더미 변수 열을 만드는 방법? 하지만 같은 외모는 내 예제에서이 번호를 확장하지 않습니다

내 예를 들어이 방법을 확장 할 수 있습니까?

해결법

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

   1.나는 최대 6000 개 기능에 대한 접근 방식 아래 시도하고 예상대로 일했다. 나는 열 수에 대한 하드 제한은 테이블에있는이 10K 기능까지 작동합니다 생각

   나는 최대 6000 개 기능에 대한 접근 방식 아래 시도하고 예상대로 일했다. 나는 열 수에 대한 하드 제한은 테이블에있는이 10K 기능까지 작동합니다 생각

   STEP 1 - 집계 사용자 / 작가 재생

   SELECT userGUID as uid, artistGUID as aid, COUNT(1) as plays 
   FROM [mydataset.stats] GROUP BY 1, 2
   

   2 단계 - 표준화가 UID와 지원 - 그들은 연속적인 숫자 1, 2, 3 그래서 .... 우리는 적어도 두 가지 이유로이 필요합니다 : A)는 나중에 동적으로 더 유용해야 할) 수와 b 등의 소형으로 SQL을 생성 만든다 / 친화적 인 열 이름을

   첫 번째 단계와 결합 -이 될 것입니다 :

   SELECT u.uid AS uid, a.aid AS aid, plays 
   FROM (
     SELECT userGUID, artistGUID, COUNT(1) AS plays 
     FROM [mydataset.stats] 
     GROUP BY 1, 2
   ) AS s
   JOIN (
     SELECT userGUID, ROW_NUMBER() OVER() AS uid FROM [mydataset.stats] GROUP BY 1
   ) AS u ON u. userGUID = s.userGUID
   JOIN (
     SELECT artistGUID, ROW_NUMBER() OVER() AS aid FROM [mydataset.stats] GROUP BY 1
   ) AS a ON a.artistGUID = s.artistGUID 
   

   테이블에하자 쓰기 출력 - mydataset.aggs

   3 단계 - 이미 한 번에 N 기능 (예술가)에 대한 접근 방식 (언급 한 질문에 위의) 제안 사용. 내 특정 예에서, 실험에 의해, 그 기본적인 접근 방식은 2000과 3000 사이의 기능의 번호를 잘 작동 발견했다. 안전을 위해 나는 한 번에 2000 기능을 사용하기로 결정

   스크립트는 동적으로 다음 분할 된 테이블을 만들 실행하는 것이 쿼리를 생성하기 위해 사용되는 아래의

   SELECT 'SELECT uid,' + 
      GROUP_CONCAT_UNQUOTED(
         'SUM(IF(aid=' + STRING(aid) + ',plays,NULL)) as a' + STRING(aid) 
      ) 
      + ' FROM [mydataset.aggs] GROUP EACH BY uid'
   FROM (SELECT aid FROM [mydataset.aggs] GROUP BY aid HAVING aid > 0 and aid < 2001)
   

   위 쿼리는 다른 쿼리 아래와 같이 아직 생산 :

   SELECT uid,SUM(IF(aid=1,plays,NULL)) a1,SUM(IF(aid=3,plays,NULL)) a3,
     SUM(IF(aid=2,plays,NULL)) a2,SUM(IF(aid=4,plays,NULL)) a4 . . .
   FROM [mydataset.aggs] GROUP EACH BY uid 
   

   이것은 실행하고 mydataset.pivot_1_2000에 기록되어야한다

   3 단계를 실행 두 번 더 우리가 세 가지 이상의 테이블 mydataset.pivot_2001_4000를 얻을 수 (조정 원조> NNNN 원조

   4 단계 - 모든 기능 최종 선회 테이블의 모든 파티션 피봇 테이블을 병합 한 테이블의 열로 표시

   위의 단계에서와 동일합니다. 처음에 우리는 쿼리를 생성하고 실행 필요 그래서 처음에 우리는 mydataset.pivot_1_2000 및 mydataset.pivot_2001_4000을 "스티치"합니다. 그런 다음 mydataset.pivot_4001_6000과 결과

   SELECT 'SELECT x.uid uid,' + 
      GROUP_CONCAT_UNQUOTED(
         'a' + STRING(aid) 
      ) 
      + ' FROM [mydataset.pivot_1_2000] AS x
   JOIN EACH [mydataset.pivot_2001_4000] AS y ON y.uid = x.uid
   '
   FROM (SELECT aid FROM [mydataset.aggs] GROUP BY aid HAVING aid < 4001 ORDER BY aid)
   

   위의 출력 문자열을 실행해야하고 mydataset.pivot_1_4000에 기록 및 결과

   그런 다음 우리는 다음과 같은 4 단계를 반복

   SELECT 'SELECT x.uid uid,' + 
      GROUP_CONCAT_UNQUOTED(
         'a' + STRING(aid) 
      ) 
      + ' FROM [mydataset.pivot_1_4000] AS x
   JOIN EACH [mydataset.pivot_4001_6000] AS y ON y.uid = x.uid
   '
   FROM (SELECT aid FROM [mydataset.aggs] GROUP BY aid HAVING aid < 6001 ORDER BY aid)
   

   mydataset.pivot_1_6000에 기록하기 결과

   생성 된 테이블 스키마 다음이 있습니다 :

   uid int, a1 int, a2 int, a3 int, . . . , a5999 int, a6000 int 
   

   노트: ㅏ. 난 단지 최대 6000 개 기능에이 방법을 시도하고는 예상대로 일했다 비. 60 분 20 내지 변화 단계 3 및 4 초 / 메인 쿼리에 대한 런타임 씨. 중요 : (90) 좋은 뉴스 1까지 변화 3 단계와 4 단계에서 청구 계층은 각각의 테이블의 크기 (30-40메가바이트) 그래서 상대적으로 작은 바이트를 청구 않는다는 것입니다. 프로젝트의 모든 2016 년 10 월 티어 1로하지만, 이후에 청구됩니다 "2016 년이되기 전에"에 대한이 문제가 될 수 있습니다. 자세한 내용은 고-계산 쿼리에서 타이밍 참조 디. BigQuery를 가진 대규모 데이터의 변환 상기 실시 예 쇼 파워! 아직 내가 저장이 기능 매트릭스가 가장 생각이 아니다 구체화하는 것이 (하지만 내가 잘못 될 수 있습니다) 생각

from https://stackoverflow.com/questions/34845697/how-to-scale-pivoting-in-bigquery by cc-by-sa and MIT license