本文介绍了SQL查询排序并实现唯一计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表格,其中包含以下详细信息
I have a table which consists of the following details
A | D1 | 迷失 | 2019 |
A | D2 | 获胜 | 2019 |
A | D3 | 联系 | 2020 |
B | D4 | 已连接 | 2020 |
B | D5 | 迷失 | 2020 |
C | D6 | 迷失 | 2020 |
D | D7 | 迷失 | 2020 |
我必须开发一个查询,在该查询中我应该每年为每个客户获得唯一的最高阶段.阶段优先级是Won>联系>丢失的.例如,A有三笔交易,分别为赢",输"和联系".所以我应该考虑赢.对于B,类似地联系,对于C和D,类似地联系
I have to develop a query where I should get the unique highest stage for each customer yearly. The Stage priority is Won > Contacted > Lost. For Example, A is having three deals which are Won, Lost, and Contacted. So I should be considering Won. Similarly Contacted for B and Lost for C and D
是否有可能获得类似
A | 获胜 | |
B | 联系 | |
C | 迷失 | |
D | 迷失 |
这样,我可以生成一个看起来像这样的数据透视表
By this, I can generate a pivot table that looks like
获胜 | 1 | |
联系 | 1 | |
迷失 | 2 |
预先感谢
推荐答案您可以按以下方式使用条件聚合:
You can use conditional aggregate as follows:
Select stage, count(case when year = 2019 then 1 end) as year2019, count(case when year = 2020 then 1 end) as year2020 From (Select t.*, row_number() over (partition by customer, year order by case when dealstage = 'won' the 1 When dealstage = 'contacted' then 2 else 3 end) as rn From your_table t) t Where rn = 1 Group by stage;更多推荐
SQL查询排序并实现唯一计数
发布评论