oracle sql 按日，周，月，年统计

oracle sql 按日，周，月，年统计

http://www.cnblogs.com/gaojing/archive/2008/11/07/1328657.html   [oracle] to_date() 与 to_char() 日期和字符串转换

http://blog.sina.com.cn/s/blog_5fcdcbf70100er92.html   SQL   按月统计

http://www.cnblogs.com/wenbhappy/archive/2008/07/02/1233660.html

SQL按照日、周、月、年统计数据

http://blog.19lou.com/10572276/viewspace-755729 SQL按日期归类统计数据

http://piaoling.iteye.com/blog/465703 oracle的rank,over partition涵数使用

http://www.programmersheaven.com/mb/oracle/344879/344879/invalid-indentifier-error-with-this-sql-statement/?S=B20000   ORA-00904: "DAY": invalid identifier

http://www.diybl.com/course/7_databases/sql/sqlServer/2008626/128408.html   Oracle SQL group by day/week/month/quarter

http://blog.csdn.net/jamex/archive/2008/06/24/2582514.aspx   Oracle的日期函数

http://stackoverflow.com/questions/482912/sql-group-by-year-month-week-day-hour-sql-vs-procedural-performance   

经过实践

根据http://blog.19lou.com/10572276/viewspace-755729 SQL按日期归类统计数据

如:
表:consume_record
字段:consume (money类型)
date (datetime类型)

请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元

--按日
select sum(consume),day([date]) from consume_record where year([date]) = '2006' group by day([date])

--按周quarter
select sum(consume),datename(week,[date]) from consume_record where year([date]) = '2006' group by datename(week,[date])

--按月
select sum(consume),month([date]) from consume_record where year([date]) = '2006' group by month([date])

--按季
select sum(consume),datename(quarter,[date]) from consume_record where year([date]) = '2006' group by datename(quarter,[date])



--指定日期你就看上面的例子变通下呀，无非就是一个聚合函数和Group by

select [date],sum(consume) from consume_record where [date] between '2006-06-01' and '2006-07-10' group by [date]

以上适用与非ORALE，因为oracle不支持 day函数

oracle中进行上述操作需要

You can choose any of the following options and replace the text string in SQLs to let the reports be as daily/weekly/monthly/Quarterly reports..

--daily
to_char(rad.created_on+15/24, 'YYYY-MM-DD')
--weekly
to_char(next_day(rad.created_on+15/24 - 7,2),'YYYY-MM-DD')

to_char(时间列,'yyyy')||to_char(时间列,'IW')  

--monthly
to_char(rad.created_on+15/24,'yyyy-mm')
--quarterly
to_char(rad.created_on+15/24,'yyyy-q') ||'Q'

 

例如我的sql：

按天----select to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'YYYY-MM-dd') a,sum(num) from udbstat group by to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'YYYY-MM-dd');

按周----select to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'ww') a,sum(num) from udbstat group by to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'ww');

因为我的timestamp是字符串，故需要先转换成date类型。

如果字段本身就是date类型，sql如下：

按小时---select to_char(timestamp, 'YYYY-MM-dd hh24') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM-dd hh24');

按天---select to_char(timestamp, 'YYYY-MM-dd') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM-dd');

按月---select to_char(timestamp, 'YYYY-MM') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM');

按年---select to_char(timestamp, 'YYYY') a,sum(num) from udbstat group by to_char(timestamp,'YYYY');

按季度---select to_char(timestamp, 'YYYY-q') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-q');

按周---select to_char(timestamp, 'ww') a,sum(num) from udbstat group by to_char(timestamp,'ww');