Mysql GROUP BY ve çoklu için COUNT WHERE yan tümceleri

1 Cevap php

Basitleştirilmiş Tablo yapısı:

CREATE TABLE IF NOT EXISTS `hpa` (
  `id` bigint(15) NOT NULL auto_increment,
  `core` varchar(50) NOT NULL,
  `hostname` varchar(50) NOT NULL,
  `status` varchar(255) NOT NULL,
  `entered_date` int(11) NOT NULL,
  `active_date` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `hostname` (`hostname`),
  KEY `status` (`status`),
  KEY `entered_date` (`entered_date`),
  KEY `core` (`core`),
  KEY `active_date` (`active_date`)
)

Bunun için, ben sadece tanımlanmış durumu ile tüm kayıtları yukarı toplamları aşağıdaki SQL sorgu var.

SELECT core,COUNT(hostname) AS hostname_count, MAX(active_date) AS last_active
          FROM `hpa`
          WHERE 
          status != 'OK' AND status != 'Repaired'
          GROUP BY core
          ORDER BY core

Bu sorgu INNER soruyu etkilemez ilgisiz veri ve ekstra sütunlara KATILDI kaldırmak için basitleştirilmiş oldu.

MAX (active_date) belirli bir günde tüm kayıtlar için aynı olduğunu ve her zaman ŞİMDİ ofset () En son günü seçin, ya da izin vermelidir. (Bir unixtime alandır)

Ben sayımını de istiyorum: (! Status = 'OK' VE status = 'Tamir')

VE ters ... sayımı: (status = 'OK' OR statüsüne = 'Tamir')

VE ilk cevap (sonradan işleme yapmak için muhtemelen sadece hızlı) 'percentage_dead' için, ikinci bölü

En son bir gün veya bir ofset İÇİN (-. Vs dün, için 86.400)

Tablo yaklaşık 500k kayıtlarını içeren ve loop aksine böylece tek bir SQL sorgusu gerçek güzel olurdu yaklaşık 5000 bir gün büyür ..

Kullanıcının bu yapabileceğini EĞER bazı yaratıcı hayal. Sen uzmanlık takdir edilmektedir.

EDIT: Ben bir ofset gelen günümüzün veri veya verisi için farklı bir SQL sorgusunu kullanarak açığım.

EDIT: Sorgu, işleri yeterince hızlı olduğunu, ancak şu anda yüzde sütunu (kötü ve iyi sayar türetilmiş bir) sıralama kullanıcılara izin veremeyiz. Bu bir gösteri tıpa değil, ama onları her şeyin üzerinde sıralamak için izin. Bu BY ORDER:

SELECT h1.core, MAX(h1.entered_date) AS last_active, 
SUM(CASE WHEN h1.status IN ('OK', 'Repaired') THEN 1 ELSE 0 END) AS good_host_count,  
SUM(CASE WHEN h1.status IN ('OK', 'Repaired') THEN 0 ELSE 1 END) AS bad_host_count 
FROM `hpa` h1 
LEFT OUTER JOIN `hpa` h2 ON (h1.hostname = h2.hostname AND h1.active_date < h2.active_date) 
WHERE h2.hostname IS NULL 
GROUP BY h1.core 
ORDER BY ( bad_host_count / ( bad_host_count + good_host_count ) ) DESC,h1.core

Gives me: #1247 - Reference 'bad_host_count' not supported (reference to group function)

EDIT: Farklı bir bölüm için çözüldü. Aşağıdaki çalışmaları ve benim için ORDER BY percentage_dead verir

SELECT c.core, c.last_active, 
SUM(CASE WHEN d.dead = 1 THEN 0 ELSE 1 END) AS good_host_count,  
SUM(CASE WHEN d.dead = 1 THEN 1 ELSE 0 END) AS bad_host_count,
( SUM(CASE WHEN d.dead = 1 THEN 1 ELSE 0 END) * 100/
( (SUM(CASE WHEN d.dead = 1 THEN 0 ELSE 1 END) )+(SUM(CASE WHEN d.dead = 1 THEN 1 ELSE 0 END) ) ) ) AS percentage_dead
FROM `agent_cores` c 
LEFT JOIN `dead_agents` d ON c.core = d.core
WHERE d.active = 1
GROUP BY c.core
ORDER BY percentage_dead

1 Cevap

Ben anlamak, son etkinlik tarihinde, OK vs tamam değil makine ismi durumunun sayısını almak istiyorum. Değil mi? Ve daha sonra bu kısım göre gruplandırılmış olmalıdır.

SELECT core, MAX(active_date)
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 1 ELSE 0 END) AS OK_host_count,
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 0 ELSE 1 END) AS broken_host_count
FROM `hpa` h1 LEFT OUTER JOIN `hpa` h2 
  ON (h1.hostname = h2.hostname AND h1.active_date < h2.active_date)
WHERE h2.hostname IS NULL
GROUP BY core
ORDER BY core;

Bu benim StackOverflow SQL sorular çok şey görüyorum "büyük-n-grup başına" sorunun bir çeşididir.

Öncelikle bir dış aynı hostname ve daha büyük bir active_date ile satırlar için katılmak yaparak yapabilirsiniz konak için son etkinlik tarih var sadece satırları seçmek istiyorum. Biz böyle bir eşleşme bulmak nerede, biz zaten verilen her bir konak için son satır var.

Sonra çekirdek tarafından grup ve durumuna göre satırları saymak.

Bugünün tarihine (hiçbir satır varsayarak gelecekte bir active_date vardır) için bir çözümdür. N gün önce satır sonucu sınırlamak için, her iki tablo kısıtlamak zorunda.

SELECT core, MAX(active_date)
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 1 ELSE 0 END) AS OK_host_count,
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 0 ELSE 1 END) AS broken_host_count
FROM `hpa` h1 LEFT OUTER JOIN `hpa` h2 
  ON (h1.hostname = h2.hostname AND h1.active_date < h2.active_date
  AND h2.active_date <= CURDATE() - INTERVAL 1 DAY)
WHERE h1.active_date <= CURDATE() - INTERVAL 1 DAY AND h2.hostname IS NULL
GROUP BY core
ORDER BY core;

OK ve kırık hostnameler arasındaki oran ile ilgili olarak, ben sadece PHP kodu bu hesaplama tavsiye ederim. SQL diğer select listesi ifadelerde sütun diğer adları referans izin vermez, böylece bir alt sorgu olarak yukarıdaki sarmak için olurdu ve bu durumda değer çok daha karmaşık.

Ben size bir UNIX zaman damgası kullanılarak dedim unuttum. Böyle bir şey yapın:

SELECT core, MAX(active_date)
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 1 ELSE 0 END) AS OK_host_count,
  SUM(CASE WHEN status IN ('OK', 'Repaired') THEN 0 ELSE 1 END) AS broken_host_count
FROM `hpa` h1 LEFT OUTER JOIN `hpa` h2 
  ON (h1.hostname = h2.hostname AND h1.active_date < h2.active_date
  AND h2.active_date <= UNIX_TIMESTAMP() - 86400)
WHERE h1.active_date <= UNIX_TIMESTAMP() - 86400 AND h2.hostname IS NULL
GROUP BY core
ORDER BY core;

etiketler