基于多列条件统计归属不同类型组合的Homes去重计数方案

需求背景 #

基于月度-家庭-类型的明细数据，按月度分组统计：

• 仅归属单个Type的唯一Homes数量
• 仅归属任意两个Type组合的唯一Homes数量

样例明细数据 #

Month   Homes   Type
Jul     HH1     Cat1
Jul     HH2     Cat2
Jul     HH2     Cat3
Jul     HH3     Cat2
Jul     HH4     Cat3
Jul     HH5     Cat2
Jul     HH6     Cat2
Jul     HH6     Cat3
Jul     HH7     Cat2
Jul     HH8     Cat3
Jul     HH9     Cat1

实现方案 #

1. SQL实现（支持Hive/Spark SQL/MySQL 8.0+） #

核心思路：先按Month+Homes分组生成每个家庭的唯一Type组合标识，再按组合标识分组计数后行转列输出。

WITH home_type_group AS (
    SELECT 
        Month,
        Homes,
        -- 排序后拼接Type避免同组合生成不同标识
        GROUP_CONCAT(DISTINCT Type ORDER BY Type SEPARATOR '+') AS type_combination
    FROM your_table_name
    GROUP BY Month, Homes
    -- 过滤仅保留单Type和双Type组合
    HAVING COUNT(DISTINCT Type) <=2
)
SELECT
    Month,
    COUNT(CASE WHEN type_combination = 'Cat1' THEN 1 END) AS Cat1,
    COUNT(CASE WHEN type_combination = 'Cat2' THEN 1 END) AS Cat2,
    COUNT(CASE WHEN type_combination = 'Cat3' THEN 1 END) AS Cat3,
    COUNT(CASE WHEN type_combination = 'Cat1+Cat2' THEN 1 END) AS `Cat1+Cat2`,
    COUNT(CASE WHEN type_combination = 'Cat2+Cat3' THEN 1 END) AS `Cat2+Cat3`,
    COUNT(CASE WHEN type_combination = 'Cat1+Cat3' THEN 1 END) AS `Cat3+Cat1`
FROM home_type_group
GROUP BY Month;

2. Python Pandas实现 #

import pandas as pd

# 读取明细数据
df = pd.read_csv('your_data_path.csv', sep='\s+')

# 生成每个家庭的Type组合标识
home_group = df.groupby(['Month', 'Homes'])['Type'].apply(
    lambda x: '+'.join(sorted(x.unique()))
).reset_index(name='type_combination')

# 过滤仅保留单/双Type组合
home_group = home_group[home_group['type_combination'].str.split('+').str.len() <=2]

# 统计各组合数量并输出宽表
result = pd.crosstab(home_group['Month'], home_group['type_combination']).reset_index()
# 补全缺失的组合列，对齐输出格式
expected_cols = ['Month', 'Cat1', 'Cat2', 'Cat3', 'Cat1+Cat2', 'Cat2+Cat3', 'Cat3+Cat1']
result = result.reindex(columns=expected_cols, fill_value=0)

输出结果 #

Month   Cat1    Cat2    Cat3    Cat1+Cat2   Cat2+Cat3   Cat3+Cat1
Jul       2       3      2           0          2           0

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