SQL查询排序并实现唯一计数 [英] SQL Query to sort and achieve Unique Count

问题描述

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

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;

这篇关于SQL查询排序并实现唯一计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！