mysql - 优化 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) 相匹配)以获得没有销售的天数。

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

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