博客

注：本文中的SQL语句，皆在Oracle数据库中验证可用，几大主流数据库的SQL语句相近，其余数据库，各位可自行修改验证~

需求：

一张表，第一个月录入数据100条，第二个月录入数据150条，第三个月录入数据200条；

需要得出的结果：1  100  2  250   3  450

以下是每段时间内，各自的数据总数的SQL

SELECT DISTINCT HIRE_YEAR_MONTH,  COUNT_
FROM (
SELECT 
TO_CHAR(create_date, 'yyyy/mm') HIRE_YEAR_MONTH, 
COUNT(*) OVER(PARTITION BY TO_CHAR(create_date, 'yyyy/mm')) 
AS COUNT_ 
FROM TableName ) a
group by a.HIRE_YEAR_MONTH,a.count_

此段SQL只能得出： 1  100  2  150   3  200

并不能达到我们的需求

以下为统计全表数据总数在【有数据变化】每个月内的Count，即第二个月需要包含自身和第一个月的总数，第三个月要包含自身和前两个月的总数：

SELECT distinct TO_CHAR(a.create_date, 'yyyymm'),
       (SELECT count (*)
          FROM gp_equ2_list b
         WHERE TO_CHAR(b.create_date, 'yyyymm') <= TO_CHAR(a.create_date, 'yyyymm'))
           pay
  FROM TableName a
 
GROUP BY TO_CHAR(a.create_date, 'yyyymm')

结果：1  100  2  250   3   450

正如以上所说，有数据变化，才会显示那个月，如果现在，我们 1  +100、2  +0、 3+ 100  用上面的第二段SQL就只会出现即结果为：1   100  3  200 而2 不会显示在结果集中，现在我们需要让它出结果为：1  100  2  100  3  200 的结果，甚至每天都显示一行数据，SQL如下：

SELECT distinct TO_CHAR(a.CUR_DATE, 'yyyymmdd') CUR_DATE,
       (SELECT count (*)
          FROM TableName b
         WHERE TO_CHAR(b.create_date, 'yyyymmdd') <= TO_CHAR(a.CUR_DATE, 'yyyymmdd')  )
           pay
  FROM (SELECT TO_DATE('2018-05-01','YYYY-MM-DD') + LEVEL - 1 CUR_DATE
FROM DUAL
CONNECT BY LEVEL <= TO_DATE('2018-07-13','YYYY-MM-DD') - TO_DATE('2018-05-01','YYYY-MM-DD') + 1) a
GROUP BY TO_CHAR(a.CUR_DATE, 'yyyymmdd')
 order by CUR_DATE desc

按月，按天皆可用以上方法：其中：yyyymmdd代表天   yyyymm  代表月，但是需要再转化一次格式，比如先将201801转为日期格式，再转成char，多了一步转化，例：to_char(TO_DATE (‘201801’, ‘yyyymmdd’),’yyyymm’)

但是按季，则会出现问题，季在Oracle中，没有实现满四个季度，年份+1的操作，比如你想查2018第一季度到2019年第二季度，你修改以上的方法，结果只是：20181,20182,20183,20184,20185,20186…..20192，没错它仅仅是将20181,20182这些当成是数学中的数字而已，此时如果我们需要实现效果上的“满4进1”，就需要把其他的过滤，则需用以下SQL：

SELECT 20181 + LEVEL - 1 CUR_DATE
FROM DUAL
where substr(to_char(20181 + LEVEL - 1),-1)<=4 and substr(to_char(20181 + LEVEL - 1),-1)>=1
CONNECT BY LEVEL <= 20193 - 20181 + 1

substr为截取字符串的函数；

注意：其中各个字段的类型需要注意，提示报错格式问题，需要运用到各种转化格式函数，如：to_char、to_date,to_number等

日期的月季周的互换见博客： Oracle 根据时间字段求出该时间所在的月、季、周