Oracle按日期累计求和

• 求各个部门在员工入职时(年份-月份)的该部门的总人数

  写法1：

  SELECT DISTINCT HIRE_YEAR_MONTH, DEPARTMENT_ID,  COUNT_
  FROM (
  SELECT 
  DEPARTMENT_ID, 
  TO_CHAR(HIRE_DATE, 'yyyy/mm') HIRE_YEAR_MONTH, 
  COUNT(*) OVER(PARTITION BY DEPARTMENT_ID ORDER BY TO_CHAR(HIRE_DATE, 'yyyy/mm')) 
  AS COUNT_ 
  FROM HR.EMPLOYEES) 
  ORDER BY DEPARTMENT_ID, HIRE_YEAR_MONTH;

  写法2：

  SELECT DEPARTMENT_ID,
     TO_CHAR(HIRE_DATE, 'yyyy/mm') HIRE_YEAR_MONTH,
     COUNT(*),
     SUM(COUNT(*)) OVER(PARTITION BY DEPARTMENT_ID ORDER BY TO_CHAR(HIRE_DATE, 'yyyy/mm')) AS COUNT_
  FROM HR.EMPLOYEES
  GROUP BY DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'yyyy/mm')