Metabase中按当前月份动态筛选列实现日期透视表需求

先明确你的数据表结构（整理后）：

原SQL的问题在于静态定义了所有12个月的列，不管当前月份都会全部展示，而且GROUP BY里包含amount是错误的（聚合列不能出现在GROUP BY中，应该只保留id和type_id）。下面给两种实用优化方案：

方案一：用Metabase内置透视表可视化（推荐，无需复杂SQL） #

这是最简便的方法，利用Metabase的可视化能力实现动态列过滤：

1. 先写基础数据查询，提取当前年份的有效数据：

SELECT 
  id,
  type_id,
  AMOUNT,
  -- 把格式日期转成可识别的周期名称
  CASE 
    WHEN LEFT(format_Date, 2) = '13' THEN 'year_amount'
    ELSE TO_CHAR(TO_DATE(LEFT(format_Date, 2), 'MM'), 'FMMonth')
  END AS period,
  -- 提取年份用于过滤
  RIGHT(format_Date, 4) AS data_year
FROM data_pivoted
-- 只保留当前年份的数据
WHERE data_year = TO_CHAR(CURRENT_DATE, 'YYYY')

2. 保存这个查询后，在Metabase中选择透视表可视化：
   • 行维度：添加id和type_id
   • 列维度：添加period
   • 值：选择AMOUNT，聚合方式选Max（因为每个id+type_id+period唯一对应一条数据）
3. 过滤列：在透视表的列设置中，添加过滤规则：
   • 对于月度period，只保留EXTRACT(MONTH FROM TO_DATE(period, 'FMMonth')) <= EXTRACT(MONTH FROM CURRENT_DATE)的列
   • 保留year_amount列

这样就能自动根据当前月份，只显示截至当月的月度列和年度总金额列。

方案二：动态SQL实现（适合必须用SQL直接输出结果的场景） #

如果需要直接用SQL输出动态列，得依赖数据库的动态SQL支持（比如PostgreSQL、MySQL），结合Metabase的变量功能：

以PostgreSQL为例，编写动态生成SELECT子句的查询：

WITH current_info AS (
  SELECT
    EXTRACT(YEAR FROM CURRENT_DATE)::TEXT AS curr_year,
    EXTRACT(MONTH FROM CURRENT_DATE)::INT AS curr_month
),
month_clauses AS (
  SELECT
    -- 生成每个月份的CASE语句
    'MAX(CASE WHEN LEFT(format_Date, 2) = ''' || LPAD(m.month::TEXT, 2, '0') || ''' THEN AMOUNT ELSE 0 END) AS ' || TO_CHAR(TO_DATE(m.month::TEXT, 'MM'), 'FMMonth') AS clause
  FROM generate_series(1, (SELECT curr_month FROM current_info)) AS m(month)
)
SELECT
  -- 拼接所有月度列和年度总金额列
  'SELECT type_id, id, ' || string_agg(clause, ', ') || ', MAX(CASE WHEN LEFT(format_Date, 2) = ''13'' THEN AMOUNT ELSE 0 END) AS year_amount FROM data_pivoted WHERE RIGHT(format_Date, 4) = ''' || (SELECT curr_year FROM current_info) || ''' GROUP BY id, type_id' AS dynamic_sql
FROM month_clauses;

运行这个查询会生成对应的静态SQL，你可以在Metabase中用变量传递当前月份，或者用Metabase的Native Query功能执行动态SQL（需要开启相应权限）。

注意：原查询的GROUP BY id, type_id, amount是错误的，必须去掉amount，否则会出现分组错误。

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