基于BigQuery分析Google Play分区表中单个应用多维度数据的指引请求

一、表关联核心思路 #

先梳理所有Google Play导出表的字段清单，找出共享的通用字段（比如Date、Package_Name、Device、App_Version等），这些字段就是跨表关联的核心依据。比如你手头的崩溃维度表（含国家、OS、语言信息），必然和p_Crashes_device_PS共享至少1-2个以上的关联键，以此实现数据合并。

二、BigQuery SQL关联实操示例 #

1. 定位高崩溃设备的细分维度 #

假设存在一张包含崩溃全维度信息的表p_Crashes_full_details_PS（字段含Date、Package_Name、Device、OS_Version、Country_Code、Language、Daily_Crashes），可以用以下SQL关联查询，精准定位问题：

WITH top_crash_device AS (
  -- 先筛选出崩溃量最高的设备品牌
  SELECT
    REGEXP_EXTRACT(Device, r'^(\w+)') AS Device_Brand,
    SUM(Daily_Crashes) AS Total_Crashes
  FROM
    `abc.google_playstore.p_Crashes_device_PS`
  GROUP BY
    Device_Brand
  ORDER BY
    Total_Crashes DESC
  LIMIT 1
)
SELECT
  EXTRACT(YEAR FROM cd.Date) AS Year,
  EXTRACT(MONTH FROM cd.Date) AS Month,
  tcd.Device_Brand,
  cd.OS_Version,
  cd.Country_Code,
  cd.Language,
  SUM(cd.Daily_Crashes) AS Monthly_Crashes,
  SUM(cd.Daily_ANRs) AS Monthly_ANRs
FROM
  top_crash_device tcd
JOIN
  `abc.google_playstore.p_Crashes_full_details_PS` cd
ON
  tcd.Device_Brand = REGEXP_EXTRACT(cd.Device, r'^(\w+)')
GROUP BY
  Year, Month, Device_Brand, OS_Version, Country_Code, Language
ORDER BY
  Monthly_Crashes DESC;

2. 交叉关联评分、安装量表做综合分析 #

如果要结合用户评分、安装量看高崩溃设备的影响范围，可以继续关联评分、安装量表：

WITH top_crash_device AS (
  SELECT
    REGEXP_EXTRACT(Device, r'^(\w+)') AS Device_Brand,
    SUM(Daily_Crashes) AS Total_Crashes
  FROM
    `abc.google_playstore.p_Crashes_device_PS`
  GROUP BY
    Device_Brand
  ORDER BY
    Total_Crashes DESC
  LIMIT 1
)
SELECT
  cd.Country_Code,
  AVG(r.Rating) AS Avg_Country_Rating,
  SUM(i.Daily_Installs) AS Total_Country_Installs,
  SUM(cd.Daily_Crashes) AS Total_Country_Crashes
FROM
  top_crash_device tcd
JOIN
  `abc.google_playstore.p_Crashes_full_details_PS` cd
  ON tcd.Device_Brand = REGEXP_EXTRACT(cd.Device, r'^(\w+)')
JOIN
  `abc.google_playstore.p_Ratings_by_country_PS` r
  ON cd.Country_Code = r.Country_Code AND EXTRACT(YEAR FROM cd.Date) = EXTRACT(YEAR FROM r.Date)
JOIN
  `abc.google_playstore.p_Installs_by_country_PS` i
  ON cd.Country_Code = i.Country_Code AND EXTRACT(YEAR FROM cd.Date) = EXTRACT(YEAR FROM i.Date)
GROUP BY
  cd.Country_Code
ORDER BY
  Total_Country_Crashes DESC;

三、Python分析进阶方向 #

如果需要更灵活的可视化、复杂统计分析，可以用Python连接BigQuery拉取数据后处理：

1. 拉取关联数据到本地 #

from google.cloud import bigquery
import pandas as pd

# 初始化BigQuery客户端
client = bigquery.Client(project="your-project-id")

# 定义查询语句（复用上面的SQL）
query = """
WITH top_crash_device AS (
  SELECT
    REGEXP_EXTRACT(Device, r'^(\w+)') AS Device_Brand,
    SUM(Daily_Crashes) AS Total_Crashes
  FROM
    `abc.google_playstore.p_Crashes_device_PS`
  GROUP BY
    Device_Brand
  ORDER BY
    Total_Crashes DESC
  LIMIT 1
)
SELECT
  EXTRACT(YEAR FROM cd.Date) AS Year,
  EXTRACT(MONTH FROM cd.Date) AS Month,
  tcd.Device_Brand,
  cd.OS_Version,
  cd.Country_Code,
  cd.Language,
  SUM(cd.Daily_Crashes) AS Monthly_Crashes
FROM
  top_crash_device tcd
JOIN
  `abc.google_playstore.p_Crashes_full_details_PS` cd
ON
  tcd.Device_Brand = REGEXP_EXTRACT(cd.Device, r'^(\w+)')
GROUP BY
  Year, Month, Device_Brand, OS_Version, Country_Code, Language
"""

# 执行查询并转为DataFrame
crash_df = client.query(query).to_dataframe()

2. 可视化维度分布 #

用Seaborn/Matplotlib快速生成可视化，直观定位问题：

import seaborn as sns
import matplotlib.pyplot as plt

# 按OS版本统计崩溃量
os_crash_dist = crash_df.groupby("OS_Version")["Monthly_Crashes"].sum().reset_index()

# 绘制柱状图
plt.figure(figsize=(12,6))
sns.barplot(x="OS_Version", y="Monthly_Crashes", data=os_crash_dist)
plt.title(f"{crash_df['Device_Brand'][0]}设备各OS版本崩溃量分布")
plt.xticks(rotation=45)
plt.show()

四、关键注意事项 #

• 先整理所有表的字段清单，标记出重复字段，这些是关联的核心；如果字段命名不一致（比如Device_Model vs Device），用正则或字符串截取做字段对齐。
• 处理大表时，优先在BigQuery中用CTE做预聚合，减少拉取到本地的数据量，提升效率。
• 若某维度数据分散在多张表中，可以逐步关联，先合并核心崩溃表和维度表，再加入评分、安装量表。

内容的提问来源于stack exchange，提问作者sdave