在R或BigQuery中透视时按mmm_yy格式日期排序列

R 实现方案（基于tidyverse） #

核心思路是先将Month字段转换为可排序的日期格式，提取按日期排序后的月份字符串顺序，再以此顺序调整透视后的列。

library(tidyverse)

# 原始数据构造（替换为你的数据源）
df <- tibble(
  Metric = c("Customer_count", "Sum_of_Txn_Value", "Customer_count", "Sum_of_Txn_Value",
             "Customer_count", "Sum_of_Txn_Value", "Customer_count", "Sum_of_Txn_Value"),
  Month = c("Oct_22", "Oct_22", "Nov_22", "Nov_22", "Dec_22", "Dec_22", "Jan_23", "Jan_23"),
  Values = c(500, 20000, 450, 15000, 350, 12000, 250, 10000)
)

# 生成按日期排序的月份字符串列表
sorted_months <- df %>%
  mutate(month_date = lubridate::my(Month)) %>%  # 将mmm_yy转为日期格式
  arrange(month_date) %>%                        # 按日期排序
  pull(Month) %>%                                # 提取月份字符串
  unique()                                       # 去重得到唯一月份

# 透视表格并按指定顺序调整列
pivoted_df <- df %>%
  pivot_wider(names_from = Month, values_from = Values) %>%
  select(Metric, all_of(sorted_months))          # 强制按排序后的月份顺序排列列

# 查看结果
print(pivoted_df)

BigQuery 实现方案 #

静态方案（已知所有月份） #

直接在PIVOT子句中按日期顺序指定月份列：

WITH raw_data AS (
  SELECT 'Customer_count' AS Metric, 'Oct_22' AS Month, 500 AS Values UNION ALL
  SELECT 'Sum_of_Txn_Value', 'Oct_22', 20000 UNION ALL
  SELECT 'Customer_count', 'Nov_22', 450 UNION ALL
  SELECT 'Sum_of_Txn_Value', 'Nov_22', 15000 UNION ALL
  SELECT 'Customer_count', 'Dec_22', 350 UNION ALL
  SELECT 'Sum_of_Txn_Value', 'Dec_22', 12000 UNION ALL
  SELECT 'Customer_count', 'Jan_23', 250 UNION ALL
  SELECT 'Sum_of_Txn_Value', 'Jan_23', 10000
)
SELECT *
FROM raw_data
PIVOT(
  SUM(Values) FOR Month IN ('Oct_22', 'Nov_22', 'Dec_22', 'Jan_23')
)
ORDER BY Metric;

动态方案（月份不固定） #

通过动态SQL自动生成按日期排序的月份列列表，适配月份数量变化的场景：

DECLARE sorted_month_list STRING;

WITH raw_data AS (
  SELECT 'Customer_count' AS Metric, 'Oct_22' AS Month, 500 AS Values UNION ALL
  SELECT 'Sum_of_Txn_Value', 'Oct_22', 20000 UNION ALL
  SELECT 'Customer_count', 'Nov_22', 450 UNION ALL
  SELECT 'Sum_of_Txn_Value', 'Nov_22', 15000 UNION ALL
  SELECT 'Customer_count', 'Dec_22', 350 UNION ALL
  SELECT 'Sum_of_Txn_Value', 'Dec_22', 12000 UNION ALL
  SELECT 'Customer_count', 'Jan_23', 250 UNION ALL
  SELECT 'Sum_of_Txn_Value', 'Jan_23', 10000
),
sorted_months AS (
  SELECT DISTINCT Month
  FROM raw_data
  ORDER BY PARSE_DATE('%b_%y', Month)  # 按mmm_yy格式解析日期并排序
)
SELECT STRING_AGG(FORMAT("'%s'", Month), ', ') INTO sorted_month_list
FROM sorted_months;

# 执行动态生成的透视SQL
EXECUTE IMMEDIATE FORMAT("""
  SELECT *
  FROM raw_data
  PIVOT(
    SUM(Values) FOR Month IN (%s)
  )
  ORDER BY Metric;
""", sorted_month_list);

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