主页 / dba / 问题 / 243919
Accepted
DeeDee
Asked: 2019-07-27 12:59:50 +0800 CST

我能做些什么来加快这个在一段时间内聚合的查询？

  • 772

我在 Redshift 中有一个星型模式数据库。我正在使用order_facts以下相关列在事实表上运行聚合：

total- FLOAT- 总订单成本

payment_date- INTEGER- 付款日期格式YYYYMMDD

shop_id-INTEGER商店维度的外键

我正在使用以下维度表：

日期

id- INTEGER- 日期格式YYYYMMDD

date- DATE- SQL 日期格式字段

商店

id--INTEGER表的主键

created- DATETIME- 商店创建时间的时间戳

我的目标是获取每个商店前 30 天的订单总量表。

我的查询是这样的，应该给出准确的答案。但是，它需要半个多小时才能运行：

SELECT of.shop_id, SUM(of.total) FROM order_facts of
INNER JOIN shops s
ON of.shop_id = s.id
INNER JOIN dates d
ON of.payment_date = d.id
WHERE d.date <= (s.created + INTERVAL '30 days')
GROUP BY of.shop_id

我尝试像这样重写它，但是在运行超过 20 分钟后查询仍然没有完成

SELECT SUM(r.total), r.shop_id
FROM (
    SELECT s.created, of.shop_id, of.total, of.payment_date
    FROM order_facts of
    INNER JOIN shops s
    ON of.shop_id = s.id
) r
INNER JOIN dates d
ON r.payment_date = d.id
WHERE d.date <= (r.created + INTERVAL '30 days')
GROUP BY r.shop_id

我目前不明白为什么要花这么长时间才能运行。理解这一点将帮助我知道如何更正查询。同样，查看上述查询的更好版本将帮助我了解效率低下的地方。无论哪种方式对我都非常有帮助。

仅获取过去 X 天或历史上每家商店的总订单量是一个非常快速的查询。因此，似乎我在加入日期表时做的不是最理想的，但不清楚是什么。

编辑：输出EXPLAIN

1
XN HashAggregate (cost=868301988.49..868301991.01 rows=1009 width=12)
2
-> XN Merge Join DS_DIST_NONE (cost=0.00..868286973.07 rows=3003084 width=12)
3
Merge Cond: ("outer".id = "inner".payment_date)
4
Join Filter: ("inner".shop_id = "outer".id)
5
-> XN Nested Loop DS_BCAST_INNER (cost=0.00..1773635510.00 rows=32472000 width=8)
6
Join Filter: (("outer".date)::timestamp without time zone > ("inner".created + '30 days'::interval))
7
-> XN Seq Scan on dates d (cost=0.00..110.00 rows=11000 width=8)
8
-> XN Seq Scan on shops s (cost=0.00..88.56 rows=8856 width=12)
9
-> XN Seq Scan on order_facts "of" (cost=0.00..90092.52 rows=9009252 width=16)
10
----- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products -----
redshift star-schema

1 个回答

  1. Best Answer

    我做了一个子查询，按商店建立每日订单总数，然后在该表上运行聚合。执行时间约为 6 秒。最终查询如下所示：

    SELECT s.shop_name, rrr.first_thirty_day_total FROM (
    SELECT SUM(rr.daily_total) AS first_thirty_day_total, rr.shop_id FROM (
        SELECT d.date, r.daily_total, r.shop_id FROM (
            SELECT SUM(of.total) AS daily_total, of.shop_id, of.payment_date
            FROM order_facts of
            GROUP BY shop_id, payment_date
        ) r
        INNER JOIN dates d
        ON r.payment_date = d.id
    ) rr
    INNER JOIN shops s
    ON s.id = rr.shop_id
    WHERE rr.date <= s.created + INTERVAL '30 days'
    GROUP BY rr.shop_id
) rrr
INNER JOIN shops s
ON rrr.shop_id = s.id
    • 0

相关问题