如何在Redshift中按变量组合统计并生成多列计数结果？

核心思路 #

利用Redshift支持的FILTER子句，直接为每个Var1-Var2-Var3-Var4的取值组合生成单独的计数列，无需创建二进制变量，同时结合GROUP BY实现不同维度的分组统计。

1. 按Constant1分组（示例筛选值为A） #

针对Constant1='A'的数据，统计每个Var组合的计数并列为单独字段：

SELECT
  Constant1,
  -- 替换为实际需要统计的Var1-Var4组合，每个组合对应一列
  COUNT(*) FILTER (WHERE Var1 = 'v1_1' AND Var2 = 'v2_1' AND Var3 = 'v3_1' AND Var4 = 'v4_1') AS combo_1,
  COUNT(*) FILTER (WHERE Var1 = 'v1_1' AND Var2 = 'v2_1' AND Var3 = 'v3_1' AND Var4 = 'v4_2') AS combo_2,
  COUNT(*) FILTER (WHERE Var1 = 'v1_2' AND Var2 = 'v2_2' AND Var3 = 'v3_2' AND Var4 = 'v4_2') AS combo_3,
  -- 可继续添加更多组合列
  COUNT(*) FILTER (WHERE Var1 = 'v1_n' AND Var2 = 'v2_n' AND Var3 = 'v3_n' AND Var4 = 'v4_n') AS combo_n
FROM your_table
WHERE Constant1 = 'A'
GROUP BY Constant1;

2. 按Constant2分组（示例筛选值为B1、B2） #

针对Constant2为B1、B2的数据，按Constant2分组统计各Var组合计数：

SELECT
  Constant2,
  -- 保持和上面一致的组合列定义，确保后续UNION时列结构匹配
  COUNT(*) FILTER (WHERE Var1 = 'v1_1' AND Var2 = 'v2_1' AND Var3 = 'v3_1' AND Var4 = 'v4_1') AS combo_1,
  COUNT(*) FILTER (WHERE Var1 = 'v1_1' AND Var2 = 'v2_1' AND Var3 = 'v3_1' AND Var4 = 'v4_2') AS combo_2,
  COUNT(*) FILTER (WHERE Var1 = 'v1_2' AND Var2 = 'v2_2' AND Var3 = 'v3_2' AND Var4 = 'v4_2') AS combo_3,
  COUNT(*) FILTER (WHERE Var1 = 'v1_n' AND Var2 = 'v2_n' AND Var3 = 'v3_n' AND Var4 = 'v4_n') AS combo_n
FROM your_table
WHERE Constant2 IN ('B1', 'B2')
GROUP BY Constant2;

3. UNION合并两种分组结果 #

为了区分不同分组维度的结果，新增group_type标识列，使用UNION ALL（避免不必要的去重，提升效率）合并两个查询：

-- 第一部分：Constant1=A的分组结果
SELECT
  'Constant1_A' AS group_type,
  Constant1 AS group_value,
  COUNT(*) FILTER (WHERE Var1 = 'v1_1' AND Var2 = 'v2_1' AND Var3 = 'v3_1' AND Var4 = 'v4_1') AS combo_1,
  COUNT(*) FILTER (WHERE Var1 = 'v1_1' AND Var2 = 'v2_1' AND Var3 = 'v3_1' AND Var4 = 'v4_2') AS combo_2,
  COUNT(*) FILTER (WHERE Var1 = 'v1_2' AND Var2 = 'v2_2' AND Var3 = 'v3_2' AND Var4 = 'v4_2') AS combo_3,
  COUNT(*) FILTER (WHERE Var1 = 'v1_n' AND Var2 = 'v2_n' AND Var3 = 'v3_n' AND Var4 = 'v4_n') AS combo_n
FROM your_table
WHERE Constant1 = 'A'
GROUP BY Constant1

UNION ALL

-- 第二部分：Constant2=B1/B2的分组结果
SELECT
  'Constant2_B1B2' AS group_type,
  Constant2 AS group_value,
  COUNT(*) FILTER (WHERE Var1 = 'v1_1' AND Var2 = 'v2_1' AND Var3 = 'v3_1' AND Var4 = 'v4_1') AS combo_1,
  COUNT(*) FILTER (WHERE Var1 = 'v1_1' AND Var2 = 'v2_1' AND Var3 = 'v3_1' AND Var4 = 'v4_2') AS combo_2,
  COUNT(*) FILTER (WHERE Var1 = 'v1_2' AND Var2 = 'v2_2' AND Var3 = 'v3_2' AND Var4 = 'v4_2') AS combo_3,
  COUNT(*) FILTER (WHERE Var1 = 'v1_n' AND Var2 = 'v2_n' AND Var3 = 'v3_n' AND Var4 = 'v4_n') AS combo_n
FROM your_table
WHERE Constant2 IN ('B1', 'B2')
GROUP BY Constant2;

额外提示 #

• 如果Var1-Var4的组合数量较多，可先通过以下查询获取所有唯一组合，再批量生成FILTER子句：

SELECT DISTINCT Var1, Var2, Var3, Var4 FROM your_table;

• 若需动态生成SQL，可结合Redshift存储过程或外部脚本（如Python）自动拼接查询语句。

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