V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
chendeshen
V2EX  ›  MySQL

求万能的 V2EX 拯救我吧~小白问一条初级 SQL

  •  
  •   chendeshen ·
    chendeshen · 2014-12-01 11:10:27 +08:00 · 3956 次点击
    这是一个创建于 3405 天前的主题,其中的信息可能已经有所发展或是发生改变。
    table: id(primary key), card_num, process_time
    不断有记录写进table,记录写入其写进数据库的时间为process_time
    求SQL: 输出所有 某同一card_num的每相邻两次记录的process_time大于一秒的记录

    例如: 第一条 卡号码121212 20140808 08:08:08
    第二条 卡号码343434 20140808 08:09:08
    第三条 卡号码565656 20140808 08:10:08
    第四条 卡号码121212 20140808 08:11:08
    e.g.第一条与第四条为同一张卡,20140808 08:08:08 到 20140808 08:11:08 大于一秒 ---》 so符合要求,最终输出这两条
    15 条回复    2014-12-02 04:24:59 +08:00
    RemRain
        1
    RemRain  
       2014-12-01 12:25:22 +08:00
    SELECT * FROM xxx
    然后在程序里面搞定
    zts1993
        2
    zts1993  
       2014-12-01 12:38:38 +08:00
    字查询和join 应该都可以

    select a.id,b.id FROM xxx a,xxx b where a.id='卡id' AND b.id='卡id' and b.process_time > a.process_time +1

    注意时间用函数替换一下。

    这只是个解决思路不是最优解,应该能找到更高效的写法,仅供参考。。

    错了不要喷我。
    phoneli
        3
    phoneli  
       2014-12-01 12:41:42 +08:00
    感觉这样的逻辑业务。不应该在用sql占用数据库。你就select * 出来,然后用shell处理一下。
    jianghu52
        4
    jianghu52  
       2014-12-01 12:57:35 +08:00
    我好奇的是相邻两条这个要求怎么在sql里面体现出来
    Automan
        5
    Automan  
       2014-12-01 12:59:39 +08:00
    其实你只要DISTINCT就可以了。。因为只要是不同的时间,那肯定是大于一秒
    msg7086
        6
    msg7086  
       2014-12-01 13:18:48 +08:00
    数据库里哪来相邻……
    7654
        7
    7654  
       2014-12-01 13:34:22 +08:00
    这样解决吧,数据库应该只提供数据,不做分析
    beginor
        8
    beginor  
       2014-12-01 14:14:30 +08:00
    对 MySql 不熟悉, 不过感觉用游标应该可以解决的;

    如果是 SQL Server 的话, 除了游标之外, 可以使用 LEAD/LAG 函数来解决 http://msdn.microsoft.com/zh-cn/library/hh231256.aspx
    loading
        9
    loading  
       2014-12-01 14:19:35 +08:00 via Android
    全选出来,order by id,process_time
    然后在程序里判断就好了。
    hcymk2
        10
    hcymk2  
       2014-12-01 14:23:41 +08:00
    select a.id ,b.id ,a.card_num from
    (select id ,process_time,card_num, @num1 := @num1 + 1 as row_number
    from (SELECT @num1:=1) r1, you_table_name order by card_num,process_time ) a ,
    (select id ,process_time,card_num, @num2 := @num2 + 1 as row_number
    from (SELECT @num2:=0) r2, you_table_name order by card_num,process_time) b
    where a.row_number=b.row_number and a.card_num=b.card_num and b.process_time-a.process_time>1;


    献丑了。
    ixiaohei
        11
    ixiaohei  
       2014-12-01 14:48:09 +08:00
    题主,如果想简单就@zts1993 说的来,不过表记录不多还好,多了效率超级低。

    最好还是@RemRain 说的,查出来自己程序里面处理,如果频繁的话,建议缓存
    oojiayu
        12
    oojiayu  
       2014-12-01 16:12:09 +08:00
    @hcymk2
    @ixiaohei
    @zts1993
    @RemRain
    你们都是数据库牛人,膜拜啊~
    jucelin
        13
    jucelin  
       2014-12-01 23:50:47 +08:00 via Android
    可以考虑提前预处理数据,例如插入前就计算出与上一次的时间差算放进库里去。
    lu18887
        14
    lu18887  
       2014-12-02 00:24:02 +08:00 via iPhone
    楼上说程序处理的各位仁兄,当数据量足够大,计算要求足够复杂的时候数据库的SQL还是不错的选择,前提是要能写对。
    jinghli
        15
    jinghli  
       2014-12-02 04:24:59 +08:00
    这是典型的SQL window function.不算是太小白的问题。我简单写了个postgresql可用的。

    SELECT
    card_num
    FROM
    (
    SELECT
    lag(process_time) OVER (PARTITION BY card_num ORDER BY process_time) AS prev_process_time,
    card_num,
    process_time
    FROM
    your_table
    ) x
    WHERE
    prev_process_time is NULL --not sure whether you need this
    OR (process_time - prev_process_time > '1 second'::interval)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3144 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 12:20 · PVG 20:20 · LAX 05:20 · JFK 08:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.