Hỏi về query lấy dữ liệu của tháng gần nhất

Liên hệ QC
H

Ha Vi

Thành viên mới
Tham gia
10/11/08
Bài viết
26
Được thích
0
Chào các bạn!
Các bạn xem giúp mình có thể viết tiếp câu Query1 trong Access lấy dữ liệu như yêu cầu bên dưới không nhé.
Nếu cột TypeName là Resin thì lấy giá trung bình của tháng gần nhất (ví dụ: Resin có mã ItemCode PU1-A025 thì tháng gần nhất là tháng 12 giá trung bình 600), còn lại các loại khác sẽ lấy giá cao nhất của tháng gần nhất (ví dụ: FOAM BAG có mã ItemCode PU4-F486 thì tháng gần nhất là tháng 12 giá cao nhất là 4000...).
Cám ơn các bạn.
 

File đính kèm

  • VA.rar
    102.8 KB · Đọc: 26
Dùng kỹ thuật gọi là truy vấn chéo nội (correlated subquery).
Đại khái:
select * from bangA as xyz where tháng in
(select max(tháng) from bangA where bangA.Mã = xyz.Mã)
 
VetMini đã viết:
Dùng kỹ thuật gọi là truy vấn chéo nội (correlated subquery).
Đại khái:
select * from bangA as xyz where tháng in
(select max(tháng) from bangA where bangA.Mã = xyz.Mã)
Nhấp chuột vào đây để mở rộng...

Chào bạn!
Mình đã làm thử câu lệnh của bạn thì dữ liệu chỉ lấy ra được giá của ngày gần nhất, chưa ra được kết quả như mình cần.
Cám ơn bạn.
 
Câu query trên sẽ lấy ra dữ liệu của tháng gần nhất, đúng như tiêu đề bạn yêu cầu.
Phần bạn yêu cầu tiếp theo trong bài thì dựa trên đống dữ liệu trên mà làm tiếp.
 
Mã: 
SELECT t.itemcode,t.itemname,t.typename,t.mon,IIF(t.typename='Resin',AVG(t.price),MAX(t.price)) as price FROM (SELECT table1.*,table2.mon FROM table1 INNER JOIN (SELECT MONTH(MAX(podate)) as mon,itemcode FROM table1 GROUP BY itemcode) table2 ON MONTH(table1.podate)=table2.mon AND table1.itemcode=table2.itemcode) t GROUP BY t.itemcode,t.itemname,t.typename,t.mon
Mình làm như sau: đầu tiên select month(max(podate)) rồi inner join với bảng chính để lấy ra các tháng gần nhất, sau cùng tính AVG hoặc MAX tùy theo typename. Mình giả định ứng với mỗi typecode thì typename chỉ là 1 trong 2 loại Resin hoặc không (không tồn tại đồng thời cả 2 loại).
 
Lần chỉnh sửa cuối:
Hau151978 đã viết:
Mã: 
SELECT t.itemcode,t.itemname,t.typename,t.mon,IIF(t.typename='Resin',AVG(t.price),MAX(t.price)) as price FROM (SELECT table1.*,table2.mon FROM table1 INNER JOIN (SELECT MONTH(MAX(podate)) as mon,itemcode FROM table1 GROUP BY itemcode) table2 ON MONTH(table1.podate)=table2.mon AND table1.itemcode=table2.itemcode) t GROUP BY t.itemcode,t.itemname,t.typename,t.mon
Mình làm như sau: đầu tiên select month(max(podate)) rồi inner join với bảng chính để lấy ra các tháng gần nhất, sau cùng tính AVG hoặc MAX tùy theo typename. Mình giả định ứng với mỗi typecode thì typename chỉ là 1 trong 2 loại Resin hoặc không (không tồn tại đồng thời cả 2 loại).
Nhấp chuột vào đây để mở rộng...

Join là toán pháp căn bản nhất để thực hiện kết nối giữa các liên hệ. Vì vậy 10 cái CSDL LH có hết 9 cái chú trọng vào hiệu suất của lệnh Join. Access cũng không ngoại lệ. Tức là dùng join hầu như luôn luôn hiệu quả hơn các lệnh khác.

Tuy nhiên, trong trường hợp như bài này, phương pháp correlated query là phương pháp trông gọn ghẽ dễ nhìn nhất.

Cái tôi đưa ra ở bài #2 cốt chỉ dùng để lọc ra các records có tháng gần nhất.

Để đi đến kết quả cuối cùng mà chủ thớt muốn thì còn phải group cái recordset lại, và tuỳ theo loại mà dùng hàm tổng (aggregate function), avg hay max.
 
VetMini đã viết:
Join là toán pháp căn bản nhất để thực hiện kết nối giữa các liên hệ. Vì vậy 10 cái CSDL LH có hết 9 cái chú trọng vào hiệu suất của lệnh Join. Access cũng không ngoại lệ. Tức là dùng join hầu như luôn luôn hiệu quả hơn các lệnh khác.

Tuy nhiên, trong trường hợp như bài này, phương pháp correlated query là phương pháp trông gọn ghẽ dễ nhìn nhất.

Cái tôi đưa ra ở bài #2 cốt chỉ dùng để lọc ra các records có tháng gần nhất.

Để đi đến kết quả cuối cùng mà chủ thớt muốn thì còn phải group cái recordset lại, và tuỳ theo loại mà dùng hàm tổng (aggregate function), avg hay max.
Nhấp chuột vào đây để mở rộng...
Em không có căn bản nên chỉ thử làm cho ra kết quả. Cách của bác và của em còn thiếu so sánh năm nữa.
 
Hau151978 đã viết:
... Cách của bác và của em còn thiếu so sánh năm nữa.
Nhấp chuột vào đây để mở rộng...

Tôi chỉ mách về kỹ thuật.

chứ thực tế mà nói, tôi còn chưa hiểu chủ thớt nói "tháng gần nhất" nghĩa là gì?
Ví dụ nếu đang ở tháng 7, thì gần là tháng 6 hay tháng 7? Rõ ràng là 7 gần hơn, nhưng tháng 7 chưa hết thì trị trung bình có dùng được hay không? Tức là lúc tính phải trừ tháng hiện hành ra?

chú thích: so sánh tháng đương nhiên phải kèm năm. Thường thì tôi dùng con số năm * 100 + tháng để so sánh, hoặc format mat date theo dạng "yyyymm"
 
VetMini đã viết:
Tôi chỉ mách về kỹ thuật.

chứ thực tế mà nói, tôi còn chưa hiểu chủ thớt nói "tháng gần nhất" nghĩa là gì?
Ví dụ nếu đang ở tháng 7, thì gần là tháng 6 hay tháng 7? Rõ ràng là 7 gần hơn, nhưng tháng 7 chưa hết thì trị trung bình có dùng được hay không? Tức là lúc tính phải trừ tháng hiện hành ra?

chú thích: so sánh tháng đương nhiên phải kèm năm. Thường thì tôi dùng con số năm * 100 + tháng để so sánh, hoặc format mat date theo dạng "yyyymm"
Nhấp chuột vào đây để mở rộng...

Cám ơn bạn Vetmini và bạn Hậu đã tận tình giúp đỡ.
Ý của mình là tháng gần nhất của dữ liệu, ví dụ trong dữ liệu 1 nguyên vật liệu từ tháng 1 tới tháng 12 đều có giá thì mình lấy tháng 12 là tháng gần nhất. “Lúa nguyên một cánh đồng” như mình thì câu lệnh của bạn Vetmini chưa ra được kết quả cuối nhưng dễ hiểu J
Câu lệnh của bạn Vetmini mình làm tiếp để ra kết quả cuối thì dữ liệu ra được nhưng chạy rất chậm mặc dù dữ liệu không nhiều, nếu được có thể xem tiếp giúp mình lý do tại sao được ko?
File dữ liệu nguồn mình link từ Excel, đường dẫn D:\VA
Câu lệnh đang chạy chậm là MaterialPriceLatestMonth
Cám ơn các bạn.
 

File đính kèm

  • VA.rar
    247.4 KB · Đọc: 26
Tôi lười tải file lắm. Bạn đưa nguyên cái câu sql lên đây xem.

Chú ý: cái câu cuối cùng mà Access nhận ấy.
Chớ đưa cái dạng "Select * from " & tenBang & " where truong1 = '" & triNaoDo & "' "; tôi cũng lười dịch lắm.
 
VetMini đã viết:
Tôi lười tải file lắm. Bạn đưa nguyên cái câu sql lên đây xem.

Chú ý: cái câu cuối cùng mà Access nhận ấy.
Chớ đưa cái dạng "Select * from " & tenBang & " where truong1 = '" & triNaoDo & "' "; tôi cũng lười dịch lắm.
Nhấp chuột vào đây để mở rộng...
SELECT MaterialPriceLatestMonthTemp.ItemCode, MaterialPriceLatestMonthTemp.ItemName, MaterialPriceLatestMonthTemp.Typename, MaterialPriceLatestMonthTemp.UoM, IIf([Typename]="Resin",Avg([PriceVND]),Max([PriceVND])) AS Price
FROM MaterialPriceLatestMonthTemp
GROUP BY MaterialPriceLatestMonthTemp.ItemCode, MaterialPriceLatestMonthTemp.ItemName, MaterialPriceLatestMonthTemp.Typename, MaterialPriceLatestMonthTemp.UoM;
Xem giúp mình nhé.
Cám ơn bạn.
 

File đính kèm

  • Capture.jpg
    Capture.jpg
    12.1 KB · Đọc: 45
Có lẽ do mày của bạn không đủ RAM để chạy correlated subquery.
Trường hợp này bắt buộc phải dùng JOIN
Nếu dùng JOIN, bạn có thể cải tiến tốc độ bằng cách đặt index cho trường ItemCode + Tháng.

=== Bổ sung ===
Bạn tính "tháng gần nhất" như thế nào?
Nếu có thể, đặt một hàm người dùng để tính.
 
Lần chỉnh sửa cuối:
Tôi viết thử query sau, đúng thì dùng nhé.

PHP: 
 SELECT MaterialPrice.POno,
       MaterialPrice.PODate,
       MaterialPrice.ItemCode,
       MaterialPrice.ItemName,
       MaterialPrice.TypeName,
       MaterialPrice.UoM,
       Q_MaxPrice.Price,
       Q_MaxPrice.Currency,
       MaterialPrice.ETADueDate
FROM   MaterialPrice
       INNER JOIN (SELECT Query_1.ItemCode,
                          IIf([Typename] = "Resin", Avg([Uprice]), Max([Uprice])
                          ) AS
                                               Price,
                          Query2.Currency
                   FROM   (SELECT Max(Month([ETADueDate])) AS MAXMONTH,
                                  Max(Year([ETADueDate]))  AS MAXYEAR,
                                  MaterialPrice.ItemCode
                           FROM   MaterialPrice
                           GROUP  BY MaterialPrice.ItemCode) AS Query_1
                          INNER JOIN (SELECT MaterialPrice.*,
                                             Month([ETADueDate]) AS MAXMONTH,
                                             Year([ETADueDate])  AS MAXYEAR
                                      FROM   MaterialPrice) AS Query2
                                  ON ( Query_1.ItemCode = Query2.ItemCode )
                                     AND ( Query_1.MAXYEAR = Query2.MAXYEAR )
                                     AND ( Query_1.MAXMONTH = Query2.MAXMONTH )
                   GROUP  BY Query_1.ItemCode,
                             Query2.Currency,
                             Query2.MAXMONTH,
                             Query2.MAXYEAR,
                             Query2.TypeName
                   ORDER  BY Query_1.ItemCode) AS Q_MaxPrice
               ON MaterialPrice.ItemCode = Q_MaxPrice.ItemCode
 
VetMini đã viết:
Có lẽ do mày của bạn không đủ RAM để chạy correlated subquery.
Trường hợp này bắt buộc phải dùng JOIN
Nếu dùng JOIN, bạn có thể cải tiến tốc độ bằng cách đặt index cho trường ItemCode + Tháng.

=== Bổ sung ===
Bạn tính "tháng gần nhất" như thế nào?
Nếu có thể, đặt một hàm người dùng để tính.
Nhấp chuột vào đây để mở rộng...


Ý bạn là có thể sử dụng hàm người dùng thay thế trong trường hợp này nếu không dùng Join ? Nếu có thời gian mình sẽ ngâm cứu tiếp về phần hàm bạn nói :)

Cám ơn bạn.
 
Lê Phát Huy đã viết:
Tôi viết thử query sau, đúng thì dùng nhé.

PHP: 
 SELECT MaterialPrice.POno,
       MaterialPrice.PODate,
       MaterialPrice.ItemCode,
       MaterialPrice.ItemName,
       MaterialPrice.TypeName,
       MaterialPrice.UoM,
       Q_MaxPrice.Price,
       Q_MaxPrice.Currency,
       MaterialPrice.ETADueDate
FROM   MaterialPrice
       INNER JOIN (SELECT Query_1.ItemCode,
                          IIf([Typename] = "Resin", Avg([Uprice]), Max([Uprice])
                          ) AS
                                               Price,
                          Query2.Currency
                   FROM   (SELECT Max(Month([ETADueDate])) AS MAXMONTH,
                                  Max(Year([ETADueDate]))  AS MAXYEAR,
                                  MaterialPrice.ItemCode
                           FROM   MaterialPrice
                           GROUP  BY MaterialPrice.ItemCode) AS Query_1
                          INNER JOIN (SELECT MaterialPrice.*,
                                             Month([ETADueDate]) AS MAXMONTH,
                                             Year([ETADueDate])  AS MAXYEAR
                                      FROM   MaterialPrice) AS Query2
                                  ON ( Query_1.ItemCode = Query2.ItemCode )
                                     AND ( Query_1.MAXYEAR = Query2.MAXYEAR )
                                     AND ( Query_1.MAXMONTH = Query2.MAXMONTH )
                   GROUP  BY Query_1.ItemCode,
                             Query2.Currency,
                             Query2.MAXMONTH,
                             Query2.MAXYEAR,
                             Query2.TypeName
                   ORDER  BY Query_1.ItemCode) AS Q_MaxPrice
               ON MaterialPrice.ItemCode = Q_MaxPrice.ItemCode
Nhấp chuột vào đây để mở rộng...

Câu lệnh chạy nhanh, giá ra đúng như mình cần, chỉ có phần ngày tháng thì hơi lạ chút xíu. Mình ví dụ thử 1 mã ItemCode PU1-A031 (mình có cắt bớt 1 số cột không cần thiết), như hình bên dưới thì giá trung bình của tháng gần nhất ra đúng nhưng dữ liệu lại hiển thị tất cả những ngày tháng có trong dữ liệu.
Cám ơn bạn.Capture.jpg
 
Join thì cứ cái nào thoả điều kiện là nó lấy. Muốn giới hạn thì phải group lại.
Sự thật là do bảng của bạn thiết kế dở. Các dữ liệu cố định như tên hàng, loại hàng phải là bảng riêng với dữ liệu thay đổi thường xuyên như giá, ngày...
 
Lần chỉnh sửa cuối:
Ha Vi đã viết:
Câu lệnh chạy nhanh, giá ra đúng như mình cần, chỉ có phần ngày tháng thì hơi lạ chút xíu. Mình ví dụ thử 1 mã ItemCode PU1-A031 (mình có cắt bớt 1 số cột không cần thiết), như hình bên dưới thì giá trung bình của tháng gần nhất ra đúng nhưng dữ liệu lại hiển thị tất cả những ngày tháng có trong dữ liệu.
Cám ơn bạn.
Nhấp chuột vào đây để mở rộng...
Đã làm cho bạn cái khó, bạn nên tuỳ biến mà sử dụng nhé.
 
VetMini đã viết:
Join thì cứ cái nào thoả điều kiện là nó lấy. Muốn giới hạn thì phải group lại.
Sự thật là do bảng của bạn thiết kế dở. Các dữ liệu cố định như tên hàng, loại hàng phải là bảng riêng với dữ liệu thay đổi thường xuyên như giá, ngày...
Nhấp chuột vào đây để mở rộng...
Không phải mình thiết kế đâu, dữ liệu nguồn bên Excel là như vậy, do đó mình mới link file excel trong access .
P/S: Không phải mình bào chữa khi bạn nói mình dở vì ngay từ đầu đã nói mình “lúa nguyên một cánh đồng ” rồi :)
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

HuynhThiMinhPhung
tìm và thay thế giá trị rỗng
Trả lời
2
Đọc
31
VetMini
V
hongphuong1997
  • Question
Lấy các ký tự bất kỳ trong các ô
Trả lời
2
Đọc
52
hongphuong1997
hongphuong1997
HuynhThiMinhPhung
nối dữ liệu từ nhiều worksheet
Trả lời
3
Đọc
95
HuynhThiMinhPhung
HuynhThiMinhPhung
LuuGiaPhúc
Tìm công thức mảng tính giá trị lớn nhất theo từng hàng
Trả lời
9
Đọc
168
VetMini
V
hongquan93vn
Tìm công thức có thể đánh STT của giá trị mỗi lần xuất hiện
2
Trả lời
25
Đọc
366
VetMini
V
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom