SQL 聚合连接和子查询优化

Question

我正在尝试按两种关系(购买和使用)的时间段获取聚合值并加入它们，以便我可以在一份报告中获得结果并绘制它们的比率。我正在使用 PostgreSQL。所需的最终报告是:dateTime, u.sum, b.sum, b.sum/u.sum

以下查询有效，但对于较大的表大小，扩展性很差。

SELECT b2.datetime AS dateTime, b2.sum AS BUY_VOLUME, u1.sum AS USE_VOLUME, 
CASE u1.sum
   WHEN 0 THEN 0
   ELSE (b2.sum / u1.sum)
END AS buyToUseRatio
    FROM(
    SELECT SUM(b.total / 100.0) AS sum, date_trunc('week', (b.datetime + INTERVAL '1 day')) - INTERVAL '1 day' as datetime
    FROM buys AS b
    WHERE 
    datetime > date_trunc('month', CURRENT_DATE) - INTERVAL '1 year'
    GROUP BY datetime) AS b2
INNER JOIN (SELECT SUM(u.amount) / 100.00 AS sum, date_trunc('week', (u.datetime + INTERVAL '1 day')) - INTERVAL '1 day' AS datetime
FROM uses AS u
WHERE 
 datetime > date_trunc('month', CURRENT_DATE) - INTERVAL '1 year'
GROUP BY datetime) AS u1 ON b2.datetime = u1.datetime
ORDER BY b2.datetime ASC;

我想知道是否有人可以通过提供替代查询来帮助我，该查询将获得所需的最终结果并且执行速度更快。

我很感激在这方面的任何帮助:-) 我的初级 SQL 有点生疏，我想不出不创建索引的另一种方法。提前致谢。

Answer 1

至少，这些索引可以帮助你查询:

create index idx_buys_datetime on buys(datetime);
create index idx_uses_datetime on uses(datetime);

您的查询似乎没问题。但是，您可以使用完全联接(而不是inner)来获取所有行，其中至少您的一个表有数据。您可以甚至使用 generate_series() 来始终获得 1 年的结果，即使您的任何一个表中都没有数据，但我不确定是否是这样你需要。另外，其他一些东西可以更容易地编写；您的查询可能如下所示:

select    dt, buy_volume, use_volume, buy_volume / nullif(use_volume, 0.0) buy_to_use_ratio
from      (select   sum(total / 100.0)  buy_volume, date_trunc('week', (datetime + interval '1 day')) - interval '1 day' dt
           from     buys
           where    datetime > date_trunc('month', current_timestamp - interval '1 year')
           group by 2) b
full join (select   sum(amount) / 100.0 use_volume, date_trunc('week', (datetime + interval '1 day')) - interval '1 day' dt
           from     uses
           where    datetime > date_trunc('month', current_timestamp - interval '1 year')
           group by 2) u using (dt)
order by  1

http://rextester.com/YVASV92568