我有三个表,其架构如下:
Table: Apps
| ID (bigint) | USERID (Bigint)| START_TIME (datetime) |
-------------------------------------------------------------
| 1 | 13 | 2013-05-03 04:42:55 |
| 2 | 13 | 2013-05-12 06:22:45 |
| 3 | 13 | 2013-06-12 08:44:24 |
| 4 | 13 | 2013-06-24 04:20:56 |
| 5 | 13 | 2013-06-26 08:20:26 |
| 6 | 13 | 2013-09-12 05:48:27 |
Table: Hosts
| ID (bigint) | APPID (Bigint)| DEVICE_ID (Bigint) |
-------------------------------------------------------------
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 1 | 1 |
| 4 | 3 | 3 |
| 5 | 1 | 4 |
| 6 | 2 | 3 |
Table: Usage
| ID (bigint) | APPID (Bigint)| HOSTID (Bigint) | Factor (varchar) |
-------------------------------------------------------------------------------------
| 1 | 1 | 1 | Low |
| 2 | 1 | 3 | High |
| 3 | 2 | 2 | Low |
| 4 | 3 | 4 | Medium |
| 5 | 1 | 5 | Low |
| 6 | 2 | 2 | Medium |
现在如果 put 是 userid,我想得到过去 6 个月中每个“因素”月的每个月(所有应用程序)的表行行数.
如果 DEVICE_ID 在一个月内出现多次(基于 START_TIME,基于加入应用程序和主机),则仅考虑使用最新行的使用情况(基于应用程序、主机和使用情况的组合)来计算计数。
上述示例的查询示例输出应为:(对于输入用户 id=13)
| MONTH | USAGE_COUNT | FACTOR |
-------------------------------------------------------------
| 5 | 0 | High |
| 6 | 0 | High |
| 7 | 0 | High |
| 8 | 0 | High |
| 9 | 0 | High |
| 10 | 0 | High |
| 5 | 2 | Low |
| 6 | 0 | Low |
| 7 | 0 | Low |
| 8 | 0 | Low |
| 9 | 0 | Low |
| 10 | 0 | Low |
| 5 | 1 | Medium |
| 6 | 1 | Medium |
| 7 | 0 | Medium |
| 8 | 0 | Medium |
| 9 | 0 | Medium |
| 10 | 0 | Medium |
这是如何计算的?
- 对于 2013 年 5 月 (05-2013),应用程序表中有两个应用程序
- 在表 Hosts 中,这些应用程序与 device_id 的 1,1,1,4,3 关联
- 对于本月(05-2013),对于 device_id=1,start_time 的最新值为:2013-05-12 06:22:45(来自表“hosts”、“apps”),因此在表“Usage”中,查找 appid=2&hostid 的组合=2,其中有两行,第一行的因子为“低”,另一行的因子为“中”,
- 对于本月(05-2013),device_id=4,通过遵循相同的过程,我们得到一个条目,即 0 Low
- 同样地计算所有值。
为了通过查询获取过去 6 个月的数据,我尝试通过以下方式获取:
SELECT MONTH(DATE_ADD(NOW(), INTERVAL aInt MONTH)) AS aMonth
FROM
(
SELECT 0 AS aInt UNION SELECT -1 UNION SELECT -2 UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
)
请检查sqlfiddle:http://sqlfiddle.com/#!2/55fc2 http://sqlfiddle.com/#!2/55fc2