根据一些共享列创建标识符/计数器并根据其他列分开 [英] Create identifier/counter based on some shared columns and seperate based on other columns

问题描述

我想基于共享列创建计算列，但计算列应重新启动"并基于第三列分组.

如下图所示，第 1 列包含属于某个实体(某种 ID)的信息.第 2 列包含存在的记录数(1、2 和 3).第 3 列包含实际数据(A、B 或 C)，但三个不同的记录之间可以存在相同的值(由第 2 列).

我之前的过程在这个thread 但随着我的深入，我相信它最初没有正确解释.

有没有办法创建我想要的结果?

解决方案

使用 带OVER() 子句的窗口函数DENSE_RANK():>

DECLARE @tbl TABLE(Column1 INT,Column2 INT,Column3 VARCHAR(100));插入@tbl 值(1,1,'A'),(1,2,'A'),(1,3,'B'),(2,1,'A'),(2,2,'A'),(2,3,'B'),(3,1,'A'),(3,2,'B'),(3,3,'V');选择 *,DENSE_RANK() OVER(PARTITION BY Column1 ORDER BY Column3) AS ComputedColumn从@tbl;

PARTITION BY 将为 column1 中的每个新值重新启动计数器，而 ORDER BY 定义排名.>

提示:请勿粘贴图片！

对于您的下一个问题，请按照我的示例创建一个独立示例来重现您的问题并添加您自己尝试过的代码.

I want to create a calculated column based on a shared column but the calculated column should "restart" and be grouped based on a third column.

As described in the picture below Column 1 contains information belonging so a certain entity (some sort of ID). Column 2 contains the number of records that exists (1, 2 and 3). Column 3 contains the actual data (A, B or C) but the same value can exist between the three different records (separated by Column 2).

My previous process was discussed in this thread but as I went along I believe that it wasn't originally explained correctly.

Is there a way to create my desired result?

解决方案

Use windowing function DENSE_RANK() with an OVER() clause:

DECLARE @tbl TABLE(Column1 INT,Column2 INT,Column3 VARCHAR(100));
INSERT INTO @tbl VALUES(1,1,'A')
                      ,(1,2,'A') 
                      ,(1,3,'B') 
                      ,(2,1,'A') 
                      ,(2,2,'A') 
                      ,(2,3,'B') 
                      ,(3,1,'A') 
                      ,(3,2,'B') 
                      ,(3,3,'V');

SELECT *
      ,DENSE_RANK() OVER(PARTITION BY Column1 ORDER BY Column3) AS ComputedColumn
FROM @tbl;

The PARTITION BY will re-start the counter for each new value in column1, while the ORDER BY defines the ranking.

Hint: Do not paste pictures!

For your next question please follow my example to create a stand-alone example reproducing your issue and add the code you've tried yourself.

这篇关于根据一些共享列创建标识符/计数器并根据其他列分开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！