Google BigQuery标准SQL：字段缺失值填补方案求助

Hey there! Let's troubleshoot why your IFNULL and COALESCE attempts aren't working for creating the STREET_NAME variable, and get this sorted out.

First, let's break down the possible reasons and solutions:

1. Verify if you're actually dealing with NULL values #

Sometimes what looks like a "null" in your data might actually be an empty string ('') instead of a proper SQL NULL. That's a super common gotcha! To confirm this, run a quick query to inspect your data:

SELECT 
  ON_STREET_NAME, 
  OFF_STREET_NAME,
  -- Check if it's a real NULL
  ISNULL(ON_STREET_NAME) AS is_on_street_null,
  -- Check if it's an empty string
  LENGTH(ON_STREET_NAME) AS on_street_length
FROM your_table
LIMIT 10;

If is_on_street_null returns 0 but on_street_length is 0, you're dealing with empty strings, not actual NULLs.

2. Solutions based on your data type #

If you have real NULL values

Double-check your syntax for creating and populating the STREET_NAME column. Here's the standard approach that works with most SQL dialects (MySQL, PostgreSQL, SQL Server, etc.):

-- Add the new column first (adjust the data type to match your existing columns)
ALTER TABLE your_table ADD COLUMN STREET_NAME VARCHAR(255);

-- Populate it using COALESCE (standard SQL, universally supported)
UPDATE your_table 
SET STREET_NAME = COALESCE(ON_STREET_NAME, OFF_STREET_NAME);

Or if you just need to generate the value in a query without altering the table:

SELECT 
  *,
  COALESCE(ON_STREET_NAME, OFF_STREET_NAME) AS STREET_NAME
FROM your_table;

If you have empty strings instead of NULL

Use a CASE statement to handle both NULLs and empty strings, or combine COALESCE with NULLIF for a cleaner approach:
Option 1: Using CASE

SELECT 
  *,
  CASE 
    WHEN ON_STREET_NAME IS NOT NULL AND ON_STREET_NAME != '' THEN ON_STREET_NAME
    ELSE OFF_STREET_NAME
  END AS STREET_NAME
FROM your_table;

Option 2: Using COALESCE + NULLIF

SELECT 
  *,
  COALESCE(NULLIF(ON_STREET_NAME, ''), OFF_STREET_NAME) AS STREET_NAME
FROM your_table;

NULLIF(ON_STREET_NAME, '') converts empty strings to NULL, so COALESCE will then automatically fall back to OFF_STREET_NAME.

3. Quick sanity check #

Don't forget to double-check for typos in table or column names—small mistakes can break everything! If you're working with a non-SQL tool (like Excel, Python pandas, etc.), let me know and I can tweak the solution for that environment.

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