Presto SQL：如何将日期转换为季度/年份格式？

1. 转换为年份格式 #

如果只需要提取年份并做聚合，可使用以下两种方式：

• 用date_trunc返回日期类型的年初值，适合保留日期特性的场景：

SELECT date_trunc('year', your_date_column) AS year_start,
       COUNT(*) AS record_count
FROM your_table
GROUP BY date_trunc('year', your_date_column)
ORDER BY year_start;

• 用extract返回数值类型的年份，适合仅需年份数字的场景：

SELECT extract(year FROM your_date_column) AS year,
       COUNT(*) AS record_count
FROM your_table
GROUP BY extract(year FROM your_date_column)
ORDER BY year;

2. 转换为带年份的季度格式 #

要生成「YYYY-QX」这类标准季度标识，有两种常用方法：

• 用format_date直接生成格式化字符串，语法简洁：

SELECT format_date('yyyy-"Q"q', your_date_column) AS year_quarter,
       SUM(your_numeric_column) AS total
FROM your_table
GROUP BY format_date('yyyy-"Q"q', your_date_column)
ORDER BY year_quarter;

• 用extract拼接生成，灵活性更高：

SELECT CONCAT(extract(year FROM your_date_column), '-Q', extract(quarter FROM your_date_column)) AS year_quarter,
       AVG(your_numeric_column) AS average_value
FROM your_table
GROUP BY year_quarter
ORDER BY year_quarter;

3. 仅提取季度数值 #

如果只需要1-4的季度数字，直接用extract即可：

SELECT extract(quarter FROM your_date_column) AS quarter,
       COUNT(*) AS record_count
FROM your_table
GROUP BY quarter
ORDER BY quarter;

4. 按季度起始日期聚合 #

若需要以季度起始日期作为分组键（保留日期格式），用date_trunc：

SELECT date_trunc('quarter', your_date_column) AS quarter_start,
       MAX(your_date_column) AS quarter_end,
       SUM(your_numeric_column) AS total
FROM your_table
GROUP BY quarter_start
ORDER BY quarter_start;

注意：请将示例中的your_date_column、your_table、your_numeric_column替换为实际业务中的列名和表名。

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