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

mm9b1k5b  于 2021-06-21  发布在  Mysql
关注(0)|答案(4)|浏览(303)

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

uoifb46i1#

您的问题与另一个依赖于“running total”上下文且不使用复杂功能的问题非常相似。它完全可以使用mysql@variables。。。

select
      t1.form_unique_identifier,
      t1.Total_Visitors,
      t1.total_drop_off,
      t1.total_Visitors - if( @lastFUI = t1.form_unique_identifier, @totDropOff, 0 ) as Remaining,
      @totDropOff := if( @lastFUI = t1.form_unique_identifier, @totDropOff + t1.total_drop_off, t1.total_drop_off ) as newDropOffTotal,
      @lastFUI := t1.form_unique_identifier
   from
      tab1 t1,
      ( select @lastFUI := '',
               @totDropOff := 0) SQLVars
   order by
      t1.form_unique_identifier,
      t1.field_number

sql fiddle答案
准备工作是通过一个查询声明mysql变量来完成的,只是将它们默认为空字符串和零。别名sqlvars没有连接,因为它只返回一行,并且永远不会导致笛卡尔结果。现在我们设定了值。。。最后一个窗体唯一标识符(@lastfui)和每个窗体的运行总丢弃量。
我包括额外的列来显示处理每一行时值的进程。很明显,您可以根据需要删除或添加更多内容。
行“t1.total\u visitors-if()”基本上是说明我所在的记录是否与最后一个表单id相同,然后从总数中减去最后一行中丢弃的记录数。如果是另一种形式,不要减去任何东西。
接下来,@totdropoff:=行基本上正在检查。。。如果我上一个窗体与我现在使用的窗体唯一id相同,我想将总累计放弃计数设置为等于上一个记录的总数加上现在放弃的总数。如果表单唯一id不同,只需将@totdropoff=此新记录上的衰减计数设置为在其后续表单记录上显示的基础。
只有在正确设置了累计总数之后,我们才希望将当前记录的表单唯一id保留到变量中,以便下次比较。
ORDERBY子句对于确保按照您在需求中指定的唯一id和字段号按顺序处理数据至关重要。
对于另一个类似的问题和解释(如果你需要一个稍微不同的方法),请查看这篇mysql文章

ovfsdjhp

ovfsdjhp2#

你在找什么 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视图

vnzz0bqm

vnzz0bqm3#

首先,你可以用前一个子句计算不包括第一行的累计和,然后从total\u visitor列中减去它。你可以试试下面的查询-

SELECT *, total_visitors - IFNULL(sum(total_drop_off)
     over(partition by (form_unique_identifier) order by field_number
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) totals
FROM tab1
GROUP BY form_unique_identifier, field_number, field_label,
         total_drop_off, total_visitors
ORDER BY form_unique_identifier;

这是小提琴-https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b8bd73ce6d971c590ba747c181de8469

djp7away

djp7away4#

您只需在 SELECT 条款:

SELECT t.*, total_visitors - (
    SELECT COALESCE(SUM(total_drop_off), 0)
    FROM yourdata AS x
    WHERE x.form_unique_identifier = t.form_unique_identifier AND x.field_number < t.field_number
) AS total_field_visitors
FROM yourdata AS t

你可以用上面的 UPDATE 通过细微的调整:

UPDATE yourdata AS toupdate
INNER JOIN (
    SELECT form_unique_identifier, field_number, total_visitors - (
        SELECT COALESCE(SUM(total_drop_off), 0)
        FROM yourdata AS x
        WHERE x.form_unique_identifier = t.form_unique_identifier AND x.field_number < t.field_number
    ) AS total_field_visitors
    FROM yourdata AS t
) AS calcdata ON toupdate.form_unique_identifier = calcdata.form_unique_identifier AND toupdate.field_number = calcdata.field_number
SET toupdate.total_field_visitors = calcdata.total_field_visitors

相关问题