mysql - 在单个响应/查询中获取不同日期范围的平均评分

Question

所以我有 4 个单独的 MySQL 查询用于获取评级:

    -- Get average rating for this week, and a count of each rating
    select my_user as Login, EXTRACT(WEEK from (update_date)) as 'WeekNo', 
    COUNT(CASE WHEN rating = 1 THEN 1 ELSE NULL END) AS '1-Stars', 
    COUNT(CASE WHEN rating = 2 THEN 1 ELSE NULL END) AS '2-Stars', 
    COUNT(CASE WHEN rating = 3 THEN 1 ELSE NULL END) AS '3-Stars',
    COUNT(CASE WHEN rating = 4 THEN 1 ELSE NULL END) AS '4-Stars',
    COUNT(CASE WHEN rating = 5 THEN 1 ELSE NULL END) AS '5-Stars', 
    round(avg(rating),2) as 'WeekAvg' 
    from rating_table where my_user IN ('u1','u2','u3')  
    AND EXTRACT(YEAR from (update_date)) = 2016 
    AND EXTRACT(WEEK from (update_date)) = (WEEKOFYEAR(NOW()) - 1)
    GROUP BY 1 ORDER BY 1;

    -- get the average rating for the past 90 days
    SELECT my_user as 'Login', round(avg(rating),2) as '90-day' 
    FROM rating_table
    WHERE update_date BETWEEN NOW() - INTERVAL 90 DAY AND NOW() 
    AND my_user in ('u1','u2','u3') 
    GROUP BY 1;

    -- get the average rating for Year to date
    SELECT my_user as 'Login', round(avg(rating),2) as 'YTD' 
    FROM rating_table 
    WHERE (update_date BETWEEN '2016-01-01 00:00:00' AND NOW())
    AND my_user in ('u1','u2','u3') 
    GROUP BY 1;

    -- get the average rating for the past 365 days
    SELECT my_user as 'Login', round(avg(rating),2) as '365-day' 
    FROM rating_table
    WHERE update_date BETWEEN NOW() - INTERVAL 365 DAY AND NOW() 
    AND my_user in ('u1','u2','u3') 
    GROUP BY 1;

我尝试了各种方法将它们放入单个查询/表中，但我就是无法使它们正常工作。这就是我希望输出的显示方式:

    +----------+--------+---------+---------+---------+---------+---------+---------+--------+------+---------+
    | Agent    | WeekNo | 1-Stars | 2-Stars | 3-Stars | 4-Stars | 5-Stars | WeekAvg | 90-day | YTD  | 365-day |
    +----------+--------+---------+---------+---------+---------+---------+---------+--------+------+---------+
    | u1       |     26 |       0 |       0 |       0 |       0 |       6 |    5.00 |   5.00 | 5.00 |    5.00 |
    | u2       |     26 |       0 |       0 |       0 |       1 |       9 |    4.90 |   4.90 | 4.90 |    4.90 |
    | u3       |     26 |       0 |       0 |       0 |       0 |       1 |    5.00 |   5.00 | 5.00 |    5.00 |
    +----------+--------+---------+---------+---------+---------+---------+---------+--------+------+---------+

我试过:

• SELECT * FROM (q1 UNION q2 etc)
• 嵌套选​​择查询
• 使用不同日期范围的子查询

这些查询是针对只读表运行的，因此我可以使用的方法有限。

我知道我已经接近答案了，但它就是不合我意。任何人都可以就此提供一些建议吗？

Answer 1

您可以简单地将它们作为不同的子查询加入:

Select * from 
  (Select * from ... /*your 1st query*/ ) t1
 Join (Select * from ... /*your 2nd query*/ ) t2 
    on t1.Login=t2.login
 Join (Select * from ... /*your 3rd query*/ ) t3
    on t1.Login=t3.login
 Join (Select * from ... /*your 4th query*/ ) t4
    on t1.Login=t4.login