Трюки с запросами 2

Вредно не читать мануалы. Когда я несколько месяцев назад начал щупать СУБД PostgreSQL, я знал лишь основы построения реляционных БД, немного понимал SQL. И нужно было сделать простую вьюшку, в которой в две колонки разделён трафик клиентов с 8 до 14 и трафик в остальное время. Некоторое время с этим справлялась вьюшка, построенная на трёх других:
CREATE OR REPLACE VIEW nf_mdh_traffic AS
 SELECT n.ip AS subnet, n.y AS year, n.m AS month, n.d AS day, n.h AS hour, sum(n.bytes) AS traf
   FROM nf_statistic n
  GROUP BY n.ip, n.y, n.m, n.d, n.h;CREATE OR REPLACE VIEW nf_mdh_traffic_btw AS
 SELECT nf_mdh_traffic.subnet, nf_mdh_traffic.year, nf_mdh_traffic.month, nf_mdh_traffic.day, sum(nf_mdh_traffic.traf) AS traf
   FROM nf_mdh_traffic
  WHERE nf_mdh_traffic.hour::double precision >= 8::double precision AND nf_mdh_traffic.hour::double precision <= 14::double precision
  GROUP BY nf_mdh_traffic.subnet, nf_mdh_traffic.year, nf_mdh_traffic.month, nf_mdh_traffic.day;CREATE OR REPLACE VIEW nf_mdh_traffic_nbtw AS
 SELECT nf_mdh_traffic.subnet, nf_mdh_traffic.year, nf_mdh_traffic.month, nf_mdh_traffic.day, sum(nf_mdh_traffic.traf) AS traf
   FROM nf_mdh_traffic
  WHERE NOT (nf_mdh_traffic.hour::double precision >= 8::double precision AND nf_mdh_traffic.hour::double precision <= 14::double precision)
  GROUP BY nf_mdh_traffic.subnet, nf_mdh_traffic.year, nf_mdh_traffic.month, nf_mdh_traffic.day;CREATE OR REPLACE VIEW nf_subnets_trafs AS
 SELECT nf_mdh_traffic_btw.subnet, nf_mdh_traffic_btw.year, nf_mdh_traffic_btw.month, nf_mdh_traffic_btw.day, nf_mdh_traffic_btw.traf AS btw_traf, nf_mdh_traffic_nbtw.traf AS nbtw_traf
   FROM nf_mdh_traffic_btw
   FULL JOIN nf_mdh_traffic_nbtw ON nf_mdh_traffic_btw.subnet = nf_mdh_traffic_nbtw.subnet AND nf_mdh_traffic_btw.year = nf_mdh_traffic_nbtw.year AND nf_mdh_traffic_btw.month = nf_mdh_traffic_nbtw.month AND nf_mdh_traffic_btw.day = nf_mdh_traffic_nbtw.day
  WHERE NOT nf_mdh_traffic_btw.subnet IS NULL
  ORDER BY nf_mdh_traffic_btw.subnet, nf_mdh_traffic_btw.year, nf_mdh_traffic_btw.month, nf_mdh_traffic_btw.day; 

А оказывается, всё можно сделать намного проще (да и работает в разы быстрее):
CREATE OR REPLACE VIEW nf_subnets_trafs AS
 SELECT nf_statistic.ip AS subnet, nf_statistic.y AS year, nf_statistic.m AS month, nf_statistic.d AS day, sum(
        CASE
            WHEN nf_statistic.h >= 8 AND nf_statistic.h <= 14 THEN nf_statistic.bytes
            ELSE 0::bigint
        END) AS btw_traf, sum(
        CASE
            WHEN NOT (nf_statistic.h >= 8 AND nf_statistic.h <= 14) THEN nf_statistic.bytes
            ELSE 0::bigint
        END) AS nbtw_traf
   FROM nf_statistic
  GROUP BY nf_statistic.ip, nf_statistic.y, nf_statistic.m, nf_statistic.d
  ORDER BY nf_statistic.ip, nf_statistic.y, nf_statistic.m, nf_statistic.d;

И вот ещё пример. Запрос, строящий детализированный отчёт по трафику всех школ заданного района. Раньше было так:
select * from 
            (SELECT region_id,  sch.sch_name AS school, '14-8', COALESCE(traf.btw::double precision / (2::double precision ^ 20::double precision), 0.0::double precision) AS local_traf, COALESCE(traf.nbtw::double precision / (2::double precision ^ 20::double precision), 0.0::double precision) AS notlocal_traf, COALESCE(traf.tot::double precision / (2::double precision ^ 20::double precision), 0.0::double precision) AS total,
             sch.subnet 
               FROM ( SELECT s.subnet as subnet, r.id as region_id, r.name AS reg_name, s.name AS sch_name, s.subnet AS ip
                       FROM schools s
                  JOIN regions r ON r.id = s.region_id
                 WHERE s.etap <= 3 and s.region_id = 1
                 ORDER BY r.id, s.rtcom_id) sch
               LEFT JOIN (( SELECT n.ip AS ip1, sum(n.bytes) AS btw
                       FROM nf_statistic n
                      WHERE n.is_local = true AND n.m = 2 AND n.y = 2009 AND NOT (n.h >= 8 AND n.h <= 14)
                      GROUP BY n.ip) btw
               FULL JOIN ( SELECT n.ip AS ip2, sum(n.bytes) AS nbtw
                       FROM nf_statistic n
                      WHERE n.is_local = false AND n.m = 2 AND n.y = 2009 AND NOT (n.h >= 8 AND n.h <= 14)
                      GROUP BY n.ip) nbtw ON nbtw.ip2 = btw.ip1
               FULL JOIN ( SELECT n.ip AS ip3, sum(n.bytes) AS tot
                  FROM nf_statistic n
                 WHERE n.y = 2009 AND n.m <= 2 AND NOT (n.h >= 8 AND n.h <= 14)
                 GROUP BY n.ip) tot ON tot.ip3 = btw.ip1 OR tot.ip3 = nbtw.ip2) traf ON traf.ip1 = sch.ip
             UNION 
             SELECT  region_id, sch.sch_name AS school, '8-14', COALESCE(traf.btw::double precision / (2::double precision ^ 20::double precision), 0.0::double precision) AS local_traf, COALESCE(traf.nbtw::double precision / (2::double precision ^ 20::double precision), 0.0::double precision) AS notlocal_traf, COALESCE(traf.tot::double precision / (2::double precision ^ 20::double precision), 0.0::double precision) AS total, 
             sch.subnet 
               FROM ( SELECT s.subnet as subnet, r.id as region_id, r.name AS reg_name, s.name AS sch_name, s.subnet AS ip
                       FROM schools s
                  JOIN regions r ON r.id = s.region_id
                 WHERE s.etap <= 3 and s.region_id = 1
                 ORDER BY r.id, s.rtcom_id) sch
               LEFT JOIN (( SELECT n.ip AS ip1, sum(n.bytes) AS btw
                       FROM nf_statistic n
                      WHERE n.is_local = true AND n.m = 2 AND n.y = 2009 AND n.h >= 8 AND n.h <= 14
                      GROUP BY n.ip) btw
               FULL JOIN ( SELECT n.ip AS ip2, sum(n.bytes) AS nbtw
                       FROM nf_statistic n
                      WHERE n.is_local = false AND n.m = 2 AND n.y = 2009 AND n.h >= 8 AND n.h <= 14
                      GROUP BY n.ip) nbtw ON nbtw.ip2 = btw.ip1
               FULL JOIN ( SELECT n.ip AS ip3, sum(n.bytes) AS tot
                  FROM nf_statistic n
                 WHERE n.y = 2009 AND n.m <= 2 AND n.h >= 8 AND n.h <= 14
                 GROUP BY n.ip) tot ON tot.ip3 = btw.ip1 OR tot.ip3 = nbtw.ip2) traf ON traf.ip1 = sch.ip ) as res
             where region_id = 1;

Теперь стало так:
select s.name, period,
sum(case when m = 2 then rs.local_traf else 0 end) / 2 ^ 20,
sum(case when m = 2 then rs.notlocal_traf else 0 end)  / 2 ^ 20,
sum(case when m <=2 then rs.local_traf+rs.notlocal_traf else 0 end)  / 2 ^ 20, ip
from (select '8-14' as period, y as y, m as m, ip as ip,
sum(case when is_local = true then bytes else 0 end) as local_traf,
sum(case when is_local = false then bytes else 0 end) as notlocal_traf
from nf_statistic n
where h between 8 and 14 and
ip in (select subnet from schools where region_id = 1)
group by ip, y, m
union select '14-8', y, m, ip,
sum(case when is_local = true then bytes else 0 end),
sum(case when is_local = false then bytes else 0 end)
from nf_statistic n
where not h between 8 and 14 and
ip in (select subnet from schools where region_id = 1)
group by ip, y, m) as rs
inner join schools s on s.subnet = rs.ip
where y = 2009 and s.etap <= 3 group by ip, y, period, s.name
order by s.name asc, period desc;

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *