Почему PostgreSQL неправильно объединяет серии?

У меня какое-то странное поведение комбинированного generate_series. В 2-х разных полигонах, которые я пытался заполнить сеткой, одна сетка встречалась гораздо реже:

введите здесь описание изображения

Запрос был таким:

SELECT
    osm_id ,
        generate_series(floor(st_xmin(way))::int, ceiling(st_xmax(way))::int, 150) x,
        generate_series(floor(st_ymin(way))::int, ceiling(st_ymax(way))::int, 150) y 
from osm_polygon
order by osm_id, x, y;

Я попытался отследить проблему и просто ввел минимальные/максимальные координаты. Сгенерируйте серию из минимальных/максимальных значений, создайте правильное количество значений: 9 и 12 строк соответственно.

  => select generate_series(9237195, 9238873, 150) x;
      x    
  ---------
   9237195
   9237345
   9237495
   9237645
   9237795
   9237945
   9238095
   9238245
   9238395
   9238545
   9238695
   9238845
  (12 rows)

  => select generate_series(7371701, 7372922, 150) y order by y;
      y    
  ---------
   7371701
   7371851
   7372001
   7372151
   7372301
   7372451
   7372601
   7372751
   7372901
  (9 rows)

Вместе они должны составить 108 рядов, верно? Нет, всего 36 рядов:

=> select generate_series(9237195, 9238873, 150) x, generate_series(7371701, 7372922, 150) y order by x, y;
      x    |    y    
  ---------+---------
   9237195 | 7371701
   9237195 | 7372151
   9237195 | 7372601
   9237345 | 7371851
   9237345 | 7372301
   9237345 | 7372751
   9237495 | 7372001
   9237495 | 7372451
   9237495 | 7372901
   9237645 | 7371701
   9237645 | 7372151
   9237645 | 7372601
   9237795 | 7371851
   9237795 | 7372301
   9237795 | 7372751
   9237945 | 7372001
   9237945 | 7372451
   9237945 | 7372901
   9238095 | 7371701
   9238095 | 7372151
   9238095 | 7372601
   9238245 | 7371851
   9238245 | 7372301
   9238245 | 7372751
   9238395 | 7372001
   9238395 | 7372451
   9238395 | 7372901
   9238545 | 7371701
   9238545 | 7372151
   9238545 | 7372601
   9238695 | 7371851
   9238695 | 7372301
   9238695 | 7372751
   9238845 | 7372001
   9238845 | 7372451
   9238845 | 7372901
(36 rows)

Здесь действительно работает грубая сила:

with a as (select generate_series(9237195, 9238873, 150) x),
    b as (select generate_series(7371701, 7372922, 150) y)
select x, y from a, b;

Получается 108 рядов. Хорошо, за исключением того, что запросы становятся более сложными.

Почему так?

Постгрес версия 9.1.


postgresql postgis combinations generate-series
person culebrón    schedule 03.07.2015    source источник

Ответы (3)


arrow_upward
6
arrow_downward

Это причуда того, как несколько функций, возвращающих набор, выполняются при вызове в SELECT-списке. Вы ожидаете, что результат будет перекрестным произведением двух, но это не так. На самом деле это наименьшее общее кратное двух строк.

Видеть:

Сравнивать:

test=>     SELECT generate_series(1,3) aval, generate_series(1,4) bval;
 aval | bval 
------+------
    1 |    1
    2 |    2
    3 |    3
    1 |    4
    2 |    1
    3 |    2
    1 |    3
    2 |    4
    3 |    1
    1 |    2
    2 |    3
    3 |    4
(12 rows)

test=>     SELECT generate_series(1,3) aval, generate_series(1,3) bval;
 aval | bval 
------+------
    1 |    1
    2 |    2
    3 |    3
(3 rows)

По этой причине в PostgreSQL 9.2 и более поздних версиях вы должны использовать запросы LATERAL, в которых вы вызываете функции, возвращающие множество, в предложении FROM:

test=>     SELECT aval, bval FROM generate_series(1,3) aval CROSS JOIN LATERAL generate_series(1,3) bval;
 aval | bval 
------+------
    1 |    1
    1 |    2
    1 |    3
    2 |    1
    2 |    2
    2 |    3
    3 |    1
    3 |    2
    3 |    3
(9 rows)

test=>     SELECT aval, bval FROM generate_series(1,3) aval CROSS JOIN LATERAL generate_series(1,4) bval;
 aval | bval 
------+------
    1 |    1
    1 |    2
    1 |    3
    1 |    4
    2 |    1
    2 |    2
    2 |    3
    2 |    4
    3 |    1
    3 |    2
    3 |    3
    3 |    4
(12 rows)

В более старых версиях вы можете использовать подзапрос в FROM, чтобы избежать нескольких SRF в термине SELECT:

test=> SELECT generate_series(1,3) aval, bval FROM (SELECT generate_series(1,4)) AS x(bval);
 aval | bval 
------+------
    1 |    1
    2 |    1
    3 |    1
    1 |    2
    2 |    2
    3 |    2
    1 |    3
    2 |    3
    3 |    3
    1 |    4
    2 |    4
    3 |    4
(12 rows)
person Craig Ringer    schedule 03.07.2015

arrow_upward
3
arrow_downward

Из документации postgresql.

"Примечание: ключевая проблема с использованием функций, возвращающих набор, в списке выбора, а не в предложении FROM, заключается в том, что помещение более одной функции, возвращающей набор, в один и тот же список выбора ведет себя не очень разумно. (Что вы на самом деле получите, если вы делаете так, это количество выходных строк, равное наименьшему общему кратному количества строк, созданных каждой функцией, возвращающей множество.)

person Nuri Tasdemir    schedule 03.07.2015
comment
Однако LATERAL был представлен только в 9.2, поэтому @culebrón не повезло, пока они не смогут обновиться. - person Craig Ringer; 03.07.2015
comment
Это верно. Однако в этом случае использование lateral ключевого слова вовсе не обязательно. Это (LATERAL) позволяет им ссылаться на столбцы, предоставленные предыдущими элементами FROM. А в нашем случае нет ссылки на предыдущий из пунктов в вызове функции. А также в качестве примечания: для функций ключевое слово (LATERAL) необязательно; аргументы функции могут содержать ссылки на столбцы, предоставленные предыдущими элементами FROM в любом случае - person Nuri Tasdemir; 03.07.2015

arrow_upward
2
arrow_downward

Я использую этот формат:

SELECT i,j FROM
generate_series(1,3) i, generate_series(1,3) j

Это работает очень хорошо.

person jurhas    schedule 03.07.2015