两个表的内连接返回错误的值

Question

下面是我的SQL查询：

SELECT  SUM(amz_event_shipment_items.quantity),
        amz_event_shipment_items.seller_sku

FROM    amz_event_shipment_items

INNER   JOIN amz_event_fees         ON amz_event_shipment_items.id = amz_event_fees.shipment_item_id
INNER   JOIN amz_shipment_events    ON amz_shipment_events.id = amz_event_shipment_items.shipment_event_id

WHERE   amz_event_fees.currency  = 'USD'
        AND amz_shipment_events.event_type <> 'RefundEvent'
        AND amz_shipment_events.posted_date BETWEEN '2016-5-1 07:00:00' AND '2016-5-7 06:59:59'

GROUP   BY amz_event_shipment_items.seller_sku

但返回的值太高了..。对我来说没道理..。

我有遗漏什么吗？

编辑

Many shipment_events for each date

Each shipment_event HAS MANY shipment_item / BELONGS TO ONE event

Each shipment_item HAS MANY shipment_fee  / BELONGS TO ONE item

Answer 1

这不是一个答案，而是一个附件。如果我正确理解，您的查询返回错误的结果，但速度相当快，而我的查询(带有EXISTS子句)返回正确的结果，但速度非常慢。

因此，消除重复的任务似乎花费了太多的时间。

这里有两个想法：

的第一个想法:立即消除重复

在加入前，我们不会把收费计算在内，而是把费用集中起来：

select 
  sum(i.quantity), 
  i.seller_sku
from amz_event_shipment_items i
join -- join with only one record per ID to substitute an EXISTS clause
(
  select distinct shipment_item_id
  from amz_event_fees
  where f.currency  = 'USD'
) f on f.shipment_item_id = i.id
and exists
(
  select *
  from amz_shipment_events e
  where e.event_type <> 'RefundEvent'
  and e.posted_date between '2016-05-01 07:00:00' and '2016-05-07 06:59:59'
  and e.id = i.shipment_event_id
)
group by i.seller_sku;

第二个想法:预聚合值

在这里，我们试图尽快进行聚合，以便使中间结果保持较小，而不必查找每个单项记录的events表。

select 
  sum(i.pre_sum_quantity), 
  i.seller_sku
from 
(
  select seller_sku, shipment_event_id, sum(quantity) as pre_sum_quantity
  from amz_event_shipment_items
  where exists
  (
    select *
    from amz_event_fees f
    where f.currency  = 'USD'
    and f.shipment_item_id = amz_event_shipment_items.id
  )
  group by seller_sku, shipment_event_id
) i
where exists
(
  select *
  from amz_shipment_events e
  where e.event_type <> 'RefundEvent'
  and e.posted_date between '2016-05-01 07:00:00' and '2016-05-07 06:59:59'
  and e.id = i.shipment_event_id
)
group by i.seller_sku;

如果只有少数几个事件类型，您也可以尝试去掉<>，从而使它更有可能使用索引：

where e.event_type in ('EarlyPaymentEvent','LatePaymentEvent')

(在这种情况下，可能需要在event_type位于posted_date之前的索引。)

我必须承认，我认为这些都不会比最初的EXISTS查询快多少，但可能值得一试。

Answer 2

可能您的一个连接正在返回比您预期的更多的记录。我试着做一个select *，然后用sku和聚光灯对结果进行排序。