咨询：如何用单SQL查询实现按设备分类的周度用户活跃度统计

针对设备数量过多无法手动枚举的场景，以下是几种单条SQL实现的方案：

方法1：使用设备分类映射表（推荐，可扩展） #

这是最规范的做法，先建立一个设备与分类的映射表，后续只需维护这个表即可，无需修改统计SQL。

步骤1：创建映射表 #

CREATE TABLE device_categories (
    device VARCHAR(100) PRIMARY KEY,
    category VARCHAR(50) NOT NULL
);

-- 批量导入设备分类数据（可通过文件导入或批量INSERT，无需手动逐条输入）
INSERT INTO device_categories (device, category)
VALUES 
('macbook pro', 'computer'),
('iphone 5s', 'phone'),
('ipad mini', 'tablet'),
-- 其他所有设备分类映射...
;

步骤2：关联查询统计 #

SELECT 
    WEEK(occurred_at) AS weeknum,
    COUNT(DISTINCT e.user_id) AS weekly_users,
    COUNT(DISTINCT CASE WHEN dc.category = 'computer' THEN e.user_id END) AS computer,
    COUNT(DISTINCT CASE WHEN dc.category = 'phone' THEN e.user_id END) AS phone,
    COUNT(DISTINCT CASE WHEN dc.category = 'tablet' THEN e.user_id END) AS tablet
FROM events1 e
JOIN device_categories dc ON e.device = dc.device
WHERE e.event_type = 'engagement' AND e.event_name = 'login'
GROUP BY weeknum
ORDER BY weeknum;

如果需要按分类行展示而非列展示，可改用以下查询：

SELECT 
    WEEK(occurred_at) AS weeknum,
    dc.category,
    COUNT(DISTINCT e.user_id) AS active_users
FROM events1 e
JOIN device_categories dc ON e.device = dc.device
WHERE e.event_type = 'engagement' AND e.event_name = 'login'
GROUP BY weeknum, dc.category
ORDER BY weeknum, dc.category;

方法2：使用字符串模式匹配（无需额外表，精度稍低） #

若无法创建映射表，可通过设备名称的关键词匹配自动归类，无需手动枚举所有设备：

SELECT 
    WEEK(occurred_at) AS weeknum,
    COUNT(DISTINCT user_id) AS weekly_users,
    COUNT(DISTINCT CASE WHEN device LIKE '%notebook%' OR device LIKE '%desktop%' OR device LIKE '%chromebook%' OR device LIKE '%surface%' THEN user_id END) AS computer,
    COUNT(DISTINCT CASE WHEN device LIKE '%phone%' THEN user_id END) AS phone,
    COUNT(DISTINCT CASE WHEN device LIKE '%tablet%' OR device LIKE '%ipad%' OR device LIKE '%kindle fire%' OR device LIKE '%nexus 7%' OR device LIKE '%nexus 10%' THEN user_id END) AS tablet
FROM events1 
WHERE event_type = 'engagement' AND event_name = 'login'
GROUP BY weeknum
ORDER BY weeknum;

注意：此方法依赖设备名称的关键词一致性，可能存在少量误分类情况。

方法3：按单个设备统计（无需合并大类） #

如果不需要合并为电脑/手机/平板这类大类，直接按每个设备统计周活跃用户：

SELECT 
    WEEK(occurred_at) AS weeknum,
    device,
    COUNT(DISTINCT user_id) AS active_users
FROM events1 
WHERE event_type = 'engagement' AND event_name = 'login'
GROUP BY weeknum, device
ORDER BY weeknum, active_users DESC;

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