优化BigQuery中人口年增长率变化范围的SQL查询及可视化

Great question! Let's refine your SQL query to be more efficient, readable, and aligned with best practices—while still delivering exactly the data you need for your line plot visualization.

Key Issues with the Original Query #

Your initial approach works, but it calculates the small_value and large_value for every single row in the dataset, which creates redundant data processing. We can streamline this by first computing the total range for each country (once per country, not per row) and then joining back to the raw data to get all yearly values.

Optimized Query Option 1: Using Window Functions with Qualify #

This version reduces redundant calculations and ensures clean, unique country-level range values:

WITH country_ranges AS (
  SELECT 
    country_name,
    -- Get the earliest year's population growth value
    FIRST_VALUE(value) OVER (PARTITION BY country_name ORDER BY year) AS first_value,
    -- Get the latest year's population growth value
    LAST_VALUE(value) OVER (
      PARTITION BY country_name 
      ORDER BY year 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_value
  FROM `bigquery-public-data.world_bank_intl_education.international_education`
  WHERE indicator_name = 'Population growth (annual %)'
  -- Ensure only one record per country-year (handles duplicate entries if any)
  QUALIFY ROW_NUMBER() OVER (PARTITION BY country_name, year ORDER BY value) = 1
),
unique_country_ranges AS (
  SELECT 
    country_name,
    (last_value - first_value) AS total_range
  FROM country_ranges
  -- Keep only one range record per country
  QUALIFY ROW_NUMBER() OVER (PARTITION BY country_name ORDER BY year) = 1
)
-- Join back to raw data to get all yearly values with the country's total range
SELECT 
  ie.country_name,
  ie.year,
  ie.value,
  ucr.total_range
FROM `bigquery-public-data.world_bank_intl_education.international_education` ie
JOIN unique_country_ranges ucr
  ON ie.country_name = ucr.country_name
WHERE ie.indicator_name = 'Population growth (annual %)'
ORDER BY ucr.total_range, ie.year;

Optimized Query Option 2: Using Aggregation with Array Functions #

This approach is more concise and leverages BigQuery's array handling for cleaner grouping:

WITH country_ranges AS (
  SELECT
    country_name,
    -- Extract first (earliest year) value from sorted array
    (SELECT value FROM UNNEST(ARRAY_AGG(value ORDER BY year)) LIMIT 1) AS first_value,
    -- Extract last (latest year) value from sorted array
    (SELECT value FROM UNNEST(ARRAY_AGG(value ORDER BY year DESC)) LIMIT 1) AS last_value
  FROM `bigquery-public-data.world_bank_intl_education.international_education`
  WHERE indicator_name = 'Population growth (annual %)'
  GROUP BY country_name
)
SELECT
  ie.country_name,
  ie.year,
  ie.value,
  (cr.last_value - cr.first_value) AS total_range
FROM `bigquery-public-data.world_bank_intl_education.international_education` ie
JOIN country_ranges cr
  ON ie.country_name = cr.country_name
WHERE ie.indicator_name = 'Population growth (annual %)'
ORDER BY total_range, year;

What Makes These Queries Better? #

• Reduced redundancy: We calculate each country's total range once, not for every row in the dataset.
• Duplicate handling: The QUALIFY clause (in Option 1) ensures we don't have duplicate entries for the same country and year.
• Readability: Logic is split into clear, purpose-driven CTEs, making it easier to debug or modify later.

Refined Python Visualization Code #

We can also tweak your visualization code to be more efficient and polished:

import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt

# Fetch data with optimized query
optimized_query = """[PASTE YOUR OPTIMIZED QUERY HERE]"""
df = wbed.query_to_pandas_safe(optimized_query)

# Get countries with smallest (3) and largest (4) range changes
country_range_summary = df.groupby('country_name')['total_range'].first().sort_values()
selected_countries = np.concatenate([country_range_summary.index[:3], country_range_summary.index[-4:]])

# Create the line plot
plt.figure(figsize=(16, 8))
sns.lineplot(
    x='year', 
    y='value', 
    data=df[df['country_name'].isin(selected_countries)], 
    hue='country_name',
    marker='o',  # Add markers for clearer year-to-year points
    linewidth=2
)

# Add plot labels and formatting
plt.title('Population Growth (Annual %) - Countries with Smallest & Largest Range Changes', fontsize=14)
plt.xlabel('Year', fontsize=12)
plt.ylabel('Population Growth Rate (%)', fontsize=12)
plt.legend(bbox_to_anchor=(1.05, 1), loc='upper left', fontsize=10)
plt.tight_layout()
plt.show()

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