如何根据下一行决定列值

Question

我正在使用 Teradata 16.20.53.13 并有一个表（最小化版本）：

CREATE TABLE TABLE_1
     (
      std_nm VARCHAR(50),      
      std_age INTEGER
     );

包含数据（最小子集）：

insert into TABLE_1 values ('abc', 31);
insert into TABLE_1 values ('abc', 36);
insert into TABLE_1 values ('abc', 35);
insert into TABLE_1 values ('xyz', 17);
insert into TABLE_1 values ('xyz', 14);

< img src="https://i.sstatic.net/baOSs.png" alt="TABLE_1">

我想要的是计数、总和和平均值std_age 仅适用于 std_nm 的最后一个唯一值。 我遵循的一种方法给了我想要的结果，但在所有行中都有计数、总和和平均值：

    select t1.std_nm, 
           t1.std_age, 
           t2.name_count, 
           t2.sum_age, 
           t2.avg_age
      from TABLE_1 as t1
inner join (
              select std_nm, 
                     count(std_nm) as name_count, 
                     sum(std_age) as sum_age, 
                     avg(std_age) as avg_age
                from TABLE_1
            group by std_nm
           ) t2
        on t1.std_nm = t2.std_nm
  order by t1.std_nm;

使用上面的 SQL，我的结果如下所示：

问题： 如何获得零/空结果/空白name_count、sum_age 和 avg_age 用于除最后一个之外的 std_nm 的所有唯一值。所以我正在寻找的结果是：

我尝试在选择 name_count、sum_age 和 avg_age 时使用 CASE但我认为可能有更好/更干净的方法来做到这一点。也许通过以更智能的方式使用 join 或其他方式。我对适用于 Teradata 16 的所有选项持开放态度。

Answer 1

SELECT D.std_nm,D.std_age,D.XCOL,
 CASE 
   WHEN D.XCOL=1 THEN SUB_Q.avg_age
   ELSE NULL
 END AS AVG_AGE,
CASE
 WHEN D.XCOL=1 THEN SUB_Q.name_count 
 ELSE NULL
END AS NAME_COUNT,
CASE
 WHEN D.XCOL=1 THEN SUB_Q.sum_age
 ELSE NULL
END AS SUM_AGE
FROM 
(
   SELECT T.std_nm,T.std_age,
   ROW_NUMBER()OVER (PARTITION BY T.std_nm ORDER BY T.std_age DESC)XCOL 
   FROM TABLE_1 AS T
)D
JOIN
(
   select std_nm, 
                 count(std_nm) as name_count, 
                 sum(std_age) as sum_age, 
                 avg(std_age) as avg_age
            from TABLE_1
        group by std_nm
)SUB_Q ON D.std_nm=SUB_Q.std_nm

如果以上内容适合您，请您尝试一下

Answer 2

@Sergey 的答案可以通过使用组聚合来避免连接来简化。这是大量的剪切和粘贴，但应该会导致解释中的一个步骤：

SELECT std_nm,std_age
  ,CASE
     WHEN Row_Number() Over (PARTITION BY std_nm ORDER BY std_age DESC)=1
     THEN Count(std_nm) Over (PARTITION BY std_nm )   
   END AS NAME_COUNT
  ,CASE
     WHEN Row_Number() Over (PARTITION BY std_nm ORDER BY std_age DESC)=1 
     THEN Sum(std_age) Over (PARTITION BY std_nm )
   END AS SUM_AGE
  ,CASE 
     WHEN Row_Number() Over (PARTITION BY std_nm ORDER BY std_age DESC)=1 
     THEN Avg(std_age) Over (PARTITION BY std_nm )
   END AS AVG_AGE
FROM TABLE_1 
;