MySQL:聚合聚合值

Question

考虑这个简单的查询:

SELECT 
    year(t.date) AS y, 
    month(t.date) AS m, 
    t.person_id AS id, 
    count(*) AS freq
FROM 
    table t
WHERE 
    t.date>='2013-01-01' AND t.date<='2013-06-30'
GROUP BY y, m, id

这会产生这样的结果:

y     m  id   freq
------------------
2013  1  100  2
2013  1  101  7
2013  1  102  1
2013  2  100  5
2013  2  101  4
2013  2  102  11
...

我想添加一个列，其中包含频率相对于每个月频率总和的百分比，例如:

y     m  id   freq perc
-----------------------
2013  1  100  2    20
2013  1  101  7    70
2013  1  102  1    10
2013  2  100  5    25
2013  2  101  4    20
2013  2  102  11   55
...

到目前为止，我的解决方案是将其添加到 select 子句中:

count(*) * 100 / (SELECT count(*) FROM table WHERE year(date)=y AND month(date)=m) AS perc

但我想避免子选择，尤其是因为所有必要的值都已存在于第一个查询的结果中。这需要对聚合值进行聚合，但我不知道该怎么做。有什么想法吗？

Answer 1

SELECT 
    year(t.date) AS y, 
    month(t.date) AS m, 
    t.person_id AS id, 
    COUNT(*) AS freq,
    CASE WHEN p.freq = 0 then 0
         ELSE (CAST(COUNT(*) as FLOAT) / p.freq) * 100 
         END AS rate
FROM table t
JOIN (
    SELECT 
        year(t.date) AS y, 
        month(t.date) AS m,  
        count(*) AS freq
    FROM table
    GROUP BY y, m
) p ON p.y = year (t.date) AND p.m = month (t.date)
WHERE t.date BETWEEN '2013-01-01' AND '2013-06-30'
GROUP BY y, m, id