Excel中针对Redshift/Postgres的动态SQL查询性能问题及实现难题

Hey there, I’ve helped folks tackle this exact scenario before—getting a dynamic IN clause working between Excel Power Query and Redshift/PostgreSQL, plus fixing the performance hit that comes with long ID lists. Let’s break this down step by step:

一、实现动态IN参数传递（替换单个等于参数） #

First, let’s assume your Excel ID list is in Sheet1, column A (starting at A2 with a header "ID"). Here’s how to turn that list into a usable IN clause:

1. Load the ID list into Power Query

   • Go to the Data tab → From Table/Range → select your ID range (include the header)
   • In the Power Query Editor, strip out any extra columns so you only have the ID column left
   • If your IDs are strings (like UUIDs or text codes), add a custom column to wrap each ID in single quotes: = "'" & [ID] & "'"
   • If your IDs are numbers, you can skip the quoting step—just keep the raw values

2. Combine the list into an IN-ready string

   • Head to the Transform tab → Aggregate → Merge Columns
   • Choose a comma (,) as the separator, name the new column something like IN_Values
   • You’ll end up with a string like 'abc123','def456','ghi789' (for strings) or 1001,1002,1003 (for numbers)

3. Build your dynamic SQL query

   • Go back to your Redshift/PostgreSQL connection in Power Query. Replace your old single-parameter SQL with this:

     = "SELECT * FROM your_target_table WHERE id IN (" & IN_Values & ")"
     

   • Pro tip: Add a guard clause for empty ID lists to avoid invalid SQL:

     = if IN_Values = "" then "SELECT * FROM your_target_table WHERE 1=0" else "SELECT * FROM your_target_table WHERE id IN (" & IN_Values & ")"
     

二、优化长ID列表的查询性能 #

If you’re working with hundreds or thousands of IDs, a raw IN clause will slow down your query (and might even hit database parser limits). Instead, use a temporary table + JOIN—this is way faster for large datasets:

1. Load your Excel IDs into a database temp table

   • In Power Query, take your raw ID list (before merging into a string) and go to Home → Close & Load To → select "Only Create Connection" and check "Add this data to the Data Model"
   • Right-click the new connection → Load To → choose "Table", and specify a temporary table in your database (e.g., #temp_ids for Redshift, or TEMP TABLE temp_ids ON COMMIT DROP for PostgreSQL)
   • Note: Redshift temp tables are session-specific; PostgreSQL temp tables with ON COMMIT DROP get cleaned up automatically when your transaction ends

2. Rewrite your query to use a JOIN

   • Replace your IN clause with a JOIN against the temp table:

     SELECT t.*
     FROM your_target_table t
     JOIN #temp_ids temp ON t.id = temp.id
     

   • This leverages database indexes to speed up the match, which is way more efficient than parsing a huge IN list

三、避坑小贴士 #

• SQL注入防护: If your string IDs contain single quotes, escape them in Power Query first: = "'" & Text.Replace([ID], "'", "''") & "'"—this prevents broken SQL and injection risks
• 数据类型匹配: Make sure your Excel ID data type matches the database’s ID column type (e.g., don’t pass text strings to a numeric ID column)
• 自动清理: Redshift temp tables vanish when your session ends; PostgreSQL’s ON COMMIT DROP temp tables clean themselves up, so no need to manually delete them

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