sql - 仅在 PostgreSQL 中按月分组查询？

Question

我有一份三个月的已售商品 list 及其各自的日期。我想创建一个表格，显示每个月(9 月、10 月、11 月)售出的商品总数，以及每个月的最畅销商品。

这是我的数据输出的样子:

  ~dateshipped~  ~item~  

    9/3/19         A
    9/3/19         A
    9/5/19         B
    10/10/19       C
    10/12/19       D
    10/12/19       D
    11/13/19       D
    11/15/19       B     
    11/15/19       B
    11/18/19       A 

期望的结果:三列:月份//商品总数//该特定月份销售的最佳商品

  ~month~  ~total_month~  ~best_sold~ 
   Sep           3             A             
   Oct           3             D
   Nov           4             B

获得解决方案的最简单方法是什么？

Answer 1

尝试像下面这样使用 cte

 with cte as 
 (
 select
 to_char(to_timestamp (EXTRACT(MONTH FROM dateshipped)::text, 'MM'), 'TMmon')
 as month, count(*) as total_month
 from table_name group by 
 to_char(to_timestamp (EXTRACT(MONTH FROM dateshipped)::text, 'MM'), 'TMmon')

  ) , cte2 as
   (
     select  to_char(to_timestamp (EXTRACT(MONTH FROM dateshipped)::text, 'MM'), 'TMmon') as month, count(*) as total_month,item
    from table_name group by
   to_char(to_timestamp (EXTRACT(MONTH FROM dateshipped)::text, 'MM'), 'TMmon'),item
   )
  ,cte3 as
  ( select t1.* from cte2 t1 where
    t1.total_month=(select max(total_month) from cte2 t2 where t1.month=t2.month)
 ) select cte.*,cte3.item as best_sold from cte join cte3 on cte.month=cte3.month
 order by cte.month 

输出

month   total_month     best_sold
nov     4                B
oct     3                D
sep     3                A

Online demo link