ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [Bigquery] 시간, 숫자, 날짜 생성하기
    DB/Bigquery 2022. 2. 20. 17:17

    PostgreSQL의 시간, 숫자, 날짜 생성하기와 마찬가지로 bigquery에서도 동일한 함수가 존재합니다. postgresql과 차이점은 오버로딩 개념으로 동일한 함수명으로 여러 타입을 받아서 처리했다면 bigquery는 각 타입마다 함수명이 다릅니다.

    generate_array(start, end [, step])

    int타입의 숫자를 입력하여 생성할 때 사용하는 함수입니다.

    SELECT GENERATE_ARRAY(1, 5) AS example_array;
    
    +-----------------+
    | example_array   |
    +-----------------+
    | [1, 2, 3, 4, 5] |
    +-----------------+
    
    SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;
    
    +---------------+
    | example_array |
    +---------------+
    | [0, 3, 6, 9]  |
    +---------------+
    
    SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;
    
    +---------------+
    | example_array |
    +---------------+
    | [10, 7, 4, 1] |
    +---------------+
    
    SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;
    
    +---------------+
    | example_array |
    +---------------+
    | []            |
    +---------------+
    
    SELECT GENERATE_ARRAY(start, 5) AS example_array
    FROM UNNEST([3, 4, 5]) AS start;
    
    +---------------+
    | example_array |
    +---------------+
    | [3, 4, 5]     |
    | [4, 5]        |
    | [5]           |
    +---------------+

    generate_date_array(start, end[, INTERVAL INT64_expr date_part])

    date 타입을 입력하여 생성할 수 있습니다.

    SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;
    
    +--------------------------------------------------+
    | example                                          |
    +--------------------------------------------------+
    | [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
    +--------------------------------------------------+
    
    SELECT GENERATE_DATE_ARRAY(
     '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;
    
    +--------------------------------------+
    | example                              |
    +--------------------------------------+
    | [2016-10-05, 2016-10-07, 2016-10-09] |
    +--------------------------------------+
    
    SELECT GENERATE_DATE_ARRAY('2016-01-01',
      '2016-12-31', INTERVAL 2 MONTH) AS example;
    
    +--------------------------------------------------------------------------+
    | example                                                                  |
    +--------------------------------------------------------------------------+
    | [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
    +--------------------------------------------------------------------------+

    generate_timestamp_array(start, end, INTERVAL step_expression date_part)

    timestamp 타입을 입력하여 생성할 수 있습니다.

    SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
                                    INTERVAL 1 DAY) AS timestamp_array;
    
    +--------------------------------------------------------------------------+
    | timestamp_array                                                          |
    +--------------------------------------------------------------------------+
    | [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
    +--------------------------------------------------------------------------+
    
    SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                    INTERVAL 1 SECOND) AS timestamp_array;
    
    +--------------------------------------------------------------------------+
    | timestamp_array                                                          |
    +--------------------------------------------------------------------------+
    | [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
    +--------------------------------------------------------------------------+

    레퍼런스

    Array functions | BigQuery | Google Cloud

    댓글