查询MySQL数据库并按日期范围分组以创建图表 [英] Query a MySQL Database and Group By Date Range to Create a Chart

问题描述

我正在寻找从MySQL数据库创建以下图表的方法.我知道如何实际创建图表(使用Excel或类似程序)，我的问题是如何获取创建图表所需的数据.在此示例中，我看到1月1日有60张票处于绿线所示的状态.

I'm looking to create the following chart from a MySQL database. I know how to actually create the chart (using excel or similar program), my problem is how to get the data needed to create the chart. In this example, I can see that on January 1, 60 tickets were in the state illustrated by the green line.

我需要跟踪日期范围内项目票据的历史状态.日期范围由项目经理确定(本例中为1月1日至1月9日).

I need to track the historical state of tickets of a project through a date range. The date range is determined by a project manager (in this case it's January 1st through January 9th).

对于每张票证，我都有以下一组历史数据.每次票证中发生某些更改(状态，描述，受让人，客户更新以及此问题中未显示的其他属性)时，都会在数据库中创建一个时间戳记"条目.

For each ticket, I have the following set of historical data. Each time something changes in the ticket (state, description, assignee, customer update, and other attributes not shown in this problem), a "timestamp" entry is made in the database.

ticket_num      status_changed_date     from_state  to_state
123456          2011-01-01 18:03:44     --          1
123456          2011-01-01 18:10:26     1           2
123456          2011-01-01 14:37:10     2           2
123456          2011-01-02 07:55:44     2           3
123456          2011-01-03 06:12:18     3           2
123456          2011-01-04 19:03:43     3           3
123456          2011-01-05 02:05:24     3           4
123456          2011-01-06 18:13:28     4           4
123456          2011-01-07 13:14:48     4           5
123456          2011-01-09 01:35:39     5           5

如何查询给定时间(由脚本确定)的数据库，并找出每个票证处于什么状态?

How can I query the database for a given time (determined by my script) and find out what state each of the tickets are in?

例如:要生成上面显示的图表，给定日期为2011-01-02 12:00:00，多少张票证处于"2"状态?

For example: To produce the chart shown above, given the date 2011-01-02 12:00:00, how many tickets were in the state "2"?

我尝试查询具有特定日期和范围的数据库，但无法找出获取数据以创建图表的正确方法.预先感谢您的帮助.

I've tried querying the database with specific dates and ranges, but can't figure out the proper way to get the data to create the chart. Thanks in advance for any help.

推荐答案

好，如果您想在特定时间获取特定状态下的记录数，我认为可能需要存储的proc.

Ok so if you are trying to get a count of records in a certain state at a certain time, I think a stored proc might be necessary.

CREATE PROCEDURE spStatesAtDate
    @Date datetime,
    @StateId int
AS
BEGIN
   SET NOCOUNT ON;

   SELECT COUNT(*) as Count
   FROM ticket_table t1
   WHERE to_state = @StateId AND status_changed_date < @Date
        AND status_changed_date = (SELECT MAX(status_changed_date) FROM ticket_table t2 where t2.ticket_num=t1.ticket_num AND status_changed_date < @Date)
END

然后在上面的示例中调用它，您的查询看起来像

then to call this for the above example, you're query would look like

EXEC spStatesAtDate @Date='2011-01-02 12:00:00', @StateId=2

这篇关于查询MySQL数据库并按日期范围分组以创建图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！