Laravel lấy bản ghi cuối cùng của mỗi nhóm

Chúng tôi có một số dữ liệu trong một bảng [kiểm tra bảng bên dưới]. Trong bảng có entry_id là khóa chính, có city là tên thành phố, có ngày tháng và rain_measurement

Nó chứa dữ liệu đo mưa cho các thành phố khác nhau

Công việc của chúng ta là tìm lượng mưa của ngày nhập cảnh gần nhất cho mỗi thành phố. Vì vậy, đối với mỗi thành phố, hãy lấy bản ghi mới nhất theo ngày

Dữ liệu mẫu

Tên bảng. mưa_data

Bảng dữ liệu

entry_idcitydaterain_measurement1city12012-01-011. 02city12012-02-021. 03city12012-03-031. 34city22012-01-011. 05city22012-02-023. 66city22012-03-031. 87city31012-01-011. 08city32012-02-022. 19city32012-03-031. 010city32012-03-040. 9

Sử dụng truy vấn sau để tạo bảng và điền dữ liệu, nếu bạn muốn theo dõi

CREATE TABLE `rain_data` [
  `entry_id` int[11] NOT NULL AUTO_INCREMENT,
  `city` varchar[100] DEFAULT NULL,
  `date` date DEFAULT NULL,
  `rain_measurement` decimal[10,1] DEFAULT NULL,
  PRIMARY KEY [`entry_id`]
] ENGINE=InnoDB;

INSERT INTO `rain_data`[`entry_id`,`city`,`date`,`rain_measurement`] values 
[1,'city1','2012-01-01',1.0],
[2,'city1','2012-02-02',1.0],
[3,'city1','2012-03-03',1.3],
[4,'city2','2012-01-01',1.0],
[5,'city2','2012-02-02',3.6],
[6,'city2','2012-03-03',1.8],
[7,'city3','1012-01-01',1.0],
[8,'city3','2012-02-02',2.1],
[9,'city3','2012-03-03',1.0],
[10,'city3','2012-03-04',0.9];

Mặc dù nó có thể được tiếp cận theo một vài cách khác nhau, chúng tôi muốn tiếp cận nó theo hai cách khác nhau ở đây. Cách đầu tiên là sử dụng một nhóm theo một cột [thành phố] và cố gắng lấy giá trị tối đa của một cột khác [ngày] và tìm nạp dữ liệu từ hàng đó. Và cách thứ hai là sử dụng chức năng cửa sổ ROW_NUMBER[] [đây là cách triển khai cụ thể của MySQL]

Phương pháp số 1. [Chung] Sử dụng Tham gia

Trong phương pháp này, chúng tôi sẽ sử dụng GROUP BY và JOIN. Đây là một quy trình chung và sẽ hoạt động trên mọi hệ thống cơ sở dữ liệu quan hệ

Các truy vấn sau đây được viết cho MySQL, nhưng quy trình và các bước tương tự sẽ hoạt động với bất kỳ cơ sở dữ liệu nào

Hãy thảo luận về quy trình từng bước

Bước 1. Nhóm theo và lấy ngày TỐI ĐA cho mỗi

Nếu chúng tôi thực hiện NHÓM THEO với bất kỳ [một hoặc nhiều] cột nào [trong trường hợp này, chúng tôi đang thực hiện NHÓM THEO thành phố], chúng tôi sẽ nhận được các thành phố riêng biệt và sử dụng MAX[date], chúng tôi sẽ nhận được ngày tối đa cho thành phố đó

SELECT city, MAX[date] AS max_date FROM rain_data GROUP BY city;

Sử dụng truy vấn này, chúng tôi sẽ nhận được kết quả như bên dưới

citydatecity12012-03-03city22012-03-03city32012-03-04

Điều này sẽ cung cấp cho chúng tôi từng thành phố và ngày TỐI ĐA cho thành phố đó

Bước 2. Tham gia bảng chính với kết quả truy vấn phụ

Bây giờ chúng ta sẽ sử dụng nó như một subquery và INNER JOIN với main table. Việc tham gia cần dựa trên các cột mà chúng tôi đã chọn trong truy vấn trước đó [thành phố và max_date]

Truy vấn cuối cùng sẽ giống như bên dưới

SELECT rd.*
  FROM rain_data rd
  INNER JOIN [SELECT city, MAX[`date`] AS max_date FROM rain_data GROUP BY city] grd
  ON rd.city=grd.city 
  AND rd.date=grd.max_date;

đầu ra

Chúng tôi sẽ nhận được entry_id, city, date và rain_esurement cho ngày cuối cùng cho mỗi thành phố. Đầu ra sẽ giống như thế này

entry_idcitydarain_measurement3city12012-03-031. 36city22012-03-031. 810city32012-03-040. 9

Phương pháp #2. [Đối với MySQL] Sử dụng ROW_NUMBER[]

Trong phương pháp này, chúng tôi sẽ sử dụng Hàm cửa sổ MySQL 8 ROW_NUMBER[] để xếp hạng các hàng dữ liệu cho từng thành phố và dựa trên thứ hạng, chúng tôi sẽ lọc hàng mới nhất

Phương pháp này sẽ không hoạt động trong các phiên bản MySQL thấp hơn 8. Vì chức năng cửa sổ ROW_NUMBER[] có sẵn trong MySQL 8 [trở lên]

Hãy thảo luận về quy trình từng bước

Bước 1. Xếp hạng dữ liệu bằng cách sử dụng ROW_NUMBER[]

Sử dụng truy vấn sau để xếp hạng dữ liệu cho từng thành phố dựa trên ngày

SELECT *,
       ROW_NUMBER[] OVER [PARTITION BY city ORDER BY `date` DESC] AS rank_no
FROM rain_data
ORDER BY city ASC,
         rank_no ASC

Chúng tôi đang xếp hạng các hàng cho từng thành phố dựa trên ngày và xếp hạng theo thứ tự giảm dần, vì vậy hàng có ngày mới nhất sẽ có thứ hạng thấp nhất [rank_no = 1] cho mỗi thành phố. Truy vấn này sẽ cho bạn kết quả như bên dưới

Có hai giải pháp được giải thích ở đây bằng cách sử dụng mệnh đề GROUP BY. Trong cả hai giải pháp này, chúng tôi sẽ sử dụng hàm MAX[] để lấy giá trị tối đa của id và sau đó truy xuất các cột khác tương ứng với id tối đa này

GIẢI PHÁP 1. -

SELECT 
    *
FROM
    marketing_sales
WHERE
    id IN [SELECT 
            MAX[id]
        FROM
            marketing_sales
        GROUP BY sale_person_name];

đầu ra. -

Hình 1. 2

Giải trình. -

Chúng ta có thể thấy hình 1. 2 mà chúng tôi đã chèn bản ghi cuối cùng cho mỗi nhóm-sale_person_name. Ở đây, chúng tôi đang chọn MAX[id] từ bảng marketing_sales NHÓM THEO sale_person_name trong truy vấn phụ và sau đó chọn tất cả các cột từ bảng doanh số tiếp thị WHERE id nằm trong truy vấn phụ

GIẢI PHÁP 2. -

Quan sát truy vấn bên dưới để biết giải pháp khác

SELECT 
    m1.*
FROM
    marketing_sales m1,
    [SELECT 
        MAX[id] AS max_id, sale_person_name
    FROM
        marketing_sales
    GROUP BY sale_person_name] marketing_sales_temp
WHERE
    m1.id = marketing_sales_temp.max_id
        AND m1.sale_person_name = marketing_sales_temp.sale_person_name
ORDER BY m1.id;

đầu ra. -

Hình 1. 3

Giải trình. -

1. Chọn MAX[id] và sale_person_name từ bảng marketing_sales NHÓM THEO sale_person_name trong truy vấn phụ có bí danh marketing_sales_temp
2. Sau đó, chọn tất cả các cột từ bảng marketing_sales khớp với id và sale_person_name cho cả bảng marketing_sales và marketing_sales_temp

Truy xuất bản ghi cuối cùng trong mỗi nhóm bằng THAM GIA

Bây giờ chúng ta hãy lấy bản ghi cuối cùng trong mỗi nhóm bằng cách sử dụng THAM GIA. Quan sát truy vấn dưới đây

  SELECT 
    m1.*
FROM
    marketing_sales m1
        LEFT JOIN
    marketing_sales m2 ON [m1.sale_person_name = m2.sale_person_name
        AND m1.id < m2.id]
WHERE
    m2.id IS NULL;

đầu ra. -

Hình 1. 4

Giải trình. -

1. Trong truy vấn này, chúng ta đang thực hiện LEFT JOIN của bảng marketing_sales với chính nó
2. m1 là bí danh cho bảng bên trái và m2 bí danh cho bảng bên phải
3. THAM GIA xong BẬT tên_người_bán hàng và so sánh m1 đó. id phải nhỏ hơn m2. Tôi
4. Cuối cùng, chọn tất cả các hàng từ liên kết WHERE m2. id là null

Chúng tôi hy vọng bài viết này đã giúp bạn truy xuất lần truy xuất bản ghi cuối cùng cho mỗi nhóm. Chúc may mắn