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
orangeChu
V2EX  ›  MySQL

请教 V 友一个 SQL 问题

  •  
  •   orangeChu · 2019-08-08 21:21:45 +08:00 · 4098 次点击
    这是一个创建于 1913 天前的主题,其中的信息可能已经有所发展或是发生改变。
    有如下数据:

    | id | phoneNum | callType | created |
    | --- | ----------- | -------- | ---------------- |
    | 1 | 15000000000 | 1 | 2019-08-08 01:02 |
    | 2 | 15000000000 | 1 | 2019-08-08 02:03 |
    | 3 | 15000000000 | 2 | 2019-08-08 04:05 |
    | 4 | 15000000000 | 1 | 2019-08-08 06:07 |

    处理成:

    | phoneNum | callType | created |
    | -------------- | -------- | ---------------- |
    | 15000000000 | 1 | 2019-08-08 06:07 |
    | 15000000000 | 2 | 2019-08-08 04:05 |
    | 15000000000(2) | 1 | 2019-08-08 02:03 |

    ---

    将数据读出来后处理,遍历后处理成想要的数据没啥问题。
    但想试试用`SQL`来处理,没成功。
    想请教一下 V 友有啥好的思路吗?

    ---
    用的是`sqlite`,有`sqlite`语法最好 :)。先谢过~
    第 1 条附言  ·  2019-08-09 10:08:10 +08:00

    解决了。附上结果,也许哪天也有朋友需要做通讯录数据的处理,可以参考一下。

    select phoneNum, count(*) as cnt, callType, max(created) created
    from (select t.*,
                 sum(case when prev_phonenum = phoneNum and prev_type = callType and prev_created=date(created) then 0 else 1 end) over (order by id) as grp
          from (select t.*,
                       lag(callType) over (order by id) as prev_type,
                       lag(phonenum) over (order by id) as prev_phonenum,
                       lag(date(created)) over (order by id) as prev_created
                from t
               ) t
         ) t
    group by phoneNum, callType, grp
    order by id desc limit 80 offset 0
    
    4 条回复    2019-08-09 10:11:12 +08:00
    orangeChu
        1
    orangeChu  
    OP
       2019-08-08 21:23:56 +08:00
    为啥`md`的`table`语法没生效?
    Iamnotfish
        2
    Iamnotfish  
       2019-08-08 22:27:20 +08:00   ❤️ 1
    '''sqlite

    select group_concat(phoneNum), callType, created from table group by callType

    '''
    抛砖引玉一下,看看大佬怎么回答。PS : V2 的 MD 语法不支持 TABLE
    orangeChu
        3
    orangeChu  
    OP
       2019-08-08 23:27:01 +08:00 via iPhone
    @Iamnotfish 感谢回复,(我还没试您给的答案,将在明天尝试一下)最后自己搞定了。
    分享一下结果。(可能没有在描述中说明场景不是那么好搞,场景是这样的,记录来电记录,每次来电插入记录到数据库。展示数据时,按倒序来显示。上下两条号码相同时,根据 callType 来决定是否展示新的一行数据;不同号码时,直接展示该数据。展示结果类似手机上的通话记录~)
    ```
    select phonenum, count(*) as cnt, type, max(created)
    from (select t.*,
    row_number() over (partition by phonenum order by id) as seqnum,
    row_number() over (partition by phonenum, type order by id) as seqnum_t
    from t
    ) t
    group by phonenum, type, (seqnum - seqnum_t);
    ```
    orangeChu
        4
    orangeChu  
    OP
       2019-08-09 10:11:12 +08:00
    @Iamnotfish 尝试了一下,得到的结果好像不是我想要的。
    ---
    补充一下原来的题目:
    data
    | id | phoneNum | callType | created |
    | --- | ----------- | -------- | ---------------- |
    | 1 | 15000000000 | 1 | 2019-08-07 01:02 |
    | 2 | 15000000000 | 1 | 2019-08-07 02:03 |
    | 3 | 15000000000 | 2 | 2019-08-07 04:05 |
    | 4 | 15000000000 | 1 | 2019-08-07 05:07 |
    | 5 | 15000000000 | 2 | 2019-08-07 06:07 |
    | 6 | 15000000000 | 1 | 2019-08-07 06:20 |
    | 7 | 15000000000 | 1 | 2019-08-07 06:30 |
    | 8 | 15000000001 | 1 | 2019-08-07 07:07 |
    | 9 | 15000000000 | 1 | 2019-08-07 08:07 |
    | 10 | 15000000000 | 1 | 2019-08-08 08:07 |

    result
    | phoneNum | callType | created | count |
    | -------------- | -------- | ---------------- | ----- |
    | 15000000000 | 1 | 2019-08-07 02:03 | 2 |
    | 15000000000 | 2 | 2019-08-07 04:05 | 1 |
    | 15000000000 | 1 | 2019-08-07 05:07 | 1 |
    | 15000000000 | 2 | 2019-08-07 06:07 | 1 |
    | 15000000000 | 1 | 2019-08-07 06:30 | 2 |
    | 15000000001 | 1 | 2019-08-07 07:07 | 1 |
    | 15000000000 | 1 | 2019-08-07 08:07 | 1 |
    | 15000000000 | 1 | 2019-08-08 08:07 | 1 |
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2739 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 14:41 · PVG 22:41 · LAX 07:41 · JFK 10:41
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.