mysql累积滚动,MySQL滚动总和累积序列化

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

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

Formula is:

LivePlant = EarlyStock + production - dead

EndStock = LivePlant - Distri

Any Sugestion how I can do it?

解决方案

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

The basic gist of this technique is:

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.

Try the following code (

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