Dotaz: Zjednoduseni dotazu

[dobraczek]

Příloha:

• dotaz.png (150586 bytů)

Prosim o radu, jak lze zjednodusit tento dotaz. Bezne bych to resil PHP, ale zajima me zda-li to vyresit i MySQL.

Nahled dotazu v priloze, nebo zde:

SELECT
	id,
	DATE(datum) as `mydate`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`b`.`datum`) ,' 00:%') ORDER BY `stupnu` LIMIT 1) as `h0`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 02:%') ORDER BY `stupnu` LIMIT 1) as `h1`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 04:%') ORDER BY `stupnu` LIMIT 1) as `h2`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 06:%') ORDER BY `stupnu` LIMIT 1) as `h3`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 08:%') ORDER BY `stupnu` LIMIT 1) as `h4`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 10:%') ORDER BY `stupnu` LIMIT 1) as `h5`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 12:%') ORDER BY `stupnu` LIMIT 1) as `h6`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 14:%') ORDER BY `stupnu` LIMIT 1) as `h7`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 16:%') ORDER BY `stupnu` LIMIT 1) as `h6`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 18:%') ORDER BY `stupnu` LIMIT 1) as `h7`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 20:%') ORDER BY `stupnu` LIMIT 1) as `h8`,
	(SELECT `stupnu` FROM `_teplota` b WHERE `b`.`datum` LIKE CONCAT('%', DATE(`a`.`datum`) ,' 22:%') ORDER BY `stupnu` LIMIT 1) as `h9`
FROM
	`_teplota` a
GROUP BY
	`mydate` DESC
LIMIT 10

Dekuji.

Odpovědi

[deadmail]

zeby nieco taketo?

SELECT 
  id, 
  DATE(datum) AS mydate, 
  MIN(IF(HOUR(datum) DIV 2 = 0, stupnu, NULL)) AS h0, 
  MIN(IF(HOUR(datum) DIV 2 = 1, stupnu, NULL)) AS h1,
  MIN(IF(HOUR(datum) DIV 2 = 2, stupnu, NULL)) AS h2
FROM
  _teplota
GROUP BY
  mydate

[dobraczek]

Příloha:

• Snímek obrazovky 2014-03-28 v 15.13.39.png (60514 bytů)

Bohuzel, toto nefunguje. Vysledky mi to sice vyhodi, ale nejsou spravne. V priloze je ukazka. Vyhodi to teplotu jaka je v jednu hodinu, nikoliv o pulnoci.

Tabulka obsahuje sloupce: id, datum(DATETIME), stupnu(DOUBLE) a chtel jsem vypsat tabulku kde bude den a stupne za kazde 2 hodiny.

Priklad tabulky do ktere davam dotaz:

|  1 | 2014-03-28 11:00:00 | 0.00 |
|  2 | 2014-03-28 12:00:00 | -0.50 |
|  3 | 2014-03-28 13:00:00 | -0.40 |
|  4 | 2014-03-28 14:00:00 | -0.40 |
|  5 | 2014-03-28 16:00:00 | -0.40 |

Muj dotaz sice funguje, ale je silene pomaly. Pravdepodobne za to muze propojeni tech dvou dotazu.

[dobraczek]

Tak uz jsem to vyresil. Dekuji za tip....

[axel]

Doporucuju ptat se ve stylu "chci udelat toto" a maximalne prilozit select s tim, ze "ted to mam takhle a zda se mi to slozite". Rada lidi vcetne mne dotaz zkoumat nebude.

[logik]

Zhruba nástřel: v tomto případě je IMHO blbina načítat to do deseti sloupců vedle, ale načet bych si to postupně.

SELECT
 max(stupnu),
 DATE(datum),
 HOUR(datum),
FROM
 (SELECT
  stupnu,
  DATE(datum) + interval mod(hour(datum,2)) zaokrouhli_datum
 FROM) data
GROUP BY datum
ORDER BY datum

"Pivot tables" (kontigngenční tabulky) pro datábázi moc práce nejsou. Pokud bys to chtěl udělat fakt v databázi, tak asi takhle:

SELECT
 max(if(hodina = 0, stupnu, NULL) as h0,
 max(if(hodina = 2, stupnu, NULL) as h2,
 ...
 max(if(hodina = 22, stupnu, NULL) as h22,
 DATE(datum),
 HOUR(datum),
FROM
 (SELECT
  DATE(datum) as datum,
  HOUR(datum) as hodina,
  stupnu  
 FROM tabulka) data
GROUP BY datum
ORDER BY datum