Google Cloud/Weather Data in BigQery - Revision history

Admin: /* Breakdown */

2018-01-08T23:23:57Z

Breakdown

@@ Line 293: / Line 293: @@
    ABS(corr_pct) > 0.5 AND
    data_count > 5
 ORDER BY
    ABS(corr_pct) DESC

Admin: /* Breakdown */

2018-01-08T23:22:57Z

Breakdown

← Older revision		Revision as of 23:22, 8 January 2018
Line 286:		Line 286:
	From (		From (

	< ~~Insert~~ query ~~from above here~~ >		<this is where the prior query goes>

	GROUP BY descriptor		GROUP BY descriptor

Admin: /* Breakdown */

2018-01-08T23:22:33Z

Breakdown

@@ Line 239: / Line 239: @@
        FROM `bigquery-public-data.new_york.311_service_requests`
        GROUP BY date
 </pre>
@@ Line 255: / Line 257: @@
      temperature
    FROM
-     demos.nyc_weather) a
+     demos.nyc_weather)a
    JOIN (
@@ Line 262: / Line 264: @@
      )y
    ON x.date=y.date
+)b
 </pre>

Admin: /* Breakdown */

2018-01-08T23:16:45Z

Breakdown

← Older revision		Revision as of 23:16, 8 January 2018
Line 262:		Line 262:
	)y		)y
	ON x.date=y.date		ON x.date=y.date
	)b		)
	ON
	~~a.date = b.date~~
	</pre>		</pre>

Admin: /* Breakdown */

2018-01-08T23:16:01Z

Breakdown

@@ Line 222: / Line 222: @@
 <pre>
    SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count
    FROM
@@ Line 240: / Line 239: @@
        FROM `bigquery-public-data.new_york.311_service_requests`
        GROUP BY date
 </pre>
@@ Line 249: / Line 246: @@
 Lastly, these quantities are joined on the date field, which we expect to be exactly the same between the two.
 =Flags=

Admin: /* Step 2: Cross-Reference Weather and 311 Results */

2018-01-08T23:12:19Z

Step 2: Cross-Reference Weather and 311 Results

← Older revision		Revision as of 23:12, 8 January 2018
Line 215:		Line 215:
	</pre>		</pre>

	Keep in mind this is a complicated SQL query.		Keep in mind this is a complicated SQL query, so we'll break it down in parts.

			===Breakdown===

	Let's start with the innermost query, which is a join of two queries:		Let's start with the innermost query, which is a join of two queries:
Line 245:		Line 247:

	The second SELECT query is getting the date of each complaint (for ALL complaints), and counting all complaints by date. This gives a total number of complaints on each day, which gives a ''relative'' measure of how many 311 calls were a particular type of complaint.		The second SELECT query is getting the date of each complaint (for ALL complaints), and counting all complaints by date. This gives a total number of complaints on each day, which gives a ''relative'' measure of how many 311 calls were a particular type of complaint.

			Lastly, these quantities are joined on the date field, which we expect to be exactly the same between the two.

	=Flags=		=Flags=

Admin: /* Step 1: Construct Weather Data Table */

2018-01-08T23:11:31Z

Step 1: Construct Weather Data Table

← Older revision		Revision as of 23:11, 8 January 2018
Line 146:		Line 146:

	Now we can cross-reference this from 311 results, and do so more easily than before.		Now we can cross-reference this from 311 results, and do so more easily than before.

			===Step 2: Cross-Reference Weather and 311 Results===

			The final step is the hardest - we have a long query to run. Let's review what the query should do first.

			We already have a list of timestamps and the corresponding weather, so we want to extract the timestamp from 311 calls (in the same format), group them by category, count them by unique days, and look up weather data for each of those groups.

			This will give us a bucket of two-tuples: (weather data, number of complaints).

			These two-tuples are then plotted and fitted to give a model of number of complaints vs. weather feature.

			On to the full query:

			<pre>
			SELECT
			descriptor,
			sum(complaint_count) as total_complaint_count,
			count(temperature) as data_count,
			ROUND(corr(temperature, avg_count),3) AS corr_count,
			ROUND(corr(temperature, avg_pct_count),3) AS corr_pct
			From (
			SELECT
			avg(pct_count) as avg_pct_count,
			avg(day_count) as avg_count,
			sum(day_count) as complaint_count,
			descriptor,
			temperature
			FROM (
			SELECT
			DATE(timestamp) AS date,
			temperature
			FROM
			demos.nyc_weather) a
			JOIN (
			SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count
			FROM
			(SELECT
			DATE(created_date) AS date,
			concat(complaint_type, ": ", descriptor) as descriptor,
			COUNT(*) AS day_count
			FROM
			`bigquery-public-data.new_york.311_service_requests`
			GROUP BY
			date,
			descriptor)x
			JOIN (
			SELECT
			DATE(created_date) AS date,
			COUNT(*) AS all_calls_count
			FROM `bigquery-public-data.new_york.311_service_requests`
			GROUP BY date
			)y
			ON x.date=y.date
			)b
			ON
			a.date = b.date
			GROUP BY
			descriptor,
			temperature
			)
			GROUP BY descriptor
			HAVING
			total_complaint_count > 5000 AND
			ABS(corr_pct) > 0.5 AND
			data_count > 5
			ORDER BY
			ABS(corr_pct) DESC
			</pre>

			Keep in mind this is a complicated SQL query.

			Let's start with the innermost query, which is a join of two queries:

			<pre>
			JOIN (
			SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count
			FROM
			(SELECT
			DATE(created_date) AS date,
			concat(complaint_type, ": ", descriptor) as descriptor,
			COUNT(*) AS day_count
			FROM
			`bigquery-public-data.new_york.311_service_requests`
			GROUP BY
			date,
			descriptor)x
			JOIN (
			SELECT
			DATE(created_date) AS date,
			COUNT(*) AS all_calls_count
			FROM `bigquery-public-data.new_york.311_service_requests`
			GROUP BY date
			)y
			ON x.date=y.date
			</pre>

			The first SELECT query is getting the date of each complaint matching a certain type of complaint, grouping them by day, and counting complaints of that type on a given date.

			The second SELECT query is getting the date of each complaint (for ALL complaints), and counting all complaints by date. This gives a total number of complaints on each day, which gives a ''relative'' measure of how many 311 calls were a particular type of complaint.

	=Flags=		=Flags=

Admin: /* Searching for Correlations */

2018-01-08T23:03:25Z

Searching for Correlations

← Older revision		Revision as of 23:03, 8 January 2018
Line 104:		Line 104:

	==Searching for Correlations==		==Searching for Correlations==

			For the second part of the lab, create a new table to store results.

			Click the triangular drop down arrow next to project name, and pick "Create new dataset".

			Click "Compose New Query". Can call it nyc_weather or something.

			===Step 1: Construct Weather Data Table===

			Step 1 will be constructing a lookup table of dates and weather characteristics. We will construct a query of the NOAA weather data, and the results of this query will populate the table nyc_weather (through the magic of BigQuery).

			<pre>
			SELECT
			-- Create a timestamp from the date components.
			timestamp(concat(year,"-",mo,"-",da)) as timestamp,
			-- Replace numerical null values with actual nulls
			AVG(IF (temp=9999.9, null, temp)) AS temperature,
			AVG(IF (visib=999.9, null, visib)) AS visibility,
			AVG(IF (wdsp="999.9", null, CAST(wdsp AS Float64))) AS wind_speed,
			AVG(IF (gust=999.9, null, gust)) AS wind_gust,
			AVG(IF (prcp=99.99, null, prcp)) AS precipitation,
			AVG(IF (sndp=999.9, null, sndp)) AS snow_depth

			FROM
			`bigquery-public-data.noaa_gsod.gsod20*`

			WHERE
			CAST(YEAR AS INT64) > 2008
			AND (stn="725030" OR -- La Guardia
			stn="744860") -- JFK
			GROUP BY timestamp
			</pre>

			Again, we're looking at the two airport weather stations.

			* The SELECT statement extracts the year, month, and day as our timestamp. It also asks for the average of several wind, precipitation, and temperature variables over observations for that day.
			* The FROM statement just indicates we are looking at weather data
			* The WHERE forces the query to look at data more recent than 2008 and from JFK or LaGuardia airport weather stations only.

			Now the nyc_weather table has a more convenient intermediate query result, which is a list of timestamps and the corresponding weather that day.

			Now we can cross-reference this from 311 results, and do so more easily than before.

	=Flags=		=Flags=

Admin: /* Flags */

2018-01-08T22:56:46Z

Flags

← Older revision		Revision as of 22:56, 8 January 2018
Line 105:		Line 105:
	==Searching for Correlations==		==Searching for Correlations==

	==Flags==		=Flags=

	[[Category:Google Cloud]]		[[Category:Google Cloud]]

Admin: /* Flags */

2018-01-08T22:56:28Z

Flags

← Older revision		Revision as of 22:56, 8 January 2018
Line 105:		Line 105:
	==Searching for Correlations==		==Searching for Correlations==

	=Flags=		==Flags==

	[[Category:Google Cloud]]		[[Category:Google Cloud]]