ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Прошу помочь с оптимизацией запроса
9 сообщений из 9, страница 1 из 1
Прошу помочь с оптимизацией запроса
    #38718435
Majestio
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Majestio
Гость
Исходные данные:

Есть четыре таблицы - Clients, RubricaNames, Rubricator, Points2Rubrica

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE public."Clients" (
  "Id" INTEGER DEFAULT nextval('"Clients_Id_seq1"'::regclass) NOT NULL, 
  "CardNumber" INTEGER, 
  "Surname" VARCHAR(48), 
  "FirstName" VARCHAR(48) NOT NULL, 
  "Patronymic" VARCHAR(48), 
  "SurnameOld" VARCHAR(48), 
  "DateContact" DATE, 
  "Gender" BOOLEAN NOT NULL, 
  "YearOfBirth" INTEGER, 
  "Address" VARCHAR(128), 
  "Phone" VARCHAR(64), 
  "Reason" VARCHAR(2048), 
  "FamilyId" INTEGER, 
) WITHOUT OIDS;



Код: plsql
1.
2.
3.
4.
5.
CREATE TABLE public."RubricaNames" (
  "Id" SERIAL, 
  "Name" VARCHAR(128), 
  "System" INTEGER DEFAULT 0, 
) WITHOUT OIDS;



Код: plsql
1.
2.
3.
4.
5.
CREATE TABLE public."Rubricator" (
  "Id" SERIAL, 
  "GroupId" INTEGER NOT NULL, 
  "Name" VARCHAR(2048), 
) WITHOUT OIDS;



Код: plsql
1.
2.
3.
4.
5.
CREATE TABLE public."Points2Rubrica" (
  "Point" INTEGER NOT NULL, 
  "Rubrica" INTEGER NOT NULL, 
  "System" INTEGER
) WITHOUT OIDS;



Где:

Clients - карточки клиентов
RubricaNames - заведенные пользователем рубрикаторы
Rubricator - значения рубрикаторов
Points2Rubrica - таблица привязок значений рубрикаторов к карточкам клиентов

Задача: вывести плоскую таблицу, в каждой записи которой есть Id, FamilyId из карточки и 12 полей - привязанных значений рубрикатора (или 0, если привязки нет), а так же 1 или 0, в зависимости от "совершеннолетия".

Пока решил следующим запросом:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
SELECT 
  c."Id",
  c."FamilyId",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Вдова/Вдовец'
                  )
             )
             LIMIT 1),0) AS "R1", 
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Жертва ЧАЭС'
                  )
             )
             LIMIT 1),0) AS "R2",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Инвалидность'
                  )
             )
             LIMIT 1),0) AS "R3",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Пенсионер'
                  )
             )
             LIMIT 1),0) AS "R4",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Семейное положение'
                  )
             )
             LIMIT 1),0) AS "R5",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Относится к группе риска'
                  )
             )
             LIMIT 1),0) AS "R6",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Получение инвалидности'
                  )
             )
             LIMIT 1),0) AS "R7",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."FamilyId" AND
              pr."System" = 4 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Отселенная семья'
                  )
             )
             LIMIT 1),0) AS "R8",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."FamilyId" AND
              pr."System" = 4 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Получение пособий'
                  )
             )
             LIMIT 1),0) AS "R9",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."FamilyId" AND
              pr."System" = 4 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Опекунская семья'
                  )
             )
             LIMIT 1),0) AS "R10",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."FamilyId" AND
              pr."System" = 4 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Семья погибшего военнослужащего'
                  )
             )
             LIMIT 1),0) AS "R11",
  CASE WHEN EXTRACT(year FROM now()) - c."YearOfBirth" < 18 THEN 1 ELSE 0 END AS "R12"
FROM public."Clients" AS c
WHERE c."DateContact" < '2014-01-11'
ORDER BY c."Id"



Но быстродействие как-то немного сомнительно. 1000 записей обрабатывается в районе 14 секунд. Как считаете, есть куда оптимизировать? И если "да", покажите пожалуйста куда копать. А еще бы лучше на примере 2 рубрикаторов.
...
Рейтинг: 0 / 0
12.08.2014, 16:01:07
| Ответить | Цитировать | Написать  
Прошу помочь с оптимизацией запроса
    #38718450
Фотография roadster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
roadster
Участник
Majestio,

http://postgresql.ru.net/manual/tutorial-join.html
...
Рейтинг: 0 / 0
12.08.2014, 16:05:43
| Ответить | Цитировать | Написать  
Прошу помочь с оптимизацией запроса
    #38718466
Фотография roadster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
roadster
Участник
Majestio1 или 0, в зависимости от "совершеннолетия".сразу правьте
Majestio
Код: plsql
1.
 "YearOfBirth" INTEGER,

на
Код: sql
1.
dayofBirth date

хотя бы по причине того, что совершеннолетие наступает в день рождения, а не в год рождения.
порадовало полеMajestio
Код: plsql
1.
"Gender" BOOLEAN

то есть гендер есть или его нет :) кстати это ещё и неполиткорректно, а в некоторых странах есть средний пол
...
Рейтинг: 0 / 0
12.08.2014, 16:10:15
| Ответить | Цитировать | Написать  
Прошу помочь с оптимизацией запроса
    #38718487
Majestio
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Majestio
Гость
roadster , про объединения я в курсе, только не понимаю каким боком их тут применить :-\ На каждый новый столбец "значения рубрикатора" объединять с одними и теми же таблицами по нескольку раз?
...
Рейтинг: 0 / 0
12.08.2014, 16:18:41
| Ответить | Цитировать | Написать  
Прошу помочь с оптимизацией запроса
    #38718502
Majestio
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Majestio
Гость
roadsterMajestio1 или 0, в зависимости от "совершеннолетия".сразу правьте
Majestio
Код: plsql
1.
 "YearOfBirth" INTEGER,

на
Код: sql
1.
2.
dayofBirth date
[/quot]

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

Заказчик настаивает только на годе рождения. В принципе его понять можно - отчеты как правило годовые. В январе за прошлый год. Думаю, так прокатит.

roadsterпорадовало поле
Majestio
Код: plsql
1.
"Gender" BOOLEAN

то есть гендер есть или его нет :) кстати это ещё и неполиткорректно, а в некоторых странах есть средний пол
Пофик. У нас такого нет.

Помогите лучче с примером на объединение. Хотя бы 2 рубрикатора, дальше я сам соображу.
...
Рейтинг: 0 / 0
12.08.2014, 16:23:56
| Ответить | Цитировать | Написать  
Прошу помочь с оптимизацией запроса
    #38718530
Фотография roadster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
roadster
Участник
MajestioЗаказчик настаивает только на годе рождения.заказчик завтра передумает и придётся много переделывать, проще в структуре заложиться на дату и доставать из неё год в запросе.
MajestioПомогите лучче с примером на объединение.хм...
по структуре всё совсем неочевидно, но если следовать описанию таблиц, а именно
Majestio Points2Rubrica - таблица привязок значений рубрикаторов к карточкам клиентов
то выглядеть это будет примерно так
Код: sql
1.
2.
3.
4.
select c.*, r.*
from public."Clients" AS c
left join public."Points2Rubrica" AS pr on (pr."Point" = c."Id")
left join public."Rubricator" AS r on (r."Id" = pr."Rubrica")


это без имени рубрики и я не понимаю почему именно 12 полей?
а если у заказчика появится новая рубрика например "Семья погибшего опекуна-инвалида, отселённого для получения пособия"? Вы будете весь год переписывать?
...
Рейтинг: 0 / 0
12.08.2014, 16:37:24
| Ответить | Цитировать | Написать  
Прошу помочь с оптимизацией запроса
    #38718533
Фотография roadster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
roadster
Участник
Majestio,

да, в примере будет много строк для одного клиента, но можно сделать вью описанного вида, а потом запросом транспонировать и агрегировать его как хочется.
...
Рейтинг: 0 / 0
12.08.2014, 16:38:40
| Ответить | Цитировать | Написать  
Прошу помочь с оптимизацией запроса
    #38718585
Majestio
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Majestio
Гость
roadsterMajestioЗаказчик настаивает только на годе рождения.заказчик завтра передумает и придётся много переделывать, проще в структуре заложиться на дату и доставать из неё год в запросе.
Специфика эксплуатации. Как видно, наверное, из сказанного выше - клиенты в критических психологических состояниях. Часто от множества вопросов бросают телефонную трубку. Заказчик (психологи) настояли на вводе или года рождения в поля, или возраста. В зависимости от числа я трактую его в программе, либо как год рождения, либо как возраст на момент регистрации. Конечно, погрешности тут явные. Изначально и было забито поле типа "дата". Настояли убрать, аргументировав вышесказанным. Это пошло под их подпись в Т3. Захотят прередалать - пусть оплачивают, ноу проблем.

roadsterMajestioПомогите лучче с примером на объединение.хм...
по структуре всё совсем неочевидно, но если следовать описанию таблиц, а именно
Majestio Points2Rubrica - таблица привязок значений рубрикаторов к карточкам клиентов
то выглядеть это будет примерно так
Код: sql
1.
2.
3.
4.
select c.*, r.*
from public."Clients" AS c
left join public."Points2Rubrica" AS pr on (pr."Point" = c."Id")
left join public."Rubricator" AS r on (r."Id" = pr."Rubrica")




Спасибо. И за оперативный ответ - отдельно. Попробую поэксперементировать с объединениями.

roadsterэто без имени рубрики и я не понимаю почему именно 12 полей?

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

roadsterа если у заказчика появится новая рубрика например "Семья погибшего опекуна-инвалида, отселённого для получения пособия"? Вы будете весь год переписывать?

Не появится) Рубрикацию мы уже месяца два согласовывали. Сложные описатели разносим по нескольким рубрикам. В вопросе выше это будет набор рубрикаторов:

* Инвалид (да/нет)
* Опекун (да/нет)
* Погибший (да/нет)
* Семья на отселении (да/нет)
* Получает пособие (да/нет)

Отчет довольно громоздкий, 96 полей "аля Семья погибшего опекуна-инвалида, отселённого для получения пособия". Решил вытащить плоскую таблицу и пересчитать на клиенте.

В информационной модели есть "Карточки клиентов" и есть "Семьи" (но в неявном виде), реализуемые привязкой карточек между собой степенью родства. Каждая семья так же рубрицируется (видно в запросе по FamilyId). Ну вот как-то так) Еще раз спасибо, пошел Navicat мучать))
...
Рейтинг: 0 / 0
12.08.2014, 17:05:45
| Ответить | Цитировать | Написать  
Прошу помочь с оптимизацией запроса
    #38719081
Majestio
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Majestio
Гость
Вопрос решен в плане оптимизации.
Новый вид запроса выполняется в 7 раз быстрее (2 сек вместо 14).
Уже более-менее "съедобно".

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
WITH cr AS (
SELECT *
FROM crosstab(
     'SELECT z."CardId", z."RubricatorId", COALESCE(z."Rubrica",0) AS "Rubrica" FROM (                                   
     WITH x AS (                                                                                                        
     SELECT cl."Id", r."GroupId", pr."Rubrica"                                                                          
      FROM public."Points2Rubrica" AS pr, public."Rubricator" AS r, public."Clients" AS cl                              
      WHERE                                                                                                             
           ((pr."Point" = cl."Id" AND pr."System" = 2) OR (pr."Point" = cl."FamilyId" AND pr."System" = 4)) AND        
            pr."Rubrica" = r."Id"                                                                                       
     )                                                                                                                  
     SELECT * FROM (                                                                                                    
     SELECT c."Id" AS "CardId", c."FamilyId" AS "CardFamilyId", rn."Name" AS "RubricatorName", rn."Id" AS "RubricatorId"
     FROM public."Clients" AS c, public."RubricaNames" AS rn                                                            
     WHERE                                                                                                              
       rn."Name" IN (                                                                                                   
      ''Вдова/Вдовец'',                                                                                                   
      ''Жертва ЧАЭС'',                                                                                                    
      ''Инвалидность'',                                                                                                   
      ''Опекунская семья'',                                                                                               
      ''Относится к группе риска'',                                                                                       
      ''Отселенная семья'',                                                                                               
      ''Пенсионер'',                                                                                                      
      ''Получение инвалидности'',                                                                                         
      ''Получение пособий'',                                                                                              
      ''Семейное положение'',                                                                                             
      ''Семья погибшего военнослужащего''                                                                                 
     )) AS s                                                                                                            
     LEFT OUTER JOIN x ON (s."RubricatorId" = x."GroupId" AND s."CardId" = x."Id")                                      
     ) AS z
     ORDER BY 1,2'
    ) as ( "Id"  int, "R1" int, "R2" int, "R3" int, "R4" int, "R5" int, "R6" int, "R7" int, "R8" int, "R9" int, "R10" int, "R11" int)
)
SELECT cr.*, cl."FamilyId", CASE WHEN (cl."YearOfBirth" < 18) OR (EXTRACT(year FROM now()) - cl."YearOfBirth" < 18) THEN 1 ELSE 0 END AS "Infant" 
FROM cr, public."Clients" AS cl
WHERE cr."Id" = cl."Id"



Для обеспечения функционирования этого "хозяйства" во FreeBSD ставим порт:

Код: sql
1.
2.
cd /usr/ports/databases/postgresql92-contrib
make install clean



Потом регаем расширения:

Код: sql
1.
CREATE EXTENSION tablefunc;



Пользуемся нужной функцией crosstab

Вопрос закрыт, если нет возражений)
...
Рейтинг: 0 / 0
13.08.2014, 10:55:04
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Прошу помочь с оптимизацией запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=124&tid=1998540]:
 0ms
get settings:
 11ms
get forum list:
 19ms
check forum access:
 6ms
check topic access:
 6ms
track hit:
 71ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 225ms
total:  401ms
созд. / загр.: 401ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]