Oracle中COUNT(*) OVER窗口函数的三类技术疑问

先看用户提供的简化SQL语句：

select AB.*, count(*) over (PARTITION BY 
    TO_CHAR(AB.CAN_DATE,'MONTH'),
    TO_CHAR(AB.CAN_DATE,'MM'),
    'XMEG'
    ORDER BY
TO_CHAR(AB.CAN_DATE,'MONTH'),
    TO_CHAR(AB.CAN_DATE,'MM'),
    'XMEG') from mytable AB;

针对三个疑问逐一解答：

1. TO_CHAR(AB.CAN_DATE,'MM')是否冗余？ #

是冗余的。TO_CHAR(AB.CAN_DATE,'MONTH')返回月份名称（比如英文环境下的JANUARY），MM返回两位数字月份（比如01），二者是一一对应的。只要数据库的NLS_DATE_LANGUAGE环境一致，这两个表达式的分区逻辑完全等价——同一月份的行一定会被分到同一个分区里，额外加MM不会改变分区结果，属于多余写法。

2. COUNT(*) OVER子句中的ORDER BY有何作用？会产生实际影响吗？ #

有实际影响，且作用关键：

• 不带ORDER BY时，COUNT(*) OVER(PARTITION BY ...)返回整个分区的总记录数，分区内所有行的这个值都相同。
• 加上ORDER BY后，Oracle会默认启用窗口帧规则：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，此时COUNT(*)计算的是从分区第一行到当前行的累计记录数。比如分区里有5条数据，第一行返回1，第二行返回2，直到第五行返回5，和不带ORDER BY的结果完全不同。
  Oracle允许该子句存在，是因为分析函数的窗口定义支持通过ORDER BY指定行的处理顺序，以此实现累计统计、排名等多样化的窗口计算需求。

3. 在PARTITION BY与ORDER BY中加入常量'XMEG'有何意义？ #

没有实际作用：

• PARTITION BY里加常量，所有行的这个值都相同，不会对分区产生任何拆分效果，和不加这个常量的分区逻辑完全一致。
• ORDER BY里加常量，由于所有行的该值都一样，不会改变排序的优先级和结果——排序逻辑还是由前面的MONTH和MM决定，常量相当于无效排序字段。
  这个常量大概率是开发过程中遗留的冗余代码，或是误添加的。

内容的提问来源于stack exchange，提问作者David McKinney