使用SQL获取每小时统计信息 [英] Getting Hourly statistics using SQL

问题描述

我们有一个表，名称为'employeeReg'，其中包含字段

employeeNo | employeeName | Registered_on

Registered_on 是时间戳.

在几天之内，我们需要每小时进行一次注册.例如.

08年1月1日下午12:01-1592注册

01 Jan 08:01-02 PM:1020注册

有人可以对此提出建议吗?

---

我们正在使用Oracle 10gR2作为数据库服务器.

解决方案

这与关于

您也许可以用TimeSlot或TO_CHAR()表达式替换'1'条目；但是，出于向后兼容的原因，这可能会按书面形式工作(但我无法在Oracle上为您验证-在IBM Informix Dynamic Server上使用EXTEND(Registered_on, YEAR TO HOUR)代替TO_CHAR()的等效方法也可以). /p>

如果您随后决定在没有条目的小时数中显示零，那么您将需要创建一个要报告的所有小时数的列表，并且需要对该列表进行LEFT OUTER JOIN该查询的结果.困难的部分是生成正确的列表-不同的DBMS具有不同的处理方式.

We have a table, name 'employeeReg' with fields

employeeNo | employeeName | Registered_on

Here Registered_on is a timestamp.

We require an hourly pattern of registrations, over a period of days. eg.

01 Jan 08 : 12 - 01 PM : 1592 registrations

01 Jan 08 : 01 - 02 PM : 1020 registrations

Can someone please suggest a query for this.

---

We are using Oracle 10gR2 as our DB server.

解决方案

This is closely related to, but slightly different from, this question about How to get the latest record for each day when there are multiple entries per day. (One point in common with many, many SQL questions - the table name was not given originally!)

The basic technique will be to find a function that will format the varied Registered_on values such that all the entries in a particular hour are grouped together. This presumably can be done with TO_CHAR() since we're dealing with Oracle (MySQL does not support this).

SELECT TO_CHAR(Registered_on, "YYYY-MM-DD HH24") AS TimeSlot,
       COUNT(*) AS Registrations
    FROM EmployeeReg
    GROUP BY 1
    ORDER BY 1;

You might be able to replace the '1' entries by TimeSlot, or by the TO_CHAR() expression; however, for reasons of backwards compatibility, it is likely that this will work as written (but I cannot verify that for you on Oracle - an equivalent works OK on IBM Informix Dynamic Server using EXTEND(Registered_on, YEAR TO HOUR) in place of TO_CHAR()).

If you then decide you want zeroes to appear for hours when there are no entries, then you will need to create a list of all the hours you do want reported, and you will need to do a LEFT OUTER JOIN of that list with the result from this query. The hard part is generating the correct list - different DBMS have different ways of doing it.

这篇关于使用SQL获取每小时统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！