postgresql - 从另一个表中选择随机 ID....对 LATERAL JOIN 感到困惑

Question

我试图掌握在 Postgres 中生成随机数据的方法，发现我对 LATERAL JOIN 有一些误解.在我之前获得的一些帮助的基础上，我有一些代码试图:

-- 生成一串数字
-- 在匹配数字序列的小时生成时间戳
-- 为每一行生成大致正态分布的随机(ish)分数
-- 从数据库中的表中随机选择一个 ID。

最后一点不起作用。当我运行下面显示的脚本时，我得到了 facility_id 的随机值，但每一行都有相同的随机值。我希望在每一行 上分配随机 ID，而不是在整个运行过程中全局分配一次。在程序思维中，facility_id在循环之前被分配，我希望它在循环中分配。我以为LATERAL JOIN会帮助我，但是

WITH facilities_count AS
(SELECT count(*) from facility)

 SELECT hour_number AS id, -- Get numbers in sequence.
       '2019-01-01 00:00'::timestamp + interval '1 HOUR' * hour_number AS stamp, -- Get hours in sequence
        ABS(TRUNC(normal_rand(1, 0, 1) * 100)) AS score, -- Create a random score in a ~normal distribution.
       random_facility.id

 FROM (SELECT * FROM generate_series(1,8760,1) AS hour_number) generated_numbers 

 LEFT JOIN LATERAL
      (SELECT id
          FROM facility 
        OFFSET floor(random() * (select count from facilities_count))
         LIMIT 1) random_facility
            ON true;

我认为子查询可能会起作用，但我还在 facility_id 的所有行中得到一个值使用此代码:

WITH facilities_counter AS
(SELECT count(*) from facility)

 SELECT hour_number AS id, -- Get numbers in sequence.
       '2019-01-01 00:00'::timestamp + interval '1 HOUR' * hour_number AS stamp, -- Get hours in sequence
        ABS(TRUNC(normal_rand(1, 0, 1) * 100)) AS score, -- Create a random score in a ~normal distribution.
         (SELECT id FROM facility OFFSET floor(random() * (select count from facilities_counter)) LIMIT 1)

 FROM (SELECT * FROM generate_series(1,8760,1) AS hour_number) generated_numbers;

我没有列出 facility表定义，但上面唯一重要的字段是 id ，因此任何表都将以相同的方式工作。

如果它对答案有任何影响，一旦我找到了这个问题的解决方案，我想使用随机 facility_id在每一行上作为输入从另一个表中选择其他内容。

感谢您的帮助。我致力于此不仅是为了获得解决方案，而且是为了尝试获得关于各种工具如何工作的更好的心智模型。我(显然)还没有达到可以阅读上述代码并在脑海中预测其行为方式的程度。获得这种理解是弄清楚如何自己解决问题的基础。换句话说，我不仅在努力解决这个问题，而且也在努力缩小我的心理差距。

过早的优化

我正在编辑我的问题，因为我需要发布更多代码。

哈!很高兴看到我不是唯一一个被指控有过早优化问题的人 ;-) 可以肯定的是，在设计或审查 session 中，我可能会愤怒地抛出这些东西。

我认为您展示的是 id用在表达式中，这使得它的结果不确定。这是我生活和重新学习的地方，我应该发布我的表结构。我们的id字段是 UUID。我试图将一些会在表达式中使用 UUID 的东西组合在一起，但它不会改变行为。

where left(id::text,1) <> 'X' -- prevent premature optimization

您描述的过早优化以及解决它的方法不是我可以预测的行为。因为缝隙。我试过使用 left 运行我的代码通过explain (costs off)表达，我得到了这样的输出:

  CTE facilities_counter
    ->  Aggregate
          ->  Seq Scan on facility
  InitPlan 3 (returns $2)
    ->  Limit
          InitPlan 2 (returns $1)
            ->  CTE Scan on facilities_counter
          ->  Seq Scan on facility facility_1
                Filter: ("left"((id)::text, 1) <> 'X'::text)
  ->  ProjectSet
        ->  Function Scan on generate_series hour_number```

I'm unclear from the output how I would distinguish the behavior you describe, which is still present here (?)

Answer 1

这是一个过早优化问题。 Postgres 预见到子查询将返回一个常量值并将其优化掉。

一个解决方案是强制它为每条记录执行子查询，方法是添加一些条件，该条件将始终评估为 true 但 Postgres 不会这样理解。

考虑:

with facilities_counter as (select count(*) from facility)
select 
    hour_number as id, -- get numbers in sequence.
    '2019-01-01 00:00'::timestamp + interval '1 hour' * hour_number as stamp,
    abs(trunc(normal_rand(1, 0, 1) * 100)) as score,
    (
        select id 
        from facility 
        where id <> -1 * hour_number   -- prevent premature optimization
        offset floor( random() * (select count from facilities_counter)) 
        limit 1
    )
from (
    select * from generate_series(1,8760,1) as hour_number
) generated_numbers;

Demo on DB Fiddle