Snowflake中如何将VARCHAR列转为日期格式并使用date_trunc按月汇总

首先得把VARCHAR类型的日期字符串转换成数据库可识别的日期类型，再用date_trunc（或对应数据库的等价函数）按月分组汇总数值。以下分常见数据库给出具体写法：

PostgreSQL 方案 #

假设日期列是date_str，数值列是value_col，表名为your_table：

1. 用to_date()函数匹配DD-Mon-YYYY格式（比如01-Jan-2023）转换日期
2. 按月截断并汇总：

SELECT
  date_trunc('month', to_date(date_str, 'DD-Mon-YYYY')) AS month,
  SUM(value_col) AS total_value
FROM your_table
GROUP BY month
ORDER BY month;

如果存在无效日期字符串，可改用try_to_date()避免报错（PostgreSQL 12及以上支持）：

SELECT
  date_trunc('month', try_to_date(date_str, 'DD-Mon-YYYY')) AS month,
  SUM(value_col) AS total_value
FROM your_table
WHERE try_to_date(date_str, 'DD-Mon-YYYY') IS NOT NULL
GROUP BY month
ORDER BY month;

MySQL 方案 #

MySQL 8.0+支持date_trunc，低版本可用DATE_FORMAT替代：

用date_trunc（MySQL 8.0+） #

SELECT
  date_trunc('month', STR_TO_DATE(date_str, '%d-%b-%Y')) AS month,
  SUM(value_col) AS total_value
FROM your_table
GROUP BY month
ORDER BY month;

低版本替代写法 #

SELECT
  DATE_FORMAT(STR_TO_DATE(date_str, '%d-%b-%Y'), '%Y-%m') AS month,
  SUM(value_col) AS total_value
FROM your_table
GROUP BY month
ORDER BY month;

处理无效日期可加判断：

SELECT
  DATE_FORMAT(STR_TO_DATE(date_str, '%d-%b-%Y'), '%Y-%m') AS month,
  SUM(value_col) AS total_value
FROM your_table
WHERE STR_TO_DATE(date_str, '%d-%b-%Y') IS NOT NULL
GROUP BY month
ORDER BY month;

Oracle 方案 #

Oracle用TO_DATE转换日期，用TRUNC()函数按月截断：

SELECT
  TRUNC(TO_DATE(date_str, 'DD-Mon-YYYY'), 'MM') AS month,
  SUM(value_col) AS total_value
FROM your_table
GROUP BY TRUNC(TO_DATE(date_str, 'DD-Mon-YYYY'), 'MM')
ORDER BY month;

处理无效日期可加VALIDATE_CONVERSION（Oracle 12c及以上）：

SELECT
  TRUNC(TO_DATE(date_str, 'DD-Mon-YYYY'), 'MM') AS month,
  SUM(value_col) AS total_value
FROM your_table
WHERE VALIDATE_CONVERSION(date_str AS DATE, 'DD-Mon-YYYY') = 1
GROUP BY TRUNC(TO_DATE(date_str, 'DD-Mon-YYYY'), 'MM')
ORDER BY month;

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