Oracle sql按年、月、周等，分组查询数据

oracle sql 按年、月、周、日，分组查询统计数据：

1、SQL语句如下

select sum(count),to_char(to_date(ext2,"YYYY-MM-dd"),"iw") from CMS_INDEXPV where site_id = "0002"
 and ext2 is not null group by to_char(to_date(ext2,"YYYY-MM-dd"),"iw") order by 
 to_char(to_date(ext2,"YYYY-MM-dd"),"iw");

其中参数意思如下，
dd—————–日（可以传递MM-dd,方便排序）；
iw —————–周；
MM—————月；
YYYY————–年；

后台代码可以根据传入不同的参数来获取不同的统计数据；

Java后台代码如下：

@At("/dateChart")
@Ok("raw")
public String dateChart(@Param("startTime") String startTime,
            @Param("endTime")String endTime,@Param("site")String site){

    /* 生成sql语句，启动$s表示传入统计类别的参数，日、周、月等 */
    Sql sql = Sqls.create(" select to_char(to_date(ext2,"YYYY-MM-dd"),"$s"),sum(count) from
     CMS_INDEXPV where site_id = ""+site+"" and ext2 between ""+startTim+"" and ""+endTime+"" 
     and ext2 is not null  group by to_char(to_date(ext2,"YYYY-MM-dd"),"$s") order by
      to_char(to_date(ext2,"YYYY-MM-dd"),"$s") asc  ");

    /* 给$s赋值，可以动态传入 */
    sql.vars().set("s","YYYY-MM-dd");

    /* 由于上一条SQL语句获取的Hashtable迭代出来没有按顺序，这里通过另一条sql，获取日期List（顺序）*/
    Sql sql2 = Sqls.create(" select to_char(to_date(ext2,"YYYY-MM-dd"),"$s") from CMS_INDEXPV 
    where site_id = ""+site+"" and ext2 between ""+startTime+"" and ""+endTime+"" and 
    ext2 is not null  group by to_char(to_date(ext2,"YYYY-MM-dd"),"$s") 
    order by to_char(to_date(ext2,"YYYY-MM-dd"),"$s") asc  ");

    sql2.vars().set("s","YYYY-MM-dd");

    /* 执行sql，获取按条件分组统计的数据HashTable */
    Hashtable<String,String> pvMap =  daoCtl.getHTable(dao, sql);

    /* 执行sql2，获取按顺序排列的日期List */
    List<String> list = daoCtl.getStrRowValues(dao,sql2);

    /* 获取查询数据的sum值 */
    Sql sqlSum = Sqls.create(" select sum(count) from CMS_INDEXPV where site_id = ""+
    site+"" and ext2 between ""+startTime+"" and ""+endTime+"" and ext2 is not null ");

    String pvSum = daoCtl.getStrRowValue(dao,sqlSum);

    /* 按格式拼接字符串 */
    StringBuffer sb = new StringBuffer("[");

    for(String key : list){
        sb.append("[""+key+"",""+pvMap.get(key)+""],");
    }

    sb.deleteCharAt(sb.length()-1);
    sb.append("]@@"+pvSum);
    return sb.toString();
}

以下是按日统计的页面效果图：

理论上可以通过传递参数的改变返回所需要的访问量统计数据，比如可以按周统计，按月统计等等

以下待开发ing……