Teradata SQL：处理单列重复值并合并提取指定列数据

Problem Overview #

Let's say you've got an original table with sequential time ranges grouped by Column 1, and you need to merge duplicate rows for the same Column 1 value:

• Keep the first Column 2 value from each duplicate group
• Keep the last Column 3 value from each duplicate group

Original Table

Desired Output

Teradata SQL Solution #

In Teradata, we can use two straightforward approaches depending on your data's consistency:

Approach 1: Simple Aggregation (Best for Sequential Timestamps)

If your rows are ordered such that Column 2 increases sequentially for each Column 1 group (like in your sample data), aggregate functions are the quickest way to get the result:

SELECT
    Column1,
    MIN(Column2) AS Column2,  -- Gets the first occurrence of Column2 in the group
    MAX(Column3) AS Column3   -- Gets the last occurrence of Column3 in the group
FROM
    YourTableName  -- Replace with your actual table name
GROUP BY
    Column1
ORDER BY
    Column1;

Approach 2: Window Functions (For Strict Row Order Control)

If you need to guarantee you're picking the first/last row in the group regardless of timestamp order (e.g., based on insertion sequence), use ROW_NUMBER() window functions:

WITH RankedRows AS (
    SELECT
        Column1,
        Column2,
        Column3,
        -- Assign row number starting at 1 for the earliest Column2 in the group
        ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2 ASC) AS rn_first,
        -- Assign row number starting at 1 for the latest Column3 in the group
        ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column3 DESC) AS rn_last
    FROM
        YourTableName
)
SELECT
    Column1,
    MAX(CASE WHEN rn_first = 1 THEN Column2 END) AS Column2,
    MAX(CASE WHEN rn_last = 1 THEN Column3 END) AS Column3
FROM
    RankedRows
GROUP BY
    Column1
ORDER BY
    Column1;

Explanation #

• Approach 1: MIN(Column2) grabs the earliest timestamp in the group (which matches the first row's value), while MAX(Column3) grabs the latest timestamp (matching the last row's value). This is efficient and works perfectly for your sample data.
• Approach 2: The CTE ranks rows within each Column1 group. We then use conditional aggregation to pull the Column2 from the first-ranked row and Column3 from the last-ranked row. This is more flexible if your data isn't strictly ordered by timestamps.

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