mysql - 查找与按字段排序的先前记录的差异

Question

我正在使用 MySQL 5.6。

这是我的源表:

form_unique_identifier  field_number    field_label         total_drop_off      total_visitors
FA11567953              0               Name                4                   100     
FA11567953              1               Dropdown            5                   100     
FA11567953              3               File                32                  100     
FA11567953              4               Multi select field  10                  100     

FA45345345              0               Name                1                   233     
FA45345345              11              Dropdown            7                   233             
FA45345345              31              File                2                   233         
FA45345345              44              Multi select field  3                   233     

FA45345356              2               Name                5                   77          
FA45345356              4               Dropdown            1                   77          
FA45345356              6               File                7                   77      
FA45345356              8               Multi select field  6                   77      

我正在尝试查找 total_field_visitors。

公式是——

total_field_visitors = total_visitors - total_drop_off of prior record

我需要它基于 form_unique_identifier 并按 field_number 排序。

我写了这个查询，但似乎效果不佳:

UPDATE table1 a
inner join table1 b
on a.form_unique_identifier = b.form_unique_identifier AND a.field_number < MIN(b.field_number)
SET a.total_field_visitors = a.total_visitors - b.total_drop_off 

如果是第一个字段，那么total_field_visitors应该设置为total_visitors。

这是我的输出表:

form_unique_identifier  field_number    field_label         total_drop_off      total_visitors   total_field_visitors
FA11567953              0               Name                4                   100              100
FA11567953              1               Dropdown            5                   100              96
FA11567953              3               File                32                  100              91
FA11567953              4               Multi select field  10                  100              59

FA45345345              0               Name                1                   233              233
FA45345345              11              Dropdown            7                   233              232
FA45345345              31              File                2                   233              225
FA45345345              44              Multi select field  3                   233              223

FA45345356              2               Name                5                   77               77
FA45345356              4               Dropdown            1                   77               72
FA45345356              6               File                7                   77               71
FA45345356              8               Multi select field  6                   77               64

Answer 1

你正在寻找具有窗口函数的SUM，但它只支持高于8.0的mysql版本。

还有另一种方法可以做到。

使用子查询来select 以使用窗口函数 执行SUM。

架构(MySQL v5.6)

CREATE TABLE table1 (
    form_unique_identifier varchar(50),
    field_number int,
    field_label varchar(50),
    total_drop_off int,
    total_visitors int  
);



INSERT INTO table1 VALUES ('FA11567953',0,'Name',4  ,100);    
INSERT INTO table1 VALUES ('FA11567953',1,'Dropdown',5  ,100);    
INSERT INTO table1 VALUES ('FA11567953',3,'File',32 ,100);    
INSERT INTO table1 VALUES ('FA11567953',4,'Multi select field',10 ,100);    
INSERT INTO table1 VALUES ('FA45345345',0 ,'Name',1,233);     
INSERT INTO table1 VALUES ('FA45345345',11,'Dropdown',7,233);             
INSERT INTO table1 VALUES ('FA45345345',31,'File',2,233);         
INSERT INTO table1 VALUES ('FA45345345',44,'Multi select field',3,233);     
INSERT INTO table1 VALUES ('FA45345356',2 ,'Name',5,77);          
INSERT INTO table1 VALUES ('FA45345356',4 ,'Dropdown',1,77);          
INSERT INTO table1 VALUES ('FA45345356',6 ,'File',7,77);      
INSERT INTO table1 VALUES ('FA45345356',8 ,'Multi select field',6,77);   

---

查询#1

SELECT 
  form_unique_identifier,
  field_number,
  field_label,
  total_drop_off,
  total_visitors,
  (total_visitors - prevVal) total_field_visitors 
FROM (
    SELECT t1.*,coalesce((
                 SELECT sum(total_drop_off) 
                 FROM table1 tt
                 WHERE tt.form_unique_identifier = t1.form_unique_identifier
                 and t1.field_number > tt.field_number    
                 order by tt.field_number DESC
            ),0)prevVal
    FROM table1 t1
) t1;

| form_unique_identifier | field_number | field_label        | total_drop_off | total_visitors | total_field_visitors |
| ---------------------- | ------------ | ------------------ | -------------- | -------------- | -------------------- |
| FA11567953             | 0            | Name               | 4              | 100            | 100                  |
| FA11567953             | 1            | Dropdown           | 5              | 100            | 96                   |
| FA11567953             | 3            | File               | 32             | 100            | 91                   |
| FA11567953             | 4            | Multi select field | 10             | 100            | 59                   |
| FA45345345             | 0            | Name               | 1              | 233            | 233                  |
| FA45345345             | 11           | Dropdown           | 7              | 233            | 232                  |
| FA45345345             | 31           | File               | 2              | 233            | 225                  |
| FA45345345             | 44           | Multi select field | 3              | 233            | 223                  |
| FA45345356             | 2            | Name               | 5              | 77             | 77                   |
| FA45345356             | 4            | Dropdown           | 1              | 77             | 72                   |
| FA45345356             | 6            | File               | 7              | 77             | 71                   |
| FA45345356             | 8            | Multi select field | 6              | 77             | 64                   |

---

View on DB Fiddle

---

如果你想做 UPDATE 只需使用 UPDATE ... JOIN

架构(MySQL v5.6)

CREATE TABLE table1 (
    form_unique_identifier varchar(50),
    field_number int,
    field_label varchar(50),
    total_drop_off int,
    total_visitors int,
    total_field_visitors int
);



INSERT INTO table1 VALUES ('FA11567953',0,'Name',4  ,100,0);     
INSERT INTO table1 VALUES ('FA11567953',1,'Dropdown',5  ,100,0);     
INSERT INTO table1 VALUES ('FA11567953',3,'File',32 ,100,0);     
INSERT INTO table1 VALUES ('FA11567953',4,'Multi select field',10 ,100,0);     
INSERT INTO table1 VALUES ('FA45345345',0 ,'Name',1,233,0);      
INSERT INTO table1 VALUES ('FA45345345',11,'Dropdown',7,233,0);              
INSERT INTO table1 VALUES ('FA45345345',31,'File',2,233,0);          
INSERT INTO table1 VALUES ('FA45345345',44,'Multi select field',3,233,0);      
INSERT INTO table1 VALUES ('FA45345356',2 ,'Name',5,77,0);           
INSERT INTO table1 VALUES ('FA45345356',4 ,'Dropdown',1,77,0);           
INSERT INTO table1 VALUES ('FA45345356',6 ,'File',7,77,0);       
INSERT INTO table1 VALUES ('FA45345356',8 ,'Multi select field',6,77,0);


UPDATE table1 a 
   JOIN (
     SELECT t1.*,coalesce((
                 SELECT sum(total_drop_off) 
                 FROM table1 tt
                 WHERE tt.form_unique_identifier = t1.form_unique_identifier
                 and t1.field_number > tt.field_number    
                 order by tt.field_number DESC
            ),0)prevVal
    FROM table1 t1
   ) b ON a.form_unique_identifier = b.form_unique_identifier
   AND a.field_number = b.field_number 
   AND a.field_label = b.field_label
   AND a.total_drop_off = b.total_drop_off
   SET a.total_field_visitors = b.total_visitors - b.prevVal

---

View on DB Fiddle