【已解决!见47#】【SQL数据库】编程难题问外野

Tips:点击图片进入下一页



LZ是非计算机专业的工科本科生。最近一个作业要处理19万行以上数据……用SQL做了半个上午,完全没有思路了,来求教。

【背景说明】
数据来自地铁运营数据,共5个字段。其中原始数据4个字段:MSID、TIME_STAMP、STATIONID、LINEID,还有一个附加的序号字段id。
MSID=用户编号
TIME_STAMP=时间戳
STATIONID=站点编号
LINEID=地铁线路编号


原始数据节选展示(表格,手机端可能显示错误):
MSIDTIMESTAMPSTATIONIDLINEID00045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:14:18559200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:14:1817195


【需求描述】对MSID、TIME_STAMP、STATIONID相同,但LINEID不同的数据进行清洗,规则为:
(1)如LINEID均大于90,删除LINEID小的一条记录;
    例:
    MSID   TIME_STAMP   STATIONID   LINEID  id
     XX           YY                  55               91     a
     XX           YY                  55               92     a+1
    删除id=a的一行

(2)如LINEID均小于20,删除与id-1的LINEID一样的记录;
    例:
    MSID   TIME_STAMP   STATIONID   LINEID  id
     XX           MM                 89               1       a
     XX           YY                  55               2       a+1
     XX           YY                  55               1       a+2
    删除id=a+2的一行


(3)如一行LINEID>90,另一行LINEID<20,删除与id-1的LINEID一样的记录。
    例:
    MSID   TIME_STAMP   STATIONID   LINEID  id
     XX           MM                 89               2       a
     XX           YY                  55               2       a+1
     XX           YY                  55              91      a+2
    删除id=a+1的一行



清洗的目的是:判断换乘情况。
该数据来自采集的手机信令数据。在此过程中,可能发生乘客在某时点同时被判定为不同状态(既处在1号线也处在2号线上)的情况。因此需要清洗。
LINEID的编号规则是:小于20的LINEID均表示地铁线路,大于90的LINEID表示乘客位于站台/地面上。

关于id列意义的补充说明:

【工作进度】
目前把所有“MSID、TIME_STAMP、STATIONID相同,但LINEID不同的数据”选出来了,然后进行不下去。运行结果如下。
。还想好好过个五一节。拜托各位了。



网友评论:
SQL不会……如果可以生成文本文件或者表格文件的话,用随便哪种编程语言写个脚本或许可以?
读入文本文件——搜索含有某个字符串的行——对该行上下的行进行处理(用3个if表示三种情况)——生成清洗后的文档,这样?


去看看group by 和max min函数一起总就完了


我提供个大概思路,不知道可不可行。
1. MSID、TIME_STAMP、STATIONID相同 <- 用这个条件分组, group by
2. 你的lineId有三个区间,那么可以通过having 来筛选,比如 group by (MSID, TIME_STAMP, STATIONID) having lineId > 90
3. 在加上你想要保留的记录的条件(这个保留条件,没看太懂)
4. 我的建议是用上面几条筛选出来要的记录,创建出新表,再把新表组合起来

看了前面的回复,这几个条件用Java,Python几下就完事了


(1)我现在正在下载sql

lz等一下

(2)sql express 版已就位

(3)实验表已建立,(主键不会没有吧
十几万条的数据,导出CSV,用python随便玩。。。
数据库就用来存储数据就行了,复杂的逻辑处理是想把机器弄死机吗

数据取出来,外面处理了再传回去
十几万条数据很少的
19万没有关联没叉积
直接排好序取上来内存里面随便玩啊
1就是最普通关联两个表删东西的sql就好形如
delete a from mytable a,mytable b where a.x1 = b.x1 and a.x2=b.x2 and a.x3>90 and b.x3>90 and a.x3

  -


首先说明,时间点是完全一致还是只要在一个窗口内都算?

如果是前者,直接先按照(msid, ts)做groupby,然后对于每个group进行count,>=2的filter吊

如果是后者,先groupby msid,然后group内sort by ts,在group内自己和自己join,要是时间差小于某个阈值则filter。

当然要是我我肯定导出来直接放到notebook里面算了,这点儿数据量。。。


不建议用在sql里面写业务.
    delete form [轨道指定]  where (MSID,TIME_STAMP,STATIONID,LINEID) in (
    select A.MSID,A.TIME_STAMP,A.STATIONID,A.LINEID,A.id  from [轨道指定] A inner join [轨道指令] B on  a.MSID =b.MSID and a.TIME_STAMP = b.TIME_STAMP and a.STATIONID = b.STATIONID
    and (
    (a.LINEID >  90  and b.LINEID >90 and a.LINEID or (a.LINEID <20 and b.LINEID <20 and a.id = b.id -1 )
    or(  ( a.LINEID>90 and b.LINEID<20) or (a.LINEID<20 or b.LINEID >90) and a.id = b.id -1 )
    ))
复制代码
hibernate spring data打酱油路过

—— 来自 Xiaomi Redmi Note 7, Android 9上的 v2.1.2
每一行1k,一共190m,读到内存里随便写吧
如果可以用C#的话直接用LINQ处理就行,语法很简单。现在五一放假电脑不在手边,可能要五月八号才能给出代码,不知道楼主是否等得住

—— 来自 samsung SM-C7010, Android 8.0.0上的 v2.1.2


看了半天……
1. ID在语义上是PK还是 (MSID, ID)两个composite PK?
2. MSID TIME_STAMP STATIONID相同的行,是否保证最多只有2行?

你比较需要的是 ROW_NUMBER() OVER (PARTITION BY MSID, TIME_STAMP, STATIONID ORDER BY LINEID)

id好像不是唯一的吧?我觉得这么写会删掉太多东西了

已改正
学习一下
需求2,3我都没看懂

(2)如LINEID均小于20,删除与id-1的LINEID一样的记录;
    例:
    MSID   TIME_STAMP   STATIONID   LINEID  id
     XX           MM                 89               1       a
     XX           YY                  55               2       a+1
     XX           YY                  55               1       a+2
    删除id=a+2的一行

这里a+2的LINEID和a+1的LINEID不一样啊?为啥要删除a+2这一行


(3)如一行LINEID>90,另一行LINEID<20,删除与id-1的LINEID一样的记录。
    例:
    MSID   TIME_STAMP   STATIONID   LINEID  id
     XX           MM                 89               2       a
     XX           YY                  55               2       a+1
     XX           YY                  55              91      a+2
    删除id=a+1的一行

这里a+1的LINEID和a+2的LINEID也不一样啊?为啥删除a+1
每个DSL都有颗图灵完备的心(
100k为什么不导出用Python做?

写的很好,但是是不是其中乱入了一段CSS代码啊。。。在01行


另外delete from打错了 hhhhh
整理了一下:

DELETE FROM [轨道指定]  
WHERE (MSID,TIME_STAMP,STATIONID,LINEID) IN (   
    SELECT a.MSID, a.TIME_STAMP, a.STATIONID, a.LINEID, a.id  
    FROM [轨道指定] a INNER JOIN [轨道指令] b ON a.MSID =b.MSID AND a.TIME_STAMP = b.TIME_STAMP AND a.STATIONID = b.STATIONID
    AND (   (a.LINEID >  90  AND b.LINEID >90 AND a.LINEID             OR (a.LINEID <20 AND b.LINEID <20 AND a.id = b.id -1 )
            OR( ( a.LINEID>90 AND b.LINEID<20) OR (a.LINEID<20 OR b.LINEID >90) AND a.id = b.id -1 )
        )
)

1,按判断条件的那三条出排序。2,按顺序读取一个你可以接受的数据量进内存,注意不能断在重复的地方。3,用你喜欢的任何高级语言按你要求的逻辑处理数据。4,写去另一个表,循环1到4至处理完
用python的pandas包可以做,数据量不大,能直接读内存

  -
又想了下既然你就19万条,那你一定不介意数据库访问次数,那么第二步只需要读前三条一致的行进内存就行,没一致就读1行转存,有的话读到下一条不再一致为止
同上,sql取出数据之后扔到高级语言里处理逻辑,对自己对后来人都方便
你做不出来的一个问题是,主楼的逻辑可能是有问题的,比如lineid是否约定好了只要前三列重复那么必然是a, a+1, a+2这样排列?不是的话后面两种情况按你那个逻辑就会找不到要删的lineid
上一楼我看错了,手机不能编辑所以请无视吧,不过问题还是存在的,你那个id是否能保证前三列一样的数据一定是顺序排好的?
用ETL工具抽取出来,加载到其他系统里,用熟悉的语言编程

—— 来自 HUAWEI VCE-AL00, Android 9上的 v2.1.2


按你这个意思
第一个情况只删id最小的?

    select
       *
    from
       (
          select
             msid,
             timestamp,
             station_id,
             lineid,
             id,
             lag(lineid) over w previous_lineid,
             lag(id) over w previous_id,
             max(lineid) over w m1,
             min(lineid) over w m2
          from
             t
             window w as
             (
                partition by msid,
                timestamp,
                station_id
             order by
                id
             )
       )
       t
    where
       (
          m2 > 90
          and lineid = m2
       )
       or
       (
          m1 < 20
          and id - 1 = previous_id
          and lineid = previous_lineid
       )
       or
       (
          m2 > 20
          and m1 < 90
          and id - 1 = previous_id
          and lineid = previous_lineid
       )
复制代码

是时候用window function了,只扫一次表 干净 无膨胀
语法默认是 MySQL 的
假DBA查了一下文档, window function 是在 having 之后执行的,所以只能套一层,那你要删数据的话 扫第二次表是无法避免的了 只能写成 in 子查询的形式。当然这些写肯定是不会有任何膨胀发生的。


抱歉,关于id列的事情我先说明下。确实PPT上的这个描述我也没看懂,是问同学问懂的。意思就是说:

首先数据是以MSID为主key,TIME_STAMP为副key排过序的。就是说同一人的记录在一起,并按时间顺序排。
当检索到MSID,TIME_STAMP,STATIONID均相同的记录时(可以确定如果有只会有2条)——
(1)两条记录中有一条的LINEID小于20,则——
  看这两条记录的,【再】上面一行A(就是说总共要看3行记录),将两条记录中LINEID等于A的LINEID的一行删除。
(2)两条记录的LINEID均大于90,则——
  删除两条记录中LINEID小的一行。

at下问过的人:
@女神アイギス
@maxwell_goblin
然后我会的语言只有:SQL,VB,C++。确实在考虑如果五一结束都折腾不出来我就换VB试试……
现在我在尝试把SQL2008 R2升级成SQL2012看看能不能运行34# @winoros  的代码。因为2008没有lag函数。


主key副key排序,用sql来描述,是说原始数据ORDER BY MSID, TIME_STAMP的意思吗?

我的意思是ID这个字段在语义上是否是主键,是不是说一个ID对应唯一的一行数据? 比如这是你建表时候的一个自增列?
看例子的意思,应该是一组(MSID, ID)确定了一行,如果LINEID有小于20的,删的是同MSID的id - 1对吧

谢谢你的回复,写得很认真……但是有两个问题。
大问题是逻辑问题,这点是我表述不清造成的,很抱歉,您感兴趣的话可以看看33# 对删除规则的进一步解释。
小问题是sql2008 R2运行时似乎where和in之间的字段名只能有1个。我把开头代码改成了:
delete form [轨道指定]  where id in (
select A.id  from ...
就解决了。

您好,主键可以设成id的。虽然我好像是没有设置……

完全一致。
我看好多层都提到了groupby...我明天理解理解。



对!作用就相当于excel表前面默认的序号。我是在sql里用

alter table [轨道信令].[dbo].[4$] add id int identity (1,1)
go

建立的。

谢谢您的心意了!但是作业是5.9提交。主楼想解决的数据清洗问题之后还有数据分析和写报告的事情,所以我还是问问坛友和同学早点解决吧
晚上有安排,今天不能写作业了。这个帖子我明天开始做作业再继续回复。
今天先谢谢楼上的大家的帮助了!
一定要用sql就上表变量和游标啊,数据又不多

  -
3L都已经告诉你答案了,建议先学习一下语文,表述不是特别有条理,看着很费劲。

三种情况分别筛选一下,填Nan删掉最大或者最小,最后再drop nan就好了
有点喝多了,如果错了请见谅。
建议生成临时表,把需要删掉的ID单独提出来,然后对着删就行。
select
case
        when a.LINEID >= 90 and b.LINEID >= 90 and a.LINEID > b.LINEID then b.ID
        when a.LINEID >= 90 and b.LINEID >= 90 and a.LINEID < b.LINEID then a.ID
        when a.LINEID <= 20 and b.LINEID <= 20 and a.ID < b.ID then a.ID
        when a.LINEID <= 20 and b.LINEID <= 20 and a.ID < b.ID then b.ID
        when a.LINEID >= 90 and b.LINEID <= 20  then b.ID
        when a.LINEID <= 20 and b.LINEID >= 90  then a.ID
end as ID
from
(select
        ID,
        MSID,
        TIME_STAMP,
        STATIONID,
        LINEID
from
        data_source)a
left join
(select
        ID,
        MSID,
        TIME_STAMP,
        STATIONID,
        LINEID
from
        data_source)b
on
a.MSID          =    b.MSID
AND
a.TIME_STAMP    =    b.TIME_STAMP
AND
a.STATIONID     =    b.STATIONID
AND
a.LINEID        <>   b.LINEID
还有补充一点,别想太多,数据清洗/数据分析/写报告其实是三件事。。不洗的话也能分析,不分析的话也能靠讲故事写报告。。。手工修就是了。不会用Python/R的话就去学啊!两天足够了!

原来 SQL server 08年的时候也没有window function

反正数据集小的话,怎么写其实都可以,只要不要写出笛卡尔积,肯定没问题
折腾了半个上午想安装SQL2012但一直报错,最终用没有lag函数的2008,在12#的基础上写出来了。
方法比较笨,大概是这个步骤:
(1)先清洗LINEID均大于90的ID:
    delete from [轨道信令].[dbo].[4$]  where id in (
    select A.id  from [轨道信令].[dbo].[4$] A inner join [轨道信令].[dbo].[4$] B on  a.MSID =b.MSID and a.TIME_STAMP = b.TIME_STAMP and a.STATIONID = b.STATIONID
    and (
    (a.LINEID >  90  and b.LINEID >90 and a.LINEID ))
复制代码结果:0行受影响。

(2)再对后两种情况进行清洗。
先建立一个新表nqid,存放需清洗行的id信息和rownum信息。
    select A.id   
    into [轨道信令].[dbo].[qid] from [轨道信令].[dbo].[4$] A inner join [轨道信令].[dbo].[4$] B
    on  a.MSID =b.MSID and a.TIME_STAMP = b.TIME_STAMP and a.STATIONID = b.STATIONID and a.LINEID != b.LINEID  
    order by a.id

    select  ROW_NUMBER() over(order by id) as rownum,id into nqid from [轨道信令].[dbo].[qid]
复制代码rownum id 1107 2108 3109 4110 ...... 48460...


(3)然后拿nqid里的id到原表去,两两一组,和每组的上一行比对,删除每组中LINEID与上一行相等的一行。
    declare @rowid int, @rowcount int
    set @rowcount=1

    while (@rowcount<=48460)
    begin
    select @rowid=id from [nqid] where rownum=@rowcount
    delete from [4$] where id =@rowid
    and ((LINEID =(select LINEID from [4$] where id =@rowid -1)
    and @rowcount %2!=0 )
    or ((LINEID =(select LINEID from [4$] where id =@rowid -2)and @rowcount %2=0)
    ))
    set @rowcount=@rowcount+1

    end
复制代码

上面这段代码跑了十几秒被我停掉看了下,确认是正确的。
然后我就开始敲这层楼,敲到现在已经跑了13分钟了,还在查询。不过总算是解决了。感谢。承诺的鹅我会慢慢加上的。
因为好像很多层都看不懂需求到底是什么,我多截取一些原始数据和教师的PPT说明附在下一楼。
原始数据前20行(含表头):

MSIDTIMESTAMPSTATIONIDLINEID00045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:14:181719200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:14:18559500045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:14:4855400045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:18:23105400045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:21:5932400045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:25:34215400045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:29:10137400045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:32:45166400045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:32:45166200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:36:27154200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:40:10167200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:43:52114200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:47:35232200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:54:1390200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:54:1390920006F2BC40D97685A19EABF9C66C459E2013/10/21 星期一 上午 9:02:41114910006F2BC40D97685A19EABF9C66C459E2013/10/21 星期一 上午 9:12:3911420006F2BC40D97685A19EABF9C66C459E2013/10/21 星期一 上午 9:20:2323220006F2BC40D97685A19EABF9C66C459E2013/10/21 星期一 上午 9:20:2323292
加上主键id列:
MSIDTIMESTAMPSTATIONIDLINEIDid00045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:14:1817192100045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:14:185595200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:14:48554300045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:18:231054400045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:21:59324500045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:25:342154600045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:29:101374700045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:32:451664800045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:32:451662900045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:36:2715421000045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:40:1016721100045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:43:5211421200045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:47:3523221300045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:54:139021400045AAF28A0F2ACA492163E6678147D2013/10/21 星期一 上午 8:54:139092150006F2BC40D97685A19EABF9C66C459E2013/10/21 星期一 上午 9:02:4111491160006F2BC40D97685A19EABF9C66C459E2013/10/21 星期一 上午 9:12:391142170006F2BC40D97685A19EABF9C66C459E2013/10/21 星期一 上午 9:20:232322180006F2BC40D97685A19EABF9C66C459E2013/10/21 星期一 上午 9:20:232329219

教师PPT说明
【已解决!见47#】【SQL数据库】编程难题问外野


也就是说,在上面列出的前20行中,id=8,id=14,id=18的行需要删除。
而如果id=14,id=15两行的LINEID交换,则此时它们之间需要删除的是id=15的行。