关于日期格式为yyyyMMddHHmmss的14位按日、月、年、季度以及去重、未去重查询

接触SQL不多，所以写的不好大家见谅。

关于oracle数据库中有date格式的日记在这不写，网上比较多。这varchar2格式或者char格式的日期网上较少，最近由于要使用到所以便写出来以便大家提供交流意见。关于去重的季度SQL由于本身接触SQL不多未能写出，望大家能够提供一些好的建议。

表名以及字段：ec_user_subscription 

字段只是部分的
字段                         类型

id                           number(20,0)//主键

user_id                number(20,0) //用户i，user表中主键

refresh_date       char(14)//日期格式：yyyyMMddHHmmss

未去重：

关键语句：

substr(u.refresh_date,0,8)  截取字符串，0位开始截取8个长度。

日统计

select substr(u.refresh_date,0,8) dateday ,count(*) from ec_user_subscription u
group by substr(u.refresh_date,0,8) order by dateday;

月统计

select substr(u.refresh_date,0,6) dateday ,count(*) from ec_user_subscription u
group by substr(u.refresh_date,0,6) order by dateday;

季度统计

select substr(refresh_date,0,4) as yeardate ,--年份
sum(decode(substr(refresh_date,5,2),'01',1,'02',1,'03',1,0)) as quarter1th ,--第一季度
sum(decode(substr(refresh_date,5,2),'04',1,'05',1,'06',1,0)) as quarter2nd ,--第二季度
sum(decode(substr(refresh_date,5,2),'07',1,'08',1,'09',1,0)) as quarter3rd ,--第三季度
sum(decode(substr(refresh_date,5,2),'10',1,'11',1,'12',1,0)) as quarter4th --第四季度
from ec_user_subscription u
group by substr(refresh_date,0,4) order by yeardate;

关于该季度统计的SQL，我查询100万条数据得出的是2S多，如图：

年度统计

select substr(u.refresh_date,0,4) dateday ,count(*) from ec_user_subscription u
group by substr(u.refresh_date,0,4) order by dateday;

去重SQL

--去重后查询出每日的用户量--

select u.refreshdate dateday ,count(*) from (
select distinct user_id ,substr(refresh_date,0,8) as refreshdate  from ec_user_subscription
) u
group by u.refreshdate order by dateday;

--去重后查询出每月的用户量--

select u.refreshdate dateday ,count(*) from (
select distinct user_id ,substr(refresh_date,0,6) as refreshdate  from ec_user_subscription
) u
group by u.refreshdate order by dateday;

--去重后查询出每年的用户量--

select u.refreshdate dateday ,count(*) from (
select distinct user_id ,substr(refresh_date,0,4) as refreshdate  from ec_user_subscription
) u
group by u.refreshdate order by dateday;