mysql - SQL hour averaging in 24hours -


hi i'm trying sql query based on hourly averaging in 24hours temperature sample table below. how can so? let me know if think high frequencies tables architecture face difficulties in future too. appreciate helps. thank you.

_id     node_id modality    timestamp       value ------- ------- ----------- ------------    ------ 4930376 2002    temperature 24/8/15 6:25    30 4930377 2403    temperature 24/8/15 6:24    30 4930378 2004    temperature 24/8/15 6:21    30 4930379 2203    temperature 24/8/15 6:26    29 4930380 2211    temperature 24/8/15 6:21    29 4930381 2401    temperature 24/8/15 6:25    33 4930382 2411    temperature 24/8/15 6:24    30 4930383 2101    temperature 24/8/15 6:22    31 4930384 2201    temperature 24/8/15 6:27    30 4930385 2111    temperature 24/8/15 6:30    36 4930386 2113    temperature 24/8/15 6:27    35 4934082 3200    temperature 24/8/15 10:28   33 4934083 2402    temperature 24/8/15 10:30   32 4934084 2213    temperature 24/8/15 10:30   33 4934085 2103    temperature 24/8/15 10:34   36 4934086 2423    temperature 24/8/15 10:32   32 4934087 2421    temperature 24/8/15 10:34   32 4934088 2413    temperature 24/8/15 10:37   32 4934089 2102    temperature 24/8/15 10:31   35 4934090 2202    temperature 24/8/15 10:35   32 4934091 2404    temperature 24/8/15 10:33   35 4934092 2121    temperature 24/8/15 10:39   36 4934093 2002    temperature 24/8/15 10:35   33 4940370 2123    temperature 24/8/15 17:44   36 4940371 2422    temperature 24/8/15 17:40   33 4940372 2001    temperature 24/8/15 17:36   35 4940373 2301    temperature 24/8/15 17:39   32 4940374 2402    temperature 24/8/15 17:36   32 4940375 2103    temperature 24/8/15 17:40   36 4940376 2423    temperature 24/8/15 17:38   33 4940377 2421    temperature 24/8/15 17:40   33 

select avg(value), _id, node_id, modality, value, timestamp     yourtable     group date(timestamp), hour(timestamp);

-

Comments

Post a Comment

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

Image
in code below when try execute main.java getting exception: exception in thread "main" org.hibernate.unknownentitytypeexception: unable locate persister: com.np.vta.test.pojo.users @ org.hibernate.internal.sessionfactoryimpl.locateentitypersister(sessionfactoryimpl.java:792) @ org.hibernate.internal.sessionimpl.locateentitypersister(sessionimpl.java:2637) @ org.hibernate.internal.sessionimpl.access$2500(sessionimpl.java:164) @ org.hibernate.internal.sessionimpl$identifierloadaccessimpl.<init>(sessionimpl.java:2575) @ org.hibernate.internal.sessionimpl$identifierloadaccessimpl.<init>(sessionimpl.java:2562) @ org.hibernate.internal.sessionimpl.byid(sessionimpl.java:1044) @ org.hibernate.internal.sessionimpl.get(sessionimpl.java:955) @ com.app.test.main.main(main.java:20) but if uncomment cfg.addclass( users.class ).addresource( "com/np/vta/test/pojo/users.hbm.xml" ); code works fine. why not reading <mapping...
Read more

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

i'm using (for first time) scikit library , got error: valueerror: empty vocabulary; perhaps documents contain stop words file "c:\users\a605563\desktop\velibprojetpreso\traitementtwitterdico.py", line 33, in <module> x_train_counts = count_vect.fit_transform(filetweets) file "c:\python27\lib\site-packages\sklearn\feature_extraction\text.py", line 804, in fit_transform self.fixed_vocabulary_) file "c:\python27\lib\site-packages\sklearn\feature_extraction\text.py", line 751, in _count_vocab raise valueerror("empty vocabulary; perhaps documents contain stop words but don't understand why that's happening. import sklearn sklearn.feature_extraction.text import countvectorizer import pandas pd import numpy import unicodedata import nltk tweetsfile = open('tweets2015-08-13.csv', 'r+') f2 = open('analyzer.txt', 'a') print tweetsfile.readline() count_vect = countvectorizer(strip_accents='ascii...
Read more

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -

Image
i'm trying build precimonious on ubuntu 16.04.3 x64. allocated 1gb memory it. file structure looks like ~ |--- llvm/ |--- precimonious/ where llvm on version 3.0 mentioned @ https://github.com/corvette-berkeley/precimonious#requirement . followed steps on readme command make ended with ... llvm[1]: linking debug shared library libllvm-3.0.so collect2: fatal error: ld terminated signal 9 [killed] compilation terminated. ... i went through answers online , might because there's not enough memory perform link. memory usage the gcc version on machine gcc version 5.4.0 20160609 (ubuntu 5.4.0-6ubuntu1~16.04.4) , installed via apt-get install build-essential . fresh droplet created on digitalocean btw. appreciated. it memory exhaustion. sampling interval memory measurement not small enough covers exact point oom killer kicks in. depending on container/vm technology, may able set vm.overcommit_memory=2 before build, process not killed (but doing requires m...
Read more