尝试下面
#standardSQLWITH history AS ( SELECt '2017-01-01' AS d, 'a' AS product, 'x' AS partner, 10 AS value UNIOn ALL SELECT '2017-01-01' AS d, 'b' AS product, 'x' AS partner, 15 AS value UNIOn ALL SELECT '2017-01-01' AS d, 'a' AS product, 'y' AS partner, 11 AS value UNIOn ALL SELECT '2017-01-01' AS d, 'b' AS product, 'y' AS partner, 16 AS value UNIOn ALL SELECT '2017-01-05' AS d, 'b' AS product, 'x' AS partner, 13 AS value UNIOn ALL SELECT '2017-01-07' AS d, 'a' AS product, 'y' AS partner, 15 AS value UNIOn ALL SELECT '2017-01-07' AS d, 'a' AS product, 'x' AS partner, 15 AS value ),daterange AS ( SELECT date_in_range FROM UNNEST(GENERATE_DATE_ARRAY('2017-01-01', '2017-01-10')) AS date_in_range ),temp AS ( SELECt d, product, partner, value, LEAD(d) OVER(PARTITION BY product, partner ORDER BY d) AS next_d FROM history ORDER BY product, partner, d)SELECt date_in_range, product, partner, valueFROM daterangeJOIN tempON daterange.date_in_range >= PARSE_DATE('%Y-%m-%d', temp.d) AND (daterange.date_in_range < PARSE_DATE('%Y-%m-%d', temp.next_d) OR temp.next_d IS NULL)ORDER BY product, partner, date_in_range
