MySQL -获取唯一键的每日计数

Question

我有一个MySQL数据库，在那里我可以存储门禁.我在努力弄清楚如何让每个键的日常实例数.

样本数据：

 id  |       timestamp      |  key 
-----------------------------------
  0  |  2013-06-09 14:58:07 |  001
  1  |  2013-06-11 07:21:13 |  001
  2  |  2013-06-11 10:05:11 |  001
  3  |  2013-06-12 07:13:59 |  002
  4  |  2013-06-12 10:05:01 |  002
  5  |  2013-06-12 14:31:01 |  001
  6  |  2013-06-13 11:28:14 |  001
  7  |  2013-06-13 07:00:28 |  002
  8  |  2013-06-14 14:05:13 |  002
  9  |  2013-06-15 09:38:23 |  002

我希望最后的结果是：

key | 09 | 10 | 11 | 12 | 13 | 14 | 15 | ACCESSED DAYS |
---------------------------------------|---------------- 
001 |  1 |  0 |  2 |  1 |  1 |  1 |  1 |             6 |
002 |  0 |  0 |  0 |  2 |  1 |  0 |  0 |             2 |

谢谢您抽时间见我!

Answer 1

您可以使用条件聚合来完成此操作：

select `key`,
       sum(day(timestamp = 9)) as `09`,
       sum(day(timestamp = 10)) as `10`,
       sum(day(timestamp = 11)) as `11`,
       sum(day(timestamp = 12)) as `12`,
       sum(day(timestamp = 13)) as `13`,
       sum(day(timestamp = 14)) as `14`,
       sum(day(timestamp = 15)) as `15`,
       count(distinct date(timestamp)) as accessdays
from t
group by `key`;

要获得天数，只需计算不同的天数。

Answer 2

这是我的思考过程

首先，让我们为一个键选择所有的日期。

SELECT DISTINCT DAY(timestamp) FROM sample_data WHERE key = ?

第二，让我们从前面的结果中计算列数

SELECT COUNT(*) as access_days FROM (SELECT DISTINCT DAY(timestamp) from sample_data WHERE key = ?) a

最后，让我们将它与一个GROUP BY混合起来，从每个单独的键中获得结果

SELECT
    b.key as key,
    (SELECT COUNT(*) FROM (SELECT DISTINCT DAY(timestamp)
     FROM sample_data a WHERE a.key = b.key)) access_days
FROM sample_data b
GROUP BY b.key