ERROR: column “order_water_record.owr_start_time“ must appear in the GROUP BY clause or be used in

以前工作中一直用的就mysql 换了工作，新公司用的事postgresql，本来觉得和mysql一样，但是在用到窗口函数的时候group by 总是报

ERROR:  column "order_water_record.owr_start_time" must appear in the GROUP BY clause or be used in an aggregate function

在网上找解决方案 好多都讲道理 给出解决方案的并不多 ，好多给的也比较复杂，不太好理解，我试着研究了解决方案 给大家分享

1. 数据源格式

2.需要按 日期 求出没个月的数量和

mysql 会这么弄

SELECT SUM
	( owr_num ),
	substr( owr_start_time, 1, 7 ) AS dateStr,
	substr( owr_start_time, 1, 4 ) AS dateYear,
	substr( owr_start_time, 6, 2 ) AS dateMonth 
FROM
	order_water_record 
GROUP BY
	substr( owr_start_time, 1, 7 )

但是 postgresql这么写直接会这样

SELECT sum(owr_num),substr(owr_start_time,1,7) as dateStr, substr(owr_start_time,1,4) as dateYear, substr(owr_start_time, 6,2) as dateMonth from order_water_record group by substr(owr_start_time,1,7)
> ERROR:  column "order_water_record.owr_start_time" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...um),substr(owr_start_time,1,7) as dateStr, substr(owr_start_...
                                                             ^

> 时间: 0.17s

至于原因  好多都有解释  不做赘述

3.解决方案

SELECT DISTINCT
	( substr( owr_start_time, 1, 7 ) ) AS dateStr,
	substr( owr_start_time, 1, 4 ) AS dateYear,
	substr( owr_start_time, 6, 2 ) AS dateMonth,
	SUM ( owr_num ) OVER ( PARTITION BY substr( owr_start_time, 1, 7 ) ) 
    AS owrNumMonth 
FROM
	order_water_record

 解释：

1.想得到  日期唯一  用了

 DISTINCT ( substr( owr_start_time, 1, 7 ) ) AS dateStr

2.求和用到了 sum  over partition组合

SUM ( owr_num ) OVER ( PARTITION BY substr( owr_start_time, 1, 7 ) ) AS owrNumMonth 

sum 不用说大家都明白  over 和后面的是连一块的  partition 就相当于mysql的group   以日期分组  

over 后面的括号中通常是以下的组合

over （PARTITION BY ........    ORDER BY ........）

order by 在用于求 环比  同比的时候 简直就是神器  有空 我会把大部分的  窗口函数和聚合函数做一个总结  以实例的方式给大家展示