postgresql - 第一次和第二次出现在 PostgreSQL 中的行 ID

Question

假设我们有一个带有日期的 ID 列表。并且我们想知道 id 第一次和第二次出现的时间。关于第一次，我创建了一个查询

SELECT year, mon, COUNT(id) AS sum_first_id
FROM (
    SELECT DISTINCT
        ON (id) DATE, id
    FROM TABLE
    GROUP BY 2, 1
    ) AS foo
GROUP BY 2, 1
ORDER BY 1, 2;

我认为这行得通。但是我如何才能找到 id 第二次出现的时间？

Answer 1

假设您有表 table_x:

select * 
from table_x
order by 1, 2

 id |    date    
----+------------
  1 | 2015-06-04
  1 | 2015-06-05
  1 | 2015-06-14
  2 | 2015-06-05
  2 | 2015-06-08
  2 | 2015-06-10
  2 | 2015-06-17
  2 | 2015-06-22
(8 rows)

要选择组中的第 n 个元素，请使用 row_number() 函数:

select id, date
from (
    select id, date, row_number() over (partition by id order by date) rn
    from table_x
    order by 1, 2
    ) sub
where rn <= 2

 id |    date    
----+------------
  1 | 2015-06-04
  1 | 2015-06-05
  2 | 2015-06-05
  2 | 2015-06-08
(4 rows)

---

看来您的查询不正确。

SELECT year, mon, COUNT(id) AS sum_first_id -- what is year, mon?
FROM (
    SELECT DISTINCT
        ON (id) DATE, id
    FROM TABLE
    GROUP BY 2, 1                           -- should be order by 2, 1
    ) AS foo
GROUP BY 2, 1
ORDER BY 1, 2;