MySql更新表来自另一个加入和分组

If I have table logs :

如果我有表日志:

ID   Date     P_id     TYPE
-------------------------------
1   2016-9-1   11    adClick       
2   2016-9-1   22   adComplete
3   2016-9-1   11   adComplete
4   2016-9-3   22    adClick        
5   2016-9-3   22    adClick        
6   2016-9-1   44    adClick        
7   2016-9-3   44    adComplete        
8   2016-9-3   44    adClick        
9   2016-9-3   11    adClick        
-------------------------------

and another table report having the same Date & P_id as follows :

和另一个具有相同Date&P_id的表报告如下:

ID   Date     P_id     clicks   
--------------------------------
1   2016-9-1   11      
2   2016-9-1   11      
3   2016-9-1   22        
4   2016-9-3   22      
5   2016-9-1   11      
6   2016-9-1   44           
5   2016-9-1   44      
6   2016-9-1   11           
---------------------------------

I need MySQL query to fill clicks in report table and according to the key (Date & P_id) :

我需要MySQL查询来填充报表中的点击并根据键(Date&P_id):

clicks = 
    count of rows having (Date & P_id) in Report table 
          divided by 
    count of rows having (Date & P_id) and Type is adClick

So the table will be :

所以表格将是:

ID   Date     P_id     clicks   
--------------------------------
1   2016-9-1   11      4 / 1
2   2016-9-1   11      4 / 1
3   2016-9-1   22        0
4   2016-9-3   22      2 / 2
5   2016-9-1   11      4 / 1
6   2016-9-1   44      2 / 1     
5   2016-9-1   44      2 / 1
6   2016-9-1   11      4 / 1      
---------------------------------

Sample, first row :

样本,第一行:

2016-9-1   11      4 / 1 

4 rows (2016-9-1   11) in report table by
1 row  (2016-9-1   11) in logs table with type=adClick

What I have tried so far :

到目前为止我尝试了什么:

UPDATE report AS r 

INNER JOIN 
(
    SELECT 
        *, count(id) AS count_value
    FROM 
        logs
    WHERE
        type= "adClick"
    GROUP BY 
        date,p_id

) log

ON  r.date=log.date AND  r.p_id=log.p_id

SET r.clicks=(log.count_value / (SELECT COUNT(lof) from report) );

Thanks in advance

提前致谢

3 个解决方案

#1

You can do this using conditional aggregation:

您可以使用条件聚合执行此操作:

UPDATE report AS r INNER JOIN 
       (SELECT date, p_id, count(*) AS cnt,
               SUM(type = 'adClick') as cnt_adclick
        FROM logs
        GROUP BY  date,p_id
       ) l
       ON r.date = l.date AND r.p_id = l.p_id
    SET r.clicks = cnt_adclick / cnt;

You can also do this with avg() instead of division:

您也可以使用avg()而不是division来执行此操作:

UPDATE report AS r INNER JOIN 
       (SELECT date, p_id, 
               AVG(type = 'adClick') as avg_clicks
        FROM logs
        GROUP BY  date,p_id
       ) l
       ON r.date = l.date AND r.p_id = l.p_id
    SET r.clicks = avg_clicks;

#2

Try using CONCAT()

尝试使用CONCAT()

UPDATE report AS r
INNER JOIN (
    SELECT
        date,
        p_id,
        count(*) AS count,
        SUM(type = 'adClick') AS count_adclick
    FROM
        logs
    GROUP BY
        date,
        p_id
) log ON r.date = log.date
AND r.p_id = log.p_id
SET r.clicks = CONCAT(log.count_adclick, ' / ', log.count);

#3

Thanks all, the following did the job for me :

谢谢大家,以下为我完成了这项工作:

UPDATE report AS r 
INNER JOIN 
(
    SELECT cnt_adclick,count(*) as cnt_report,date, report.P_id
    FROM report
    INNER JOIN 
    (
        SELECT date as date, P_id, SUM(event_type = 'adClick') as cnt_adclick
        FROM logs
        GROUP BY date,P_id
    ) inner_log

    ON report.date = inner_log.date AND report.P_id = inner_log.P_id
    GROUP BY report.date, report.P_id
) l

ON r.date = l.date AND r.P_id= l.P_id
SET r.clicks = cnt_adclick / cnt_report;

#1