优化MySQL查询以使用时间范围获取数据

Question

我正在使用Google Chart API构建产品销售报告的折线图，并使用UNION ALL将数据按天分离，基本上是为每一天运行相同的查询循环。例如，一整年的报告将重复查询，使用UNION ALL 365次。我猜这不是最佳实践的例子，如果有人能指点我如何优化这个查询，我将非常感激。

  SELECT SUM(op.qty) AS qty
  FROM uc_order_products op
  LEFT JOIN uc_orders o ON o.order_id = op.order_id
  LEFT JOIN node n ON n.nid = op.nid
  LEFT JOIN node_type nt ON nt.type = n.type
  WHERE (o.created > 1247695200) AND (o.created < 1247781600)
  AND (o.order_status = 'completed')
  AND (nt.type = 'book' OR nt.type = 'digital_movie')

  UNION ALL 

  SELECT SUM(op.qty) AS qty
  FROM uc_order_products op
  LEFT JOIN uc_orders o ON o.order_id = op.order_id
  LEFT JOIN node n ON n.nid = op.nid
  LEFT JOIN node_type nt ON nt.type = n.type
  WHERE (o.created > 1247781600) AND (o.created < 1247868000)
  AND (o.order_status = 'completed')
  AND (nt.type = 'book' OR nt.type = 'digital_movie')

  UNION ALL

  SELECT SUM(.......

---

感谢您的快速回复！根据jspcal提供的查询示例，我的查询最终变成了这样：

  SELECT SUM(op.qty) AS qty, DATE_FORMAT(FROM_UNIXTIME(o.created),'%d-%m-%Y') AS day
  FROM uc_order_products op
  LEFT JOIN uc_orders o ON o.order_id = op.order_id
  LEFT JOIN node n ON n.nid = op.nid
  LEFT JOIN node_type nt ON nt.type = n.type
  WHERE (o.created > 1247695200) AND (o.created < 1263596400)
  AND (o.order_status = 'completed')
  AND (nt.type = 'book' OR nt.type = 'digital_movie')
  GROUP BY day

使用PHP，我将完整的日期范围数组与查询结果中的strtotime($data->day)匹配的数组键组合起来，以获取没有销售的天数。

- Borgenk

---

也许我在这里漏掉了什么，但是为什么你不只运行一个查询，其中created >你的时间范围的开始和<你的时间范围的结束呢？ - Willis Blackburn

Answer 1

你可以按天分组：

select sum(op.qty) as qty, date_format(o.created, '%Y-%m-%d') as day
from ... where ... o.created >= subdate(now(), interval 1 year) and
o.created <= now() group by day order by day

将报告数据集中每天的总和

- jspcal

Answer 2

你应该能够转换

WHERE (o.created > 1247695200) AND (o.created < 1247781600)
AND (o.order_status = 'completed')
AND (nt.type = 'book' OR nt.type = 'digital_movie')


WHERE (o.created > 1247781600) AND (o.created < 1247868000)     
AND (o.order_status = 'completed')     
AND (nt.type = 'book' OR nt.type = 'digital_movie')

....

转换为类似于这样的内容

WHERE   (
                (o.created > 1247695200) AND (o.created < 1247781600)
            OR  (o.created > 1247781600) AND (o.created < 1247868000)
            ...
        )
AND (o.order_status = 'completed')
AND (nt.type = 'book' OR nt.type = 'digital_movie')

- Adriaan Stander