使用BigQuery与Standard SQL按日统计p_id出现次数并关联名称

嘿，这个需求很清晰，我给你准备了两种常见场景的BigQuery SQL实现方案，你可以根据实际需求来选：

场景1：按日期统计每个p_id的每日出现次数 #

如果你需要看每个日期下各个p_id的出现次数，同时带上q_id对应的名称，用GROUP BY结合表关联就能搞定：

-- 替换成你实际的项目、数据集和表名
CREATE OR REPLACE TABLE `your_project.your_dataset.daily_pid_count` AS
SELECT
  mt.p_time,
  mt.p_id,
  qnt.q_name, -- 关联过来的q_id对应名称
  COUNT(*) AS occurrence_count -- p_id当日的出现次数
FROM
  `your_project.your_dataset.main_table` mt -- 你的主表（包含p_time、p_id、q_id）
LEFT JOIN
  `your_project.your_dataset.q_name_table` qnt -- 存储q_id和对应名称的关联表
ON
  mt.q_id = qnt.q_id -- 关联条件：主表和关联表的q_id匹配
GROUP BY
  mt.p_time, mt.p_id, qnt.q_name -- 按日期、p_id、名称分组统计
ORDER BY
  mt.p_time DESC, occurrence_count DESC; -- 按日期倒序、次数倒序排列结果

场景2：统计每个p_id的全局总出现次数（不按日期拆分） #

如果只需要每个p_id在所有日期的总出现次数，去掉日期分组即可：

CREATE OR REPLACE TABLE `your_project.your_dataset.total_pid_count` AS
SELECT
  mt.p_id,
  qnt.q_name,
  COUNT(*) AS total_occurrence_count -- p_id的全局总出现次数
FROM
  `your_project.your_dataset.main_table` mt
LEFT JOIN
  `your_project.your_dataset.q_name_table` qnt
ON
  mt.q_id = qnt.q_id
GROUP BY
  mt.p_id, qnt.q_name
ORDER BY
  total_occurrence_count DESC;

几个关键细节提醒： #

• 用CREATE OR REPLACE TABLE可以直接生成你需要的结果表，记得把代码里的项目、数据集、表名替换成你自己的
• 这里用了LEFT JOIN，确保即使某个q_id在关联表中没有匹配的名称，主表的记录也会被保留；如果只需要保留有对应名称的记录，可以换成INNER JOIN
• 如果你的表结构和我假设的有差异（比如主表中p_id的字段名不对，或者关联表的名称字段叫name而不是q_name），只需要调整对应的字段名称就行

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