sql子查询优化多聚合操作

gmxoilav 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(376)

我试图找出不同表格和分组的平均值和总和。我还想将返回的表压缩为一行。对于我的子查询，我得到这个结果

使用外部查询，我希望：

这是我到目前为止的sql代码。它的工作，但它的性能是非常缓慢的，我不知道为什么或如何优化它。

select sum(sub.count) as count, avg(sub.opened) as opened,
avg(sub.clicked) as clicked, avg(sub.started_watching) as started_watching,
sum(sub.views) as views
from (
select p.id, count(e.id) as count, 
avg(e.opened) as opened, avg(e.read_email) as clicked,
avg(e.started_video) as started_watching, sum(e.views) as views
from projects p
inner join guests g
on g.project_id = p.id
inner join videos v
on v.guest_id = g.id
inner join emails e
on e.video_id=v.id
group by p.id) sub;

sql mysql

来源：https://stackoverflow.com/questions/49944404/sql-subquery-optimization-multiple-aggregate-operations

1条答案

按热度按时间

2hh7jdfx1#

查看您的查询，主要问题与子查询结果的创建有关
因此，改进这个查询很重要，例如为每个表的联接中涉及的列添加适当的索引，并最终为select中使用的列添加复合索引（在联接中使用的列之后）

Select  sum(sub.count) as count
        , avg(sub.opened) as opened
        , avg(sub.clicked) as clicked
        , avg(sub.started_watching) as started_watching
        , sum(sub.views) as views
  from (
    select p.id
        , count(e.id) as count
        , avg(e.opened) as opened
        , avg(e.read_email) as clicked
        , avg(e.started_video) as started_watching
        , sum(e.views) as views
    from projects p
    inner join guests g on g.project_id = p.id
    inner join videos v on v.guest_id = g.id
    inner join emails e on e.video_id=v.id
    group by p.id
  ) sub;

所以要确保你有合适的索引

guests  (project_id)
videos  (guest_id)
emails  (video_id, id, opened, read_email,started_video, views)

and obvious on projects (id)

赞(0）回复(0）举报 2021-06-21

我来回答

sql子查询优化多聚合操作

1条答案

相关问题

热门标签

最新问答