Alexander Mylnikov

CSC Бызы данных, 9 дз

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

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

SELECT * FROM BusSummaryPerProject;

project_id  | new  | assigned | fixed | verified | not reproducible
1001          45     24         3       0          10
1002          56     10         37      104        15

Для упрощения можете считать, что множество статусов известно и не собирается меняться.

Во-вторых, для каждого статуса хочется видеть компоненту с максимальным, минимальным и медианным количеством багов с таким статусом. Например, если у нас 7 компонент и количество багов со статусом “new” в них распределено так:

C1 | 10
C2 | 50
C3 | 15
C4 | 30
C5 | 20
C6 | 25
C7 | 35

то в строке представления для статуса new должно быть:

SELECT * FROM StatisticsPerStatus WHERE status='new';

status | max_component | max_value | min_component | min_value | median_component | median_value
new      C2              50          C1              10          C6                 25

Если число компонент четное, то считайте медианой большее из двух возможных значений. Если медианное значение принадлежит нескольким компонентам, то добавьте лексикографическую сортировку по названию компоненты (так, если бы в примере выше в компоненте C4 было бы 25 багов, то медианой стала бы она, если бы в компоненте C5 было 25 багов, то медианой осталась бы C6, а если бы и в C4 и в C5 было бы 25 багов, то медианой стала бы C5)

Собсвтенно решение:

Пунт 1

Для вывода информаци по кадому проекту я построил впомогательно представление которое получает отношение "проект  - статус  - количество"

-- View: statustoproject

-- DROP VIEW statustoproject;

CREATE OR REPLACE VIEW statustoproject AS 
 SELECT count(bug.status_id) AS count, bug.status_id AS status, component.project_id AS project
   FROM bug
   JOIN bugcomponent ON bug.num = bugcomponent.bug_num
   JOIN component ON bugcomponent.component_id = component.id
  GROUP BY component.project_id, bug.status_id
  ORDER BY component.project_id, bug.status_id;

ALTER TABLE statustoproject
  OWNER TO postgres;

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

-- View: bussummaryperproject

-- DROP VIEW bussummaryperproject;

CREATE OR REPLACE VIEW bussummaryperproject AS 
 SELECT project.id AS project_id, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 1), 0::bigint) AS new, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 2), 0::bigint) AS assigned, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 3), 0::bigint) AS fixed, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 4), 0::bigint) AS verified, COALESCE(( SELECT statustoproject.count
           FROM statustoproject
          WHERE statustoproject.project = project.id AND statustoproject.status = 5), 0::bigint) AS "not reproducible"
   FROM project;

ALTER TABLE bussummaryperproject
  OWNER TO postgres;

Пункт 2

Самым сложным для меня было создать функцию медианы

CREATE OR REPLACE FUNCTION array_median(numeric[])
  RETURNS numeric AS
$$
    SELECT CASE WHEN array_upper($1,1) = 0 THEN null ELSE asorted[ceiling(array_upper(asorted,1)/2.0)] END
    FROM (SELECT ARRAY(SELECT ($1)[n] FROM
generate_series(1, array_upper($1, 1)) AS n
    WHERE ($1)[n] IS NOT NULL
            ORDER BY ($1)[n]
) As asorted) As foo ;
$$
  LANGUAGE 'sql' IMMUTABLE;
CREATE  AGGREGATE median(numeric) (
                  SFUNC=array_append,
                  STYPE=numeric[],
                  FINALFUNC=array_median
                );

Далее как и в первом примере я строю вспомогательное представление.

-- View: statustocomponent

-- DROP VIEW statustocomponent;

CREATE OR REPLACE VIEW statustocomponent AS 
 SELECT DISTINCT count(bug.status_id) AS count, bug.status_id AS status, component.id, component.title AS componentname
   FROM bug
   JOIN bugcomponent ON bug.num = bugcomponent.bug_num
   JOIN component ON bugcomponent.component_id = component.id
  GROUP BY component.id, bug.status_id
  ORDER BY component.id, bug.status_id;

ALTER TABLE statustocomponent
  OWNER TO postgres;

Далее строю нужно отношение.

-- View: statisticsperstatus

-- DROP VIEW statisticsperstatus;

CREATE OR REPLACE VIEW statisticsperstatus AS 
 SELECT bugstatus.value AS status, COALESCE(( SELECT statustocomponent.componentname
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id
          ORDER BY statustocomponent.count DESC, statustocomponent.componentname
         LIMIT 1), 'null'::character varying) AS max_component, COALESCE(( SELECT max(statustocomponent.count) AS max
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id), 0::bigint) AS max_value, COALESCE(( SELECT statustocomponent.componentname
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id
          ORDER BY statustocomponent.count, statustocomponent.componentname
         LIMIT 1), 'null'::character varying) AS min_component, COALESCE(( SELECT min(statustocomponent.count) AS min
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id), 0::bigint) AS min_value, COALESCE(( SELECT statustocomponent.componentname
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id AND statustocomponent.count::numeric = (( SELECT median(statustocomponent.count::numeric) AS median
                   FROM statustocomponent
                  WHERE statustocomponent.status = bugstatus.id))
          ORDER BY statustocomponent.componentname
         LIMIT 1), 'null'::character varying) AS median_component, COALESCE(( SELECT median(statustocomponent.count::numeric) AS median
           FROM statustocomponent
          WHERE statustocomponent.status = bugstatus.id), 0::numeric) AS median_value
   FROM bugstatus;

ALTER TABLE statisticsperstatus
  OWNER TO postgres;

Работет все это достаточно шутсро, поэтому решиние претендует быть верным.
Вомозможно я неверно лексиграфически упрорячиваю названия компонент

Дамп базы dbhw9.sql