能否在SQL的UDF中传入列名？重复查询逻辑优化咨询

Great question! The short answer is yes, you can simplify this repetitive logic, but the exact approach depends on your SQL engine and the tools you're using. Let's break down practical solutions for common scenarios:

1. Use a Window UDF (if your engine supports it) #

Some modern SQL engines like BigQuery support window function UDFs, which let you encapsulate the windowed CASE/MAX logic directly. Here's an example:

First, define the UDF:

CREATE OR REPLACE FUNCTION fn_check_date(col DATE, anchor_date DATE) 
RETURNS INT64 
WINDOW AS (PARTITION BY user_id)
LANGUAGE SQL AS
MAX(CASE WHEN col <= anchor_date THEN 1 ELSE 0 END);

Then use it in your query:

SELECT DISTINCT 
  fn_check_date(table_2."GRP1_MINIMUM_DATE", cohort."ANCHOR_DATE") OVER (PARTITION BY cohort."USER_ID") AS "GRP1_MINIMUM_DATE",
  fn_check_date(table_2."GRP2_MINIMUM_DATE", cohort."ANCHOR_DATE") OVER (PARTITION BY cohort."USER_ID") AS "GRP2_MINIMUM_DATE"
FROM INPUT_COHORT cohort 
LEFT JOIN INVOLVE_EVER table_2 ON cohort."USER_ID" = table_2."USER_ID"

Note: The OVER clause still needs to be included in the query (to specify the partition column from your cohort table), but the core conditional logic is encapsulated.

2. Replace Window Functions with Aggregation (Simpler Alternative) #

Your original logic checks if any row for a user has the group date <= the anchor date. This can be rewritten using GROUP BY instead of window functions + DISTINCT, which is often cleaner and avoids needing UDFs entirely:

WITH user_date_checks AS (
  SELECT
    cohort."USER_ID",
    cohort."ANCHOR_DATE",
    MAX(CASE WHEN table_2."GRP1_MINIMUM_DATE" <= cohort."ANCHOR_DATE" THEN 1 ELSE 0 END) AS "GRP1_MINIMUM_DATE",
    MAX(CASE WHEN table_2."GRP2_MINIMUM_DATE" <= cohort."ANCHOR_DATE" THEN 1 ELSE 0 END) AS "GRP2_MINIMUM_DATE",
    -- Add other GRP columns here
  FROM INPUT_COHORT cohort
  LEFT JOIN INVOLVE_EVER table_2 ON cohort."USER_ID" = table_2."USER_ID"
  GROUP BY cohort."USER_ID", cohort."ANCHOR_DATE"
)
SELECT * FROM user_date_checks;

This produces the same result as your original query but eliminates the need for window functions and DISTINCT.

3. Use Dynamic SQL for Auto-Generated Columns #

If you have many GRP* columns and want to avoid writing each one manually, use dynamic SQL (supported in engines like PostgreSQL, SQL Server, Snowflake). This lets you generate the repetitive SELECT clauses programmatically.

Example for PostgreSQL:

CREATE OR REPLACE FUNCTION generate_cohort_query()
RETURNS TEXT AS $$
DECLARE
  grp_columns TEXT[];
  select_clause TEXT;
BEGIN
  -- Fetch all GRP*_MINIMUM_DATE columns from your table
  SELECT array_agg(column_name) INTO grp_columns
  FROM information_schema.columns
  WHERE table_name = 'INVOLVE_EVER' 
    AND column_name LIKE 'GRP%_MINIMUM_DATE';

  -- Build the SELECT clause with repeated logic
  select_clause := string_agg(
    'MAX(CASE WHEN table_2."' || col || '" <= cohort."ANCHOR_DATE" THEN 1 ELSE 0 END) OVER (PARTITION BY cohort."USER_ID") AS "' || col || '"',
    ', '
  ) FROM unnest(grp_columns) col;

  -- Return the full query string
  RETURN 'SELECT DISTINCT ' || select_clause || ' FROM INPUT_COHORT cohort LEFT JOIN INVOLVE_EVER table_2 ON cohort."USER_ID" = table_2."USER_ID"';
END;
$$ LANGUAGE plpgsql;

-- Execute the generated query
EXECUTE generate_cohort_query();

This will automatically include all matching columns without manual repetition.

4. Use Macros (with tools like dbt or SnowSQL) #

If you use data transformation tools like dbt, you can create a macro to encapsulate the repetitive logic. This is great for maintainability across multiple queries:

Example dbt macro:

{% macro check_group_date(col_name) %}
MAX(CASE WHEN table_2.{{ col_name }} <= cohort."ANCHOR_DATE" THEN 1 ELSE 0 END) OVER (PARTITION BY cohort."USER_ID") AS {{ col_name }}
{% endmacro %}

Then use it in your query:

SELECT DISTINCT 
  {{ check_group_date('GRP1_MINIMUM_DATE') }},
  {{ check_group_date('GRP2_MINIMUM_DATE') }},
  {{ check_group_date('GRP3_MINIMUM_DATE') }}
FROM INPUT_COHORT cohort 
LEFT JOIN INVOLVE_EVER table_2 ON cohort."USER_ID" = table_2."USER_ID"

Key Notes #

• Traditional scalar UDFs can't directly accept column names as identifiers (they work with values, not schema objects), but window UDFs or macros get around this.
• The aggregation approach is often the simplest if you don't need to use UDFs or dynamic SQL.

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