JOIN
GROUP BY
body = {"rows":[ {"json": {"column_name":7.7,}} ]}response = bigquery.tabledata().insertAll( projectId=PROJECT_ID, datasetId=DATASET_ID, tableId=TABLE_ID, body=body).execute()
SUM()
COUNT()
AVG()
MIN()
MAX()
FIRST_VALUE
LAST_VALUE()
COVAR_POP()
COVAR_SAMP()
STDDEV_POP()
STDDEV_SAMP()
VAR_POP()
VAR_SAMP()
SELECT CORR(a.data, b.data) corr, a.room room, count(*) cFROM ( SELECT TIME(USEC_TO_TIMESTAMP(INTEGER(Timestamp / 60000000) * 60000000)) time, AVG(DATA) data, room FROM [io_sensor_data.moscone_io13] WHERE DATE(USEC_TO_TIMESTAMP(Timestamp- 8*60*60000000)) = '2013-05-16' AND sensortype='temperature' GROUP EACH BY time, room) aJOIN EACH ( SELECT TIME(USEC_TO_TIMESTAMP(INTEGER(Timestamp / 60000000) * 60000000)) time, AVG(data) data, room FROM [io_sensor_data.moscone_io13] WHERE DATE(USEC_TO_TIMESTAMP(Timestamp- 8*60*60000000)) = '2013-05-17' AND sensortype='temperature' GROUP EACH BY time, room) b ON a.time=b.time AND a.room = b.room GROUP EACH BY roomHAVING corr IS NOT NULL AND c > 800 ORDER EACH BY corr DESC
SELECT CORR(a.data, b.data) corr, a.sensortype a_sensortype, b.sensortype b_sensortype, a.room room, count(*) cFROM ( SELECT TIME(USEC_TO_TIMESTAMP(INTEGER(Timestamp / 60000000) * 60000000)) time, AVG(DATA) data, room, sensortype FROM [io_sensor_data.moscone_io13] WHERE DATE(USEC_TO_TIMESTAMP(Timestamp- 8*60*60000000)) = '2013-05-16' AND sensortype != 'pressure' AND sensortype != 'altitude' AND room != 'None' GROUP EACH BY time, room, sensortype) aJOIN EACH ( SELECT TIME(USEC_TO_TIMESTAMP(INTEGER(Timestamp / 60000000) * 60000000)) time, AVG(data) data, room, sensortype FROM [io_sensor_data.moscone_io13] WHERE DATE(USEC_TO_TIMESTAMP(Timestamp- 8*60*60000000)) = '2013-05-16' AND sensortype != 'pressure' AND sensortype != 'altitude' AND room != 'None' GROUP EACH BY time, room, sensortype) b ON a.time=b.time AND a.room = b.room WHERE a.sensortype > b.sensortype GROUP EACH BY room, a_sensortype, b_sensortypeHAVING corr IS NOT NULL AND c > 800 ORDER EACH BY corr DESC
Use promo code NEXT1720 to save $300 off general admission
+(1).png)
.png)
.png)
.png)
.png)
Follow