【Kaggle学习笔记】 | Intro to SQL_kaggle使用sql-CSDN博客

本文链接：https://blog.csdn.net/Continuing_Road/article/details/107376384

BigQuery

BigQuery是一个将SQL查询应用于大型数据集的网页服务

from google.cloud import bigquery 
client = bigquery.Client() # 创建一个bigquery对象

# 访问数据集步骤：
# 1.用dataset()方法构造对数据集的引用（API）；
# 2.用get_dataset()方法得到数据集
dataset_ref = client.dataset("hacker_news", project="bigquery-public-data") # 黑客新闻数据集
dataset = client.get_dataset(dataset_ref)

# 用list_tables()方法得到数据集里的表格目录
tables = list(client.list_tables(dataset))
for table in tables:  
    print(table.table_id)

# 用table()、get_table()方法访问该数据集里的full表格，与访问数据集类似
table_ref = dataset_ref.table("full")
table = client.get_table(table_ref)

table.schema # 得到该表格下每列的信息（标签、数据类型、是否允许缺失值、描述等）
client.list_rows(table, max_results=5).to_dataframe() # 将表格前5行数据以dataframe格式输出
client.list_rows(table, selected_fields=table.schema[:1], max_results=5).to_dataframe() # 看前两列数据

数据集操作

SQL查询：

SELECT：要查询的数据列名

FROM：选择待选择的数据集，格式为bigquery-public-data.+数据集名称（注意FROM后的符号）

WHERE：匹配条件

# 用query设置查询在global_air_quality数据集中国家在美国的城市的所有值
query = """
        SELECT city
        FROM `bigquery-public-data.openaq.global_air_quality`
        WHERE country = 'US'
        """
# 得到查询结果并转化为dataframe格式
client = bigquery.Client()
query_job = client.query(query)
us_cities = query_job.to_dataframe()
us_cities.city.value_counts().head()

# kaggle上避免一次性扫描过多数据的查询方法，适用于大型数据
query = """
        SELECT score, title
        FROM `bigquery-public-data.hacker_news.full`
        WHERE type = "job" 
        """

# 先了解查询后的数据集大小
dry_run_config = bigquery.QueryJobConfig(dry_run=True)
dry_run_query_job = client.query(query, job_config=dry_run_config)
print("This query will process {} bytes.".format(dry_run_query_job.total_bytes_processed))

# 设定一个较大的查询大小，开始计算查询所得数据集的score的平均值
ONE_GB = 1000*1000*1000
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=ONE_GB)
safe_query_job = client.query(query, job_config=safe_config)
job_post_scores = safe_query_job.to_dataframe()
job_post_scores.score.mean()

COUNT：得到的结果中加多一列，返回数值的个数（还有求平均值AVG等函数）

GROUP BY：设置一列，将该列中所有相等的值视为一组

HAVING：和GROUP BY一起使用，只返回符合条件得组别

# 以parent列为组别分类标准，id为统计量，返回id数量大于10的parent组
query_popular = """
                SELECT parent, COUNT(id)
                FROM `bigquery-public-data.hacker_news.comments`
                GROUP BY parent
                HAVING COUNT(id) > 10
                """

# AS NumPosts将count数值返回的列名定为NumPosts，count(1)指第二列，也即属性值
query_improved = """
                 SELECT parent, COUNT(1) AS NumPosts
                 FROM `bigquery-public-data.hacker_news.comments`
                 GROUP BY parent
                 HAVING COUNT(1) > 10
                 
# 后续显示的操作跟上述一样

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YPZxGzUV-1594864035685)(F:\大学\课外\AI\笔记\Kaggle笔记\Intro to SQL\微信截图_20200415101909.png)] [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DZ69mo0C-1594864035688)(F:\大学\课外\AI\笔记\Kaggle笔记\Intro to SQL\微信截图_20200415101922.png)]

ORDER BY：用来对数据进行排序，默认降序，可用DESC参数进行改变

EXTRACT：在SELECT类使用，用于改变时间的显示

# 以day_of_week列（从原来数据的timestamp_of_crash列换成每周的星期几而得）为组别分类标准，id为统计量，返回降序排列的统计结果（事故发生总数）
query = """
        SELECT COUNT(consecutive_number) AS num_accidents, 
               EXTRACT(DAYOFWEEK FROM timestamp_of_crash) AS day_of_week
        FROM `bigquery-public-data.nhtsa_traffic_fatalities.accident_2015`
        GROUP BY day_of_week
        ORDER BY num_accidents DESC
        """

# 后续显示的操作跟上述一样

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M1RdtPqp-1594864035691)(F:\大学\课外\AI\笔记\Kaggle笔记\Intro to SQL\微信截图_20200416092224.png)] [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0QkTRDKl-1594864035696)(F:\大学\课外\AI\笔记\Kaggle笔记\Intro to SQL\微信截图_20200416090832.png)]

AS：为列标签命名

WITH AS：只选取数据集的一部分数据作为来源，即只选取数据集中的需要关注的几列数据

speeds_query = """
               WITH RelevantRides AS
               (
                   SELECT EXTRACT(HOUR FROM trip_start_timestamp) AS hour_of_day, 
                          trip_miles, 
                          trip_seconds
                   FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                   WHERE trip_start_timestamp > '2017-01-01' AND 
                         trip_start_timestamp < '2017-07-01' AND 
                         trip_seconds > 0 AND 
                         trip_miles > 0
               )
               SELECT hour_of_day,
                      COUNT(1) AS num_trips,
                      3600 * SUM(trip_miles) / SUM(trip_seconds) AS avg_mph
               FROM RelevantRides
               GROUP BY hour_of_day
               ORDER BY hour_of_day
               """

# 后续显示的操作跟上述一样

多个数据集数据的合并

JOIN：在一个query查询中合并两个数据集或两个表格的数据，比较常用的是INNER JOIN

ON：不同表格里的数据匹配的标准

# 将github_repos数据集里的sample_files和licenses两个表格里的数据合并在一个查询里
query = """
        SELECT L.license, COUNT(1) AS number_of_files
        FROM `bigquery-public-data.github_repos.sample_files` AS sf
        INNER JOIN `bigquery-public-data.github_repos.licenses` AS L 
            ON sf.repo_name = L.repo_name 
        GROUP BY L.license
        ORDER BY number_of_files DESC
        """

# 后续显示的操作跟上述一样

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0AWNkr0k-1594864035700)(F:\大学\课外\AI\笔记\Kaggle笔记\Intro to SQL\微信截图_20200416181648.png)]

文本数据操作

LIKE：在WHERE语句中使用，用于匹配含有特定字符格式的文本

# 返回Name字段中含有ipl的数据
query = """
        SELECT * 
        FROM `bigquery-public-data.pet_records.pets` 
        WHERE Name LIKE '%ipl%'
        """

字符格式的文本

# 返回Name字段中含有ipl的数据
query = """
        SELECT * 
        FROM `bigquery-public-data.pet_records.pets` 
        WHERE Name LIKE '%ipl%'
        """