4

Is it possible to select the record with highest version of each group and store it as a materialised view in bigquery?

Source table

version name value
1       a    100
1       b    200
1       c    300
2       c    400

I only want to get the last record of each group (group by version), so the output should be like this:

version name value
1       a    100
1       b    200
2       c    400

Tried with answer from: https://stackoverflow.com/a/1313293/5839247, got an error: Materialized views do not support analytic functions or WITH OFFSET. Tried with the method from this answer: https://stackoverflow.com/a/7745635/5839247, got Unsupported operator in materialized view: Join.

Documentation: https://cloud.google.com/bigquery/docs/materialized-views#supported_mvs

Yang
  • 177
  • 4
  • 20

1 Answers1

4

Currently, BigQuery's materialized view doesn't support OFFSET. So, you have to create another view to use OFFSET.

1. Create materialized view using ARRAY_AGG

CREATE MATERIALIZED VIEW dataset.mv_name AS
SELECT
  name, 
  ARRAY_AGG(version ORDER BY version DESC) as versions,
  ARRAY_AGG(value ORDER BY version DESC) as values
FROM
  base_table
GROUP BY
  name

2. Then, create another view using OFFSET

CREATE VIEW dataset.v_name AS
SELECT
  name,
  versions[OFFSET(0)] as last_version,
  values[OFFSET(0)] as last_value
FROM
  dataset.mv_name

Here is official document.

Shinya Yaginuma
  • 41
  • 3
  • 2
    Is there a better way to do this now that BQ supports (in preview) `inner join` and materialized views without aggregates? – ProGirlXOXO Jul 07 '21 at 21:10
  • in case values are a JSON value (since 2022-01), running the offset on the materialized view will incur in a full scan of the JSON instead of taking advantage of the JSON shredding. I didn't find a good solution working with JSONs yet unfortunately – M4rk Apr 01 '22 at 22:32