如何在 Teradata sql 中获取一行中的最大列值? [英] How to get maximum column values across a row in Teradata sql?
问题描述
我有一个名为 cnst_chrctrstc_abc 的表，每行有 10 列 (equ_gender1 - bb_population_flag)，其中包含数值 (count) .我想从这 10 个数字列的每一行中获得最多 5 个值.
I have a table named cnst_chrctrstc_abc with 10 columns (equ_gender1 - bb_population_flag) each row which contain numeric values (count) . I want to get maximum 5 values out of each row across those 10 numeric columns.
我的查询类似于以下内容..
The query I have looks something like the following ..
SEL
FROM
(
SEL
SUM(CASE WHEN COALESCE(act.equ_gender1,'') = COALESCE(inact.equ_gender1,'') THEN 0 ELSE 1 END ) AS equ_gender1_chg_cnt,
SUM(CASE WHEN COALESCE(act.exp_ex_bmyr1,'') = COALESCE(inact.exp_ex_bmyr1,'') THEN 0 ELSE 1 END ) AS exp_ex_bmyr1_chg_cnt,
SUM(CASE WHEN COALESCE(act.equ_age1,'') = COALESCE(inact.equ_age1,'') THEN 0 ELSE 1 END ) AS equ_age1_chg_cnt,
SUM(CASE WHEN COALESCE(act.maritalstatus1,'') = COALESCE(inact.maritalstatus1,'') THEN 0 ELSE 1 END ) AS maritalstatus1_chg_cnt,
SUM(CASE WHEN COALESCE(act.person_type1,'') = COALESCE(inact.person_type1,'') THEN 0 ELSE 1 END ) AS person_type1_chg_cnt,
SUM(CASE WHEN COALESCE(act.homeowner,'') = COALESCE(inact.homeowner,'') THEN 0 ELSE 1 END ) AS homeowner_chg_cnt,
SUM(CASE WHEN COALESCE(act.dwelling_size,'') = COALESCE(inact.dwelling_size,'') THEN 0 ELSE 1 END ) AS dwelling_size_chg_cnt,
SUM(CASE WHEN COALESCE(act.lengthofresidence,'') = COALESCE(inact.lengthofresidence,'') THEN 0 ELSE 1 END ) AS lengthofresidence_chg_cnt,
SUM(CASE WHEN COALESCE(act.childrenage0_18,'') = COALESCE(inact.childrenage0_18,'') THEN 0 ELSE 1 END ) AS childrenage0_18_chg_cnt,
SUM(CASE WHEN COALESCE(act.bb_population_flag,'') = COALESCE(inact.bb_population_flag,'') THEN 0 ELSE 1 END ) AS bb_population_flag
FROM
(SEL * FROM arc_mdm_Tbls.cnst_chrctrstc_abc WHERE load_id=1024 AND cnst_chrctrstc_end_dt='9999-12-31' (DATE))act
LEFT JOIN
(SEL * FROM arc_mdm_Tbls.cnst_chrctrstc_abc WHERE load_id=1024 AND cnst_chrctrstc_end_dt<'9999-12-31' (DATE)
QUALIFY ROW_NUMBER() OVER (PARTITION BY cnst_mstr_id ORDER BY cnst_chrctrstc_strt_ts DESC)=1
)inact
ON act.cnst_mstr_id = inact.cnst_mstr_id
)X
我知道 SEL GREATEST 会产生每一行的最大值.但我想要 5 个最高值并为它们分配一个等级.
I know SEL GREATEST would produce the maximum value out of each row . But I want 5 top values and assign a rank to them.
对于某些行，前五列可能包含前 5 个值，而对于后五列，即房主到 bb_population_flag 可能包含前 5 个值.
Something like for some row first five columns may hold the top 5 values and for some last five i.e. homeowner to bb_population_flag may hold the top 5 values.
因此，如果来自 cnst_chrctrstc_abc 的列和值类似于以下内容
so if the columns and values from cnst_chrctrstc_abc look something like the following
cdi_batch_id | a | b | c | d | e | f | g | h | i |j
1024 |116|105|102|100|117|119|108|104|101|121
所以选择查询应该返回我的列 j,f,e,a,g 具有前 5 个值.然后我会相应地为他们分配一个等级.
so the select query should return me columns j,f,e,a,g having the top 5 values. And then I would assign a rank to them accordingly .
是否应该使用 unpivot 或其他方法来完成?提前致谢.
Should it be done using unpivot or something ? Thanks in advance.
推荐答案
是的，您需要对结果进行逆透视.
Yes, you need to unpivot your result.
在 TD14.10 之前，您将需要这些列名称的列表，或者作为表格
Before TD14.10 you will need a list of those column names, either as a table
create table ColumnList (col varchar(128));
Insert into ColumnList('equ_gender1' );
Insert into ColumnList('exp_ex_bmyr1' );
Insert into ColumnList('equ_age1' );
Insert into ColumnList('maritalstatus1' );
Insert into ColumnList('person_type1' );
Insert into ColumnList('homeowner' );
Insert into ColumnList('dwelling_size' );
Insert into ColumnList('lengthofresidence' );
Insert into ColumnList('childrenage0_18' );
Insert into ColumnList('bb_population_flag');
或使用笨重的
with ColumnList as
(
select * from (select 'equ_gender1' as Col) as dt union all
select * from (select 'exp_ex_bmyr1' as Col) as dt union all
select * from (select 'equ_age1' as Col) as dt union all
select * from (select 'maritalstatus1' as Col) as dt union all
select * from (select 'person_type1' as Col) as dt union all
select * from (select 'homeowner' as Col) as dt union all
select * from (select 'dwelling_size' as Col) as dt union all
select * from (select 'lengthofresidence' as Col) as dt union all
select * from (select 'childrenage0_18' as Col) as dt union all
select * from (select 'bb_population_flag' as Col) as dt
)
然后您交叉加入以取消透视:
Then you CROSS JOIN to unpivot:
select
col,
case col
when 'equ_gender1' then equ_gender1
when 'exp_ex_bmyr1' then exp_ex_bmyr1
when 'equ_age1' then equ_age1
when 'maritalstatus1' then maritalstatus1
when 'person_type1' then person_type1
when 'homeowner' then homeowner
when 'dwelling_size' then dwelling_size
when 'lengthofresidence' then lengthofresidence
when 'childrenage0_18' then childrenage0_18
when 'bb_population_flag' then bb_population_flag
end as Counts,
rank() over (order by Counts desc) as rnk
FROM
(
your current select
) as dt
cross join ColumnList
qualify rnk <= 5
在 TD14.10 中，您可以使用 TD_UNPIVOT 函数:
In TD14.10 you could utilize the TD_UNPIVOT function:
SELECT Col, rank() over (order by Counts desc) as rnk
from TD_UNPIVOT(
ON (
your current select
)
USING
VALUE_COLUMNS('Counts')
UNPIVOT_COLUMN('Col')
COLUMN_LIST('equ_gender1'
,'exp_ex_bmyr1'
,'equ_age1'
,'maritalstatus1'
,'person_type1'
,'homeowner'
,'dwelling_size'
,'lengthofresidence'
,'childrenage0_18'
,'bb_population_flag')
) dt
qualify rnk <= 5;
此外，您可以用单个 OLAP 函数替换 LEFT JOIN.根据每个 cnst_mstr_id 的行数，这可能更有效，因为您无论如何都需要 ROW_NUMBER:
Additionally you might replace your LEFT JOIN with a single OLAP-function. Depending on the number of rows per cnst_mstr_id this might be more efficient as you need a ROW_NUMBER anyway:
SEL
SUM(CASE WHEN COALESCE(equ_gender1,'') = COALESCE(last_equ_gender1,'') THEN 0 ELSE 1 END ) AS equ_gender1_chg_cnt,
...
FROM
( SELECT
min(equ_gender1) OVER (PARTITION BY cnst_mstr_id ORDER BY cnst_chrctrstc_strt_ts DESC rows between 1 following and 1 following) as equ_gender1,
...
FROM arc_mdm_Tbls.cnst_chrctrstc_abc
WHERE load_id=1024
qualify cnst_chrctrstc_end_dt= date '9999-12-31'
)act
这篇关于如何在 Teradata sql 中获取一行中的最大列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
