淘宝客网站开发定制,网站备案查询 站长的怎么实现,国内开源代码网站,wordpress 家庭照片BigQuery中的复杂SQL查询
背景与目标 在数据分析中#xff0c;我们通常需要从多个数据源中获取信息#xff0c;以便进行深入的分析。这时#xff0c;BigQuery提供的JOIN、UNION和子查询等复杂SQL语句非常实用。本文将以Google BigQuery的公共数据集为例#xff0c;介绍如何…BigQuery中的复杂SQL查询
背景与目标 在数据分析中我们通常需要从多个数据源中获取信息以便进行深入的分析。这时BigQuery提供的JOIN、UNION和子查询等复杂SQL语句非常实用。本文将以Google BigQuery的公共数据集为例介绍如何使用这些高级SQL操作并展示具体的使用场景如从人口统计数据和城市服务请求中获取洞察。 1. JOIN操作整合多表信息
在多表分析中JOIN用于合并相关的表数据。例如假设我们希望查看2000至2010年间美国常见的女性名字及旧金山的311服务请求类型。这个分析可以帮助我们在城市服务和人口统计之间发现潜在的联系。 SELECTnames.name AS popular_name,names.year AS year,requests.category AS service_request,requests.created_date AS request_date
FROMbigquery-public-data.usa_names.usa_1910_current AS names
JOINbigquery-public-data.san_francisco.311_service_requests AS requests
ONnames.year EXTRACT(YEAR FROM requests.created_date)
WHEREnames.gender FAND names.year BETWEEN 2000 AND 2010
LIMIT 100;查询结果示例
popular_name year service_request
Emma 2008 311 External Request
Abigail 2008 311 External Request
Ava 2008 311 External Request
Sophia 2008 311 External Request
Isabella 2008 311 External Request
...2. UNION操作合并多个数据源
UNION操作适用于字段结构相似的多表合并例如合并不同城市的311服务请求。
SELECT San Francisco AS city,category as request_type,created_date as requested_date
FROMbigquery-public-data.san_francisco.311_service_requests
UNION ALL
SELECT New York AS city,complaint_type AS request_type,created_date AS requested_date
FROMbigquery-public-data.new_york_311.311_service_requests;此查询将旧金山和纽约的311服务请求整合在一个表中使我们可以在一个表中查看两地的公共服务需求数据。 3. 子查询嵌套查询实现高级筛选
子查询用于从一个查询的结果中进一步筛选或聚合数据。例如我们希望在旧金山市2019年最常见的五个服务请求中找到每个请求类型的平均处理时间。
SELECT main.category,AVG(main.request_duration) AS avg_duration
FROM (SELECT category,TIMESTAMP_DIFF(closed_date, created_date, MINUTE) AS request_durationFROM bigquery-public-data.san_francisco.311_service_requestsWHERE EXTRACT(YEAR FROM created_date) 2016
) AS main
GROUP BY main.category
ORDER BY avg_duration DESC
LIMIT 5查询结果示例
category avg_duration
SFHA Requests 696681.24735376
General Request - HUMAN RESOURCES 562258.918918919
General Request - MEDICAL EXAMINER 561643.0
General Request - CONVENTION FACILITIES 546616.0
General Request - STATUS OF WOMEN 532976.0解释
内部查询从旧金山的311服务请求数据中提取2016年的请求类型和每个请求的处理时间。外部查询通过聚合函数AVG计算每种服务类型的平均处理时间并按时间排序。 总结
BigQuery提供了强大的JOIN、UNION和子查询操作帮助我们更灵活地整合和分析多来源数据。这些操作在业务分析和数据仓库管理中非常实用通过合理应用这些SQL操作可以有效提高数据分析的深度和效率。
