MySQL 组合多个查询的最佳方式

Question

这里是 MySQL 新手。我有这三个单独的查询。每个按年周分组并选择一个额外的字段。

-- tickets_by_hosts
select 
    yearweek(r.created_at) as week,
    count(zt.id) as tickets_by_hosts
from reservations r
inner join zendesk_tickets zt on zt.reservation_code = r.confirmation_code and zt.requester_id = r.host_id
where 
    r.created_at > '2011-8-20 00:00:00' and status != 0
group by yearweek(r.created_at)
order by week desc


-- tickets_by_guests
select 
    yearweek(r.created_at) as week,
    count(zt.id) as tickets_by_guests
from reservations r
inner join zendesk_tickets zt on zt.reservation_code = r.confirmation_code and zt.requester_id = r.guest_id
where 
    r.created_at > '2011-8-20 00:00:00' and status != 0
group by yearweek(r.created_at)
order by week desc

-- reservations
select 
    yearweek(r.created_at) as week,
    count(r.id) as reservations
from reservations r
where 
    r.created_at > '2011-8-20 00:00:00' and status != 0
group by yearweek(r.created_at)
order by week desc

什么是组合这三个查询的最佳方式，以便结果按年周排列，如下所示:

week  tickets_by_hosts  tickets_by_guests  reservations
...   ...               ...                ...

谢谢!一直在谷歌上搜索这个，但到目前为止没有运气。

Answer 1

在您的情况下，这些不需要是单独的查询。您可以使用 CASE 在聚合(SUM、COUNT 等)函数中实现条件逻辑。

select 
    yearweek(r.created_at) as week,
    SUM(CASE WHEN zt.requester_id = r.host_id THEN 1 ELSE 0 END ) as tickets_by_hosts,
    SUM(CASE WHEN zt.requester_id = r.guest_id THEN 1 ELSE 0 END ) as tickets_by_guests,
    COUNT(*) AS reservations,
from reservations r
inner join zendesk_tickets zt 
    on zt.reservation_code = r.confirmation_code 
where 
    r.created_at > '2011-8-20 00:00:00' and status != 0
group by yearweek(r.created_at)
order by week desc

效率较低的方法是:

SELECT Q1.Week, Q1.Tickets_by_hosts, Q2.Tickets_by_guests, Q3.reservations
FROM (
        select 
            yearweek(r.created_at) as week,
            count(zt.id) as tickets_by_hosts
        from reservations r
        inner join zendesk_tickets zt on zt.reservation_code = r.confirmation_code and zt.requester_id = r.host_id
        where 
            r.created_at > '2011-8-20 00:00:00' and status != 0
        group by yearweek(r.created_at)
        order by week desc
     ) Q1
     INNER JOIN (
                    select 
                        yearweek(r.created_at) as week,
                        count(zt.id) as tickets_by_guests
                    from reservations r
                    inner join zendesk_tickets zt on zt.reservation_code = r.confirmation_code and zt.requester_id = r.guest_id
                    where 
                        r.created_at > '2011-8-20 00:00:00' and status != 0
                    group by yearweek(r.created_at)
                    order by week desc
                ) Q2 
            ON Q1.week = Q2.Week
    INNER JOIN (
                select 
                    yearweek(r.created_at) as week,
                    count(r.id) as reservations
                from reservations r
                where 
                    r.created_at > '2011-8-20 00:00:00' and status != 0
                group by yearweek(r.created_at)
                order by week desc  
               ) Q3
            ON Q1.week = Q3.week

对于第二个示例，我重写了查询以使用您作为子查询(或派生表)发布的每个示例，然后将它们连接在一起。但是，在这种情况下，数据库将完成扫描表和多次计算聚合的所有工作，您还将进行获取动态结果集并将它们连接在一起的工作(由于这些结果集的性质，您真的不会从索引中获益太多。第二个选项是错误的方法，但我将其包含在内是为了让您知道如何使用派生表，这在将来可能会有帮助。