Блог

Мое требование состоит в том, чтобы написать sql-запрос, чтобы получить количество событий с учетом субрегиона (сбоя), которые произошли для управляемых объектов. Моя база данных — postgres 8.4. Позвольте мне объяснить, используя структуру таблицы.

Мои таблицы в django: управляемый объект:

 class Managedobject(models.Model):
   name                = models.CharField(max_length=200, unique=True)
   iscontainer         = models.BooleanField(default=False,)
   parentkey           = models.ForeignKey('self', null=True)
  

Таблица событий:

 class Event(models.Model):
    Name        = models.CharField(verbose_name=_('Name'))
    foid        = models.ForeignKey(Managedobject)
  

Записи управляемых объектов:

 NOC
   Chennai
      MO_1
      MO_2
      MO_3
   Mumbai
      MO_4
      MO_5
      MO_6
   Delhi
   Bangalore
IP
   Calcutta
   Cochin
  

Записи событий:

 event1 MO_1
event2 MO_2
event3 MO_3
event4 MO_5
event5 MO_6    
  

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

 for NOC region:
  Chennai - 3
  Mumbai - 2
  Delhi - 0
  Bangalore - 0
  

Пока я могу получить результат в двух разных запросах.

1. Получаем подобласти.

    select id from managedobject where iscontainer = True and parentkey = 3489
     

2. Для каждой области (используя цикл for), получите количество следующим образом:

    SELECT count(*) 
   from event ev 
   WHERE ev.foid 
   IN (
       WITH RECURSIVE q AS (
           SELECT h 
           FROM managedobject h 
           WHERE parentkey = 3489 
           UNION ALL 
           SELECT hi 
           FROM q 
           JOIN managedobject hi 
           ON hi.parentkey = (q.h).id 
       ) 
       SELECT (q.h).id FROM q
   )
     

Пожалуйста, помогите объединить запросы, чтобы сделать их одним запросом и получить 5 лучших регионов. Поскольку запрос в django сложный, я использую необработанный sql-запрос.

Я получил запрос:

 WITH RECURSIVE q AS ( 
  SELECT  h, 
          1 AS level, 
          id AS ckey, 
          displayname as dname 
  FROM managedobject h 
  WHERE parentkey = 3489  
    and logicalnode=True 

 UNION ALL 

 SELECT  hi, 
         q.level   1 AS level, 
         ckey, 
         dname 
 FROM q 
   JOIN managedobject hi ON hi.parentkey = (q.h).id 
) 
SELECT count(ckey) as ccount, 
       ckey, 
       dname 
FROM q 
  JOIN event as ev on ev.foid_id = (q.h).id 
GROUP BY ckey, dname 
ORDER BY ccount DESC 
LIMIT 5