如何在SQL中使用case when和partition by将多个值合并为一个值

Question

我有一个表，在一个字段中包含3个不同的名称、这些名称关联的各个帐户、唯一ID、每对的总计数，以及基于总计数值的排名列。此处示例：

LOGO     |  Account |  ID    |   Count_Per_Logo   |  Rank
Walmart     Acct_A     ABC          3                 1
Walmart     Acct_A     DEF          3                 1
Walmart     Acct_A     GHI          3                 1
Vudu        Acct_A     JKL          1                 2
Bonobos     Acct_A     MNO          1                 2

我的目标是将LOGO字段"合并"为一个LOGO到一个Account，因此对于每个唯一的ID，它们应该只与1个徽标关联(而不是上面显示的3个(。

所需输出

LOGO     |  Account |  ID    |   Count_Per_Logo   |  Rank   | Consolidated_LOGO
Walmart     Acct_A     ABC          3                 1       Walmart     
Walmart     Acct_A     DEF          3                 1       Walmart     
Walmart     Acct_A     GHI          3                 1       Walmart     
Vudu        Acct_A     JKL          1                 2       Walmart     
Bonobos     Acct_A     MNO          1                 2       Walmart     

现在，我正在使用带有partitionby的count来获得排名，但我不确定如何创建一个基于排名值具有"所需徽标"的新字段，是否需要滞后函数？如有任何帮助，将不胜感激

电流逻辑

SELECT LOGO, ACCOUNT, ID FROM
(SELECT *, DENSE_RANK() OVER (PARTITION BY ACCOUNT ORDER BY LOGO_NAME_CNT DESC) AS LOGO_RANK
FROM
(SELECT *, count(ACCOUNT) over (partition by LOGO) AS LOGO_NAME_CNT FROM "TABLE1" WHERE ACCOUNT = 'Acct_A')
)
WHERE LOGO_RANK = 1

Answer 1

所以看起来您的基本SQL是：

SELECT
a.*
,dense_rank() over (partition by account order by count_per_logo desc) as rank
FROM (
SELECT 
logo
,account
,id
,count(logo) over (partition by account, logo) as count_per_logo
FROM VALUES 
('walmart', 'acct_a', 'abc'),   
('walmart', 'acct_a', 'def'), 
('walmart', 'acct_a', 'ghi'),
('vudu', 'acct_a', 'jkl'),
('bonobos', 'acct_a', 'mno')
v(logo, account, id)
) AS a
ORDER BY 4 DESC, 5;

那么你只需要添加一个类似FIRST_VALUE的：

SELECT
a.*
,dense_rank() over (partition by account order by count_per_logo desc) as rank
,first_value(logo) over (partition by account order by count_per_logo desc) as Consolidated_LOGO
FROM (
SELECT 
logo
,account
,id
,count(logo) over (partition by account, logo) as count_per_logo
FROM VALUES 
('walmart', 'acct_a', 'abc'),   
('walmart', 'acct_a', 'def'), 
('walmart', 'acct_a', 'ghi'),
('vudu', 'acct_a', 'jkl'),
('bonobos', 'acct_a', 'mno')
v(logo, account, id)
) AS a
ORDER BY 4 DESC, 5;

它给出：：

IDCOUNT_PER_LOGO合并_LOGO<1><2>沃尔玛mno<1><2>沃尔玛

LOGO	账户	排名
沃尔玛
沃尔玛
沃尔玛
vudu	acct_a	jkl
倭黑猩猩	acct_a

Simeon的答案应该有效，只要你没有关系。事实上，如果你不在乎领带，会变得更简单

select logo,
account,
id,
mode(logo) over (partition by account) as consolidated_logo
from your_table;

如果你想为每个与计数相关的标志实例复制结果集，请考虑下面的

with cte as
(select logo, account 
from your_table 
group by logo, account
qualify max(count(*)) over (partition by account)=count(*))

select a.*, b.logo as consolidated_logo
from your_table a
join cte b on a.account=b.account
order by b.logo;