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
1
luefei OP 。。。这个格式。。好蛋疼。。
|
2
luefei OP 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 |
3
luefei OP 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 这样的格式 讲究下吧,各位大牛求指导。 |
4
herojaxy 2015-10-26 10:46:59 +08:00
这排版...没看懂啥意思
|
5
luefei OP @herojaxy 额 数据格式三列 日期 appid 调用量, 结果 发出去后 日期那一列就合进去了。第一次发问。不知道怎么发图片。尝试了两次 这个格式都没调整对。
|
6
bugsnail 2015-10-26 12:49:57 +08:00 1
表名,字段名,必须
数据一行就可以了,只是为了形式 关开格式的问题,不单单只能用空格的,你可以把空格替换成--再发上来都可以..... 最后,自行 google V2EX 发图 .... |
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
这样取出最大两个的总数,大概就这意思,不知道符不符合 |
10
luefei OP @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 |
11
luefei OP 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 |
13
iyaozhen 2015-10-26 15:07:55 +08:00
取 4 天?每天的调用量最大的 2 个?
|
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
我猜是这样,没测试过 |
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
|
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
这个应该就是结果了 |
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 天的,那个分组前两条不会了,请大神.... |
20
luefei OP @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);
|
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; |
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/ |