'BigQuery translation from Oracle - min & max with partition & range
I am trying to translate a query from Oracle to BigQuery and coming up with an error. No matter how I try to rearrange the BigQuery version, I'm not getting the results that I expect. The values are far too large on the BigQuery version. Can someone take a look and see what might be missing?
Oracle Version
MAX(LASTWK) OVER(
PARTITION BY TO_CHAR(STRT_DT, 'D'), HR, TIME_INT
ORDER BY
TRUNC(STRT_DT)
RANGE BETWEEN 35 PRECEDING AND 0 PRECEDING
) MAXCNT,
MIN(LASTWK) OVER(
PARTITION BY TO_CHAR(STRT_DT, 'D'), HR, TIME_INT
ORDER BY
TRUNC(STRT_DT)
RANGE BETWEEN 35 PRECEDING AND 0 PRECEDING
) MINCNT
BigQuery version that isn't working as expected:
MAX(LASTWK) OVER(
PARTITION BY format_date('%d',STRT_DT), HR, cast(TIME_INT as int64)
ORDER BY
datetime_trunc(STRT_DT, day)
RANGE BETWEEN 35 PRECEDING AND 0 PRECEDING
) MAXCNT,
MIN(LASTWK) OVER(
PARTITION BY format_date('%d',STRT_DT), HR, cast(TIME_INT as int64)
ORDER BY
datetime_trunc(STRT_DT, day)
RANGE BETWEEN 35 PRECEDING AND 0 PRECEDING
) MINCNT
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
