Использование таблиц, генерируемых в рантайме -> Форум на Исходниках.Ру

	Наши проекты: Журнал · Discuz!ML · Wiki · DRKB · Помощь проекту

Здравствуйте, Гость ! [3.129.23.30]

информация о разделе

Данный раздел предназначается для обсуждения вопросов использования баз данных, за исключением составления запросов на SQL. Для этого выделен специальный раздел. Убедительная просьба - соблюдать "Правила форума" и не пренебрегать "Правильным оформлением своих тем". Прежде, чем создавать тему, имеет смысл заглянуть в раздел "Базы данных: FAQ", возможно там уже есть ответ.

Модераторы: Chow, Bas, MIF

Новое голосование

Использование таблиц, генерируемых в рантайме

JoeUser

Сообщ. #1 , 16.12.14, 16:25

Unregistered

Доделываю отчетность по своему проекту, и уже в третьем отчете заказчик желает знать распределение определенных показателей в разрезе диапазона возрастов. Вобщем вопрос конечно из разряда "с какой стороны намазывать бутерброд правильно?", тем не менее ...

Пока решил так, создаю таблицу, с помощью которой разношу данные:

SELECT

FROM (

SELECT '0-4 года' AS "Rubrica" , 1 AS "SortKey", 0 AS "Min", 4 AS "Max" UNION

SELECT '5-9 лет' AS "Rubrica" , 2 AS "SortKey", 5 AS "Min", 9 AS "Max" UNION

SELECT '10-14 лет' AS "Rubrica" , 3 AS "SortKey", 10 AS "Min", 14 AS "Max" UNION

SELECT '15-19 лет' AS "Rubrica" , 4 AS "SortKey", 15 AS "Min", 19 AS "Max" UNION

SELECT '20-29 лет' AS "Rubrica" , 5 AS "SortKey", 20 AS "Min", 29 AS "Max" UNION

SELECT '30-39 лет' AS "Rubrica" , 6 AS "SortKey", 30 AS "Min", 39 AS "Max" UNION

SELECT '40-49 лет' AS "Rubrica" , 7 AS "SortKey", 40 AS "Min", 49 AS "Max" UNION

SELECT '50-59 лет' AS "Rubrica" , 8 AS "SortKey", 50 AS "Min", 59 AS "Max" UNION

SELECT '60 лет и старше' AS "Rubrica" , 9 AS "SortKey", 60 AS "Min", 300 AS "Max"

) AS r

ORDER BY

r."SortKey"

Результат таков:

Rubrica	Sortkey	Min	Max
0-4 года	1	0	4
5-9 лет	2	5	9
10-14 лет	3	10	14
15-19 лет	4	15	19
20-29 лет	5	20	29
30-39 лет	6	30	39
40-49 лет	7	40	49
50-59 лет	8	50	59
60 лет и старше	9	60	300

Собственно пару вопросов:

1) Может просто создать таблицу в БД и не париться?
2) Если создать таблицу в БД, значительно ли ускориться работа на сложных запросах? (Пока очень сложных нет, пример приведу ниже)
3) Какие есть лучшие решения-альтернативы?

Пример использования:

Скрытый текст

SELECT

y."Rubrica",

COALESCE(x."C1",0) AS "C1",

COALESCE(x."C2",0) AS "C2",

COALESCE(x."C3",0) AS "C3",

COALESCE(x."C4",0) AS "C4",

COALESCE(x."C5",0) AS "C5",

COALESCE(x."C6",0) AS "C6",

COALESCE(x."C7",0) AS "C7",

COALESCE(x."C8",0) AS "C8",

COALESCE(x."C7",0)+COALESCE(x."C8",0) AS "Total"

FROM (

SELECT

x."Rubrica",

x."SortKey",

SUM(x."C1") AS "C1",

SUM(x."C2") AS "C2",

SUM(x."C3") AS "C3",

SUM(x."C4") AS "C4",

SUM(x."C5") AS "C5",

SUM(x."C5") AS "C6",

SUM(x."C7") AS "C7",

SUM(x."C8") AS "C8"

FROM (

SELECT

CASE WHEN x."Holiday" = 0 AND x."Time" BETWEEN '08:00:00' AND '19:59:59' AND x."State" = 1 THEN 1 ELSE 0 END AS "C1",

CASE WHEN x."Holiday" = 0 AND x."Time" BETWEEN '08:00:00' AND '19:59:59' AND x."State" = 0 THEN 1 ELSE 0 END AS "C2",

CASE WHEN x."Holiday" = 0 AND ((x."Time" BETWEEN '20:00:00' AND '23:59:59') OR (x."Time" BETWEEN '00:00:00' AND '07:59:59')) AND x."State" = 1 THEN 1 ELSE 0 END AS "C3",

CASE WHEN x."Holiday" = 0 AND ((x."Time" BETWEEN '20:00:00' AND '23:59:59') OR (x."Time" BETWEEN '00:00:00' AND '07:59:59')) AND x."State" = 0 THEN 1 ELSE 0 END AS "C4",

CASE WHEN x."Holiday" = 1 AND x."State" = 1 THEN 1 ELSE 0 END AS "C5",

CASE WHEN x."Holiday" = 1 AND x."State" = 0 THEN 1 ELSE 0 END AS "C6",

CASE WHEN x."State" = 1 THEN 1 ELSE 0 END AS "C7",

CASE WHEN x."State" = 0 THEN 1 ELSE 0 END AS "C8",

1 AS "C9",

y."Rubrica",

y."SortKey",

x."Age"

FROM (

SELECT

x.*, CASE WHEN (COALESCE(ca."Id",0)>0) THEN 1 ELSE 0 END AS "Holiday"

FROM (

SELECT

x."Client",

x."Gender",

CASE WHEN x."YearOfBirth" < 120 THEN x."YearOfBirth" ELSE EXTRACT(YEAR FROM CURRENT_DATE)- x."YearOfBirth" END AS "Age" ,

x."FamilyId",

x."Service",

x."Date",

x."Time",

x."State"

FROM (

SELECT

ROW_NUMBER() OVER (PARTITION BY x."Client") AS "Row" ,*

FROM (

SELECT

c."Id" AS "Client", c."Gender", c."YearOfBirth", c."FamilyId", s."Id" AS "Service", s."Date", s."Time", s."State"

FROM

public."Clients" AS c

LEFT JOIN

public."Service2Clients" AS sc ON

sc."Client" = c."Id"

LEFT JOIN

public."Service" AS s ON

s."Id" = sc."Service"

WHERE

c."DateContact" BETWEEN '2014-01-01' AND '2014-09-24'

AND

sc."Client" NOTNULL

AND

sc."Present"

ORDER BY

c."Id", s."Date", s."Time"

) AS x

WHERE

x."Row" = 1

) AS x

LEFT JOIN

public."Calendar" AS ca ON

x."Date" = ca."Date"

) AS x

LEFT JOIN (