获取重复类别集的行号

huangapple go评论51阅读模式
英文:

Get row number for repeated sets of categories

问题

以下是您要翻译的内容:

我有一张表格:

    id   |    cat
    -----+------------
    1    |    A
    2    |    A
    3    |    B
    4    |    B
    5    |    A
    6    |    A
    7    |    B
    8    |    A
    9    |    B
    10   |    B

我想要一个按类别分组但按集合递增的行编号,如下:

    id   |    cat     |   row_number
    -----+------------+-------------
    1    |    A       |   1
    2    |    A       |   1
    3    |    B       |   2
    4    |    B       |   2
    5    |    A       |   3
    6    |    A       |   3
    7    |    B       |   4
    8    |    A       |   5
    9    |    B       |   6
    10   |    B       |   6

我尝试使用窗口函数,但是“partition by”对每个类别都会重置。

    SELECT *, row_number() over(PARTITION BY cat ORDER BY id)
    from data order by id

请帮忙。

谢谢
英文:

I have a table like:

id   |    cat
-----+------------
1    |    A
2    |    A
3    |    B
4    |    B
5    |    A
6    |    A
7    |    B
8    |    A
9    |    B
10   |    B

I want a row number that group the sets by category but increments by sets, like:

id   |    cat     |   row_number
-----+------------+-------------
1    |    A       |   1
2    |    A       |   1
3    |    B       |   2
4    |    B       |   2
5    |    A       |   3
6    |    A       |   3
7    |    B       |   4
8    |    A       |   5
9    |    B       |   6
10   |    B       |   6

I've tried using window functions, but the "partition by" resets for every category.

SELECT *, row_number() over(PARTITION BY cat ORDER BY id)
from data order by id

Please help.

Thanks

答案1

得分: 1

以下是翻译的内容:

我们可以将此问题表述为间隙和岛屿问题,然后使用 DENSE_RANK()

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn1,
              ROW_NUMBER() OVER (PARTITION BY cat ORDER BY id) rn2
    FROM yourTable
),
cte2 AS (
    SELECT *, MIN(id) OVER (PARTITION BY cat, rn1 - rn2) AS min_id
    FROM cte
)

SELECT id, cat, DENSE_RANK() OVER (ORDER BY min_id) AS row_number
FROM cte2
ORDER BY id;

第一个CTE按照id的顺序找到所有的岛屿,定义为具有相同cat值的记录组在相同的id序列中。第二个CTE为每条记录分配了最小的id值。最后,在外部查询中,我们使用具有最小id的DENSE_RANK来生成所需的序列。

英文:

We can phrase this as a gaps and islands problem, and then use DENSE_RANK();

<!-- language: sql -->

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn1,
              ROW_NUMBER() OVER (PARTITION BY cat ORDER BY id) rn2
    FROM yourTable
),
cte2 AS (
    SELECT *, MIN(id) OVER (PARTITION BY cat, rn1 - rn2) AS min_id
    FROM cte
)

SELECT id, cat, DENSE_RANK() OVER (ORDER BY min_id) AS row_number
FROM cte2
ORDER BY id;

The first CTE finds all islands as defined by groups of records having the same cat value in the same id sequence. The second CTE assigns the minimum id value to each record. Finally, in the outer query, we use DENSE_RANK with this minimum id to generate the desired sequence.

huangapple
  • 本文由 发表于 2023年4月4日 06:40:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/75924237.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定