mysql查询查找每日增减

qco9c6ql 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(194)

我是mysql的新手。我有一张table如下。它有库存水平为不同的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

sql mysql window-functions Date count

来源：https://stackoverflow.com/questions/62407882/mysql-query-to-find-daily-increase-or-decrease

2条答案

按热度按时间

r8uurelv1#

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`;

小提琴
如果某一行 sku 对某些人来说 date 则使用最近的上一个日期的值。
如果中间人 date 缺少值（如 2020-03-20 以及 2020-03-21 在示例数据中）则输出中也没有此日期。它可以在两个统计列的值都等于零的情况下进行综合添加。

赞(0）回复(0）举报 2021-07-26

bejyjqdl2#

假设每个sku每天都出现，您可以使用窗口函数（mysql 8.0中提供）和聚合：

select
    date,
    sum(stock < coalesce(lag_stock, 0)) decrease,
    sum(stock > coalesce(lag_stock, 0)) 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 < coalesce(lag_stock, 0)) decrease,
    sum(stock > coalesce(lag_stock, 0)) 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 or lag_date is null
group by date

赞(0）回复(0）举报 2021-07-26

我来回答

mysql查询查找每日增减

2条答案

相关问题

热门标签

最新问答