sql - 在 Postgresql 中每月对结果的几个日期进行分组

Question

我在 Postgres 服务器上有下表，该表不断更新新日期(最早的日期是 3 年前)，但为了简单起见，我们只调出 3 条记录:

id      date1         date2        date3 
1245    01/04/2018    05/04/2018   03/05/2018   
1255    01/04/2018    01/06/2018   04/06/2018 
1231    03/05/2018    07/05/2018   07/06/2018
1234    01/04/2017    01/04/2017   01/06/2018

我想创建一个 sql View ，它将为我提供每种日期类型的单位总数

month     date1 date2 date3    
Jun       0     1     3   
May       1     1     1 
Apr       2     1     0
Apr17     1     1     0

有没有简单的方法来做到这一点？到目前为止，我的 sql 看起来像

CREATE OR REPLACE VIEW monthly_view 
AS
SELECT row_number() OVER (ORDER BY b.id) AS id,
       'month',
       count(*) as date1,
       count(*) as date2,
       count(*) as date3
FROM mytable b
GROUP BY month
ORDER BY month desc

但是显然不行

Answer 1

已测试!

路飞哥们，你想做坏事，但这是解决方案；)

    --all months    with allmonths as (        select to_char(date1, 'TMmon') as mm from tmp_a        union        select to_char(date2, 'TMmon') as mm from tmp_a        union        select to_char(date3, 'TMmon') as mm from tmp_a    ),    --count_in_date1    count_in_date1 as (        select            t1.mm,            count(t2.date1) as count_in_date1        from            allmonths t1 left join tmp_a t2                 on to_char(t2.date1, 'TMmon') = t1.mm        group by             t1.mm    ),    --count_in_date2    count_in_date2 as (        select            t1.mm,            count(t2.date2) as count_in_date2        from            allmonths t1 left join tmp_a t2                 on to_char(t2.date2, 'TMmon') = t1.mm        group by             t1.mm    ),    --count_in_date3    count_in_date3 as (        select            t1.mm,            count(t2.date3) as count_in_date3        from            allmonths t1 left join tmp_a t2                 on to_char(t2.date3, 'TMmon') = t1.mm        group by             t1.mm    )    --join all count together    select t1.mm, count_in_date1, count_in_date2, count_in_date3     from count_in_date1 t1, count_in_date2 t2, count_in_date3 t3        where t1.mm=t2.mm and t1.mm=t3.mm