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

这样的需求,一条 sql 语句没法搞定吧

  •  
  •   enenaaa · 2016-12-08 17:10:25 +08:00 · 4095 次点击
    这是一个创建于 2886 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有这样一个表

    
    |id|logtime|
    |----| ----|
    | 1 | 5 |
    | 2 | 6 |
    | 3 | 6 |
    | 4 | 8 |
    | 5 | 8 |
    

    现在需要按 logtime 分组查询,每行求 logtime 小于等于当前值的 id 数。 例如 logtime=8 这行,求 logtime <= 8 的 count(id) 值。 最终结果是这样:

    
    |count|logtime|
    |----|----|
    | 1 | 5 |
    | 3 | 6 |
    | 5 | 8 |
    
    

    在一条 sql 语句内完成整表查询,不能用存储过程,不能用临时表,表变量,存储函数。 按我的理解,数据库查询是每行扫描一次。这个查询里一行需要重复扫描多次,应该是没法一次搞定的。 诸位有何良策。

    14 条回复    2016-12-09 13:49:34 +08:00
    sagaxu
        1
    sagaxu  
       2016-12-08 17:34:19 +08:00 via Android
    select logtime,count(*) as cnt from t group by logtime order by logtime

    然后业务代码里,对 cnt 值进行迭代累加,遍历一次即可,业务代码算法复杂度 O(n)
    liprais
        2
    liprais  
       2016-12-08 17:41:59 +08:00
    不用 mysql 就行了
    sgzhan
        3
    sgzhan  
       2016-12-08 17:56:16 +08:00
    为什么不能
    select logtime, count(id) as cnt from t where id<=logtime group by logtime 不就符合你的需求么?
    enenaaa
        4
    enenaaa  
    OP
       2016-12-08 17:59:50 +08:00
    @sgzhan id 和 logtime 之间没有对应联系。 而且你这个语句也不对, group by 是用 logtime 分组,不包含小于当前值的行
    akira
        5
    akira  
       2016-12-08 18:07:03 +08:00   ❤️ 1
    select count(1) `count` ,b.logtime from test ,
    (
    select distinct logtime from test
    )b
    where test.logtime <= b.logtime
    group by b.logtime
    order by b.logtime
    weizhiyao008
        6
    weizhiyao008  
       2016-12-08 18:29:53 +08:00   ❤️ 1
    mssql
    ```
    select b.logtime,(select count(id) from table1 a where a.logtime <= b.logtime) as logcount
    from table1 b
    group by b.logtime
    ```
    weizhiyao008
        7
    weizhiyao008  
       2016-12-08 18:32:39 +08:00
    @weizhiyao008 忘记看节点了, mysql 。。逃。。
    iEverX
        8
    iEverX  
       2016-12-08 18:46:12 +08:00   ❤️ 1
    SELECT
    COUNT(1) AS cnt, a.logtime
    FROM
    (SELECT DISTINCT
    logtime
    FROM
    my) a
    JOIN
    my b ON a.logtime >= b.logtime
    GROUP BY a.logtime;
    TaMud
        9
    TaMud  
       2016-12-08 19:17:47 +08:00
    3 楼正解
    多表统计也可以使用 3 楼的方法
    非常方便
    Aksura
        10
    Aksura  
       2016-12-08 21:44:57 +08:00
    5 楼才是正解, 3 楼的不符合题意。
    enenaaa
        11
    enenaaa  
    OP
       2016-12-08 21:47:42 +08:00
    @akira
    @weizhiyao008
    @iEverX
    谢谢, 你们思路是对的。我想歪了
    tusj
        12
    tusj  
       2016-12-09 10:20:10 +08:00
    我在想, 如果表很大, 这种 join 加 group by 的写法会不会很耗资源和时间?
    如果楼主手上有数据可以对比一下, 看看在 logtime 是索引的情况下, 哪种快些.

    use test;
    drop table if exists test;
    create table test (id bigint, logtime bigint);
    insert into test values (1, 5);
    insert into test values (2, 6);
    insert into test values (3, 6);
    insert into test values (4, 8);
    insert into test values (5, 8);
    commit;

    -- 楼上各位的写法
    SELECT COUNT(1) le_count, b.logtime
    FROM test,
    (
    SELECT DISTINCT logtime
    FROM test
    ) b
    WHERE test.logtime <= b.logtime
    GROUP BY b.logtime
    ORDER BY b.logtime

    -- 我的写法
    SELECT (
    SELECT COUNT(1)
    FROM test t
    WHERE t.logtime <= a.logtime) as le_count, a.logtime
    FROM (
    SELECT DISTINCT logtime
    FROM test) a;
    enenaaa
        13
    enenaaa  
    OP
       2016-12-09 10:46:43 +08:00
    @tusj 我的看法,排除掉索引的影响,没有本质区别。 这几种写法都是转换成了 join ,运算复杂度都是 n*n 级别。
    tusj
        14
    tusj  
       2016-12-09 13:49:34 +08:00
    @enenaaa 执行计划不一样.
    你可以 explain 看一下.
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1224 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 35ms · UTC 18:16 · PVG 02:16 · LAX 11:16 · JFK 14:16
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.