Deduplicate rows in a BigQuery partition

Question

I have a table with many duplicated rows - but I only want to deduplicate rows one partition at a time.

How can I do this?

As an example, you can start with a table partitioned by date and filled with random integers from 1 to 5:

CREATE OR REPLACE TABLE `temp.many_random`
PARTITION BY d
AS 
SELECT DATE('2018-10-01') d, fhoffa.x.random_int(0,5) random_int
FROM UNNEST(GENERATE_ARRAY(1, 100))
UNION ALL
SELECT CURRENT_DATE() d, fhoffa.x.random_int(0,5) random_int
FROM UNNEST(GENERATE_ARRAY(1, 100))

Answer 1

Additional answer - for complex rows that can't use DISTINCT:

MERGE `temp.many_random` t
USING (
  # choose a single row to delete the duplicates
  SELECT a.*
  FROM (
    SELECT ANY_VALUE(a) a
    FROM `temp.many_random` a
    WHERE d='2018-10-01'
    GROUP BY d, random_int # id
  )
)
ON FALSE
WHEN NOT MATCHED BY SOURCE AND d='2018-10-01' 
  # delete the duplicates
  THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT ROW