Postgres LEFT JOIN создает больше строк, чем в левой таблице

Я использую 32-разрядную версию Postgres 9.1.3 в Windows 7 x64. (Необходимо использовать 32-разрядную версию, потому что нет версии Windows PostGIS, совместимой с 64-разрядной версией Postgres.) (EDIT: Начиная с PostGIS 2.0, она совместима с 64-разрядной версией Postgres в Windows.)

У меня есть запрос, который left объединяет таблицу (consistent.master) с временной таблицей, а затем вставляет полученные данные в третью таблицу (consistent.masternew).

Поскольку это left join, результирующая таблица должна иметь такое же количество строк, как и левая таблица в запросе. Однако, если я запускаю это:

SELECT count(*)
FROM consistent.master

Я получаю 2085343. Но если я запускаю это:

SELECT count(*)
FROM consistent.masternew

Я получаю 2085703.

Как masternew может иметь больше строк, чем master? Разве masternew не должно иметь такое же количество строк, как master, левая таблица в запросе?

Ниже приведен запрос. Таблицы master и masternew должны иметь одинаковую структуру.

--temporary table created here
--I am trying to locate where multiple tickets were written on
--a single traffic stop
WITH stops AS (
    SELECT citation_id,
           rank() OVER (ORDER BY offense_timestamp,
                     defendant_dl,
                     offense_street_number,
                     offense_street_name) AS stop
    FROM   consistent.master
    WHERE  citing_jurisdiction=1
)

--Here's the insert statement. Below you'll see it's
--pulling data from a select query
INSERT INTO consistent.masternew (arrest_id,
  citation_id,
  defendant_dl,
  defendant_dl_state,
  defendant_zip,
  defendant_race,
  defendant_sex,
  defendant_dob,
  vehicle_licenseplate,
  vehicle_licenseplate_state,
  vehicle_registration_expiration_date,
  vehicle_year,
  vehicle_make,
  vehicle_model,
  vehicle_color,
  offense_timestamp,
  offense_street_number,
  offense_street_name,
  offense_crossstreet_number,
  offense_crossstreet_name,
  offense_county,
  officer_id,
  offense_code,
  speed_alleged,
  speed_limit,
  work_zone,
  school_zone,
  offense_location,
  source,
  citing_jurisdiction,
  the_geom)

--Here's the select query that the insert statement is using.    
SELECT stops.stop,
  master.citation_id,
  defendant_dl,
  defendant_dl_state,
  defendant_zip,
  defendant_race,
  defendant_sex,
  defendant_dob,
  vehicle_licenseplate,
  vehicle_licenseplate_state,
  vehicle_registration_expiration_date,
  vehicle_year,
  vehicle_make,
  vehicle_model,
  vehicle_color,
  offense_timestamp,
  offense_street_number,
  offense_street_name,
  offense_crossstreet_number,
  offense_crossstreet_name,
  offense_county,
  officer_id,
  offense_code,
  speed_alleged,
  speed_limit,
  work_zone,
  school_zone,
  offense_location,
  source,
  citing_jurisdiction,
  the_geom
FROM consistent.master LEFT JOIN stops
ON stops.citation_id = master.citation_id

Если это имеет значение, я запустил VACUUM FULL ANALYZE и переиндексировал обе таблицы. (Не уверен в точных командах, сделал это через pgAdmin III.)


postgresql sql-insert left-join
person Aren Cambre    schedule 18.03.2012    source источник

Ответы (2)


arrow_upward
11
arrow_downward

Левое соединение не обязательно имеет то же количество строк, что и количество строк в левой таблице. По сути, это похоже на обычное соединение, за исключением того, что также добавляются строки левой таблицы, которые не появляются при обычном объединении. Таким образом, если у вас есть более одной строки в правой таблице, которая соответствует одной строке в левой таблице, у вас может быть больше строк в ваших результатах, чем количество строк в левой таблице.

Чтобы сделать то, что вы хотите сделать, вы должны использовать группу и количество для обнаружения кратных.

select citation_id
from stops join master on stops.citation_id = master.citation_id
group by citation_id
having count(*) > 1
person Rémi    schedule 18.03.2012
comment
Спасибо. Это точно. LEFT JOIN означает, что минимальное количество строк в объединении будет равно количеству строк в левой таблице. - person Aren Cambre; 19.03.2012

arrow_upward
4
arrow_downward

Иногда вы знаете, что их несколько, но вам все равно. Вы просто хотите взять первую или верхнюю запись.
Если это так, вы можете использовать SELECT DISTINCT ON:

FROM consistent.master LEFT JOIN (SELECT DISTINCT ON (citation_id) * FROM stops) s
ON s.citation_id = master.citation_id

Где citation_id — это столбец, который вы хотите взять в первую (любую) строку для каждого совпадения.

Возможно, вы захотите убедиться, что это детерминировано, и использовать ORDER BY с каким-либо другим упорядоченным столбцом:

SELECT DISTINCT ON (citation_id) * FROM stops ORDER BY citation_id, created_at
person Andy Hayden    schedule 28.07.2018
comment
вздох, Google приводит меня к моему собственному ответу ... я чувствую, что должен быть лучший способ! - person Andy Hayden; 03.10.2019
comment
Разве это не всегда будет возвращать строки, упорядоченные по citation_id? Я предполагаю, что это первичный ключ, автоматическое увеличение... На самом деле не помогает с желаемым порядком. - person Kevin Parker; 02.03.2020