Как определить значение столбца на основе следующей строки
Я использую 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);
Я хочу иметь подсчет, сумму и среднее значение 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, кроме последнего. Итак, результат, который я ищу:
Я пытаюсь использовать CASE при выборе name_count, sum_age и avg_age, но я думаю, что может быть лучший/более чистый способ сделать это. Возможно, используя более разумное соединение или что-то еще. Я открыт для всех вариантов, которые работают на Teradata 16.
Это не имеет значения, но если это необходимо, скажем, наибольшее значение для «abc» (36) и наибольшее значение для «xyz» (17). Но было бы прекрасно, если бы это было наименьшее или любое другое значение std_age. Я просто хочу, чтобы последнее значение std_nm в конечном результате имело «количество», «сумму» и «среднее». Остальные должны быть нулевыми/пустыми/нулевыми.
Ответы 2
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
Не могли бы вы попробовать выше, если это подходит для вас
Он отлично работает и именно то, что я искал. Я просто добавил ORDER BY D.std_nm,D.std_age; в конце и изменил последовательность столбцов на NAME_COUNT, SUM_AGE и AVG_AGE. Спасибо @Сергей. Я действительно ценю твою помощь.
Ответ @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
;
Это круто. Спасибо, что поделились
Думаю, первый вопрос, который вы должны себе задать: каково последнее значение? Это «abc», 35 или «abc», 31 или что-то другое