SQL Server - 逻辑问题 - 从 ID 获取排名 [英] SQL Server - logical question - Get rank from IDs

问题描述

我正在尝试使用 Redshift POSTGRES 8 编写查询以解决逻辑问题.

I'm trying to write a query to solve a logical problem using Redshift POSTGRES 8.

输入列是一堆 ID 和订单 ID，所需的输出基本上是 ID 的等级，如您在屏幕截图中所见.(对不起，我还不允许在我的 StackOverflow 帖子中嵌入图片)

Input column is a bunch of IDs and Order IDs and desired output is basically a rank of the ID as you can see in the screenshot. (I'm sorry I'm not allowed to embed images into my StackOverflow posts yet)

如果您能帮我用 SQL 回答这个问题，那就太好了！谢谢

If you could help me answer this question using SQL, that would be great! Thanks

数据

<头>

订单编号	id	尺寸	期望的输出
1	abcd	2	1
1	abcd	2	1
1	efgh	5	2
1	efgh	5	2
1	efgh	5	2
1	efgh	5	2
2	aa	2	1
2	aa	2	1
2	bb	2	2
2	bb	2	2

推荐答案

SELECT
  *,
  DENSE_RANK() OVER (PARTITION BY order_item_id ORDER BY id)   AS desired_result
FROM
  your_table

DENSE_RANK() 根据 ORDER BY 创建从 1 开始的序列.

DENSE_RANK() creates sequences starting from 1 according to the ORDER BY.

任何具有相同 ID 的行都将获得相同的值，并且 RANK() 将在平局的情况下跳过值 DENSE_RANK() 没有.

Any rows with the same ID will get the same value, and where RANK() would skip values in the event of ties DENSE_RANK() does not.

PARTITION BY 允许为每个不同的 order_item_id 创建新序列.

The PARTITION BY allows new sequences to be created for each different order_item_id.

这篇关于SQL Server - 逻辑问题 - 从 ID 获取排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！