mysql滚动和累计序列化

ldioqlga 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(401)

例如，我有下面的表（tb\u事务）

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

然后我试着做一个今年的报告表，比如下面的表格

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\ U交易的第一个数据，而2月的earlystock是endstock 1月，以此类推。
我期望的表格比我想做一个今年的报告表格，像下面的表格

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=早期库存+生产-死亡
endstock=liveplant-分布
有什么建议我怎么做吗？
这是测试用的小提琴

mysql serialization cumulative-sum

来源：https://stackoverflow.com/questions/52886896/mysql-rolling-sum-cumulative-serialized

1条答案

按热度按时间

kyks70gy1#

这看起来像一个滚动和问题。在mysql 8.0.2及更高版本中，使用窗口函数可以以不太冗长的方式实现。但是，由于您的mysql版本是5.6，我们可以使用用户定义的会话变量来模拟这种行为。
这项技术的基本要点是：
首先，在派生表中，计算特定年份和月份的各种活动（如死亡、分布等）的合计和值。在您的例子中，您有跨不同年份的数据，因此仅按月份进行分组的方法是行不通的。你需要按年份和月份分组。此外，仅将结果集限制为“当前年度”也无济于事，因为您需要上一年12月的期末股票价值，以便获得下一年1月的早期股票价值。
现在，使用这个子select查询的结果集，并根据给定的定义确定最终库存和早期库存。从概念上讲，这就像编写应用程序代码（例如：php）；我们使用前一行的结束库存值作为当前行的早期库存。最后，将结束库存值设置为当前行的结束库存（计算后）。
现在，由于您不希望行对应于上一年；我建议您可以忽略应用程序代码中的那一行。如果您只想在查询中处理它，则仍然是；然后您必须再次将完整的结果集作为派生表，并使用 Where 从年份（而不是当前年份）中筛选出行。
尝试以下代码（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

赞(0）回复(0）举报 2021-06-20

我来回答

mysql滚动和累计序列化

1条答案

相关问题

热门标签

最新问答