SQL技术需求：基于起止街道号生成连续街道号并保留邮编

Hey Nick, no worries about your English at all! Let's tackle this problem together—expanding street number ranges into individual records is a common task, and we can easily do this with Python's Pandas library (super handy for data manipulation). Here's a step-by-step solution:

First, let's define a sample dataset matching your structure:

import pandas as pd

# Your input dataset (replace with your actual data)
input_data = {
    'inferior_street_number': [100, 200, 305],
    'superior_street_number': [104, 202, 305],
    'street_name': ['Main St', 'Oak Ave', 'Pine Rd'],
    'zip_code': ['10001', '90210', '60601']
}

df = pd.DataFrame(input_data)

This code will generate all consecutive street numbers for each range and expand them into separate rows:

# Create a list of all street numbers in each range
df['street_number'] = df.apply(
    lambda row: list(range(row['inferior_street_number'], row['superior_street_number'] + 1)),
    axis=1
)

# Explode the list into individual rows
expanded_df = df.explode('street_number').reset_index(drop=True)

# Optional: Remove the original range columns and reorder columns
expanded_df = expanded_df.drop(['inferior_street_number', 'superior_street_number'], axis=1)
expanded_df = expanded_df[['street_number', 'street_name', 'zip_code']]

After running the code, your expanded dataset will look like this:

• If your ranges only include even or odd numbers, adjust the range step: use range(start, end+1, 2) for even numbers (or odd, depending on your start value).
• For large datasets, this method is far more efficient than manual Excel filling.
• If you're not using Python, you can achieve similar results in Excel with a combination of SEQUENCE and XLOOKUP functions, but it's less scalable for big data.

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