多项记录的和持续时间

Question

我有一张桌子上有松开的柱子：

id  device_id (string) used_at (datetime) app_id (string) duration (int)
1   device-1           2018-07-20         chrome          30
2   device-1           2018-07-19         chrome          1
3   device-2           2018-07-19         chrome          2
4   device-2           2018-07-20         facebook        100
5   device-1           2018-07-20         chrome          20
6   device-3           2018-07-20         chrome          10
7   device-1           2018-07-20         facebook        5

我需要得到指定设备的10个最常用的app_id -1和used_at的列表，这个列表应该有持续时间和。

所以我要买设备-1和uset_at >= 2018-07-18：

device-1 chrome duration (51)
device-1 facebook duration (5)

有人能建议sql吗？

Answer 1

我不太清楚你在这篇文章中要求什么。

这是我想出来的

CREATE TABLE device_table
(
    id INT PRIMARY KEY NOT NULL auto_increment
  , defice_id VARCHAR(20)
  , used_at DATETIME
  , app_id VARCHAR(30)
  , duration INT
);

INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'chrome', 30);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-19', 'chrome', 1);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-2', '2018-07-19', 'chrome', 2);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-2', '2018-07-20', 'facebook', 100);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'chrome', 20);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-3', '2018-07-20', 'chrome', 10);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'facebook', 5);

SELECT device_id
     , app_id
     , CONCAT('duration (', CAST(SUM(duration) AS CHAR), ')') 
FROM device_table
-- remove filter to include everything else
WHERE device_id = 'device-1'
-- if you want top ten for "device-1" then comment out the date filter
AND   used_at >= '2018-07-18'
GROUP BY device_id
       , app_id
-- and uncomment the following line
--LIMIT 10
;

你能多解释一下你的问题吗?这样我们其他人就能明白你在这里想要做什么？

如果您希望使用所有设备中的前2位，请使用以下命令：

SELECT device_id
     , app_id
     , CONCAT('duration (', CAST(sum(duration) AS CHAR), ')') 
FROM device_table
GROUP BY device_id
       , app_id
ORDER BY duration DESC
LIMIT 2 -- change this to how many you want
;

Answer 2

我在MySQL 5.6中测试过

create table `ccc` (
  id int not null auto_increment,
  device_id varchar(50),
  used_at date,
  app_id varchar(50),
  duration int,
  primary key(`id`)
)engine=innodb;

insert into ccc (device_id, used_at, app_id, duration) values('device-1', '2018-07-20', 'chrome', 30);

insert into ccc (device_id, used_at, app_id, duration) values('device-1', '2018-07-19', 'chrome', 1);

insert into ccc (device_id, used_at, app_id, duration) values('device-2', '2018-07-19', 'chrome', 2);

insert into ccc (device_id, used_at, app_id, duration) values('device-2', '2018-07-20', 'facebook', 100);

insert into ccc (device_id, used_at, app_id, duration) values('device-1', '2018-07-20', 'chrome', 20);

insert into ccc (device_id, used_at, app_id, duration) values('device-3', '2018-07-20', 'chrome', 10);

insert into ccc (device_id, used_at, app_id, duration) values('device-1', '2018-07-20', 'facebook', 5);

SELECT device_id, app_id, sum(duration) 
FROM ccc 
WHERE device_id = 'device-1' AND used_at >= '2018-07-18' 
GROUP BY app_id;

+-----------+----------+---------------+
| device_id | app_id   | sum(duration) |
+-----------+----------+---------------+
| device-1  | chrome   |            51 |
| device-1  | facebook |             5 |
+-----------+----------+---------------+