mysql - 2 个表上的 INNER JOIN 返回错误值

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;

第二个想法:预聚合值

在这里，我们尝试尽快聚合，以使中间结果保持较小，而不必查找事件表中的每个单个项目记录。

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 快很多。查询，但可能值得一试。