如何在BigQuery SQL中按特定条件识别、标记或删除重复记录？

Hey there! Let's tackle your two main needs for your hotel regulatory database in BigQuery—adding the IS Duplicate column, and handling duplicate records based on specific conditions.

1. Adding the IS Duplicate Column #

To flag duplicates based on Report type and Location (ignoring Date differences), you can use a window function to count occurrences per group. Here's the SQL query:

SELECT
  *,
  CASE
    WHEN COUNT(*) OVER (PARTITION BY `Report type`, Location) > 1 THEN 'duplicate'
    ELSE 'Not Duplicate'
  END AS `IS Duplicate`
FROM
  your_hotel_regulatory_table;

How this works: #

• PARTITION BY \Report type`, Location` groups all records that share the same report type and location (regardless of date).
• COUNT(*) OVER (...) counts how many records are in each group.
• The CASE statement labels groups with more than 1 record as duplicate, others as Not Duplicate.

2. Distinguishing or Removing Duplicate Records #

Absolutely, you can both identify and delete duplicates using specific conditions in BigQuery. Here are two common approaches:

Distinguish Duplicates (Mark Ranked Records) #

Use ROW_NUMBER() to assign a rank to each record within its Report type + Location group. This helps you tell which records are duplicates and which is the "primary" one (e.g., latest or earliest date):

SELECT
  *,
  ROW_NUMBER() OVER (
    PARTITION BY `Report type`, Location 
    ORDER BY Date DESC  -- Use ASC to keep the earliest record instead
  ) AS record_rank
FROM
  your_hotel_regulatory_table;

• Records with record_rank = 1 are the latest (or earliest) in their group, while ranks >1 are duplicates.

Remove Duplicates #

You have two main options here: create a cleaned table with no duplicates, or delete duplicates directly from the original table.

Option 1: Create a Cleaned Table (Recommended for Safety)

This creates a new table with only one record per Report type + Location group (keeping the latest date):

CREATE OR REPLACE TABLE your_hotel_regulatory_table_cleaned AS
SELECT
  * EXCEPT(record_rank)
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY `Report type`, Location 
      ORDER BY Date DESC
    ) AS record_rank
  FROM
    your_hotel_regulatory_table
)
WHERE record_rank = 1;

Option 2: Delete Duplicates Directly from the Original Table

If you need to modify the original table, use a DELETE statement with a subquery to target duplicate records:

DELETE FROM your_hotel_regulatory_table
WHERE (`Report type`, Location, Date) IN (
  SELECT `Report type`, Location, Date
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER (
        PARTITION BY `Report type`, Location 
        ORDER BY Date DESC
      ) AS record_rank
    FROM your_hotel_regulatory_table
  )
  WHERE record_rank > 1
);

• This deletes all records in each group except the one with record_rank = 1 (the latest date in this example).

内容的提问来源于stack exchange，提问作者Harun