主页 / dba / 问题 / 173566
Accepted
beldaz
Asked: 2017-05-15 22:42:40 +0800 CST

PostgreSQL 使用 LATERAL 随机化组合

  • 772

在下面的示例中，我有一个表格foo，我想从中随机选择每组一行。

CREATE TABLE foo (
  line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);

假设我想分组line % 10。我可以这样做：

SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 AS bin, random() x
    FROM foo
    ORDER BY x
) X

我想做的是从每个垃圾箱中随机挑选几次。我原以为我可以用generate_series()和做到这一点LATERAL

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, random() x
    FROM foo
    ORDER BY x
) X
ORDER BY bin) Q
ORDER BY bin, i;

但是，当我在 PostgreSQL 9.5 中执行此操作时，我发现每次迭代都得到相同line的给定值，例如，bini

i;line;bin
1;530;0
2;530;0
3;530;0
1;611;1
2;611;1
3;611;1
...

我很困惑，因为我认为包含的子查询random()对于来自generate_series().

编辑：我意识到我可以通过生成更多组合并从中进行选择来实现相同的目标

SELECT DISTINCT ON (bin, round) round, bin, line
FROM (
    SELECT line, line % 10 as bin, round
    FROM foo, generate_series(1,3) round
    ORDER BY bin, random()
) X;

所以我的问题很简单，为什么第一种方法行不通？

编辑：问题似乎是，如果子查询以某种方式相关（感谢@ypercube 的评论），则 LATERAL 仅充当 for 循环。因此，可以通过添加以下小更改来修复我原来的方法

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(
SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, m.i, random() x -- <NOTE m.i HERE
    FROM foo
    ORDER BY x
) X
ORDER BY bin
LIMIT 3
) Q
ORDER BY bin, i;
postgresql subquery

2 个回答

  1. Best Answer

    我会像这样编写查询，LIMIT (3)而不是使用DISTINCT ON.

    generate_series(0, 9)用于获取所有不同的垃圾箱。(SELECT DISTINCT line % 10 FROM foo) AS g (bin)如果“bins”不是从 0 到 9 的所有整数，您可以改为使用：

    SELECT 
    g.bin, 
    ROW_NUMBER() OVER (PARTITION BY g.bin ORDER BY d.x) AS i,
    d.* 
FROM 
    generate_series(0, 9) AS g (bin), 
  LATERAL 
    ( SELECT f.*, random() x 
      FROM foo AS f 
      WHERE  f.line % 10 = g.bin 
      ORDER BY x 
      LIMIT 3
    ) AS d
ORDER BY 
    bin, x ;

    此外，如果您不需要random()输出中的数字，您可以ORDER BY random()在子查询中使用并x从 select 和 order by 子句中删除 - 或者替换ORDER BY d.x为ORDER BY d.line.

    • 5

  2. 我想做的是从每个垃圾箱中随机挑选几次。

    有很多方法可以解决这个问题。每一个都会引入更多的随机性并花费更多的时间。

    1. TABLESAMPLE SYSTEM和tsm_system_rows
    2. TABLESAMPLE BERNOULLI
    3. 创建临时箱并使用统计数据掷骰子。
    4. 创建临时箱并随机排序和挑选。

    在大多数情况下，TABLEAMPLE SYSTEM获取tsm_system_rows表的“公平”抽样就足够了。它具有不必访问整个表的额外优势。

    如果您需要间隔更均匀的示例，TABLESAMPLE BERNOULLI将访问整个表格并从其中的所有页面中进行选择。

    如果您想临时进行，我想您也需要这样做。

    SELECT *
FROM (
  SELECT dense_rank() OVER (PARTITION BY bin ORDER BY random()), *
  FROM (
    SELECT line % 10 AS bin, line
    FROM foo                          
  ) AS t
) AS t                       
WHERE dense_rank <= 3
ORDER BY line;
    • 3

相关问题