MySQL 使用数据透视行计算百分比

Question

我得到的产品销售表如下。(简化)

Prod_id Mon Sale                
1   1   102.54              
1   2   91.87               
1   3   106.76              
1   4   78.11               
2   1   1102.54             
2   2   191.87              
2   3   1226.76             
2   4   978.11              

我想计算以第 1 个月为基准的每种产品的销售额百分比。

Prod_id Mon Sale    Perc            
1   1   102.54  100 - pivot for product 1       
1   2   91.87   90          
1   3   106.76  104         
1   4   78.11   76          

2   1   1102.54 100 - pivot for product 2       
2   2   191.87  17          
2   3   1226.76 111         
2   4   978.11  89  

请建议如何在 mysql 中执行此操作。谢谢。

Answer 1

也许是这样的

drop table if exists t;
create table t
(Prod_id int,Mon int, Sale decimal(10,2));
insert into t values                
(1  , 1  , 102.54)  ,            
(1  , 2  , 91.87  ) ,            
(1  , 3  , 106.76  ),            
(1  , 4  , 78.11   ),            
(2  , 1  , 1102.54 ),            
(2  , 2  , 191.87  ),            
(2  , 3  , 1226.76 ),            
(2  , 4  , 978.11  );

select prod_id,mon,sale,
         if(prod_id <> @pprod , @per:= 100.00,@per:=(sale/@psale) * 100) per,
         @psale:=sale previoussale,
         @pprod:=prod_id prevprod
from t ,(select @pprod = 0,@psale = 0.00, @per=0.00) r
order by prod_id,mon     

+---------+------+---------+------------+--------------+----------+
| prod_id | mon  | sale    | per        | previoussale | prevprod |
+---------+------+---------+------------+--------------+----------+
|       1 |    1 |  102.54 | 100.000000 |       102.54 |        1 |
|       1 |    2 |   91.87 |  89.594305 |        91.87 |        1 |
|       1 |    3 |  106.76 | 116.207685 |       106.76 |        1 |
|       1 |    4 |   78.11 |  73.164106 |        78.11 |        1 |
|       2 |    1 | 1102.54 | 100.000000 |      1102.54 |        2 |
|       2 |    2 |  191.87 |  17.402543 |       191.87 |        2 |
|       2 |    3 | 1226.76 | 639.370407 |      1226.76 |        2 |
|       2 |    4 |  978.11 |  79.731162 |       978.11 |        2 |
+---------+------+---------+------------+--------------+----------+
8 rows in set (0.00 sec)

如果您想显示与第一次销售相比的百分比变化，请包含基线变量并修改计算

select prod_id,mon,sale,
         if(prod_id <> @pprod , @bl:=sale,@bl:=@bl) base,
            if(prod_id <> @pprod , @per:= 100.00,@per:=cast(((sale/@bl) * 100) as int)) per,
         @psale:=sale previoussale,
         @pprod:=prod_id prevprod
from t ,(select @bl:=0,@pprod = 0,@psale = 0.00, @per=0.00) r
order by prod_id,mon;

+---------+------+---------+---------+--------+--------------+----------+
| prod_id | mon  | sale    | base    | per    | previoussale | prevprod |
+---------+------+---------+---------+--------+--------------+----------+
|       1 |    1 |  102.54 | 102.54  | 100.00 |       102.54 |        1 |
|       1 |    2 |   91.87 | 102.54  |  90.00 |        91.87 |        1 |
|       1 |    3 |  106.76 | 102.54  | 104.00 |       106.76 |        1 |
|       1 |    4 |   78.11 | 102.54  |  76.00 |        78.11 |        1 |
|       2 |    1 | 1102.54 | 1102.54 | 100.00 |      1102.54 |        2 |
|       2 |    2 |  191.87 | 1102.54 |  17.00 |       191.87 |        2 |
|       2 |    3 | 1226.76 | 1102.54 | 111.00 |      1226.76 |        2 |
|       2 |    4 |  978.11 | 1102.54 |  89.00 |       978.11 |        2 |
+---------+------+---------+---------+--------+--------------+----------+
8 rows in set (0.00 sec)

如果您想更改之前的销售

select prod_id,mon,sale,
         if(prod_id <> @pprod , @bl:=sale,@bl:=@bl) base,
            if(prod_id <> @pprod , @per:= 100.00,@per:=cast(((sale/@bl) * 100) as int)) PerChangeFromFirstSale,
            if(prod_id <> @pprod , @per:= 100.00,@per:=cast(((sale/@psale) * 100) as int)) PerChangeFromPreviousSale,
         @psale:=sale previoussale,
         @pprod:=prod_id prevprod
from t ,(select @bl:=0,@pprod = 0,@psale = 0.00, @per=0.00) r
order by prod_id,mon ;    

+---------+------+---------+---------+--------- ---------------+-----------------

----------+--------------+----------+
| prod_id | mon  | sale    | base    | PerChangeFromFirstSale | PerChangeFromPreviousSale | previoussale | prevprod |
+---------+------+---------+---------+------------------------+---------------------------+--------------+----------+
|       1 |    1 |  102.54 | 102.54  |                 100.00 |                    100.00 |       102.54 |        1 |
|       1 |    2 |   91.87 | 102.54  |                  90.00 |                     90.00 |        91.87 |        1 |
|       1 |    3 |  106.76 | 102.54  |                 104.00 |                    116.00 |       106.76 |        1 |
|       1 |    4 |   78.11 | 102.54  |                  76.00 |                     73.00 |        78.11 |        1 |
|       2 |    1 | 1102.54 | 1102.54 |                 100.00 |                    100.00 |      1102.54 |        2 |
|       2 |    2 |  191.87 | 1102.54 |                  17.00 |                     17.00 |       191.87 |        2 |
|       2 |    3 | 1226.76 | 1102.54 |                 111.00 |                    639.00 |      1226.76 |        2 |
|       2 |    4 |  978.11 | 1102.54 |                  89.00 |                     80.00 |       978.11 |        2 |
+---------+------+---------+---------+------------------------+---------------------------+--------------+----------+
8 rows in set (0.00 sec)