英文:
Need query to get data as per below
问题
Input Table是
Target| Agent| RankA
-|-|-
15000| A| null
15000| A| null
14500| A| null
所需输出是
Target| Agent| RankA
-|-|-
15000| A| 1| 33
15000| A| 1| 33
14500| A| 3| 100
因为第一条记录是15000，所以它的排名是1。
因为第二条记录也是15000，所以它的排名也是1。
因为第三条记录是14500，所以它的排名是3，而不是2，因为即使前两条记录相同，现在的计数是2，所以第三条记录的新排名将是3。
相应的查询是
```sql
here is script for data preparation
```sql
SELECT 15000 TARGET,
'' AS AGENT,
NULL RANKA
INTO #TABLEA;
INSERT INTO #TABLEA
VALUES (1400, 'B', NULL);
英文:
Input Table is
| Target | Agent | RankA |
|---|---|---|
| 15000 | A | null |
| 15000 | A | null |
| 14500 | A | null |
required output is
| Target | Agent | RankA |
|---|---|---|
| 15000 | A | 1 |
| 15000 | A | 1 |
| 14500 | A | 3 |
as first record is 15000, so its rank is 1
as second record is 15000, so its rank is also 1
as third record is 14500, so its rank is also 3 and not 2 because even though first two records have same record, the count is now 2 so new rank will be 3 for third record
need query for the same..
here is script for data preparation
SELECT 15000 TARGET,
'' AS AGENT,
NULL RANKA
INTO #TABLEA;
INSERT INTO #TABLEA
VALUES (1400, 'B', NULL);
答案1
得分: 1
使用 rank() 来获取排名，以及 rank/count 来获取百分位：
with cte as (
select Target, Agent, rank() over (partition by Agent order by Target desc) as Ranka
from TableA
)
select Target, Agent, cast(Ranka as float)/CAST(count(*) over (partition by Agent) AS FLOAT)*100 as Percentile
from cte
或者，如果你不需要百分位：
select Target, Agent, rank() over (partition by Agent order by Target desc) as Ranka
from TableA
英文:
You can do it using rank() to get the rank, and rank/count to get Percentile
with cte as (
select Target, Agent, rank() over (partition by Agent order by Target desc) as Ranka
from TableA
)
select Target, Agent, cast(Ranka as float)/CAST(count(*) over (partition by Agent) AS FLOAT)*100 as Percentile
from cte
Or this if you dont need Percentile :
select Target, Agent, rank() over (partition by Agent order by Target desc) as Ranka
from TableA
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论