SQL查询排序并实现唯一计数

本文介绍了SQL查询排序并实现唯一计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表格，其中包含以下详细信息

I have a table which consists of the following details

客户交易 DealStage Year

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

是否有可能获得类似

客户 HighestStage2019 HighestStage2020

A	获胜
B		联系
C		迷失
D		迷失

这样，我可以生成一个看起来像这样的数据透视表

By this, I can generate a pivot table that looks like

阶段 CustomerCount2019 CustomerCount2020

获胜	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;