Блог

Во-первых, я прошу прощения. У меня нет возможности изменять способ хранения данных, поэтому мне приходится работать с ними так, как есть.

У меня есть несколько таблиц во всепроникающей базе данных, которая выглядит следующим образом:

 Records:
id |  first_name  |  last_name  | address   | phone ....
 1 |  John
 2 |  Bill
 3 |  Jared
 4 |  Dave


Users:
id | parent_id| first_name
 2 |        2 | Bill
 3 |        1 | Jared
 4 |        1 | Dave

Times:
user_id | Date
 4      | 2020-09-29
  

Это медицинские записи, User это будет пациент записи, которому может быть 3 года, в то время Records как он будет содержать платежную информацию, но они могут быть или не быть пациентами.

Мне нужны данные из Records таблицы на основе Times таблицы, но я должен просмотреть Users таблицу, чтобы получить ассоциацию. Проблема в том, что я хочу не просто получить точные совпадающие записи, но назначить их family группам и вернуть Records для всех членов этого family

Итак, в этом случае я бы посмотрел на Times таблицу, получив user_id 4, который соответствует Users таблице, дает мне Dave , кто находится в family 1. Теперь мне нужно найти все Users в этом family , а затем получить все эти записи из Records таблицы вместе с записью для John .

Мой предыдущий план состоял в том, чтобы сгладить два столбца id parent_id и возвращать только те, которые находятся в пределах определенного периода времени. Это единственный способ, которым я понял это до сих пор:

 SELECT "parent_id" as ids from  "Users" as u
where u."parent_id" IN(
    SELECT  "parent_id" FROM "Users" where "id" IN(  
        SELECT "user_id"
        FROM Times as a
        WHERE a.Date >=  CURRENT_DATE() - 2
        AND a.Date <=  CURRENT_DATE()   5
    )
)
UNION ALL

SELECT "id" as ids from  "Users" as u
where u."parent_id" IN(
    SELECT  "parent_id" FROM "Users" where "id" IN(  
        SELECT "user_id"
        FROM Times as a
        WHERE a.Date >=  CURRENT_DATE() - 2
        AND a.Date <=  CURRENT_DATE()   5
    )
)
  

Проблема в том, что мне нужно использовать его в качестве фильтра для другого запроса, подобного этому:

 SELECT id, first_name, last_name 
FROM Records WHERE id 
IN(
   insert query here
)
  

Что вернет:

 Output:
id |  first_name  |  last_name  
 1 |  John        |  Smith
 3 |  Jared       |  Smith
 4 |  Dave        |  Smith
  

Что я могу здесь сделать? Эффективность важна, в этих таблицах сотни тысяч записей. Из UNION ALL -за этого я не могу использовать то, что у меня есть в Pervasive.

Я создал ваши таблицы и добавил некоторые данные на основе вашего сообщения и попробовал ваш запрос как опубликованный, и он сработал. У меня недостаточно данных для тестирования производительности, но вы должны иметь возможность использовать средство просмотра плана запроса / плана запроса, чтобы определить, оптимизирован ли оператор. Я использовал как PSQL 11, так и Zen 14 (текущую версию Pervasive PSQL) и получил те же (ожидаемые) результаты. Запрос, который я использовал:

 create table "Users" 
(id int,
parent_id int, 
first_name char(100)
);
create table times 
(user_id int, 
"Date" date);

create table records
(id int,
first_name char(100), 
last_name char(100), 
address char(250), 
phone char(13));

insert into records values (1, 'john', 'smith', 'addr1', 'phone');
insert into records values (2, 'Bill', 'smith', 'addr1', 'phone');
insert into records values (3, 'Jared', 'smith', 'addr1', 'phone');
insert into records values (4, 'Dave', 'smith', 'addr1', 'phone');

insert into "Users" values (2,2,'Bill');
insert into "Users" values (3,1,'Jared');
insert into "Users" values (4,1,'Dave');

insert into Times values (4,'2020-09-29');

SELECT id, first_name, last_name 
FROM Records WHERE id 
IN(
   SELECT "parent_id" as ids from  "Users" as u
where u."parent_id" IN(
    SELECT  "parent_id" FROM "Users" where "id" IN(  
        SELECT "user_id"
        FROM Times as a
        WHERE a.Date >=  CURRENT_DATE() - 2
        AND a.Date <=  CURRENT_DATE()   5
    )
)
UNION ALL

SELECT "id" as ids from  "Users" as u
where u."parent_id" IN(
    SELECT  "parent_id" FROM "Users" where "id" IN(  
        SELECT "user_id"
        FROM Times as a
        WHERE a.Date >=  CURRENT_DATE() - 2
        AND a.Date <=  CURRENT_DATE()   5
    )
)
)