主页 / dba / 问题 / 249293
Accepted
Slim
Asked: 2019-09-21 05:28:32 +0800 CST

Postgres：获取“分区号”而不是 row_number

  • 772

图有以下数据的表，如何排除“单独”值？

id; data
(1, 'foo'),
(2, 'foo'),
(3, 'foo'),
(4, 'bar'),
(5, 'bar');
(6, 'jak');

我试试这个：

select id, data, row_number() over (partition by data)
from t;

 -- RESULT
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3
6   jak     1

 -- EXPECTED (exclude partition with one row)
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3

row_number()为每个分区中的行提供索引.

如何按分区获得行数？某种WHERE partition_count > 1.

小提琴

postgresql group-by

1 个回答

  1. Best Answer

    用作count窗口函数：

    SELECT id, data
FROM (SELECT id, data,
             count(*) OVER (PARTITION BY data) AS c
      FROM t) AS q
WHERE c > 1;
    • 3

相关问题