Micke, av smutsig nyfikenhet, hur lång tid tar min fråga?
Hitta datum som _inte_ finns i tabell
36 svar · 2 052 visningar · startad av MickeA.com · sida 2 av 2
Frågan, av MickeA.com
Hej Har en tabell i en MySQL databas där jag lagrar statistik. Det kan finnas x antal rader med samma datum. Det jag vill göra är att hämta ut MIN() och MAX() datum och ta reda på om det saknas någon dag mellan dessa två. T.ex. 2010-01-18 = MAX 2010-01-17 2010-01-16 2010-01-15 2010-01-13 2010-01-12 2010-01-11 = MIN I exemplet ovan saknas 2010-01-14, går det att med enbart SQL ta reda
Läs frågan i sin helhet →
Micke, av smutsig nyfikenhet, hur lång tid tar min fråga?
0.16 sek, men den ger "Empty set" av någon anledning!? Jag har bytt ut dina fält/tabellnamn mot mina motsvarande.
Har du petat in vettiga datum på alla tre ställen? Säker på att intervallet är så stort att det innefattar minst en existerande post? (Se buggbeskrivningen)
Det här kör jag:
SELECT
DATE_ADD(g1.datetime, INTERVAL 1 DAY) AS vacancystart,
DATEDIFF(COALESCE(MIN(g2.datetime),'2010-01-19'),g1.datetime)-1 AS vacancylength
FROM `stats` g1
LEFT JOIN `stats` g2 ON g2.datetime > g1.datetime
WHERE g1.datetime < '2010-01-19' AND g1.datetime > '2009-01-01'
GROUP BY g1.datetime
HAVING vacancylength>0;
Nu har frågan stått och tuggat i 7750 sekunder, så jag stänger av den nu... :)
Kör jag min feeta procedure på samma datum får jag:
+------------+-------------------+
| tmpDate | count(joiner.dte) |
+------------+-------------------+
| 2009-01-01 | 0 |
| 2009-01-02 | 0 |
| 2009-01-03 | 0 |
| 2009-01-04 | 0 |
| 2009-01-05 | 0 |
| 2009-01-06 | 0 |
| 2009-01-07 | 0 |
| 2009-01-08 | 0 |
| 2009-01-09 | 0 |
| 2009-01-10 | 0 |
| 2009-01-11 | 0 |
| 2009-01-12 | 0 |
| 2009-01-13 | 0 |
| 2009-01-14 | 0 |
| 2009-01-15 | 0 |
| 2009-01-16 | 0 |
| 2009-01-17 | 0 |
| 2009-01-18 | 0 |
| 2009-01-19 | 0 |
| 2009-01-20 | 0 |
| 2009-01-21 | 0 |
| 2009-01-22 | 0 |
| 2009-01-23 | 0 |
| 2009-01-24 | 0 |
| 2009-01-25 | 0 |
| 2009-01-26 | 0 |
| 2009-01-27 | 0 |
| 2009-01-28 | 0 |
| 2009-01-29 | 0 |
| 2009-01-30 | 0 |
| 2009-01-31 | 0 |
| 2009-02-01 | 0 |
| 2009-02-02 | 0 |
| 2009-02-03 | 0 |
| 2009-02-04 | 0 |
| 2009-02-05 | 0 |
| 2009-02-06 | 0 |
| 2009-02-07 | 0 |
| 2009-02-08 | 0 |
| 2009-02-09 | 0 |
| 2009-02-10 | 0 |
| 2009-02-11 | 0 |
| 2009-02-12 | 0 |
| 2009-02-13 | 0 |
| 2009-02-14 | 0 |
| 2009-02-15 | 0 |
| 2009-02-16 | 0 |
| 2009-02-17 | 0 |
| 2009-02-18 | 0 |
| 2010-01-13 | 0 |
| 2010-01-14 | 0 |
| 2010-01-15 | 0 |
| 2010-01-16 | 0 |
| 2010-01-17 | 0 |
+------------+-------------------+
54 rows in set (1.80 sec)
Query OK, 0 rows affected, 1 warning (2.17 sec)
Nu ser jag probsemet med min fråga... joinen joinar en massa rader som den bara kastar bort (tror jag) Återkommer med en ny fråga snart.
Men alldeles oavsett hur du gör så har du allt att vinna på att sätta på indexering på datumfältet!
Men alldeles oavsett hur du gör så har du allt att vinna på att sätta på indexering på datumfältet!
Vilken typ av index? Jag lade till en kolumn som primärnyckel med AI, men det kanske inte gör någon skillnad?
Vanligt index. (För datumkolumnen) Möjligen unique om det är ställt bortom allt tvivel att det inte existerar dublettdatum.
Att ha en annan nolumn som primärnyckel gör inte sökningar baserat på datum snabbare. Genom att indexera datumkolumnen kan DB-motorn göra saker som att leta upp nästa datum snabbare. (Vilket den behöver göra internt.)
Tabellen krympte med ~1Mb efter att jag satt datetime som index. Coolt.
Så, pröva både min fråga och proceduren nu. :)
Nu tänkte jag modifera den här proceduren något, jag vill nämligen kunna ange tabellnamn som indata till den, tillsammas med två datum.
Det funkar så att jag har flera tabeller med statistik och fältet jag ska jämföra emot heter "datetime" i alla tabeller, så det är endast tabellnamnet jag behöver kunna "ändra".
Såhär ser den fungerande funktionen ut:
DELIMITER //
DROP PROCEDURE IF EXISTS checkDateAvailability//
CREATE PROCEDURE checkDateAvailability(d1 date, d2 date)
BEGIN
DECLARE currentDate DATETIME;
DROP TEMPORARY TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo(tmpDate DATE NOT NULL);
SET currentDate = d1;
WHILE currentDate <= d2 DO
INSERT INTO foo(tmpDate) VALUES(currentDate);
SET currentDate = DATE_ADD(currentDate, INTERVAL 1 DAY);
END WHILE;
SELECT
foo.tmpDate, count(joiner.dte)
FROM
foo
LEFT JOIN
(SELECT
DISTINCT(my_table.datetime) as dte
FROM
my_table
) AS joiner
ON foo.tmpDate = joiner.dte
GROUP BY
foo.tmpDate
HAVING
COUNT(joiner.dte) = 0
ORDER BY
foo.tmpDate ASC;
DROP TEMPORARY TABLE foo;
END//
DELIMITER ;
Är lite osäker på datatypen jag ska skicka in, antar att det ska vara "STR" eller något sånt.
Förslag?
Det går, men blir inte snyggt;
DELIMITER //
DROP PROCEDURE IF EXISTS checkDateAvailability//
CREATE PROCEDURE checkDateAvailability(tblname varchar(30), d1 date, d2 date)
BEGIN
DECLARE currentDate DATETIME;
DROP TEMPORARY TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo(tmpDate DATE NOT NULL);
SET currentDate = d1;
WHILE currentDate <= d2 DO
INSERT INTO foo(tmpDate) VALUES(currentDate);
SET currentDate = DATE_ADD(currentDate, INTERVAL 1 DAY);
END WHILE;
SET @prep = CONCAT('SELECT foo.tmpDate, count(joiner.dte) FROM foo LEFT JOIN (SELECT DISTINCT(', tblname, '.datetime) as dte FROM ', tblname, ') AS joiner ON foo.tmpDate = joiner.dte GROUP BY foo.tmpDate HAVING COUNT(joiner.dte) = 0 ORDER BY foo.tmpDate ASC;');
PREPARE stmt FROM @prep;
EXECUTE stmt;
DEALLOCATE PREPARE @prep;
DROP TEMPORARY TABLE foo;
END//
DELIMITER ;
Lite osäker dock, men det bör fungera. Notera att du måste skriva ihop frågan till en sträng.
Jag får:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '@prep
;
Är det bättre att ha fyra olika procedurer, eller är det något annat som gör att det inte "är snyggt"?
Kan tillägga att jag även vill kunna få ut alla datum i ett recordset, måste jag använda mig av IN/OUT då? Verkar som om man (med PHP) får köra två separata anrop, t.ex.:
mysql_query("CALL check...");
$res = mysql_query("SELECT @MIN_OUT_VAR");
Hur hänger det ihop?
Har läst på lite nu och det verkar som om det inte går att få tillbaka ett recordset till PHP på ett smidigt sätt. Man kan däremot använda mysqli och följande kod:
$db = mysqli_connect($db_host, $db_user, $db_passwd);
$db->select_db($db_name);
$db->query("SET NAMES utf8") or die(mysqli_error());
$db->query("SET CHARACTER SET utf8") or die(mysqli_error());
$db->multi_query("CALL checkDateAvailability('2009-01-01', '2010-03-01')");
while($result = $db->store_result()){
while($row = $result->fetch_row()){
echo $row[0] . "<br />";
}
$result->close();
$db->next_result();
}
Ger exakt det resultat jag vill ha:
2009-01-01
2009-01-02
2009-01-03
2009-01-04
2009-01-05
2009-01-06
2009-01-07
...
2009-02-16
2009-02-17
2009-02-18
Då återstår bara det här med tabellnamnet...
Hm, @ kanske är överflödig?
...
SET prep = CONCAT('SELECT foo.tmpDate, count(joiner.dte) FROM foo LEFT JOIN (SELECT DISTINCT(', tblname, '.datetime) as dte FROM ', tblname, ') AS joiner ON foo.tmpDate = joiner.dte GROUP BY foo.tmpDate HAVING COUNT(joiner.dte) = 0 ORDER BY foo.tmpDate ASC;');
PREPARE stmt FROM prep;
EXECUTE stmt;
DEALLOCATE PREPARE prep;
...
Hm, @ är alltså nödvändigt som jag trodde.
Följande är från MySQL.com, och gäller från version 5.0 (då stored procedures introducerades).
delimiter //
DROP PROCEDURE IF EXISTS colavg//
CREATE PROCEDURE colavg(IN tbl CHAR(64), IN col CHAR(64))
READS SQL DATA
COMMENT 'Selects the average of column col in table tbl'
BEGIN
SET @s = CONCAT('SELECT AVG(' , col , ') FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END;
//
delimiter ;
http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html
Märkligt om det inte fungerar? Har inte ork att försöka själv tyvärr...