来自另一个联接和组的MySql更新表

Question

如果我有桌子日志：

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        
-------------------------------

另一个表报告具有相同的日期& 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           
---------------------------------

我需要MySQL查询来填充report表中的单击，并根据键(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

所以这张桌子是：

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      
---------------------------------

样本，第一行：

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

到目前为止，我已经尝试过：

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) );

提前感谢

Answer 1

谢谢大家，以下是为我做的工作：

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;

Answer 2

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

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;

您也可以使用avg()而不是除法来完成这个任务：

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;

Answer 3

尝试使用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);