Блог

Я работаю в Postgres 9.6. У меня есть таблица с именем person , которая выглядит следующим образом:

  id         | integer (pk)        
 name       | character varying(300) 
 name_slug  | character varying(50)
  

И вызывается другая таблица, person_to_manor которая выглядит следующим образом, где person_id это внешний ключ к person.id :

  id        | integer (pk)
 manor_id  | integer
 person_id | integer 
  

Я хочу объединить эти две таблицы, чтобы заполнить третью таблицу, canonical_person в которой находится первичный ключ name_slug , и которая имеет следующие поля:

  name_slug  | character varying(50) (pk)
 name       | character varying(300) 
 num_manor  | integer     
  

где:

• name_slug является ли первичный ключ
• name является наиболее распространенным значением person.name при группировке по name_slug
• num_l66 это количество строк в person_to_manor , которые соответствуют любому из значений id для этого значения name_slug .

Возможно ли это в одном SQL-запросе? Это все, что у меня есть…

 INSERT INTO canonical_person
VALUES (
  SELECT name_slug,
  [most popular value of name from `array_agg(distinct name) from person`],
  COUNT(number of rows in person_to_manor that match any of `array_agg(distinct id) from person`)
  FROM person
  GROUP BY name_slug);
  

Что-то вроде этого?

Я создал три таблицы

 CREATE TABLE test.person (
id int4 NOT NULL,
"name" varchar(300) NULL,
name_slug varchar(50) NULL,
CONSTRAINT person_pkey PRIMARY KEY (id)
);


CREATE TABLE test.person_to_manor (
id int4 NOT NULL,
manor_id int4 NULL,
person_id int4 NULL,
CONSTRAINT person_to_manor_pkey PRIMARY KEY (id),
CONSTRAINT person_to_manor_person_id_fkey FOREIGN KEY (person_id) REFERENCES 
test.person(id)
);


CREATE TABLE test.canonical_person (
name_slug varchar(50) NOT NULL,
"name" varchar(300) NULL,
num_manor int4 NULL,
CONSTRAINT canonical_person_pkey PRIMARY KEY (name_slug)
);
  

Со следующими значениями

 select * from test.person;

id|name|name_slug
--|----|---------
 0|a   |ab       
 1|b   |aa       
 2|c   |ab       
 3|a   |bb       
 4|a   |ab       


select * from test.person_to_manor;

id|manor_id|person_id
--|--------|---------
 1|       5|        0
 2|       6|        0
 3|       7|        2
  

Я запускаю этот запрос

 insert into  test.canonical_person
select name_slug,
       name as most_popular_name,
       sub.n as count_rows
from (
        select name,
               name_slug,count(*) as n,  
               row_number () over(order by count(*) desc)  as n_max
        from test.person 
        group by name,name_slug 
        order by n_max asc
     ) as sub
where sub.n_max =1;
  

Результат после запроса

 select * from test.canonical_person;

name_slug|name|num_manor
---------|----|---------
ab       |a   |        2
  

Это ваша цель?