Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Немного вопросов по функциям / 18 сообщений из 18, страница 1 из 1
27.07.2015, 12:17
    #39016586
Majestio
Majestio
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
Доброго времени суток!

Решил часть часто-используемого кода вынести во VIEW, но т.к. ранее этим не пользовался, полез в сеть за объяснялками и примерами. Во многих местах советуют в случае необходимости параметров писать функцию. Ладно, посмотрел как это делается, написал вот такое:
 
Код: 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.
CREATE OR REPLACE FUNCTION public."ClientOnService" (
  datebegin date,
  dateend date
)
RETURNS TABLE (
  id integer,
  familyid integer,
  age integer,
  sex integer,
  ischild integer,
  isparent integer
) AS
$body$
BEGIN
  RETURN QUERY 
  SELECT
    x."Id"::INTEGER,
    x."FamilyId"::INTEGER,
    x."Age"::INTEGER,
    x."Sex"::INTEGER,
    CASE WHEN
      SUM(CASE WHEN COALESCE(x."Childs1",0)>0 THEN 1 ELSE 0 END) = 0 AND
      SUM(CASE WHEN COALESCE(fa2."Relation",0)>0 THEN 1 ELSE 0 END) = 0 AND
      x."Age" < 18
      THEN 1::INTEGER ELSE 0::INTEGER END AS "IsChild",
    CASE WHEN
      SUM(CASE WHEN COALESCE(x."Childs1",0)>0 THEN 1 ELSE 0 END) > 0 AND
      SUM(CASE WHEN COALESCE(fa2."Relation",0)>0 THEN 1 ELSE 0 END) = 0
      THEN 1::INTEGER ELSE 0::INTEGER END AS "IsParent"
  FROM (
    SELECT
      c."Id",
      c."FamilyId",
      CASE WHEN c."YearOfBirth" < 150 THEN c."YearOfBirth" ELSE (EXTRACT(year FROM now()) - c."YearOfBirth") END AS "Age",
      c."Gender" AS "Sex",
      c."Gender",
      CASE WHEN ((COALESCE(fa1."Relation",0)>0) AND (fa1."ClientOne" = c."Id")) THEN fa1."ClientTwo"
           WHEN ((COALESCE(fa1."Relation",0)>0) AND (fa1."ClientTwo" = c."Id")) THEN fa1."ClientOne" ELSE 0 END AS "Childs1"
    FROM
      "Clients" AS c
    LEFT JOIN
      "Family" AS fa1 ON
        (fa1."ClientOne" = c."Id" AND fa1."Relation" IN (15,16,19,20)) OR
        (fa1."ClientTwo" = c."Id" AND fa1."Relation" IN (10,21,22,30))
    WHERE
      c."FamilyId" IN (
        SELECT
          c."FamilyId"
        FROM
          "Service" AS s,
          "Service2Clients" AS sc,
          "Clients" AS c
        WHERE
          c."Id" = sc."Client" AND
          sc."Service" = s."Id" AND
          s."Date" BETWEEN DateBegin AND DateEnd AND
          s."State" > 0 AND
          sc."Present"
      )
  ) AS x
  LEFT JOIN
    "Family" AS fa2 ON
      (fa2."ClientOne" = x."Childs1" AND fa2."Relation" IN (15,16,19,20)) OR
      (fa2."ClientTwo" = x."Childs1" AND fa2."Relation" IN (10,21,22,30))
  GROUP BY
    x."Id", x."FamilyId", x."Age", x."Sex";
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;


Запустил в SQL-менеджере, запросило две даты и выдало результат. Все устроило.
Ну собственно, возникли вопросы:

Эта функция и есть "хранимая процедура" или это еще какой-то дополнительный механизм?

В настройках видел еще такой описатель LANGUAGE 'sql' , чем это лучше/хуже чем LANGUAGE 'plpgsql' ? Или вообще не заморачиваться?

При написании было много ругни на возвращаемые типы, пришлось каждому добавить "приведение" в виде ::INTEGER. Вопрос какого лешего если они и так целые? Или где я ошибаюсь?

Написал изначально для помещения в аргументы констант, получаемых из GUI. Но, почитавши далее, понял, что данную функцию в принципе можно использовать как обычную таблицу. Если это действительно так, подскажите плс как, к примеру, сделать FULL OUTER JOIN с другой таблицей, чтобы использовались поля с другой таблицы в качестве аргументов. Табличка примера ниже.

Код: plsql
1.
2.
3.
4.
5.
6.
CREATE TABLE public."Period" (
  "Id" SERIAL, 
  "StartDate" DATE NOT NULL, 
  "StopDate" DATE NOT NULL, 
  CONSTRAINT "Period_pkey" PRIMARY KEY("Id")
) WITHOUT OIDS;



WBR,
Majestio
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2015, 12:50
    #39016637
mad_nazgul
mad_nazgul
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
MajestioЭта функция и есть "хранимая процедура" или это еще какой-то дополнительный механизм?



В рамках PostgreSQL это одно и то же.

MajestioВ настройках видел еще такой описатель LANGUAGE 'sql' , чем это лучше/хуже чем LANGUAGE 'plpgsql' ? Или вообще не заморачиваться?



"Это вообще три разных человека".
"sql" - это просто sql
"plpgsql" - это процедурный язык похожий на plsql

MajestioПри написании было много ругни на возвращаемые типы, пришлось каждому добавить "приведение" в виде ::INTEGER. Вопрос какого лешего если они и так целые? Или где я ошибаюсь?



С 9 версии PostgreSQL ужесточил типизацию.
Так что нужно обязательно явно указывать возвращаемый тип.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2015, 12:59
    #39016655
Majestio
Majestio
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
mad_nazgul , спасибо за понятный ответ! :)

Еще бы на последний вопрос какой-нить инфы ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2015, 15:59
    #39016932
PCContra
PCContra
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
plpgsql медленне чистого sql
У вас чистый sql. Можете поменять

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
AS
$body$
BEGIN
  RETURN QUERY 
  SELECT 
...
$body$
LANGUAGE 'plpgsql'


на
Код: sql
1.
2.
3.
4.
5.
6.
AS
$$
SELECT
...
$$
LANGUAGE 'sql'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2015, 16:02
    #39016939
PCContra
PCContra
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
ТСНаписал изначально для помещения в аргументы констант, получаемых из GUI. Но, почитавши далее, понял, что данную функцию в принципе можно использовать как обычную таблицу. Если это действительно так, подскажите плс как, к примеру, сделать FULL OUTER JOIN с другой таблицей, чтобы использовались поля с другой таблицы в качестве аргументов. Табличка примера ниже.

1) Вам не тяжело смотреть на ваш код?
Перейдите на запросы CTE (WITH ...)
Указывайте хоть сто таблиц в запросе.

2)В функции можете писать несколько команд, разделенных точкой с запятой ;

Последней командой выведите результат
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2015, 17:10
    #39017020
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
PCContra <>
1) Вам не тяжело смотреть на ваш код?
Перейдите на запросы CTE (WITH ...)
Указывайте хоть сто таблиц в запросе.

никогда (в текушей реализации CTE в PG ) не используйте CTE, особо к большим таблицам, если не хотите целенаправленно материализовать промежуточный результат.

т.е. никогда не следуйте советам некоторых местных клоунов, выделивших себя любовью к лаконичности стиля деклараций ПЖ.


В отличии от оракла -- тут CTE не алиас, а всегда -- материализованный набор. Пользуйтесь этим, как хинтом , если нужно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2015, 18:24
    #39017056
p2.
p2.
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
qwwqт.е. никогда не следуйте советам некоторых местных клоунов, выделивших себя любовью к лаконичности стиля деклараций ПЖ.
Не понял, чем смотреть на with в каждом запросе лучше view или функции, но для начала противпису нужно продемонстрировать параметризацию with.
Majestioв случае необходимости параметров писать функцию
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 10:22
    #39017391
Majestio
Majestio
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
PCContra, спасибо за советы, отдельные приму на вооружение :) По поводу CTE ... уже достаточно давно решил для себя по минимуму этим пользоваться, а лучше вообще не пользоваться. Причиной тому были словленные тормоза на одном из моих "монстриков"-запросов. Переписал тогда, запрос заметно ускорился. Вообщем остался "осадочек" :) И тут я где-то согласен с qwwq , чисто интуитивно.

Увы, моя практика использования SQL слишком краткосрочна, приходится учится "на лету". Ценность многих решений нивелируется недостаточной практикой, поэтому часто пишу не совсем эффективно, но понятно мне (когда скорость выполнения получается приемлема). Для примера, вот один из запросов, которым формирую один из своих отчетов:

 

Код: 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.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
SELECT
  SUM(r."Z01"), SUM(r."Z02"), SUM(r."Z03"), SUM(r."Z04"), SUM(r."Z05"), SUM(r."Z06"), SUM(r."Z07"), SUM(r."Z08"), SUM(r."Z09"), SUM(r."Z10"),
  SUM(r."Z11"), SUM(r."Z12"), SUM(r."Z13"), SUM(r."Z14"), SUM(r."Z15"), SUM(r."Z16"), SUM(r."Z17"), SUM(r."Z18"), SUM(r."Z19"), SUM(r."Z20"),
  SUM(r."Z21"), SUM(r."Z22"), SUM(r."Z23"), SUM(r."Z24"), SUM(r."Z25"), SUM(r."Z26"), SUM(r."Z27"), SUM(r."Z28"), SUM(r."Z29"), SUM(r."Z30"),
  SUM(r."Z31"), SUM(r."Z32"), SUM(r."Z33"), SUM(r."Z34")
FROM (
  SELECT
    *,
    CASE WHEN z."Z03"+z."Z04"+z."Z05"+z."Z06"+z."Z07"+z."Z08"+z."Z09"+
              z."Z10"+z."Z11"+z."Z12"+z."Z13"+z."Z14"+z."Z15"+z."Z16"+z."Z17"+z."Z18"+z."Z19"+
              z."Z20"+z."Z21"+z."Z22"+z."Z23"+z."Z24"+z."Z25"+z."Z26"+z."Z27"+z."Z28"+z."Z29"+
              z."Z30"+z."Z31"+z."Z32"+z."Z33" > 0 THEN 0 ELSE 1 END AS "Z34"
  FROM (
    SELECT
      x."FamilyId",
      -- 1 +
      SUM(x."X02") AS "Z01",
      -- 2 +
      SUM(x."X03") AS "Z02",
      -- 3 +
      CASE WHEN SUM(x."X03")>2 THEN 1 ELSE 0 END AS "Z03",
      -- 4 +
      CASE WHEN SUM(x."X05")>0 THEN 1 ELSE 0 END AS "Z04",
      -- 5 +
      CASE WHEN SUM(x."X06")=1 THEN 1 ELSE 0 END AS "Z05",
      -- 6 +
      CASE WHEN SUM(x."X06")=1 AND SUM(x."X03")>0 THEN 1 ELSE 0 END AS "Z06",
      -- 7 +
      CASE WHEN SUM(x."X07")>0 THEN 1 ELSE 0 END AS "Z07",
      -- 8 +
      CASE WHEN SUM(x."X08")>0 THEN 1 ELSE 0 END AS "Z08",
      -- 9 +
      CASE WHEN SUM(x."X09")>0 THEN 1 ELSE 0 END AS "Z09",
      -- 10 +
      CASE WHEN SUM(x."X10")>0 AND SUM(x."X03")>0 THEN 1 ELSE 0 END AS "Z10",
      -- 11 +
      CASE WHEN SUM(x."X11")=1 THEN 1 ELSE 0 END AS "Z11",
      -- 12 +
      CASE WHEN SUM(x."X12")=1 THEN 1 ELSE 0 END AS "Z12",
      -- 13 +
      CASE WHEN SUM(x."X13")>0 THEN 1 ELSE 0 END AS "Z13",
      -- 14 +
      CASE WHEN SUM(x."X14")=1 THEN 1 ELSE 0 END AS "Z14",
      -- 15 +
      CASE WHEN SUM(x."X15")>0 THEN 1 ELSE 0 END AS "Z15",
      -- 16 +
      CASE WHEN SUM(x."X16")>0 THEN 1 ELSE 0 END AS "Z16",
      -- 17 +
      CASE WHEN MIN(x."X17")=0 THEN 0 ELSE 1 END AS "Z17",
      -- 18 +
      CASE WHEN SUM(x."X03")>0 AND SUM(x."X03")<3 THEN 1 ELSE 0 END AS "Z18",
      -- 19 +
      CASE WHEN SUM(x."X19")>0 THEN 1 ELSE 0 END AS "Z19",
      -- 20 +
      CASE WHEN SUM(x."X20")>0 THEN 1 ELSE 0 END AS "Z20",
      -- 21 +
      CASE WHEN SUM(x."X21")>0 THEN 1 ELSE 0 END AS "Z21",
      -- 22 +
      CASE WHEN SUM(x."X21")>0 AND SUM(x."X03")>0 THEN 1 ELSE 0 END AS "Z22",
      -- 23 + 
      CASE WHEN SUM(x."X23")>0 THEN 1 ELSE 0 END AS "Z23",
      -- 24 + 
      CASE WHEN SUM(x."X24")>0 THEN 1 ELSE 0 END AS "Z24",
      -- 25 + 
      CASE WHEN SUM(x."X25")>0 THEN 1 ELSE 0 END AS "Z25",
      -- 26 +
      CASE WHEN SUM(x."X16")>0 AND SUM(x."X03")>0 THEN 1 ELSE 0 END AS "Z26",
      -- 27 +
      CASE WHEN SUM(x."X27")>0 THEN 1 ELSE 0 END AS "Z27",
      -- 28 +
      CASE WHEN SUM(x."X03")>0 THEN 1 ELSE 0 END AS "Z28",
      -- 29 +
      CASE WHEN SUM(x."X29")>0 THEN 1 ELSE 0 END AS "Z29",
      -- 30 +
      CASE WHEN SUM(x."X30")>0 THEN 1 ELSE 0 END AS "Z30",
      -- 31 +
      CASE WHEN SUM(x."X31")>0 THEN 1 ELSE 0 END AS "Z31",
      -- 32 +
      CASE WHEN SUM(x."X32")>0 THEN 1 ELSE 0 END AS "Z32",
      -- 33 +
      CASE WHEN SUM(x."X33")>0 THEN 1 ELSE 0 END AS "Z33"
    FROM (
      SELECT
        x."Id",
        x."Age",
        x."Gender",
        x."FamilyId",
        x."IsChild",
        x."IsParent",
        -- это человек семьи (для просчета количества людей в семье)
        1 AS "X01",                                   
        -- это ребенок до 14 лет
        CASE WHEN x."IsChild">0 AND x."Age"<14 THEN 1 ELSE 0 END AS "X02",                                   
        -- это ребенок до 18 лет
        CASE WHEN x."IsChild">0 THEN 1 ELSE 0 END AS "X03",
        -- это родитель, для подсчета родителей
        CASE WHEN x."IsParent">0 THEN 1 ELSE 0 END AS "X04",
        -- это молодая семья                             
        CASE WHEN x."R10">0 THEN 1 ELSE 0 END AS "X05",
        -- это вдова или вдовец                             
        CASE WHEN x."R01" = 694 THEN 1 ELSE 0 END AS "X06",
        -- это женщина-мать, родившая вне брака
        CASE WHEN (x."R01" NOT IN (692,693,694)) AND (x."Gender">0) AND (x."IsParent">0) THEN 1 ELSE 0 END AS "X07",
        -- это несовершеннолетняя одинокая мать
        CASE WHEN (x."R01" NOT IN (692,697)) AND (x."Gender">0) AND (x."IsParent">0) AND (x."Age"<18) THEN 1 ELSE 0 END AS "X08",
        -- это одинокая мать, получающая пособие
        CASE WHEN (x."R01" NOT IN (692,697)) AND (x."Gender">0) AND (x."IsParent">0) AND (x."R11">0) THEN 1 ELSE 0 END AS "X09",
        -- это разведенный родитель
        CASE WHEN (x."R01" = 693) AND (x."IsParent">0) THEN 1 ELSE 0 END AS "X10",
        -- это семья усыновителей + родитель
        CASE WHEN (x."R12" > 0) AND (x."IsParent">0) THEN 1 ELSE 0 END AS "X11",
        -- это гражданин пожилого возраста
        CASE WHEN (x."Age" > 60) THEN 1 ELSE 0 END AS "X12",
        -- это одиноко-проживающий гражданин пожилого возраста
        CASE WHEN (x."Age" > 60) AND (x."R03" > 0) THEN 1 ELSE 0 END AS "X13",
        -- это инвалид I-II группы
        CASE WHEN (x."R02" IN (863,864)) THEN 1 ELSE 0 END AS "X14",
        -- это одиноко-проживающий инвалид I-II группы
        CASE WHEN (x."R02" IN (863,864)) AND (x."R03" > 0) THEN 1 ELSE 0 END AS "X15",
        -- это выпускник-родитель
        CASE WHEN (x."IsParent" > 0) AND (x."R04" > 0) THEN 1 ELSE 0 END AS "X16",
        -- это пенсионер
        CASE WHEN (x."R05" > 0) OR (x."R08" = 858) THEN 1 ELSE 0 END AS "X17",
        -- несовершеннолетний родитель
        CASE WHEN (x."IsParent" > 0) AND (x."Age" < 18) THEN 1 ELSE 0 END AS "X19",
        -- несовершеннолетний родитель из числа детей-сирот
        CASE WHEN (x."IsParent" > 0) AND (x."Age" < 18) AND (x."R09" > 0) THEN 1 ELSE 0 END AS "X20",
        -- родитель-инвалид I-II группы
        CASE WHEN (x."IsParent" > 0) AND (x."R02" IN (863,864)) THEN 1 ELSE 0 END AS "X21",
        -- ребенок в социально-опасном положении
        CASE WHEN (x."IsChild" > 0) AND (x."R06" > 0) THEN 1 ELSE 0 END AS "X23",
        -- ребенок-инвалид с детства
        CASE WHEN (x."IsChild" > 0) AND (x."R02" = 1235) THEN 1 ELSE 0 END AS "X24",
        -- ребенок из числа детей сирот
        CASE WHEN (x."IsChild" > 0) AND (x."R09" > 0) THEN 1 ELSE 0 END AS "X25",
        -- ребенок выпускник интернат
        CASE WHEN (x."IsChild" > 0) AND (x."R04" > 0) THEN 1 ELSE 0 END AS "X26",
        -- ребенок c болезнями
        CASE WHEN (x."IsChild" > 0) AND (x."R07" > 0) THEN 1 ELSE 0 END AS "X27",
        -- из семьи беженцев
        CASE WHEN (x."R14" > 0) THEN 1 ELSE 0 END AS "X29",
        -- из семьи опекунов
        CASE WHEN (x."R13" > 0) THEN 1 ELSE 0 END AS "X30",
        -- из семьи приемной
        CASE WHEN (x."R15" > 0) THEN 1 ELSE 0 END AS "X31",
        -- из семьи отселенной
        CASE WHEN (x."R16" > 0) THEN 1 ELSE 0 END AS "X32",
        -- из семьи, получающей пособие
        CASE WHEN (x."R11" > 0) THEN 1 ELSE 0 END AS "X33"
      FROM (
        SELECT
          x.*,
          COALESCE(pr01."Rubrica",0) AS "R01", -- Семейное положение
          COALESCE(pr02."Rubrica",0) AS "R02", -- Инвалидность
          COALESCE(pr03."Rubrica",0) AS "R03", -- Одинокое проживание
          COALESCE(pr04."Rubrica",0) AS "R04", -- Выпускник интернатных учреждений             
          COALESCE(pr05."Rubrica",0) AS "R05", -- Пенсионер
          COALESCE(pr06."Rubrica",0) AS "R06", -- Социально опасное положение              
          COALESCE(pr07."Rubrica",0) AS "R07", -- Наличие заболеваний
          COALESCE(pr08."Rubrica",0) AS "R08", -- Социальный статус
          COALESCE(pr09."Rubrica",0) AS "R09", -- Из числа детей-сирот
          COALESCE(pr10."Rubrica",0) AS "R10", -- Молодая семья                 
          COALESCE(pr11."Rubrica",0) AS "R11", -- Получение пособий 
          COALESCE(pr12."Rubrica",0) AS "R12", -- Семья усыновителей               
          COALESCE(pr13."Rubrica",0) AS "R13", -- Опекунская семья                   
          COALESCE(pr14."Rubrica",0) AS "R14", -- Семья беженцев
          COALESCE(pr15."Rubrica",0) AS "R15", -- Приемная семья
          COALESCE(pr16."Rubrica",0) AS "R16"  -- Отселенная семья
        FROM (
          SELECT
            x."Id",
            x."Age",
            CASE WHEN x."Gender" THEN 1 ELSE 0 END AS "Gender",
            x."FamilyId",
            CASE WHEN 
              SUM(CASE WHEN COALESCE(x."Childs1",0)>0 THEN 1 ELSE 0 END) = 0 AND 
              SUM(CASE WHEN COALESCE(fa2."Relation",0)>0 THEN 1 ELSE 0 END) = 0 AND
              x."Age" < 18 
              THEN 1 ELSE 0 END AS "IsChild",
            CASE WHEN 
              SUM(CASE WHEN COALESCE(x."Childs1",0)>0 THEN 1 ELSE 0 END) > 0 AND 
              SUM(CASE WHEN COALESCE(fa2."Relation",0)>0 THEN 1 ELSE 0 END) = 0
              THEN 1 ELSE 0 END AS "IsParent"
          FROM (
            SELECT
              c."Id",
              CASE WHEN c."YearOfBirth" < 150 THEN c."YearOfBirth" ELSE (EXTRACT(year FROM now()) - c."YearOfBirth") END AS "Age",
              c."Gender",
              c."FamilyId",
              CASE WHEN ((COALESCE(fa1."Relation",0)>0) AND (fa1."ClientOne" = c."Id")) THEN fa1."ClientTwo" 
                   WHEN ((COALESCE(fa1."Relation",0)>0) AND (fa1."ClientTwo" = c."Id")) THEN fa1."ClientOne" ELSE 0 END AS "Childs1"
            FROM
              "Clients" AS c
            LEFT JOIN
              "Family" AS fa1 ON
                (fa1."ClientOne" = c."Id" AND fa1."Relation" IN (15,16,19,20)) OR
                (fa1."ClientTwo" = c."Id" AND fa1."Relation" IN (10,21,22,30))
            WHERE
              c."FamilyId" IN (
                SELECT 
                  c."FamilyId"
                FROM
                  "Service" AS s,
                  "Service2Clients" AS sc,
                  "Clients" AS c
                WHERE 
                  c."Id" = sc."Client" AND 
                  sc."Service" = s."Id" AND 
                  s."Date" BETWEEN '2015-01-01' AND '2015-12-31' AND 
                  s."State" > 0 AND 
                  sc."Present"
              )
          ) AS x
          LEFT JOIN
            "Family" AS fa2 ON
              (fa2."ClientOne" = x."Childs1" AND fa2."Relation" IN (15,16,19,20)) OR
              (fa2."ClientTwo" = x."Childs1" AND fa2."Relation" IN (10,21,22,30))
          GROUP BY
            x."Id", x."Age", x."Gender", x."FamilyId"
        ) AS x
        LEFT JOIN "Points2Rubrica" AS pr01 ON 
          pr01."Point" = x."Id" AND pr01."System" & 2 = 2 AND 
          pr01."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 111)
        LEFT JOIN "Points2Rubrica" AS pr02 ON 
          pr02."Point" = x."Id" AND pr02."System" & 2 = 2 AND 
          pr02."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 100)
        LEFT JOIN "Points2Rubrica" AS pr03 ON 
          pr03."Point" = x."Id" AND pr03."System" & 2 = 2 AND 
          pr03."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 104)
        LEFT JOIN "Points2Rubrica" AS pr04 ON 
          pr04."Point" = x."Id" AND pr04."System" & 2 = 2 AND 
          pr04."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 156)
        LEFT JOIN "Points2Rubrica" AS pr05 ON 
          pr05."Point" = x."Id" AND pr05."System" & 2 = 2 AND 
          pr05."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 106)
        LEFT JOIN "Points2Rubrica" AS pr06 ON 
          pr06."Point" = x."Id" AND pr06."System" & 2 = 2 AND 
          pr06."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 130)
        LEFT JOIN "Points2Rubrica" AS pr07 ON 
          pr07."Point" = x."Id" AND pr07."System" & 2 = 2 AND 
          pr07."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 153)
        LEFT JOIN "Points2Rubrica" AS pr08 ON 
          pr08."Point" = x."FamilyId" AND pr08."System" & 2 = 2 AND 
          pr08."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 114)
        LEFT JOIN "Points2Rubrica" AS pr09 ON 
          pr09."Point" = x."FamilyId" AND pr09."System" & 2 = 2 AND 
          pr09."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 112)
        LEFT JOIN "Points2Rubrica" AS pr10 ON 
          pr10."Point" = x."FamilyId" AND pr10."System" & 4 = 4 AND 
          pr10."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 125)
        LEFT JOIN "Points2Rubrica" AS pr11 ON 
          pr11."Point" = x."FamilyId" AND pr11."System" & 4 = 4 AND 
          pr11."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 128)
        LEFT JOIN "Points2Rubrica" AS pr12 ON 
          pr12."Point" = x."FamilyId" AND pr12."System" & 4 = 4 AND 
          pr12."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 155)
        LEFT JOIN "Points2Rubrica" AS pr13 ON 
          pr13."Point" = x."FamilyId" AND pr13."System" & 4 = 4 AND 
          pr13."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 126)
        LEFT JOIN "Points2Rubrica" AS pr14 ON 
          pr14."Point" = x."FamilyId" AND pr14."System" & 4 = 4 AND 
          pr14."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 154)
        LEFT JOIN "Points2Rubrica" AS pr15 ON 
          pr15."Point" = x."FamilyId" AND pr15."System" & 4 = 4 AND 
          pr15."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 152)
        LEFT JOIN "Points2Rubrica" AS pr16 ON 
          pr16."Point" = x."FamilyId" AND pr16."System" & 4 = 4 AND 
          pr16."Rubrica" IN (SELECT r."Id" FROM "Rubricator" AS r WHERE r."GroupId" = 127)
      ) AS x
    ) AS x
    GROUP BY
      x."FamilyId"
  ) AS z
) AS r



Поэтому, в той функции для меня не все так страшно, страшно получается позже :) О5 же, чисто интуитивно понимаю, что в продемонстрированном выше вполне бы мог использовать crosstab вместо кучи джоинов. Но ... до сих пор не понимаю, как тот же crosstab может развернуть результат по отсутствующему в выборке полю. Вернее понимаю, нужно собственно это поле добавить в подзапрос, а потом в вышележащем запросе его выинуть... Но как сделал - так мне нагляднее. Скорость выполнения приведенного на 5000 записях основной таблицы - около пол-секунды, меня это устраивает. Прогноз роста таблицы - около 1000 записей/год.

Еще раз всем спасибо за советы. И это .... и все же последний вопрос моего первого поста ... Напишите плс маленький пример, если это несложно для вас.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 10:32
    #39017397
PCContra
PCContra
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
qwwqтут я где-то согласен с qwwq, чисто интуитивно
Надо посмотреть будет, взять на заметку. qwwq у нас суровый, краснодарские пацаны все такие ;-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 10:58
    #39017432
PCContra
PCContra
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
qwwqникогда (в текушей реализации CTE в PG ) не используйте CTE, особо к большим таблицам, если не хотите целенаправленно материализовать промежуточный результат.

т.е. никогда не следуйте советам некоторых местных клоунов, выделивших себя любовью к лаконичности стиля деклараций ПЖ.


Maxim Boguk PS: 15 лет работы с Postgresql приучили к тому что самые простые и дубовые решения лучше всего если они обеспечивают приемлемую производительность (так как были моменты когда через 3 года после запуска я не мог понять как работает особо хитрая система написанная лично мной в подобном стиле :)).

Каждый сам по себе решает...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 11:00
    #39017438
Majestio
Majestio
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
qwwq никогда (в текушей реализации CTE в PG ) не используйте CTE, особо к большим таблицам, если не хотите целенаправленно материализовать промежуточный результат.

qwwq , выделенное осознал (частично) и запомнил :) Для полного понимания, раз уж пошел разговор об этом, хотелось бы выяснить остальные моменты, "оставшиеся за кадром".

Собственно, вопрос ...

И все же, есть ли такие ситуации, когда CTE является наилучшим решением, приносящим наибольший профит? Или "никогда" нужно понимать буквально как "никогда", без всяких "если"? А если все же такие ситуации есть, каковы они, каковы их отличительные признаки?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 11:44
    #39017486
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
Majestio,

блин, читатели,
надо отличать конструкцию "никогда $1..если не $2" от всеобщего "никогда $1"

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


вообще говоря "никогда, $1 к большим таблицам, если размер мат-й выборки будет большим, и вы не понимаете, что вы этим хотите достигнуть"

в этом смысле "вложенный подзапрос" -- гораздо более "простое по Богику" решение, чем CTE. Ибо оставляют простор в планировании оптимизатору.

Насколько я понял писиконтрика -- он именно на вложенные подзапросы батоны крошит. Или булки мнёт. А это -- тупость и непонимание разницы физической реализации. Вот если бы CTE был реализован как алиас к вложенному, а не как материализованный темповый набор -- тогда бы речи за простоту имели бы основание. А пока -- нет.

ну да, ещё я любитель пользоваться CTE в конструкциях WITH recursive -- для организации ручного index--seek [nestedloop] там, где это не умеет оптимайзер ПЖ. Но большая часть этого исскюства отмирает за ненадобностью с появлением внятного LATERAL( ... LIMIT ...) .
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 12:38
    #39017528
ОКТОГЕН
ОКТОГЕН
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
MajestioИ все же, есть ли такие ситуации, когда CTE является наилучшим решением, приносящим наибольший профит? Или "никогда" нужно понимать буквально как "никогда", без всяких "если"? А если все же такие ситуации есть, каковы они, каковы их отличительные признаки?
Есть такие ситуации. Если вам надо использовать наборы-агрегаты с небольшим количеством
данных от большой таблицы несколько раз.
Или не агрегат, а просто что-нить заковыристое, например, каждая N-я точка на графике+ экстремумы+начало и конец, чтобы это потом использовать для чего-то ещё.
То есть сам итог маленький, а исходная табличка - большая.
Пишу иногда сильно помороченные запросы для отчётов - очень выручает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 15:00
    #39017701
PCContra
PCContra
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
qwwqну да, ещё я любитель пользоваться CTE в конструкциях WITH recursive -- для организации ручного index--seek [nestedloop] там, где это не умеет оптимайзер ПЖ. Но большая часть этого исскюства отмирает за ненадобностью с появлением внятного LATERAL( ... LIMIT ...)

Можно ссылку на примеры, пожалуйста, - заинтересовала технология.

Я использую with, где результат небольшой (на мой взгляд), а выборка сложная
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 18:03
    #39017992
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
PCContraqwwqну да, ещё я любитель пользоваться CTE в конструкциях WITH recursive -- для организации ручного index--seek [nestedloop] там, где это не умеет оптимайзер ПЖ. Но большая часть этого исскюства отмирает за ненадобностью с появлением внятного LATERAL( ... LIMIT ...)

Можно ссылку на примеры, пожалуйста, - заинтересовала технология.

Я использую with, где результат небольшой (на мой взгляд), а выборка сложная
один из вариантов , из частоупотребительных, был где-то изложен в т.ч. у максима в pdf.
и тут. -- реализация DISTINCT при наличии индекса и большой повторяемости. Случай не единственный, но как шаблон--паттерн -- оно всё довольно однотипно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 18:10
    #39018004
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
PCContraqwwqну да, ещё я любитель пользоваться CTE в конструкциях WITH recursive -- для организации ручного index--seek [nestedloop] там, где это не умеет оптимайзер ПЖ. Но большая часть этого исскюства отмирает за ненадобностью с появлением внятного LATERAL( ... LIMIT ...)

Можно ссылку на примеры, пожалуйста, - заинтересовала технология.

Я использую with, где результат небольшой (на мой взгляд), а выборка сложная

Вот тут вот лежит
http://pgday.ru/files/pgmaster14/max.boguk.query.optimization.pdf

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2015, 18:25
    #39018011
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
Maxim BogukPCContraпропущено...


Можно ссылку на примеры, пожалуйста, - заинтересовала технология.

Я использую with, где результат небольшой (на мой взгляд), а выборка сложная

Вот тут вот лежит
http://pgday.ru/files/pgmaster14/max.boguk.query.optimization.pdf

--
Maxim Boguk
www.postgresql-consulting.ru

А правильный вынос мозга на счет WITH (RECURSIVE) и вообще реализации сложных итеративных алгоритмов в SQL запросах тут:
http://astarsan.livejournal.com/6465.html
и его разбор
http://astarsan.livejournal.com/6895.html

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2016, 11:30
    #39210888
claygod
claygod
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного вопросов по функциям
PCContraplpgsql медленнее чистого sql
. . .

Скажите, а среди PL/pgSQL, PL/Tcl, PL/Perl, PL/Python также есть ощутимая разница ?
(без учёта математических расчётов и тому подобного)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Немного вопросов по функциям / 18 сообщений из 18, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 541ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1997306]:
 0ms
get settings:
 9ms
get forum list:
 21ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 178ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 72ms
get tp. blocked users:
 2ms
others: 238ms
total:  541ms
x
x
Закрыть


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