如何在Tableau中关联Redshift不同Schema下的相同查询？

Got it, let's work through this. You’ve got two identical email activity queries pulling from separate Redshift schemas, and you need to either combine their results or link them together—here’s how to tackle both common scenarios:

Option 1: Combine Datasets (Union) #

If you want to merge all email activity records from both schemas into a single result set (great for aggregating across both sources), use UNION ALL (it’s faster than UNION because it doesn’t waste time deduplicating, though swap to UNION if you need to remove exact duplicates).

Here’s the adjusted query:

WITH activities_schema1 AS (
    SELECT 
        A.lead_id, 
        A.primary_attribute_value_id, 
        T.name AS activity_name,
        A.activity_date,
        'marketo_sc' AS data_source -- Optional: Tag where the record came from
    FROM marketo_sc.lead_activities A
    JOIN marketo_sc.lead_activity_types T 
        ON A.activity_type_id = T.id
    WHERE T.name IN ('Send Email', 'Email Delivered', 'Open Email', 'Click Email', 'Unsubscribe Email')
    GROUP BY A.lead_id, A.primary_attribute_value_id, T.name, A.activity_date -- Fill in your omitted GROUP BY fields here
),
activities_schema2 AS (
    SELECT 
        A.lead_id, 
        A.primary_attribute_value_id, 
        T.name AS activity_name,
        A.activity_date,
        'your_second_schema' AS data_source -- Replace with your actual second schema name
    FROM your_second_schema.lead_activities A
    JOIN your_second_schema.lead_activity_types T 
        ON A.activity_type_id = T.id
    WHERE T.name IN ('Send Email', 'Email Delivered', 'Open Email', 'Click Email', 'Unsubscribe Email')
    GROUP BY A.lead_id, A.primary_attribute_value_id, T.name, A.activity_date -- Match the GROUP BY from the first CTE exactly
)
SELECT * FROM activities_schema1
UNION ALL
SELECT * FROM activities_schema2;

• The data_source column is optional but super helpful for filtering or validating which schema each record originated from later on.
• Double-check that both CTEs return identical column names and data types—Redshift will throw an error if they don’t align.

Option 2: Link Datasets (Join) #

If you need to compare or correlate records across the two schemas (e.g., see if a lead has matching email activities in both sources), use a join. The type of join depends on which records you want to keep:

• INNER JOIN: Only keep records that exist in both schemas
• LEFT JOIN: Keep all records from the first schema, plus matches from the second
• FULL OUTER JOIN: Keep all records from both schemas, even if there’s no match

Example using FULL OUTER JOIN to capture everything:

WITH activities_schema1 AS (
    SELECT 
        A.lead_id, 
        A.primary_attribute_value_id, 
        T.name AS activity_name_schema1,
        A.activity_date AS activity_date_schema1
    FROM marketo_sc.lead_activities A
    JOIN marketo_sc.lead_activity_types T 
        ON A.activity_type_id = T.id
    WHERE T.name IN ('Send Email', 'Email Delivered', 'Open Email', 'Click Email', 'Unsubscribe Email')
    GROUP BY A.lead_id, A.primary_attribute_value_id, T.name, A.activity_date
),
activities_schema2 AS (
    SELECT 
        A.lead_id, 
        A.primary_attribute_value_id, 
        T.name AS activity_name_schema2,
        A.activity_date AS activity_date_schema2
    FROM your_second_schema.lead_activities A
    JOIN your_second_schema.lead_activity_types T 
        ON A.activity_type_id = T.id
    WHERE T.name IN ('Send Email', 'Email Delivered', 'Open Email', 'Click Email', 'Unsubscribe Email')
    GROUP BY A.lead_id, A.primary_attribute_value_id, T.name, A.activity_date
)
SELECT 
    COALESCE(s1.lead_id, s2.lead_id) AS lead_id,
    COALESCE(s1.primary_attribute_value_id, s2.primary_attribute_value_id) AS email_id,
    s1.activity_name_schema1,
    s1.activity_date_schema1,
    s2.activity_name_schema2,
    s2.activity_date_schema2
FROM activities_schema1 s1
FULL OUTER JOIN activities_schema2 s2
    ON s1.lead_id = s2.lead_id
    AND s1.primary_attribute_value_id = s2.primary_attribute_value_id;

• Use COALESCE to avoid NULLs in the shared lead_id and email_id columns when one side of the join has no match.
• Adjust the join keys if you need to correlate on different fields (e.g., just lead_id if you don’t care about matching specific emails).

Quick Notes #

1. Permissions: Make sure your Redshift user has SELECT access to both schemas’ lead_activities and lead_activity_types tables.
2. GROUP BY Consistency: Redshift enforces strict GROUP BY rules—every non-aggregated column in your SELECT must be included in the GROUP BY clause. Don’t skip any fields you originally had in your query.
3. Performance: For large datasets, consider adding filters to the WHERE clauses (e.g., date ranges) to reduce the data being processed before the union/join.

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