5

I am facing a issue. Basically, i want to delete all duplicate rows, i did it successfully in a few tables. But there is a table that contains FLOAT column which is REVENUE. It show this error:

Error: Partitioning by expressions of type FLOAT64 is not allowed at [5:70] Here are my codes:

CREATE OR REPLACE TABLE `pops-204909.monthly_reports.top_20_countries_revenue` AS
SELECT * EXCEPT(rn)
FROM 
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY CMS_ID, DATE,COUNTRY_NAME,REVENUE ORDER BY DATE  ) rn
  FROM `pops-204909.monthly_reports.top_20_countries_revenue`
)
WHERE rn = 1
Tim Swast
  • 14,091
  • 4
  • 38
  • 61
Phuc Trinh
  • 355
  • 1
  • 5
  • 10

1 Answers1

5

If you are 100% sure that you actually need REVENUE in the columns for partition, you need to convert into a string.

Usually this is a mistake, and partitioning only by CMS_ID should be fine.

CAST(REVENUE as STRING)

Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • CMS_ID is repeated montly therefore i gotta use other columns in PARTITION. – Phuc Trinh Sep 17 '18 at 09:14
  • 1
    Side note: storing revenue as a `NUMERIC` type is probably a better idea, since you won't have issues with precision loss as you would with `FLOAT64`. You can also use `NUMERIC` inside `PARTITION BY`. – Elliott Brossard Sep 17 '18 at 15:46