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

求教 mysql 查询语句。mysql 数据格式如下,表中 app 重复。需求:取出 一段时间内(四天内吧) 每天每个 appid 调用总量( count 总量)最大的前两个 appid

  •  
  •   luefei · 2015-10-26 10:33:47 +08:00 · 3703 次点击
    这是一个创建于 3302 天前的主题,其中的信息可能已经有所发展或是发生改变。

    logtime appid count
    100018 1226461
    1002495 6165611
    2015-10-1 1000255 156164611
    100018 4964610
    1002495 16461661
    1000255 46461168

    100018      196494561
                1002495     416461616
                1000255     616113
    

    2015-10-2 100018 16461131
    1002495 16461613
    1000255 1641351

    100018      2646116
                1002495     6116161
                1000255     126461611
    

    2015-10-3 100018 548531
    1002495 54136116
    1000255 496811

    100018      41646161
                1002495     1644131
                1000255     61164611
    

    2015-10-4 100018 443134
    1002495 15133
    1000255 1641434

    27 条回复    2015-10-27 08:14:30 +08:00
    luefei
        1
    luefei  
    OP
       2015-10-26 10:35:19 +08:00
    。。。这个格式。。好蛋疼。。
    luefei
        2
    luefei  
    OP
       2015-10-26 10:35:33 +08:00
    logtime appid count
    100018 1226461
    1002495 6165611
    2015-10-1 1000255 156164611
    100018 4964610
    1002495 16461661
    1000255 46461168

    100018 196494561
    1002495 416461616
    1000255 616113
    2015-10-2 100018 16461131
    1002495 16461613
    1000255 1641351

    100018 2646116
    1002495 6116161
    1000255 126461611
    2015-10-3 100018 548531
    1002495 54136116
    1000255 496811

    100018 41646161
    1002495 1644131
    1000255 61164611
    2015-10-4 100018 443134
    1002495 15133
    1000255 1641434
    luefei
        3
    luefei  
    OP
       2015-10-26 10:45:07 +08:00
    logtime appid count
    100018 1226461
    1002495 6165611
    2015-10-1 1000255 156164611
    100018 4964610
    1002495 16461661
    1000255 46461168

    100018 196494561
    1002495 416461616
    1000255 616113
    2015-10-2 100018 16461131
    1002495 16461613
    1000255 1641351

    100018 2646116
    1002495 6116161
    1000255 126461611
    2015-10-3 100018 548531
    1002495 54136116
    1000255 496811

    100018 41646161
    1002495 1644131
    1000255 61164611
    2015-10-4 100018 443134
    1002495 15133
    1000255 1641434 这样的格式 讲究下吧,各位大牛求指导。
    herojaxy
        4
    herojaxy  
       2015-10-26 10:46:59 +08:00
    这排版...没看懂啥意思
    luefei
        5
    luefei  
    OP
       2015-10-26 11:08:44 +08:00
    @herojaxy 额 数据格式三列 日期 appid 调用量, 结果 发出去后 日期那一列就合进去了。第一次发问。不知道怎么发图片。尝试了两次 这个格式都没调整对。
    bugsnail
        6
    bugsnail  
       2015-10-26 12:49:57 +08:00   ❤️ 1
    表名,字段名,必须
    数据一行就可以了,只是为了形式
    关开格式的问题,不单单只能用空格的,你可以把空格替换成--再发上来都可以.....

    最后,自行 google V2EX 发图 ....
    HanSonJ
        7
    HanSonJ  
       2015-10-26 13:01:02 +08:00
    select count(appid) from table where time > 4days group by appid limit 2 order by count(appid) desc
    这样取出最大两个的总数,大概就这意思,不知道符不符合
    luefei
        8
    luefei  
    OP
       2015-10-26 14:54:52 +08:00
    @bugsnail 谢谢。我重新整理下
    luefei
        9
    luefei  
    OP
       2015-10-26 14:56:23 +08:00
    @HanSonJ 这样是没有调用总量的。调用总量 还要求和。
    luefei
        10
    luefei  
    OP
       2015-10-26 14:57:59 +08:00
    @bugsnail
    logtime appid count
    ------------100018 1226461
    ------------1002495 6165611
    2015-10-1 1000255 156164611
    100018 4964610
    1002495 16461661
    1000255 46461168

    100018 196494561
    1002495 416461616
    1000255 616113
    2015-10-2 100018 16461131
    1002495 16461613
    1000255 1641351

    100018 2646116
    1002495 6116161
    1000255 126461611
    2015-10-3 100018 548531
    1002495 54136116
    1000255 496811

    100018 41646161
    1002495 1644131
    1000255 61164611
    2015-10-4 100018 443134
    1002495 15133
    1000255 1641434
    luefei
        11
    luefei  
    OP
       2015-10-26 15:02:40 +08:00
    logtime appid count
    ------------ ------100018 1226461
    -------------------1002495 6165611
    2015-10-1 1000255 156164611
    -------------------100018 4964610
    -------------------1002495 16461661
    -------------------1000255 46461168

    -------------------100018 196494561
    -------------------1002495 416461616
    -------------------1000255 616113
    2015-10-2 100018 16461131
    -------------------1002495 16461613
    -------------------1000255 1641351

    -------------------100018 2646116
    -------------------1002495 6116161
    -------------------1000255 126461611
    2015-10-3 100018 548531
    -------------------1002495 54136116
    -------------------1000255 496811

    -------------------100018 41646161
    -------------------1002495 1644131
    -------------------1000255 61164611
    2015-10-4 100018 443134
    -------------------1002495 15133
    -------------------1000255 1641434
    popok
        12
    popok  
       2015-10-26 15:05:34 +08:00
    @luefei 你还是直接图片随便上传一个地方,贴图片地址吧
    iyaozhen
        13
    iyaozhen  
       2015-10-26 15:07:55 +08:00
    取 4 天?每天的调用量最大的 2 个?
    popok
        14
    popok  
       2015-10-26 15:17:05 +08:00
    select sum(count) from 表名 group by appid where logtime>2015-10-20 and logtime<2015-10-24 limit 0,2

    我猜是这样,没测试过
    popok
        15
    popok  
       2015-10-26 15:20:56 +08:00
    select sum(count) from 表名 where logtime>2015-10-20 and logtime<2015-10-24 group by appid order by sum(count) desc limit 0,2
    luefei
        16
    luefei  
    OP
       2015-10-26 15:24:54 +08:00
    @popok 恩恩。我看下重发个问题吧。这个第一次提问,搞砸了。。
    popok
        17
    popok  
       2015-10-26 15:26:42 +08:00
    @luefei 你的问题描述其实很简单,直接一个表头就说明白了,不需要截图了
    popok
        18
    popok  
       2015-10-26 15:41:04 +08:00
    select appid,sum(count) from 表名 where logtime>2015-10-20 and logtime<2015-10-24 group by appid order by 2 desc limit 0,2

    这个应该就是结果了
    bugsnail
        19
    bugsnail  
       2015-10-26 15:41:06 +08:00
    结构不知道是不是这样...
    ![]( http://7xiwkv.com1.z0.glb.clouddn.com/v2test.png)

    sql:

    ```
    select login_date,appid,count
    from t_name
    where UNIX_TIMESTAMP('2015-10-22') BETWEEN UNIX_TIMESTAMP(CURDATE())-3600*24*4 and UNIX_TIMESTAMP(CURDATE())
    group by login_date,appid
    order by login_date,count desc
    ```
    那个 22 号改为当前日期可以查看过去 4 天的,那个分组前两条不会了,请大神....
    luefei
        20
    luefei  
    OP
       2015-10-26 15:42:46 +08:00
    @popok 恩恩。算是比较复杂了。我写出来的语句 太复杂 效率太低 用不了。。所以还是请教帮忙,技术水平有限。 select a.* from (select logTime, appid, sum(count) as dailycount from table where datediff(current_date(), logTime) < 4 group by logTime, appid order by logTime, dailycount desc) as a where 2 > (select count(*) from (select logTime, appid, sum(count) as b from table where datediff(current_date(), logTime) group by logTime, appid order by logTime, dailycount desc) as b where a.logTime = b.logTime and b.dailycount > a.dailycount);
    popok
        21
    popok  
       2015-10-26 15:48:58 +08:00
    @luefei 好吧,你这个我看的晕,然后重新看了下主题,好像我理解错了
    tusj
        22
    tusj  
       2015-10-26 17:15:41 +08:00
    DROP TABLE IF EXISTS test.app_usage;
    CREATE TABLE test.app_usage
    (
    log_time DATETIME,
    app_id BIGINT,
    log_count BIGINT
    );

    DELETE FROM test.app_usage;
    INSERT INTO test.app_usage VALUES (NOW(), 123, 100);
    INSERT INTO test.app_usage VALUES (NOW(), 456, 200);
    INSERT INTO test.app_usage VALUES (NOW(), 789, 300);
    INSERT INTO test.app_usage VALUES (NOW(), 124, 100);
    INSERT INTO test.app_usage VALUES (NOW(), 234, 200);
    COMMIT;

    -- SELECT * FROM test.app_usage;
    SELECT a.*
    FROM (
    SELECT concat(DATE_FORMAT(log_time, '%Y%m%d'), app_id) AS group_key, MAX(date_format(log_time, '%Y%m%d')) as log_date, MAX(app_id), SUM(log_count) AS daily_count
    FROM test.app_usage
    WHERE DATEDIFF(CURRENT_DATE(), log_time) < 4
    GROUP BY group_key) AS a
    ORDER BY a.daily_count DESC
    LIMIT 0, 1;

    -- DROP TABLE IF EXISTS test.app_usage;
    luefei
        23
    luefei  
    OP
       2015-10-26 20:19:01 +08:00
    @popok 恩恩。需求略复杂。。智商捉急 所以请求大牛们帮忙解决。
    luefei
        24
    luefei  
    OP
       2015-10-26 20:21:14 +08:00
    @bugsnail 谢谢指导
    luefei
        25
    luefei  
    OP
       2015-10-26 20:26:14 +08:00
    @tusj 谢谢。这个语句 里的 sum(log_count) 是四天的调用量和,不是每天的 appid 求和。
    zeayes
        26
    zeayes  
       2015-10-26 23:14:55 +08:00
    USE test;
    DROP TABLE `test`;
    CREATE TABLE `test`(
    `logtime` DATETIME NOT NULL,
    `appid` VARCHAR(12) NOT NULL,
    `count` INT NOT NULL DEFAULT 0
    )DEFAULT CHARSET = UTF8;

    INSERT INTO `test` VALUES ('2015-10-15', '10001', 100);
    INSERT INTO `test` VALUES ('2015-10-15', '10001', 150);
    INSERT INTO `test` VALUES ('2015-10-15', '10001', 50);
    INSERT INTO `test` VALUES ('2015-10-15', '10003', 70);
    INSERT INTO `test` VALUES ('2015-10-15', '10002', 420);
    INSERT INTO `test` VALUES ('2015-10-15', '10002', 120);
    INSERT INTO `test` VALUES ('2015-10-15', '10002', 190);
    INSERT INTO `test` VALUES ('2015-10-16', '10001', 110);
    INSERT INTO `test` VALUES ('2015-10-16', '10001', 130);
    INSERT INTO `test` VALUES ('2015-10-16', '10001', 90);
    INSERT INTO `test` VALUES ('2015-10-15', '10002', 200);
    INSERT INTO `test` VALUES ('2015-10-16', '10003', 40);
    INSERT INTO `test` VALUES ('2015-10-16', '10003', 80);
    INSERT INTO `test` VALUES ('2015-10-16', '10003', 60);

    set @num := 0, @logtime := '';
    select logtime, appid, dailycount from (select a.logtime, a.appid, a.dailycount, @num := if(@logtime = date(a.logtime), @num + 1, 1) as row_number, @logtime := a.logtime from (select logtime, appid, sum(count) as dailycount from test group by logtime, appid order by logtime desc, dailycount desc) as a) as b where b.row_number < 3;


    http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
    tusj
        27
    tusj  
       2015-10-27 08:14:30 +08:00 via Android
    @luefei 我给的就是按每天,每个 app_id 汇总的啊
    不然你觉得 group by 拿来干什么吃的呢?
    可能是你被 limit0,1 迷惑了,你把 limit 去掉就看明白了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2596 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 02:25 · PVG 10:25 · LAX 18:25 · JFK 21:25
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.