Genereer statistieken in MySQL

Ik heb een tabel met berichten en ik wil een grafiek genereren die laat zien hoeveel berichten de afgelopen 30 minuten zijn gemaakt, en de laatste 30 minuten daarvoor enz. De berichten worden geselecteerd door hun post_handler en post_status.

De tabelstructuur ziet er als volgt uit.

CREATE TABLE IF NOT EXISTS `posts` (
  `post_title` varchar(255) NOT NULL,
  `post_content` text NOT NULL,
  `post_date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `post_handler` varchar(255) NOT NULL,
  `post_status` tinyint(4) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `post_status` (`post_status`),
  KEY `post_status_2` (`post_status`,`id`),
  KEY `post_handler` (`post_handler`),
  KEY `post_date_added` (`post_date_added`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2300131 ;

De resultaten die ik zou willen ontvangen, gesorteerd op post_date_added.

period_start period_end  posts
2011-12-06 19:23:44 2011-12-06 19:53:44 10
2011-12-06 19:53:44 2011-12-06 20:23:44 39
2011-12-06 20:23:44 2011-12-06 20:53:44 40

Op dit moment gebruik ik een oplossing waarbij ik deze query vele malen moet uitvoeren en vervolgens de gegevens in een andere tabel uit het PHP-script moet invoegen.

SELECT COUNT(*) FROM posts WHERE post_handler = 'test' AND post_status = 1 AND post_date_added BETWEEN '2011-12-06 19:23:44' AND '2011-12-06 19:53:44'

Ken je een andere oplossing? Is er een manier om een ​​query uit te voeren die ook resultaten invoegt in de database, alles in één query?

0

2 antwoord

Het is vrij eenvoudig te groeperen door onderscheidende tijdparameters, zoals uur, minuut, dag of wat dan ook. Als u dit met een uur wilt groeperen, kan een mogelijke query er als volgt uitzien:

SELECT DATE_FORMAT(post_date_added,"%Y-%m-%d %H") AS "_Date", 
    COUNT(*)
FROM posts 
WHERE post_handler = 'test'
    AND post_status = 1
GROUP BY _Date;

(voer dit uit met een mysql-querytool naar keuze om de uitvoer te bekijken).

Als u echter 30 minuten als basis van uw groep wilt beschouwen, wordt het SQL-gedeelte lastiger. Gebruik voor dit speciale doel, omdat je je alleen in twee verschillende subsets hoeft te verdelen, met deze aanpak:

SELECT DATE_FORMAT(post_date_added,"%Y-%m-%d %H") AS "_Date", 
    "00" AS "semihour", 
    COUNT(*)
FROM posts 
WHERE post_handler = 'test'
    AND DATE_FORMAT(post_date_added,"%i") < 30
    AND post_status = 1
GROUP BY _Date
UNION
SELECT DATE_FORMAT(post_date_added,"%Y-%m-%d %H") AS "_Date", 
    "30" AS "semihour", 
     COUNT(*)
FROM posts 
WHERE post_handler = 'test'
    AND DATE_FORMAT(post_date_added,"%i") >= 30
    AND post_status = 1
GROUP BY _Date;

Nogmaals, voer dit uit met een mysql-querytool naar keuze om de uitvoer te bekijken. Je zou daar ook wiskundige onderscheidingen kunnen toevoegen die werken met CASE of IF en dergelijke, maar persoonlijk zou ik ofwel per uur of minuut groeperen om de SQL een stuk eenvoudiger te houden.

Gebruik deze syntaxis om deze getallen rechtstreeks toe te voegen aan uw grafiekdatabase:

INSERT INTO yourtable (yourfields)
SELECT ...

Meer informatie hierover vindt u hier in de MySQL documentatie.

1
toegevoegd

In (very) brief: yes, you can insert the results of a query into another table. Take a look at INSERT ... SELECT here: http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

In wezen zou je gewoon veranderen wat je hebt in iets als

INSERT INTO post_statistics_table (period_start, period_end, posts)
    SELECT ?, ?, COUNT(*) FROM posts
         WHERE post_handler = 'test'
           AND post_status = 1
           AND post_date_added BETWEEN ? AND ?

and then fill in the four ?s with the same two DATETIMEs, repeated. ($from, $to, $from, $to)

1
toegevoegd