Postgres中shop_dre_operational表年月分列转日期实现跨表关联方法

以下逻辑可直接在Metabase的原生查询编辑器中执行，通过列转行操作将横向存储的12个月份字段转换为纵向的年月结构，同时生成标准Date类型的关联字段：

方案1：使用Postgres数组函数批量转换（推荐） #

该方案无需重复写12段查询逻辑，执行效率更高：

SELECT
  t.year,
  t.month_name,
  t.month_value,
  -- 生成当月首日的Date类型字段，可直接用于关联其他表的日期字段
  TO_DATE(CONCAT(t.year, '-', t.month_num, '-01'), 'YYYY-MM-DD') AS report_date
FROM (
  SELECT
    year,
    -- 三个数组的元素顺序必须一一对应
    UNNEST(ARRAY['january','february','march','april','may','june','july','august','september','october','november','december']) AS month_name,
    UNNEST(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12]) AS month_num,
    UNNEST(ARRAY[january,february,march,april,may,june,july,august,september,october,november,december]) AS month_value
  FROM shop_dre_operational
) t

转换后原表中每一行（对应一整年的数据）会拆分为12行，每行对应一个年月的数值，新增的report_date为标准Date类型。

关联查询示例 #

你可以将上述转换逻辑封装为CTE（公共表表达式），直接和其他表做关联：

WITH shop_date_converted AS (
  SELECT
    t.year,
    t.month_name,
    t.month_value,
    TO_DATE(CONCAT(t.year, '-', t.month_num, '-01'), 'YYYY-MM-DD') AS report_date
  FROM (
    SELECT
      year,
      UNNEST(ARRAY['january','february','march','april','may','june','july','august','september','october','november','december']) AS month_name,
      UNNEST(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12]) AS month_num,
      UNNEST(ARRAY[january,february,march,april,may,june,july,august,september,october,november,december]) AS month_value
    FROM shop_dre_operational
  ) t
)
SELECT * 
FROM shop_date_converted s
-- 如需关联日历表的任意日期匹配当月，可使用DATE_TRUNC做月份对齐
INNER JOIN calendar_aux c 
ON DATE_TRUNC('month', c.cal_date) = s.report_date

方案2：使用UNION ALL逐行拼接（兼容性更好） #

如果对Postgres数组语法不熟悉，可以用UNION ALL逐月累加：

SELECT year, 1 AS month_num, january AS month_value, TO_DATE(CONCAT(year, '-01-01'), 'YYYY-MM-DD') AS report_date FROM shop_dre_operational
UNION ALL
SELECT year, 2 AS month_num, february AS month_value, TO_DATE(CONCAT(year, '-02-01'), 'YYYY-MM-DD') AS report_date FROM shop_dre_operational
UNION ALL
SELECT year, 3 AS month_num, march AS month_value, TO_DATE(CONCAT(year, '-03-01'), 'YYYY-MM-DD') AS report_date FROM shop_dre_operational
-- 按上述格式补全4-12月的查询逻辑即可

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