Lấy giá trị lớn nhất theo chu kì trong toàn bộ bảng

dailammoc · February 18, 2015, 10:54am

Mình có query lấy giá trị lớn nhất trong vòng một giờ(theo chu kì) của một thiết bị cụ thể như sau:

SELECT from_unixtime(thoiGian), nhienLieu
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
       concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
     from gtse.tblDoXang
     where accountID = 'vinhnghia' 
        and deviceID = '14C-00263'
        and from_unixtime(thoiGian) between '2014-10-01 00:00:00' and '2014-11-18 23:59:59'
     group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)))

Giờ mình luốn select cả bảng để lấy tất cả các giá trị của các account và device khác nhau thoả mãn điều kiện là chỉ lấy giá trị lớn nhất của nhienLieu trong mỗi một giờ. Mình thử truy vấn này:

 SELECT from_unixtime(thoiGian), nhienLieu
    FROM `gtse`.`tblDoXang`
    where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
        (select
           concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
         from gtse.tblDoXang
         group by
            date(from_unixtime(thoiGian)),
            hour(from_unixtime(thoiGian)))

Sau đó mình check lại thì thấy nó lấy thiếu một số giá trị max so với query mình thử với accountID và deviceID ở trên. Do trong bảng tblDoXang mình có rất nhiều account nên việc lấy thủ công như đoạn query trên(đầu tiên) là không thể. Vậy cho mình hỏi cách nào lấy cả bảng (thỏa mãn điều kiện max) không?

tuancoi2506 · November 18, 2014, 5:47pm

Bạn thử join 2 bảng lại (bảng kia BND là bảng chứa accountid,deviceid) xem.

Ví dụ:

SELECT from_unixtime(thoiGian), nhienLieu
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
       concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
     from gtse.tblDoXang DX, BANGNAODO BND
     where DX.accountID = BND.accountID 
        and DX.deviceID =BND.deviceID 
        and from_unixtime(thoiGian) between '2014-10-01 00:00:00' and '2014-11-18 23:59:59'
     group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian))),
        BND.accountID ,BND.deviceID

dailammoc · November 18, 2014, 6:06pm

Có vẻ như mình nói chưa rõ ý của mình thì phải, cả hai truy vấn trên của mình đều là lấy từ bảng tblDoXang. Chỉ khác là truy vấn đầu mình chỉ rõ ra accountID, deviceID và thoiGian còn truy vấn thứ hai mình muốn lấy cả bảng(cho tất cả các accountID và deviceID). Và cái truy vấn thứ hai lấy ra kết quả không như mong muốn.

tuancoi2506 · November 18, 2014, 6:30pm

Vậy mình ngĩ ra 2 hướng :
Hướng 1: BANGNAODO là bảng tblDoXang luôn cũng được mà, có nhất thiết fai là bảng khác đâu.

Hướng thứ 2 : Mình ngĩ câu của bạn có thể làm đơn giản như vậy được mà (ý tưởng thôi nhé, k biết đúng không, nếu muốn bạn có thể thử)

select max(nhienlieu), accountID , deviceID 
from tblDoXang
where thoi gian between '2014-10-01 00:00:00' and '2014-11-18 23:59:59'
group by hour(thoigian), accountID , deviceID

group by hour(thoigian) là bạn gom nhóm theo giờ. Cú pháp lấy giờ bạn lên search lại cho chắc nhé

Bạn thử xem có được không.

dailammoc · November 19, 2014, 1:36am

Query của bạn chỉ được max của mỗi xe trong khoảng thời gian đó. Có nghĩa là ví dụ có 5 xe thì ra được 5 cái giá trị max. Trong khi trong khoảng thời gian đó, mỗi xe sẽ có khoảng trên dưới 10 cái giá trị max(theo mỗi một giờ).

tuancoi2506 · November 19, 2014, 1:40am

ý bạn là mỗi xe sẽ có 1 giá trị max theo accountID và deviceID, nếu vậy bạn gom nhóm thêm mã xe vào nữa.

dailammoc · November 19, 2014, 2:03am

Chính xác là query này. Thanks vì gợi ý của bạn.

SELECT from_unixtime(thoiGian), nhienLieu
    FROM `gtse`.`tblDoXang`
    where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
        (select
           concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
         from gtse.tblDoXang
         group by
            date(from_unixtime(thoiGian)),
            hour(from_unixtime(thoiGian)),
            accountID,deviceID -- them hai gia tri nay nua la chuan
         )