MySQL滚动总和累积序列化 [英] mysql rolling sum cumulative serialized

问题描述

例如，我有下面的表格(tb_transaction)

For example I have the bellow table (tb_transaction)

id_trans    date_trans  production_plant    dead_plant  distribution_plant
25          2017-12-31  1000                100             200
26          2018-01-17  150                 0               0
27          2018-02-07  0                   50              100
28          2018-03-07  250                 0               75
29          2018-05-10  500                 50              0

比起本年度，我尝试制作一个报表表，例如波纹管表

Than I try to make a report table for this Year, like bellow table

month   EarlyStock  production  dead    LivePlant   Distri  EndStock    
January                 150         0       150         0       150 
February                0           50      -50         100     -150    
March                   250         0       250         75      175 
April                   0           0       0           0       0   
May                     500         50      450         0       450 
June                    0           0       0           0       0   
July                    0           0       0           0       0   
August                  0           0       0           0       0   
September               0           0       0           0       0   
October                 0           0       0           0       0   
November                0           0       0           0       0   
December                0           0       0           0       0

1月的EarlyStock是2017年12月的EndStock(假定12月的EarlyStock是0)，这是tb_transaction的第一个数据，而2月的EarlyStock是1月的EndStock，依此类推.

Which EarlyStock for January is EndStock December 2017 (assumed EarlyStock for December is 0) which is first data from tb_transaction than EarlyStock for February is EndStock January and so on.

我的预期表是 比起本年度，我尝试制作一个报表表，例如波纹管表

My expected table is Than I try to make a report table for this Year, like bellow table

month   EarlyStock  production  dead    LivePlant   Distri  EndStock    
January     700         150         0       850         0       850 
February    850         0           50      800         100     700 
March       700         250         0       950         75      875 
April       875         0           0       875         0       875 
May         875         500         50      1325        0       1325    
June                    0           0       0           0       0   
July                    0           0       0           0       0   
August                  0           0       0           0       0   
September               0           0       0           0       0   
October                 0           0       0           0       0   
November                0           0       0           0       0   
December                0           0       0           0       0

公式为:

• LivePlant = EarlyStock +生产-已死
• EndStock = LivePlant-Distri

任何Sugestion我该怎么做?

Any Sugestion how I can do it?

在这里测试数据库小提琴

推荐答案

它看起来像是窗口函数，它可以以不太冗长的方式实现.在 MySQL 8.0.2及更高版本 .但是，由于您的 MySQL版本是5.6 ，我们可以使用用户定义的会话变量.

It looks like a Rolling Sum problem. It is doable in less verbose manner using Window Functions in MySQL 8.0.2 and onwards. But, since your MySQL version is 5.6, we can emulate this behavior using User-defined Session variables.

此技术的基本要点是:

• 首先，在派生表中，计算特定年份和月份中各种活动(如已死"，已分发"等)的总和值.就您而言，您拥有跨不同年份的数据，因此仅对月"进行分组的方法将行不通.您需要在年份和月份上进行分组依据.另外，仅将结果集限制为当前年度"也无济于事，因为您将需要从去年12月开始的期末库存价值，以便获得下一年1月的早期"库存价值.
• 现在，使用此子选择查询的结果集，并根据给定的定义确定最终库存"和早期库存".从概念上讲，这就像编写应用程序代码(例如:PHP)；我们将上一行的期末存货价值用作当前行的早期存货.最后，将结束库存"值设置为当前行的结束库存(后计算).
• 现在，由于您不希望与上一年相对应的行；我建议您可以忽略应用程序代码中的该行.仍然，如果您只想在查询中处理它；那么您将不得不再次将完整的结果集作为派生表"，并使用Where来过滤掉除当前年份以外的年份中的行.
• Firstly, in a Derived table, compute the aggregated sum value of various activities like Dead, Distributed etc for a particular Year and Month. In your case, you have data across various year(s), so your approach of doing a grouping on Month alone will not work. You need to Group By on both the year and month. Also, restricting the result-set to Current year alone will not help, as you will need End stock value from the December month of the previous year, in order to get Early stock value for the January month of the next year.
• Now, use this sub-select query's result set, and determine End Stock and Early Stock as per your given definition(s). Conceptually, it is like writing an application code (eg: PHP); we use the previous row's End stock value as Early stock for the current row. At the end, set End stock value to current row's end stock (post computation).
• Now, since you dont want the row corresponding to previous year; I would suggest that you can ignore that row in your Application code. Still if you want to handle it in query only; then you will have to take the complete result-set as a Derived table again, and use Where to filter out row(s) from year(s), other than the current year.

尝试以下代码( DB Fiddle演示 ):

Try the following code (DB Fiddle DEMO):

SELECT t1.year_no,
       t1.month_name,
       @early := @endst                             AS EarlyStock,
       @prod := t1.production                       AS production,
       @dead := t1.dead                             AS dead,
       ( @early + @prod - @dead )                   AS LivePlant,
       @dist := t1.distri                           AS Distri,
       @endst := ( @early + @prod - @dead - @dist ) AS EndStock
FROM   (SELECT Coalesce(Year(trans.date_trans), Year(CURRENT_DATE())) AS year_no,
               Coalesce(Month(trans.date_trans), mon.id_month) AS month_no,
               mon.month_name,
               Coalesce(Sum(trans.production_plant), 0)    AS production,
               Coalesce(Sum(trans.dead_plant), 0)          AS dead,
               Coalesce(Sum(trans.distribution_plant), 0)  AS Distri
        FROM   tb_month AS mon
               LEFT JOIN tb_transaction AS trans
                      ON Month(trans.date_trans) = mon.id_month
        GROUP  BY year_no,
                  month_no,
                  mon.month_name
        ORDER  BY year_no,
                  month_no) AS t1
       CROSS JOIN (SELECT @prod := 0,
                          @dead := 0,
                          @dist := 0,
                          @early := 0,
                          @endst := 0) AS user_init_vars  

这篇关于MySQL滚动总和累积序列化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！