[SQL] (SQL의 28 개 라인을 작성하지 않고도) 28 일 슬라이딩 윈도우 집계에 대한 BigQuery의 SQL

(SQL의 28 개 라인을 작성하지 않고도) 28 일 슬라이딩 윈도우 집계에 대한 BigQuery의 SQL

나는 LAG 함수를 사용하여 BigQuery의에서 28 일 이동 합을 계산하기 위해 노력하고있어.

이 질문에 대한 정상 응답

BigQuery의 SQL 창 집계 슬라이딩

펠리페에서 호파 당신이 LAG 기능을 사용할 수 있음을 나타냅니다. 이것의 예는 다음과 같습니다

SELECT
    spend + spend_lagged_1day + spend_lagged_2day + spend_lagged_3day + ... +  spend_lagged_27day as spend_28_day_sum,
    user,
    date
FROM (
  SELECT spend,
         LAG(spend, 1) OVER (PARTITION BY user ORDER BY date) spend_lagged_1day,
         LAG(spend, 2) OVER (PARTITION BY user ORDER BY date) spend_lagged_2day,
         LAG(spend, 3) OVER (PARTITION BY user ORDER BY date) spend_lagged_3day,
         ...
         LAG(spend, 28) OVER (PARTITION BY user ORDER BY date) spend_lagged_day,
         user,
         date
  FROM user_spend
)

SQL의 28 개 라인을 작성하지 않고도이 할 수있는 방법이 있나요!

해결법

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

   1.BigQuery를 문서는 표현이 ROWS 또는 RANGE 후 나타날 수있는 지정하지 않기 때문에 도구를 지원하는 윈도우 함수의 복잡성을 설명하는 좋은 일을하지 않습니다. 그것은 실제로 당신은 여기로 웹에서 다른 장소를 문서화 찾을 수 윈도우 함수에 대한 SQL 2003 표준을 지원합니다.

   BigQuery를 문서는 표현이 ROWS 또는 RANGE 후 나타날 수있는 지정하지 않기 때문에 도구를 지원하는 윈도우 함수의 복잡성을 설명하는 좋은 일을하지 않습니다. 그것은 실제로 당신은 여기로 웹에서 다른 장소를 문서화 찾을 수 윈도우 함수에 대한 SQL 2003 표준을 지원합니다.

   즉, 당신이 하나의 창 기능으로 원하는 효과를 얻을 수 있다는 것을 의미한다. 는 현재 전 많은 행이 합계에 포함하는 방법이기 때문에 범위는 27입니다.

   SELECT spend,
          SUM(spend) OVER (PARTITION BY user ORDER BY date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW),
          user,
          date
   FROM user_spend;
   

   범위는 매우 유용 할 수 있습니다 바인딩. 테이블 일부 사용자에 대한 날짜 누락 된 경우, 27 개 PRECEDING 행을 다시보다 27 일 이상 갈 것입니다,하지만 범위는 날짜 값 자체를 기반으로 창을 생성합니다. 다음 쿼리에서 날짜 필드는 BigQuery를 TIMESTAMP이며, 범위는 마이크로 초 단위로 지정됩니다. 나는 당신이 BigQuery에서 같은 날짜 계산을 할 때마다, 당신이 그것을 철저하게 확인 그것은 당신에게 예상되는 답을주고 만들어 테스트하는 것이 좋을 걸.

   SELECT spend,
          SUM(spend) OVER (PARTITION BY user ORDER BY date RANGE BETWEEN 27 * 24 * 60 * 60 * 1000000 PRECEDING AND CURRENT ROW),
          user,
          date
   FROM user_spend;
   

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

   2.BigQuery를 : 윈도우 절에서 롤링 시간 범위를 얻을하는 방법 .....

   BigQuery를 : 윈도우 절에서 롤링 시간 범위를 얻을하는 방법 .....

   이것은 이전 게시물이지만, 나는 해결책을 찾고 긴 시간을 할애하고,이 게시물은 그래서 어쩌면이 의지 도움말 사람을했다.

   창문 조항의 파티션 매일에 대한 기록이없는, 정확하게 롤링 시간 범위를 얻을 수있는 RANGE 절을 사용해야하는 경우 (ROWS는 너무 멀리 다시는 '돈 때문에 갈 것입니다 수 기록을 검색 할 것 t은 PARTITION BY)의 모든 일에 대한 기록이있다. 문제는 BigQuery를 RANGE 절에 날짜를 지원하지 않는다는 것입니다.

   BigQuery에서의 문서에서 :

   내가 찾은 해결 방법은 RANGE 절과 함께 ORDER BY 절에 UNIX_DATE (date_expression)를 사용했다 :

   SUM (값) 5 ~ 열 1 ORDER BY UNIX_DATE (일) RANGE BY OVER (PARTITION 앞뒤 현재 행

from https://stackoverflow.com/questions/27557919/bigquery-sql-for-28-day-sliding-window-aggregate-without-writing-28-lines-of-sq by cc-by-sa and MIT license