我有一个查询,我试图根据Oracle中两个unix时间戳之间的小时数汇总数据。最难的部分是即使在查询中没有发现任何错误,我也会收到错误提示“ ORA-00936:缺少表达式 ”。在这里需要一些专家建议。以下是查询-
询问 -
select DATE(FROM_UNIXTIME(C.DATETIMEORIGINATION)) the_date, HOUR(FROM_UNIXTIME(C.DATETIMEORIGINATION)) the_hour, count(c.RECORD_ID) the_count FROM TABLE_A C WHERE C.DATETIMEORIGINATION between 1380033019 AND 1379702408 GROUP BY 1,2;
任何帮助是极大的赞赏。谢谢
如果您希望该the_date字段为实际日期:
the_date
select trunc(date '1970-01-01' + datetimeorigination / (24*60*60)) as the_date, to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'HH24') as the_hour, count(record_id) from table_a group by trunc(date '1970-01-01' + datetimeorigination / (24*60*60)), to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'HH24'); THE_DATE THE_HOUR COUNT(RECORD_ID) --------- -------- ---------------- 24-SEP-13 14 1 20-SEP-13 18 1
如果您希望将小时值作为数字,则可以将该字段包装在to_number()通话中。如果这是为了显示,那么您还应该显式格式化日期:
to_number()
select to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'YYYY-MM-DD') as the_date, to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'HH24') as the_hour, count(record_id) from table_a group by to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'YYYY-MM-DD'), to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'HH24'); THE_DATE THE_HOUR COUNT(RECORD_ID) ---------- -------- ---------------- 2013-09-24 14 1 2013-09-20 18 1
或将日期和时间放在一个字段中:
select to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'YYYY-MM-DD HH24') as the_hour, count(record_id) from table_a group by to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'YYYY-MM-DD HH24'); THE_HOUR COUNT(RECORD_ID) ------------- ---------------- 2013-09-24 14 1 2013-09-20 18 1
取决于您要查看的内容以及要使用的内容。
无论用于聚合的字段是什么,都需要在group by子句中以相同的方式指定它们-您不能使用位置表示法,例如group by 1, 2。您已经意识到,between值必须按升序排列,否则根本找不到任何内容。
group by
group by 1, 2
between