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

最新推荐文章于 2023-06-09 14:01:36 发布

Jalant

最新推荐文章于 2023-06-09 14:01:36 发布

阅读量408

点赞数

分类专栏：学习笔记文章标签： SQL Oracle Blog performance HTML

本文链接：https://blog.youkuaiyun.com/Jalant/article/details/83887549

版权

学习笔记专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用Oracle SQL按日、周、月、季度进行数据汇总统计，包括具体SQL语句示例及日期函数的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.youkuaiyun.com/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');