如何根据列的变化值对记录进行分组/排名?

2021-09-10 00:00:00 sql tsql sql-server

我有下表按 Id、Year DESC 排序

<前>ID 年份有效1 2011 11 2010 11 2009 01 2002 14 2013 14 2012 14 2011 1等等.

我想要的是一个额外的排名字段，例如:

<前>Id 年份有效等级1 2011 1 11 2010 1 11 2009 0 21 2002 1 34 2013 1 14 2012 1 14 2011 1 1等等.

对于有效字段中的每个更改，基本上每个 Id 交替排名.通过这种方式，我可以在 rank=1 字段上查询，以获得每个 Id 的所有 Valid=1 记录，直到第一个 Valid=0.或者是否有一种更简单的方法来选择匹配某个条件的第一条记录(对于 Id=1 仅前两条记录).我已经玩过 ROW_NUMBER()、RANK() 和 PARTITION BY，但我似乎无法让它工作.必须避免嵌套查询，因为实际查询是针对大型数据库运行的.

有人有什么想法吗?

谢谢和欢呼，奈奎斯特

解决方案

是的，使用 Left JOIN 我们可以做到这一点.请参阅下面的代码和结果.

第一张图片是实际插入的数据，第二张图片是预期的结果.

声明@t 表(身份证号码,_YEAR INT,有效的 TINYINT)插入 @t( id, [_YEAR], 有效 )选择 1,2011,1UNION ALL SELECT 1,2010,1UNION ALL SELECT 1,2009,0UNION ALL SELECT 1,2002,1UNION ALL SELECT 4,2013,1UNION ALL SELECT 4,2012,1UNION ALL SELECT 4,2011,1UNION ALL SELECT 5,2013,0UNION ALL SELECT 5,2011,1UNION ALL SELECT 5,2010,1UNION ALL SELECT 6,2010,1UNION ALL SELECT 6,2011,0UNION ALL SELECT 6,2014,1选择 q1.*来自@t q1左加入(SELECT id,MAX(_YEAR) 零年从TWHERE 有效 = 0按 ID 分组)q2ON q1.id=q2.id在哪里(q2.ID 为空)或者(q2.id 不为空且 q1.id 不为空 AND q1.id=q2.id AND q1.[_YEAR] > q2.ZeroYear)

编辑-1:在上面对列 ZeroYear 的查询中，之前我做了 MIN(_YEAR) 但正如你在Andriy M"的评论中看到的，而不是 MIN 正确的函数是 MAX.

I have the following table ordered by Id, Year DESC

Id   Year   Valid
1    2011   1
1    2010   1
1    2009   0
1    2002   1
4    2013   1
4    2012   1
4    2011   1
etc.

What I would like to have is an extra rank field like:

Id   Year   Valid   Rank
1    2011   1       1
1    2010   1       1
1    2009   0       2
1    2002   1       3
4    2013   1       1
4    2012   1       1
4    2011   1       1
etc.

Basically per Id alternating ranks for every change in the Valid field. In such a way I can query on the rank=1 field to have all Valid=1 records for each and every Id up to the first Valid=0. Or is there an easier way to select the first top number of records matching a certain condition (For Id=1 only the first two records). I already played with ROW_NUMBER(), RANK() and PARTITION BY but I can't seem to get it to work. Must avoid nesting of queries since the actual query is run against a large database.

Anyone any ideas?

Thanks and cheers, Nyquist

解决方案

yes, using Left JOIN we can do that. see the below code and result.

first image is actual data inserted and second image is expected result.

DECLARE @t TABLE
(
    id      INT
    ,_YEAR  INT
    ,valid  TINYINT
)
INSERT INTO @t( id, [_YEAR], valid )
            SELECT 1,2011,1
UNION ALL   SELECT 1,2010,1
UNION ALL   SELECT 1,2009,0
UNION ALL   SELECT 1,2002,1
UNION ALL   SELECT 4,2013,1
UNION ALL   SELECT 4,2012,1
UNION ALL   SELECT 4,2011,1
UNION ALL   SELECT 5,2013,0
UNION ALL   SELECT 5,2011,1
UNION ALL   SELECT 5,2010,1
UNION ALL   SELECT 6,2010,1
UNION ALL   SELECT 6,2011,0
UNION ALL   SELECT 6,2014,1


SELECT  q1.*
FROM @t q1
LEFT JOIN 
(
    SELECT id,MAX(_YEAR) ZeroYear
    FROM @t
    WHERE valid = 0
    GROUP BY id
)q2
    ON q1.id=q2.id
WHERE 
(q2.ID IS NULL)
OR
(q2.id IS NOT NULL AND q1.id IS NOT NULL AND q1.id=q2.id AND q1.[_YEAR] > q2.ZeroYear)

Edit-1: In above query for the column ZeroYear, previously i did MIN(_YEAR) but as you can see in the comment from "Andriy M" instead of MIN right function is MAX.

相关文章