查找每日增减的mysql查询

Question

我是MySQL的新手。 我有一张如下的桌子。 它具有每天存储的不同SKU的库存水平。 我正在尝试编写一个查询，该查询输出在若干天内每天有多少SKU增加了库存和多少减少了库存水平。 我的主要问题是找出任何两个日期之间每日股票水平的差异，并将其分类为增加或减少。 表中不存储每日增减金额。 如有任何帮助/指导，将不胜感激。

表:

+------------+---------+-------+
| Date       | SKU     | Stock |
+------------+---------+-------+
| 2020-03-23 | SKU1001 | 23149 |
| 2020-03-23 | SKU1002 | 29218 |
| 2020-03-23 | SKU1003 | 14827 |
| 2020-03-23 | SKU1004 |  8852 |
| 2020-03-23 | SKU1005 | 47362 |
| 2020-03-23 | SKU1006 |  3572 |
| 2020-03-23 | SKU1007 |  8744 |
| 2020-03-23 | SKU1008 | 22788 |
| 2020-03-23 | SKU1009 | 41897 |
| 2020-03-23 | SKU1010 | 28245 |
| 2020-03-22 | SKU1001 | 18326 |
| 2020-03-22 | SKU1002 | 23123 |
| 2020-03-22 | SKU1003 | 48501 |
| 2020-03-22 | SKU1004 | 44070 |
| 2020-03-22 | SKU1005 |  3160 |
| 2020-03-22 | SKU1006 | 46216 |
| 2020-03-22 | SKU1007 |  1620 |
| 2020-03-22 | SKU1008 |  3314 |
| 2020-03-22 | SKU1009 | 32254 |
| 2020-03-22 | SKU1010 |  1442 |
| 2020-03-19 | SKU1001 | 40992 |
| 2020-03-19 | SKU1002 | 31477 |
| 2020-03-19 | SKU1003 | 22976 |
| 2020-03-19 | SKU1004 | 26858 |
| 2020-03-19 | SKU1005 | 32397 |
| 2020-03-19 | SKU1006 | 37801 |
| 2020-03-19 | SKU1007 | 19530 |
| 2020-03-19 | SKU1008 | 35202 |
| 2020-03-19 | SKU1009 | 11723 |
| 2020-03-19 | SKU1010 | 21201 |
| 2020-03-18 | SKU1001 |  7449 |
| 2020-03-18 | SKU1002 | 10404 |
| 2020-03-18 | SKU1003 | 34944 |
| 2020-03-18 | SKU1004 |  5696 |
| 2020-03-18 | SKU1005 | 14732 |
| 2020-03-18 | SKU1006 |  9916 |
| 2020-03-18 | SKU1007 | 46623 |
| 2020-03-18 | SKU1008 |  6755 |
| 2020-03-18 | SKU1009 | 42848 |
| 2020-03-18 | SKU1010 |  5209 |
| 2020-03-17 | SKU1001 | 31777 |
| 2020-03-17 | SKU1002 | 36504 |
| 2020-03-17 | SKU1003 | 43737 |
| 2020-03-17 | SKU1004 | 27706 |
| 2020-03-17 | SKU1005 | 12099 |
| 2020-03-17 | SKU1006 | 39922 |
| 2020-03-17 | SKU1007 |  4897 |
| 2020-03-17 | SKU1008 | 14773 |
| 2020-03-17 | SKU1009 | 20108 |
| 2020-03-17 | SKU1010 | 40094 |
| 2020-03-16 | SKU1001 | 15459 |
| 2020-03-16 | SKU1002 | 39511 |
| 2020-03-16 | SKU1003 | 13586 |
| 2020-03-16 | SKU1004 | 29648 |
| 2020-03-16 | SKU1005 | 41381 |
| 2020-03-16 | SKU1006 | 27868 |
| 2020-03-16 | SKU1007 |  4220 |
| 2020-03-16 | SKU1008 | 22182 |
| 2020-03-16 | SKU1009 |  9079 |
| 2020-03-16 | SKU1010 | 33130 |
| 2020-03-15 | SKU1001 | 29597 |
| 2020-03-15 | SKU1002 | 41033 |
| 2020-03-15 | SKU1003 | 40937 |
| 2020-03-15 | SKU1004 | 34551 |
| 2020-03-15 | SKU1005 |  7283 |
| 2020-03-15 | SKU1006 | 40625 |
| 2020-03-15 | SKU1007 |  7935 |
| 2020-03-15 | SKU1008 | 30623 |
| 2020-03-15 | SKU1009 | 27591 |
| 2020-03-15 | SKU1010 |  7633 |
| 2020-03-12 | SKU1001 | 21712 |
| 2020-03-12 | SKU1002 | 11933 |
| 2020-03-12 | SKU1003 | 25913 |
| 2020-03-12 | SKU1004 | 33388 |
| 2020-03-12 | SKU1005 | 44811 |
| 2020-03-12 | SKU1006 | 10177 |
| 2020-03-12 | SKU1007 |  4748 |
| 2020-03-12 | SKU1008 | 48676 |
| 2020-03-12 | SKU1009 | 44767 |
| 2020-03-12 | SKU1010 | 33986 |
| 2020-03-11 | SKU1001 |  9156 |
| 2020-03-11 | SKU1002 | 48079 |
| 2020-03-11 | SKU1003 |  8815 |
| 2020-03-11 | SKU1004 | 15756 |
| 2020-03-11 | SKU1005 |  4446 |
| 2020-03-11 | SKU1006 | 40009 |
| 2020-03-11 | SKU1007 | 15591 |
| 2020-03-11 | SKU1008 | 12904 |
| 2020-03-11 | SKU1009 | 34635 |
| 2020-03-11 | SKU1010 | 20042 |
| 2020-03-10 | SKU1001 | 11811 |
| 2020-03-10 | SKU1002 | 26257 |
| 2020-03-10 | SKU1003 | 11387 |
| 2020-03-10 | SKU1004 | 30888 |
| 2020-03-10 | SKU1005 | 12192 |
| 2020-03-10 | SKU1006 |  5236 |
| 2020-03-10 | SKU1007 | 26115 |
| 2020-03-10 | SKU1008 | 34821 |
| 2020-03-10 | SKU1009 | 15294 |
| 2020-03-10 | SKU1010 |  3344 |
+------------+---------+-------+

所需输出:

Date        Decrease    Increase
2020-03-10                10
2020-03-11         6       4
2020-03-12         3       7
2020-03-15         4       6
2020-03-16         8       2
2020-03-17         4       6
2020-03-18         7       3
2020-03-19         3       7
2020-03-22         6       4
2020-03-23         3       7

Answer 1

WITH
cte1 AS ( SELECT DISTINCT `date`
          FROM test ),
cte2 AS ( SELECT DISTINCT sku
          FROM test ),
cte3 AS ( SELECT cte1.`date`, 
                 cte2.sku, 
                 COALESCE( stock,
                           FIRST_VALUE(stock) OVER (PARTITION BY cte2.sku 
                                                    ORDER BY cte1.`date` DESC
                                                    ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), 
                           0) stock
          FROM cte1
          CROSS JOIN cte2
          LEFT JOIN test ON cte1.`date` = test.`date`
                        AND cte2.sku = test.sku ),
cte4 AS ( SELECT `date`, 
                 sku, 
                 stock,
                 COALESCE( LAG(stock) OVER (PARTITION BY sku
                                            ORDER BY `date`),
                           0 ) lag_stock 
          FROM cte3)
SELECT `date`,
       SUM(stock < lag_stock) Decrease,
       SUM(stock > lag_stock) Increase
FROM cte4
GROUP BY `date`;

小提琴

如果某个date的特定SKU的行不存在，则使用最近的上一个日期的值。

如果缺少某个中间的date值（如示例数据中的2020-03-20和2020-03-21)，则输出中也缺少该日期。

Answer 2

假设每一个SKU每天都出现，您可以使用窗口函数（在MySQL8.0中可用）和聚合:

select
    date,
    sum(stock < lag_stock) decrease,
    sum(stock > lag_stock) increase
from (
    select t.*, lag(stock) over(partition by sku order by date) lag_stock
    from mytable t
) t
group by date

我们可以使用一个附加条件来过滤掉存在间隙的SKU:

select
    date,
    sum(stock < lag_stock) decrease,
    sum(stock > lag_stock) increase
from (
    select 
        t.*, 
        lag(stock) over(partition by sku order by date) lag_stock,
        lag(date) over(partition by sku order by date) lag_date
    from mytable t
) t
where date = lag_date + interval 1 day
group by date