时间戳

首页 > 解决方案 > 查找与按字段排序的先前记录的差异

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

问题描述

我正在使用 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

标签： mysqlsqlgroup-by

解决方案

您正在寻找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                   |

在 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

在 DB Fiddle 上查看

推荐阅读