mysql - 请优化SQL查询帮助

Question

我有两个表:

• 行动
• 主持人

表格的示例数据如下:

Action

  | ID (bigint) |   ACTION_TIME (datetime)  |   ACTION_NAME (varchar)   |
  -----------------------------------------------------------------------
  |         1   |   2013-08-03 04:42:55     |           test            |
  |         2   |   2013-10-01 06:22:45     |         test56            |
  |         3   |   2013-12-12 08:44:24     |          cloud            |
  |         4   |   2014-01-05 04:20:56     |           demo            |
  |         5   |   2014-03-10 08:20:26     |        console            |
  |         6   |   2014-03-12 05:48:27     |           temp            |

主机

  | ID (bigint) |   ACTION_ID (bigint)  |       DEVICE_ID (bigint) |  CRITICAL_COUNT (bigint)  |  HIGH_COUNT (bigint)   |
  ------------------------------------------------------------------------------------------------------------------------
  |         1   |        1              |                      1   |         200               |            400         |   
  |         2   |        1              |                      2   |         100               |            200         |
  |         3   |        2              |                      1   |         350               |            100         |
  |         4   |        4              |                      3   |           2               |             50         |
  |         5   |        5              |                      2   |           4               |              2         |
  |         6   |        6              |                      2   |          50               |            100         |

我需要使用这些数据生成折线图(趋势)。我能够弄清楚如何生成图表，但无法编写正确的 SQL 来检索数据。

说明:如上表所示，有操作，每个操作涉及主机(一个或多个)。并且每个主机都有critical_count, high_count。

现在我想获取 critical_count、high_count(SUM)的月度数据。

**There is one very **important** condition here. If more than one action has same device_id (hosts) in a month, only the latest data (basing on action_time) for that device should be used. The result should include data for last 12 months(including present month). At this month it should be Apr 2013 - Mar 2014**

例如:对于上面的数据，结果应该是:

  |     MONTH   |   CRITICAL_COUNT   |  HIGH_COUNT  |
  ---------------------------------------------------
  |         4   |        0           |       0      |
  |         5   |        0           |       0      |
  |         6   |        0           |       0      |
  |         7   |        0           |       0      |
  |         8   |      300           |     600      |
  |         9   |        0           |       0      |
  |        10   |      350           |     100      |
  |        11   |        0           |       0      |
  |        12   |        0           |       0      |
  |         1   |        2           |      50      |
  |         2   |        0           |       0      |
  |         3   |       50           |     100      |

请使用以下 SQL Fiddle:

http://sqlfiddle.com/#!2/d179d

谢谢。

Answer 1

select MONTH, CRITICAL_COUNT, HIGH_COUNT from
(
select 
       YEAR(action_time) YEAR, 
       MONTH(action_time) MONTH, 
       SUM(critical_count) CRITICAL_COUNT,
       SUM(high_count) HIGH_COUNT
FROM hosts H1 inner join actions A1 on A1.id = H1.action_id
WHERE DATE_SUB(CURRENT_DATE,INTERVAL 1 YEAR) <= action_time
AND A1.action_time = 
(
  select max(A2.action_time)
  from hosts H2 inner join actions A2 on A2.id = H2.action_id
  where 
  H2.device_id = H1.device_id 
  and MONTH(A2.action_time) = MONTH(A1.action_time)
  and YEAR(A2.action_time) = YEAR(A1.action_time)
)
group by MONTH(action_time), year(action_time)

Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 11 MONTH)), MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 11 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 10 MONTH)), MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 10 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 9 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 9 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 8 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 8 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 7 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 7 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 5 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 5 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 4 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 4 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 3 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 3 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 2 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 2 MONTH)),0,0
Union All Select YEAR(DATE_SUB(CURRENT_DATE,INTERVAL 1 MONTH)),  MONTH(DATE_SUB(CURRENT_DATE,INTERVAL 1 MONTH)),0,0
) ALL_MONTHS
where (CRITICAL_COUNT > 0 or HIGH_COUNT> 0)
or (CRITICAL_COUNT = 0 and HIGH_COUNT = 0)
group by YEAR,MONTH
order by YEAR,MONTH

Fiddle Demo