如何在Redshift psql中自动遍历表列并执行统计查询

Hey there! I feel your pain—manual copy-pasting for every column is no fun, especially when it drains your mouse battery. Let's get this automated directly in Redshift without having to pull that massive dataset into pandas. Dynamic SQL is the way to go here, and it's simpler than writing Python UDFs for this use case (since Python UDFs in Redshift are designed for row-level processing, not bulk column stats).

Step 1: Automatically Generate the Stats Query #

We'll use Redshift's system catalog (information_schema.columns) to fetch all numeric columns from your table, then dynamically build a single query that unions stats for each column. This way, you get one row per column with all your required metrics.

First, run this query to generate the full stats SQL:

WITH column_list AS (
  SELECT column_name
  FROM information_schema.columns
  WHERE table_name = 'my_table'
    AND table_schema = 'public' -- Replace with your table's schema (e.g., public)
    -- Filter to only numeric columns (since stats like percentiles don't work on strings/dates)
    AND data_type IN ('integer', 'numeric', 'decimal', 'bigint', 'smallint', 'real', 'double precision')
)
SELECT STRING_AGG(
  format(
    $$SELECT '%s' AS col_name,
           percentile_cont(0.05) WITHIN GROUP (ORDER BY %s) AS perct_5,
           percentile_cont(0.1) WITHIN GROUP (ORDER BY %s) AS perct_10,
           percentile_cont(0.25) WITHIN GROUP (ORDER BY %s) AS perct_25,
           percentile_cont(0.5) WITHIN GROUP (ORDER BY %s) AS perct_50,
           percentile_cont(0.75) WITHIN GROUP (ORDER BY %s) AS perct_75,
           percentile_cont(0.9) WITHIN GROUP (ORDER BY %s) AS perct_90,
           percentile_cont(0.95) WITHIN GROUP (ORDER BY %s) AS perct_95,
           variance(%s) AS col_var,
           AVG(%s) AS col_avg
    FROM my_table$$,
    column_name, column_name, column_name, column_name, column_name, column_name, column_name, column_name, column_name, column_name
  ),
  ' UNION ALL '
) AS dynamic_stats_sql
FROM column_list;

Step 2: Run the Generated SQL #

Copy the output of the above query (the dynamic_stats_sql value) and execute it directly in your Redshift psql session. This will return a result set where each row corresponds to one column's stats—exactly what you need.

Step 3 (Optional): Wrap It in a Stored Procedure for Reuse #

If you need to run this frequently, wrap the logic in a stored procedure so you can call it with a single command:

CREATE OR REPLACE PROCEDURE get_column_stats(schema_name VARCHAR, table_name VARCHAR)
LANGUAGE plpgsql
AS $$
DECLARE
  dynamic_sql TEXT;
BEGIN
  WITH column_list AS (
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = get_column_stats.table_name
      AND table_schema = get_column_stats.schema_name
      AND data_type IN ('integer', 'numeric', 'decimal', 'bigint', 'smallint', 'real', 'double precision')
  )
  SELECT STRING_AGG(
    format(
      $$SELECT '%s' AS col_name,
             percentile_cont(0.05) WITHIN GROUP (ORDER BY %s) AS perct_5,
             percentile_cont(0.1) WITHIN GROUP (ORDER BY %s) AS perct_10,
             percentile_cont(0.25) WITHIN GROUP (ORDER BY %s) AS perct_25,
             percentile_cont(0.5) WITHIN GROUP (ORDER BY %s) AS perct_50,
             percentile_cont(0.75) WITHIN GROUP (ORDER BY %s) AS perct_75,
             percentile_cont(0.9) WITHIN GROUP (ORDER BY %s) AS perct_90,
             percentile_cont(0.95) WITHIN GROUP (ORDER BY %s) AS perct_95,
             variance(%s) AS col_var,
             AVG(%s) AS col_avg
      FROM %I.%I$$,
      column_name, column_name, column_name, column_name, column_name, column_name, column_name, column_name, column_name, column_name, schema_name, table_name
    ),
    ' UNION ALL '
  ) INTO dynamic_sql
  FROM column_list;

  -- Execute the dynamically built query
  EXECUTE dynamic_sql;
END;
$$;

To use the stored procedure, just run:

CALL get_column_stats('public', 'my_table');

Key Notes #

• Filtering Numeric Columns: The query only includes numeric data types because functions like percentile_cont and variance don't work on non-numeric columns (strings, dates, etc.). Adjust the data_type list if you need to include other numeric types specific to your schema.
• Performance: Since this runs entirely within Redshift, it leverages the cluster's processing power without moving data to your local machine—perfect for large tables.
• Customization: If you need to add a WHERE clause to filter rows (e.g., for a specific date range), just add it to the FROM my_table line in the format string.

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