복붙노트

[SQL] 어떻게 Google BigQuery에서 결석 날짜를 차지 최신 시리즈를 생성?

SQL

어떻게 Google BigQuery에서 결석 날짜를 차지 최신 시리즈를 생성?

나는 구글 빅 쿼리 테이블에서 판매 매일 합계를 얻기 위해 노력하고 있어요. 나는 그것에 대해 다음 코드를 사용했다.

select Day(InvoiceDate) date, Sum(InvoiceAmount) sales from test_gmail_com.sales 
where year(InvoiceDate) = Year(current_date()) and
Month(InvoiceDate) = Month(current_date())
group by date order by date

위의 질의에서 테이블에 있던 매일 판매의 합계를 제공합니다. 어떤 날은 어떤 판매를하지 않아도 기회가있다. 상황이 그런 종류의, 나는 판매의 합계로해야 30 뭣 31 행 매달에, 예를 들어 0이되어야 날짜와 금액을 얻을 필요가있다. 예를 들면 아래에 표시됩니다. 매월 넷째 날은 영업을하지 않습니다. 그래서 그것의 합이 0이어야합니다.

date | sales
-----+------
1    |   259
-----+------
2    |   359
-----+------
3    |   45
-----+------
4    |    0
-----+------
5    |  156

그것은 큰 쿼리에서 할 수 있습니까? 기본적으로 날짜 열은 1에서 일련해야한다 - 28/29/30 또는 31 일 올해의 달에 따라

해결법

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

    1.(- 날짜가 생성 범위하는 당신이 제어 할 수있는 사람들을 변경하여 예를 들어 아래는 CURRENT_DATE ()까지 2015년 6월 1일에서 모든 날짜 인에서) 주어진 범위의 모든 날짜를에 생성 비행을 아래에 사용할 수 있습니다

    (- 날짜가 생성 범위하는 당신이 제어 할 수있는 사람들을 변경하여 예를 들어 아래는 CURRENT_DATE ()까지 2015년 6월 1일에서 모든 날짜 인에서) 주어진 범위의 모든 날짜를에 생성 비행을 아래에 사용할 수 있습니다

    SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS calendar_day
FROM (
     SELECT ROW_NUMBER() OVER() AS pos, *
     FROM (FLATTEN((
     SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS h
     FROM (SELECT NULL)),h
)))

    그래서, 지금 - 좌 테이블에 가입하면 차지하고 모든 날짜가하는 데 사용할 수 있습니다. 아래의 가능성의 예를 참조

    SELECT
  calendar_day,
  IFNULL(sales, 0) AS sales
FROM (
  SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS calendar_day
  FROM (
       SELECT ROW_NUMBER() OVER() AS pos, *
       FROM (FLATTEN((
       SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS h
       FROM (SELECT NULL)),h
  )))
) AS all_dates
LEFT JOIN (
  SELECT DAY(InvoiceDate) DATE, SUM(InvoiceAmount) sales 
  FROM test_gmail_com.sales 
  WHERE YEAR(InvoiceDate) = YEAR(CURRENT_DATE()) AND
  MONTH(InvoiceDate) = MONTH(CURRENT_DATE())
  GROUP BY DATE 
)
ON DATE = calendar_day

    아래는 이전 달의 모든 일을 제공합니다

    SELECT DATE(DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE(), -1, "MONTH"), 1 - DAY(CURRENT_DATE()), "DAY"), pos - 1, "DAY")) AS calendar_day
FROM (
     SELECT ROW_NUMBER() OVER() AS pos, *
     FROM (FLATTEN((
     SELECT SPLIT(RPAD('', 1 + DATEDIFF(DATE_ADD(CURRENT_DATE(), - DAY(CURRENT_DATE()), "DAY"), DATE_ADD(DATE_ADD(CURRENT_DATE(), -1, "MONTH"), 1 - DAY(CURRENT_DATE()), "DAY")), '.'),'') AS h
     FROM (SELECT NULL)),h
)))
  2. ==============================

    2.당신이 위에 무엇을해야 테이블에 합류 한 후 날짜 목록을 생성하고하는 것은 쉬운 것 같다. 나는 generate_date_array + unnest를 사용하고 아주 깨끗한 보인다.

    당신이 위에 무엇을해야 테이블에 합류 한 후 날짜 목록을 생성하고하는 것은 쉬운 것 같다. 나는 generate_date_array + unnest를 사용하고 아주 깨끗한 보인다.

    일 (행 당 하나의 일)의 목록을 생성하려면 :

      SELECT
  *
  FROM 
    UNNEST(GENERATE_DATE_ARRAY('2018-10-01', '2020-09-30', INTERVAL 1 DAY)) AS example
  3. ==============================

    3.코드를 단순화하기 위해 표준 SQL 언어와 generate_array 기능을 사용 :

    코드를 단순화하기 위해 표준 SQL 언어와 generate_array 기능을 사용 :

    WITH serialnum AS (
  SELECT
    sn
  FROM
    UNNEST(GENERATE_ARRAY(0, 
                          DATE_DIFF(DATE_ADD(DATE_TRUNC(CURRENT_DATE()
                                                      , MONTH)
                                          , INTERVAL 1 MONTH)
                                  , DATE_TRUNC(CURRENT_DATE(), MONTH)
                                  , DAY) - 1)
                          ) AS sn
), date_seq AS (
SELECT
    DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH),
            INTERVAL(sn) DAY) AS this_day
FROM
  serialnum
)
SELECT
    Day(InvoiceDate) date
    , Sum(IFNULL(InvoiceAmount, 0)) sales
FROM
    date_seq
    LEFT JOIN
    test_gmail_com.sales
ON
    date_seq.this_day = DAY(test_gmail_com.sales.InvoiceDate)
WHERE
    year(InvoiceDate) = Year(current_date())
    and
    Month(InvoiceDate) = Month(current_date())
GROUP BY
    date
ORDER BY
    date
;

    최신 정보

    또는, 간단 여전히 generate_date_array 기능을 사용 :

    WITH date_seq AS (
SELECT
  GENERATE_DATE_ARRAY(DATE_TRUNC(CURRENT_DATE(), MONTH), 
                      DATE_ADD(DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH)
                                        , INTERVAL 1 MONTH)
                               , INTERVAL -1 DAY)
                      , INTERVAL 1 DAY)
    AS this_day
)
SELECT
    Day(InvoiceDate) date
    , Sum(IFNULL(InvoiceAmount, 0)) sales
FROM
    date_seq
    LEFT JOIN
    test_gmail_com.sales
ON
    date_seq.this_day = DAY(test_gmail_com.sales.InvoiceDate)
WHERE
    year(InvoiceDate) = Year(current_date())
    and
    Month(InvoiceDate) = Month(current_date())
GROUP BY
    date
ORDER BY
    date
;
  4. ==============================

    4.이러한 목적을 위해 '달력'테이블, 테이블이 실용적입니다 일정 범위 내에서 단지 목록의 모든 일. 특정 질문에 대해,이 숫자와 함께이 표를 얻기 위해 1 31 빠른 방법을 테이블을 가지고 충분하면 CSV 파일로 저장,이 숫자와 스프레드 시트를 만들고 테이블로의 BigQuery에이 파일을 가져 오는 것입니다 .

    이러한 목적을 위해 '달력'테이블, 테이블이 실용적입니다 일정 범위 내에서 단지 목록의 모든 일. 특정 질문에 대해,이 숫자와 함께이 표를 얻기 위해 1 31 빠른 방법을 테이블을 가지고 충분하면 CSV 파일로 저장,이 숫자와 스프레드 시트를 만들고 테이블로의 BigQuery에이 파일을 가져 오는 것입니다 .

    그런 다음 왼쪽 외부 판매로 IFNULL (판매, 0)로,이 테이블에 당신의 결과 집합을 가입 할 수 있습니다.

    당신이 옳다고 월 (28--31) 당 일수를 원한다면, 당신은 기본적으로 두 가지 옵션이 있습니다. 어느 쪽이든 당신은 몇 년 커버 적절한 일정 테이블을 만들고 년, 월, 일을 사용하여 조인. 또는 당신은 달과 연도를 기준으로 숫자 1--31 및 삭제 번호와 간단한 테이블을 사용합니다.

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

    5.표준 SQL의 경우

    표준 SQL의 경우

    WITH

splitted AS (
  SELECT
    *
  FROM
    UNNEST( SPLIT(RPAD('',
          1 + DATE_DIFF(CURRENT_DATE(), DATE("2015-06-01"), DAY),
          '.'),''))),
  with_row_numbers AS (
  SELECT
    ROW_NUMBER() OVER() AS pos,
    *
  FROM
    splitted),
  calendar_day AS (
  SELECT
    DATE_ADD(DATE("2015-06-01"), INTERVAL (pos - 1) DAY) AS day
  FROM
    with_row_numbers)
SELECT
  *
FROM
  calendar_day
ORDER BY
  day DESC

    6. from https://stackoverflow.com/questions/38694040/how-to-generate-date-series-to-occupy-absent-dates-in-google-biqquery by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 행에 열 및 열로 MySQL의 트랜스 행 [중복]  (0) 2020.06.12
[SQL] 분산 트랜잭션 오류?  (0) 2020.06.12
[SQL] 어떻게 선택적 매개 변수 SQL 쿼리를 처리하기 위해?  (0) 2020.06.12
[SQL] 어떻게 SQL에서 필드 값 당 행의 수를 제한 할 수 있습니까?  (0) 2020.06.12
[SQL] 여러 행을 업데이트 할 때 NULL 유형을 주조  (0) 2020.06.12
,

티스토리툴바