mysql根据年度图表,查询MySQL数据库并按日期范围分组以创建图表

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.

6c127f764864ff04a07abbd1154dde59.png

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?

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.

解决方案

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

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值