postgresql - 使用 Postgresql 查找值簇

Question

考虑以下示例表:

CREATE TABLE rndtbl AS
SELECT
  generate_series(1, 10) AS id,
  random() AS val;

我想为每个 id 找到一个 cluster_id，这样集群之间的距离至少为 0.1。我将如何计算这样的集群分配？

一个具体的例子是:

select * from rndtbl ;
 id |        val
----+-------------------
  1 | 0.485714662820101
  2 | 0.185201027430594
  3 | 0.368477711919695
  4 | 0.687312887981534
  5 | 0.978742253035307
  6 | 0.961830694694072
  7 |  0.10397826647386
  8 | 0.644958863966167
  9 | 0.912827260326594
 10 | 0.196085536852479
(10 rows)

结果将是:ids (2,7,10) 在一个簇中，(5,6,9) 在另一个簇中，(4,8) 在另一个簇中，以及 (1 ) 和 (3) 作为单例集群。

Answer 1

来自

SELECT * FROM rndtbl ;
┌────┬────────────────────┐
│ id │        val         │
├────┼────────────────────┤
│  1 │  0.153776332736015 │
│  2 │  0.572575284633785 │
│  3 │  0.998213059268892 │
│  4 │  0.654628816060722 │
│  5 │  0.692200613208115 │
│  6 │  0.572836415842175 │
│  7 │ 0.0788379465229809 │
│  8 │  0.390280921943486 │
│  9 │  0.611408909317106 │
│ 10 │  0.555164183024317 │
└────┴────────────────────┘
(10 rows)

使用LAG 窗口函数了解当前行是否在新簇中:

SELECT *, val - LAG(val) OVER (ORDER BY val) > 0.1 AS new_cluster
FROM rndtbl ;
┌────┬────────────────────┬─────────────┐
│ id │        val         │ new_cluster │
├────┼────────────────────┼─────────────┤
│  7 │ 0.0788379465229809 │ (null)      │
│  1 │  0.153776332736015 │ f           │
│  8 │  0.390280921943486 │ t           │
│ 10 │  0.555164183024317 │ t           │
│  2 │  0.572575284633785 │ f           │
│  6 │  0.572836415842175 │ f           │
│  9 │  0.611408909317106 │ f           │
│  4 │  0.654628816060722 │ f           │
│  5 │  0.692200613208115 │ f           │
│  3 │  0.998213059268892 │ t           │
└────┴────────────────────┴─────────────┘
(10 rows)

最后你可以SUMtrue的个数(仍然按val排序)得到行的簇(从0开始计数) :

SELECT *, SUM(COALESCE(new_cluster::int, 0)) OVER (ORDER BY val) AS nb_cluster
FROM (
  SELECT *, val - LAG(val) OVER (ORDER BY val) > 0.1 AS new_cluster 
  FROM rndtbl 
) t
;
┌────┬────────────────────┬─────────────┬────────────┐
│ id │        val         │ new_cluster │ nb_cluster │
├────┼────────────────────┼─────────────┼────────────┤
│  7 │ 0.0788379465229809 │ (null)      │          0 │
│  1 │  0.153776332736015 │ f           │          0 │
│  8 │  0.390280921943486 │ t           │          1 │
│ 10 │  0.555164183024317 │ t           │          2 │
│  2 │  0.572575284633785 │ f           │          2 │
│  6 │  0.572836415842175 │ f           │          2 │
│  9 │  0.611408909317106 │ f           │          2 │
│  4 │  0.654628816060722 │ f           │          2 │
│  5 │  0.692200613208115 │ f           │          2 │
│  3 │  0.998213059268892 │ t           │          3 │
└────┴────────────────────┴─────────────┴────────────┘
(10 rows)