时间戳

首页 > 解决方案 > Postgress to_date 和提取组合

sql - Postgress to_date 和提取组合

问题描述

我在 postgres 中有下表：

+----------+-------+
| date     | money |
+----------+-------+
| 20191212 | 10    |
+----------+-------+
| 20200101 | 20    |
+----------+-------+
| 20200102 | 30    |
+----------+-------+

我想创建 2 个查询，按年按金额分组，按月分组。日期列是 varchar，我无法将其转换为日期。

+------+-------+
| year | money |
+------+-------+
| 2019 | 10    |
+------+-------+
| 2020 | 50    |
+------+-------+

+---------+-------+
| month   | money |
+---------+-------+
| 2019-12 | 10    |
+---------+-------+
| 2020-01 | 50    |
+---------+-------+

我试图结合 to_date 和 extract 函数，但它不起作用：

WITH date_year AS (
    SELECT to_date(date, 'YYYYMMDD') as date_year from sales
)
SELECT EXTRACT(YEAR FROM date_year) as year, sum(money) as sum FROM sales group by year

WITH date_month AS (
    SELECT to_date(date, 'YYYYMMDD') as date_month from sales
)
SELECT EXTRACT(MONTH FROM date_month) as month, sum(money) as sum FROM sales group by month

标签： sqlpostgresqlaggregate-functions

解决方案

group by您可以在子句中使用函数或别名：

select extract(year from to_date(date, 'YYYYMMDD')) as y_year, sum(money) as sum 
from sales 
group by y_year

select extract(year from to_date(date, 'YYYYMMDD')) || '-' || extract(month from to_date(date, 'YYYYMMDD')) as year_month, sum(money) as sum 
from sales 
group by year_month

我使用了y_year因为year在 postgres 中保留。

您也可以考虑使用date_trunc：

select date_trunc('month', to_date(date, 'YYYYMMDD')) as year_month, sum(money) as sum 
from sales 
group by year_month

推荐阅读