MySQL行之间的区别

Question

MySQL行之间的区别

3

我有一个 mysql 表格，内容如下：

日期产品 ID 销售量 ----- ---------- ---- 2013-04-20 09:00 ABC 10 2013-04-20 09:00 DEF 15 2013-04-20 09:00 HIJ 15 2013-04-20 10:00 ABC 5 2013-04-20 10:00 DEF 10 2013-04-20 10:00 HIJ 20

我想要得到每个产品每小时的销售量差异，并按照差异值从大到小排序，所以以上例子的结果如下：

2013-04-20 10:00 HIJ 5 2013-04-20 10:00 ABC -5 2013-04-20 10:00 DEF -5

我尝试了一些方法，比如将表格与自身连接，但是我做不对。请问该如何实现？

- Kint

你可以展示一下你的工作吗？ - Muhannad A.Alhariri

1

问题有点不清楚，不太清楚你想做什么。在您的预期结果中，HIJ不应该是5吗？因为它在10:00卖了20个，在09:00卖了15个？此外，如果我理解正确，您只是想显示每个产品行的小时销售差异和前一小时之间的差异吗？ - jesse_galley

是的，你说得对 - 对此我感到抱歉。我已经编辑了我的问题。 - Kint

3个回答

0

使用临时表很容易。首先通过以下方式对数据进行汇总：

CREATE TABLE sales_summary
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id CHAR(3) NOT NULL,
    date DATETIME NOT NULL,
    sold INT NOT NULL
)
SELECT
    NULL AS id,
    product_id,
    DATE_FORMAT(date, '%Y-%m-%d %H:00:00') date,
    SUM(sold) AS sold
FROM
    sales
GROUP BY
    product_id,
    DATE_FORMAT(date, '%Y-%m-%d %H:00:00')
ORDER BY
    1,2;

通过这个汇总表，你可以创建你的报告：

SELECT
    b.product_id,
    a.date AS prev_hour,
    b.date AS this_hour,
    b.sold - a.sold AS diff_sold,
    a.sold AS prev_sold,
    b.sold AS this_sold
FROM
    sales_summary a
INNER JOIN
    sales_summary b ON b.id = a.id + 1 AND b.product_id = a.product_id 
ORDER BY
    a.product_id,
    a.date DESC;

它返回：

+------------+---------------------+---------------------+-----------+-----------+-----------+
| product_id | prev_hour           | this_hour           | diff_sold | prev_sold | this_sold |
+------------+---------------------+---------------------+-----------+-----------+-----------+
| ABC        | 2013-04-20 10:00:00 | 2013-04-20 11:00:00 |        40 |        10 |        50 |
| ABC        | 2013-04-20 09:00:00 | 2013-04-20 10:00:00 |       -10 |        20 |        10 |
| DEF        | 2013-04-20 09:00:00 | 2013-04-20 10:00:00 |       -10 |        30 |        20 |
| HIJ        | 2013-04-20 09:00:00 | 2013-04-20 10:00:00 |        10 |        30 |        40 |
+------------+---------------------+---------------------+-----------+-----------+-----------+

一个可工作的示例在http://sqlfiddle.com/#!2/897d9/1/0处。

- Ross Smith II

0

这可以通过相关子查询简单地完成，尽管在大型表上可能不太高效：

select `date`, `productId`, ifnull(sold-(
  select sold
  from sales i
  where i.date < s.date
  and i.productId = s.productId
  limit 1
), sold) as diff
from sales s;

一个需要注意的地方：那个查询以及其他答案中的查询都依赖于一个前提条件：表中的数据已经按小时分组，这在大多数情况下并不适用。

这里是fiddle。

- jesse_galley

抱歉，如果销售不恰好在整点发生（http://sqlfiddle.com/#!2/3df18/2/0），这个答案就无效了。 - Ross Smith II

从技术上讲，是的，它可以，只是将整个小时标记为该小时的第一次出现。这可以通过在选择语句中用hour(time())包装时间戳来轻松解决。请记住，这个答案是唯一一个不假设所有数据已经按小时排列的答案。http://sqlfiddle.com/#!2/3df18/3 - jesse_galley

抱歉，但仍然不正确，http://sqlfiddle.com/#!2/d98f5/1 使用相同的输入数据，并且具有正确的值（ABC/上午11点为40，而不是30）。 - Ross Smith II

你是正确的，我没有注意到。在我有时间修复它之前，我已经删除了错误的查询。 - jesse_galley

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- fthiella · Accepted Answer

您可以使用以下查询语句：

SELECT t.date, t.productID, t.sold-tp.sold
FROM (
  SELECT t1.date, t1.productID, t1.sold, MAX(t2.date) date_prec
  FROM
    yourtable t1 INNER JOIN yourtable t2
    ON t1.productID=t2.productID AND t1.date>t2.date
  GROUP BY
    t1.date, t1.productID, t1.sold) t INNER JOIN yourtable tp
  ON t.productID=tp.productID and t.date_prec=tp.date

请查看这里的fiddle 链接。

在子查询中，我将yourtable与自身连接，使用相同的产品ID并满足条件t1.date>t2.date。通过按t1.date、productID和sold进行分组，您可以获得上一个日期时间，即MAX(t2.date)。然后，我再次将此子查询与yourtable连接，以获取前一天的sold值。编辑您可能还想使用这个：

SELECT t.date, t.productID, t.sold-tp.sold
FROM
  yourtable t INNER JOIN yourtable tp
  ON t.productID = tp.productID
     AND t.date = tp.date + INTERVAL 1 HOUR

在这里，我返回了前一个小时的sold和sold之间的差异。

Fiddle在这里。