Remove duplicate rows according to the attribute in google BigQuery SQL

google-bigquery, sql

Solution

You could use ROW_NUMBER

WITH CTE AS 
(SELECT *, ROW_NUMBER() OVER (PARTITION BY transactionid ORDER BY 
transactionid) ROW FROM [YourTable]) 

DELETE [YourTable] 
FROM [YourTable]
JOIN CTE ON [YourTable].transactionid ON CTE.transactionid
                              WHERE CTE.ROW > 1

Problem

I have one table called: result I am using BigQuery to select the data from GA ``` SELECT Date, totals.pageviews, h.transaction.transactionId, h.item.itemQuantity, h.transaction.transactionRevenue, totals.bounces, fullvisitorid, totals.timeOnSite, device.browser, device.deviceCategory, trafficSource.source, channelGrouping, h.page.pagePath, h.eventInfo.eventCategory, device.operatingSystem FROM `atomic-life-148403.126959513.ga_sessions_*`, UNNEST(hits) AS h WHERE _TABLE_SUFFIX BETWEEN REPLACE(CAST(DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR) AS STRING), '-','') AND CONCAT('intraday_', REPLACE(CAST(DATE_ADD(CURRENT_DATE(), INTERVAL 0 DAY) AS STRING), '-','')) ORDER BY date DESC ``` There are some records duplicated. How to remove the duplicated record from the table? I would like to get the following results.

Original source