sql - PostgreSQL : Mark the first row of a group

Question

我有一张这样的 table :

id | group_id | name
------------------------
1  |    1     | richard
2  |    1     | ray
3  |    2     | enzo
4  |    2     | shiela
5  |    2     | anne

我可以毫无问题地选择每个组，但是我想通过 group_id 标记每个组的第一次出现。然后将其添加为列以标记该行是该组的第一次出现。

例如，第 1 组的 Richard，或第 2 组的 Enzo，等等。

我应该能够使用:

         select
         t.* 
           case
           when (condition)
           ...(boolean result here)
           end as is_first_row
         from t

结果为:

id | group_id | name    |is_first_row
-------------------------------
1  |    1     | richard | t
2  |    1     | ray     | f
3  |    2     | enzo    | t
4  |    2     | shiela  | f
5  |    2     | anne    | f

如何为选择查询制定条件语句？

Answer 1

使用row_number():

with my_table(id, group_id, name) as (
values
    (1, 1, 'richard'),
    (2, 1, 'ray'),
    (3, 2, 'enzo'),
    (4, 2, 'shiela'),
    (5, 2, 'anne')
)

select *, row_number() over w = 1 as is_first_row
from my_table
window w as (partition by group_id order by id);

 id | group_id |  name   | is_first_row 
----+----------+---------+--------------
  1 |        1 | richard | t
  2 |        1 | ray     | f
  3 |        2 | enzo    | t
  4 |        2 | shiela  | f
  5 |        2 | anne    | f
(5 rows)    

---

选择 row_number() 以查看其工作原理。行号按 group_id 在分区中计算，即对于每个 group_id 分别按 id 顺序计算:

with my_table(id, group_id, name) as (
values
    (1, 1, 'richard'),
    (2, 1, 'ray'),
    (3, 2, 'enzo'),
    (4, 2, 'shiela'),
    (5, 2, 'anne')
)

select *, row_number() over w
from my_table
window w as (partition by group_id order by id);

 id | group_id |  name   | row_number 
----+----------+---------+------------
  1 |        1 | richard |          1
  2 |        1 | ray     |          2
  3 |        2 | enzo    |          1
  4 |        2 | shiela  |          2
  5 |        2 | anne    |          3
(5 rows)