GROUP BY dle datetime?

[Karel Zak]

On Mon, 12 Feb 2001, Zdenek Kotala wrote:


> Resim podobny problem (lisi se pouze tim ze delam sumu po 5 minutach),
> ale komplikovanejsi je to
> v tom, ze potrebuji delat group pres dve polozky (cas a objekt, ktereho
> se to tyka).
> Tabulka ma pre milion zaznamu (velikost zaznamu je <100B) a jaksi dotaz:
> 
> SELECT time, sum(p1), sum(p2), sum(p3), zarizeni 
> FROM tabulka
> GROUP by zarizeni, time
> 
> skonci hlasenim postgresu, ze dosla pamet. Polozka time je predpocitana,
> aby byl cas rozdelen 
> na petiminutove intervaly.
> 
> Potreboval bych poradit jak dotaz preformulovat a  nebo pripadne
> rozdelit.

 Hmm, mozna nejdrive udelat spojeni tech polozek pro group by do jedne
a pak teprve group by:

 select p1, p2, p3, time || zarizeni as grupator into temp 
mytemp from tabulka;

 select grupator, sum(p1), sum(p2), sum(p3) from mytemp
group by grupator;

 ...ale rikam rovnou rada je to blba a nejsem si jist kolik pameti to 
usetri.

 Nejlepsi by bylo rozdelit tu tabulku na mensi tabulky a pak udelat:

insert into mezisoucet select ... from t1 group by zarizeni, time;
insert into mezisoucet select ... from t2 group by zarizeni, time;
	.
	.
insert into mezisoucet select ... from t(n) group by zarizeni, time; 

select ... from mezisoucet by zarizeni, time;

			Karel