mysql - 按周统计所有类型设备

Question

我有一张 table :

idint(11) NOT NULL
type varchar(64) NOT NULL
created_on datetime NOT NULL

使用doctrine2时，我可以在一次查询中按周对所有类型设备(iSO、Android...)进行计数吗？

示例输出:

Week iOS Android Other
1     4    5       6
2     3    5       9

Answer 1

这是完全可能的，使用类似于 Output Sum of some column in week intervals throughout a year, week dates consistent with day 中详细介绍的方法。但是在您描述的输出中并不容易。处理这个问题的最佳方法是执行 SELECT，按 WEEK 和 TYPE 进行分组。即

SELECT

    -- Get the week and month of each row
    YEAR(created_on) AS Year,
    WEEK(created_on) AS Week,
    type

    -- Count how many rows are in this group
    COUNT(*) AS frequency

FROM yourTable AS yt

-- Order by month and then week, so that week 4 in Jan comes before week 4 in Feb
GROUP BY
    Year ASC,
    Week ASC,
    type ASC

这将给出如下输出

Year    Week    Type       frequency
2013    1       'iOS'      4
2013    1       'Android'  5
2013    1       'Other'    6
2013    2       'iOS'      3
2013    2       'Android'  5
2013    2       'Other'    9