如何在BigQuery中基于关联键统计不同外出频次用户的分类差异？

一、不同外出频次分组的Top10目的地分类统计 #

数据基础 #

现有两张关联表：

• 表1：id唯一，记录每个用户的外出频次（times_of_going_out）
• 表2：id可重复，记录用户对应的目的地分类（category_name）及所在城市

查询语句 #

以下SQL用于关联两张表，分别统计两类用户的Top10目的地分类：

WITH user_category_link AS (
    SELECT 
        t1.id,
        t1.times_of_going_out,
        t2.category_name
    FROM `your-project.your-dataset.table1` t1
    INNER JOIN `your-project.your-dataset.table2` t2 
        ON t1.id = t2.id
)
-- 外出频次<5次用户的Top10分类
SELECT 
    '<5次' AS user_segment,
    category_name,
    COUNT(DISTINCT id) AS user_num,
    RANK() OVER(ORDER BY COUNT(DISTINCT id) DESC) AS rank
FROM user_category_link
WHERE times_of_going_out < 5
GROUP BY category_name
ORDER BY user_num DESC
LIMIT 10

UNION ALL

-- 外出频次>5次用户的Top10分类
SELECT 
    '>5次' AS user_segment,
    category_name,
    COUNT(DISTINCT id) AS user_num,
    RANK() OVER(ORDER BY COUNT(DISTINCT id) DESC) AS rank
FROM user_category_link
WHERE times_of_going_out > 5
GROUP BY category_name
ORDER BY user_num DESC
LIMIT 10;

基于样本数据的结果 #

• <5次用户组：所有样本用户都属于该组，Top分类为：
  • Recreation Centers（2位用户）
  • Games & Game Supplies（1位用户）
  • Telephone Companies（1位用户）
• >5次用户组：样本数据中无符合条件的用户，无统计结果。

二、特定频次用户的常去分类 #

1. 外出2次的用户 #

查询语句：

SELECT 
    category_name,
    COUNT(DISTINCT t1.id) AS user_num
FROM `your-project.your-dataset.table1` t1
JOIN `your-project.your-dataset.table2` t2 
    ON t1.id = t2.id
WHERE times_of_going_out = 2
GROUP BY category_name;

结果：仅用户fef666外出2次，常去分类为 Recreation Centers。

2. 外出6次的用户 #

样本数据中不存在外出频次为6次的用户，因此无对应常去分类结果。

内容的提问来源于stack exchange，提问作者アルサ