使用DuckDB从列中生成随机蛋白质ID对

我尝试使用DuckDB从一列数据中生成随机配对。

我有一列蛋白质登录号，数据如下：

┌──────────────┐
│ protein_upkb │
│   varchar    │
├──────────────┤
│ G1XNZ0       │
│ G1XP19       │
│ G1XP66       │
│ G1XP70       │
│ G1XPL1       │
│ G1XPQ7       │
│ G1XQ23       │
│ G1XQ44       │
│ G1XQ89       │
│ G1XQH2       │
├──────────────┤
│   10 rows    │
└──────────────┘

我希望将这些蛋白质ID生成如下形式的随机配对：

┌────────────┬────────────┐
│     p1     │     p2     │
│  varchar   │  varchar   │
├────────────┼────────────┤
│ G1XNZ0     │ G1XP19     │
│ G1XP19     │ G1XP66     │
│ G1XP66     │ G1XP70     │
│ G1XP70     │ G1XPL1     │
│ G1XPL1     │ G1XPQ7     │
│ G1XPQ7     │ G1XQ23     │
│ G1XQ23     │ G1XQ44     │
│ G1XQ44     │ G1XQ89     │
│ G1XQ89     │ G1XQH2     │
│ G1XQH2     │ G1XNZ0     │
├────────────┴────────────┤
│ 10 rows       2 columns │
└─────────────────────────┘

注意：这只是示例，实际表中有数千个ID。

子查询方式 #

我首先通过为每行分配随机数并排序来打乱蛋白质顺序：

CREATE VIEW proteins AS
  SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet');

SELECT * FROM proteins ORDER BY x DESC LIMIT 10;

执行结果如下：

┌──────────────┬────────────────────┐
│ protein_upkb │         x          │
│   varchar    │       double       │
├──────────────┼────────────────────┤
│ A0A1H6HM63   │ 0.9999986232724041 │
│ A0A1G6CK58   │ 0.9999978158157319 │
│ A0A2C5XBA3   │ 0.9999923389405012 │
│ A0A1H9T955   │ 0.9999855090864003 │
│ Q05Q16       │ 0.9999655580613762 │
│ R5PE70       │  0.999956940067932 │
│ R5GUN0       │ 0.9999453630298376 │
│ A0A0L0UJ42   │ 0.9999357375781983 │
│ W6ZJY1       │ 0.9999311361461878 │
│ F6D0F2       │ 0.9999301459174603 │
├──────────────┴────────────────────┤
│ 10 rows                 2 columns │
└───────────────────────────────────┘

随后我尝试用子查询生成随机配对，一列按x降序排序，另一列按x升序排序。但结果仅生成了1个随机对，而非我需要的255622个。

cursor = duckdb.sql("""
SELECT
(SELECT protein_upkb FROM proteins ORDER BY x DESC) as p1,
(SELECT protein_upkb FROM proteins ORDER BY x ASC) as p2,
LIMIT 10;
""").show()

结果：

┌─────────┬─────────┐
│   p1    │   p2    │
│ varchar │ varchar │
├─────────┼─────────┤
│ Q28RH7  │ D8LJ06  │
└─────────┴─────────┘

从两个视图中查询 #

我尝试创建两个视图proteins1和proteins2，分别用random()独立随机排序，最后通过从两个表中选择protein_upkb列来生成配对。结果依然不符合预期：p2是随机的蛋白质序列，但p1始终是同一个蛋白质。

cursor = duckdb.sql("""
CREATE VIEW proteins1 AS
  SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet') 
  ORDER BY x ASC;

CREATE VIEW proteins2 AS
  SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet') 
  ORDER BY x ASC;

SELECT ps1.protein_upkb as p1, ps2.protein_upkb as p2,
FROM proteins1 as ps1, proteins2 as ps2
LIMIT 10;
""").show()

结果：

┌────────────┬────────────┐
│     p1     │     p2     │
│  varchar   │  varchar   │
├────────────┼────────────┤
│ A0A394DPL7 │ A0A1I3L166 │
│ A0A394DPL7 │ A0A0Q3WJP1 │
│ A0A394DPL7 │ A0A093SP34 │
│ A0A394DPL7 │ A0A127EQY9 │
│ A0A394DPL7 │ K6UP11     │
│ A0A394DPL7 │ A0A1I6M9F9 │
│ A0A394DPL7 │ A0A0Q3SWF8 │
│ A0A394DPL7 │ A0A069RD68 │
│ A0A394DPL7 │ S9ZHA8     │
│ A0A394DPL7 │ Q5P5L0     │
├────────────┴────────────┤
│ 10 rows       2 columns │
└─────────────────────────┘

可在对应测试环境中运行上述代码进行验证。

内容的提问来源于stack exchange，提问作者Joseph Szymborski