คือผมต้องการดึงข้อมูลย้อนหลัง 30 ตัว ก่อนหน้า แต่ละ record ให้มาแสดงใน แต่ละ record อ่ะครับ (งงใช่มั้ยล่ะ)
ผมมีข้อมูลอยู่ตารางนึง มีฟิลตามนี้
RecNo , LotNo , Qty
ทีนี้ผมอยาก query ข้อมูลจากตารางนี้ โดยให้แต่ละ record ที่ query มา มีค่าเฉลี่ยของ Qty ย้อนหลังจาก record นั้นๆ อยู่ด้วย คือ
RecNo , LotNo , Qty , *** Avg30Qty ***
เช่น
ข้อมูล RecNo ที่ 31 Avg30Qty ก็จะแสดงข้อมูล == ค่าเฉลี่ย Qty ของ RecNo ที่ 1-30
ข้อมูล RecNo ที่ 32 Avg30Qty ก็จะแสดงข้อมูล == ค่าเฉลี่ย Qty ของ RecNo ที่ 2-31
ข้อมูล RecNo ที่ 33 Avg30Qty ก็จะแสดงข้อมูล == ค่าเฉลี่ย Qty ของ RecNo ที่ 3-32
ช่วยแนะนำหน่อยครับว่าผมจะเขียน query ยังไงดี นึกไม่ออกจริงๆครับ ที่นึกออกก็คือใช้ loop fecth ทีละ record แต่ผมคิดว่ามันน่าจะมีวิธีที่ดี หรือง่ายกว่านี้มั้ยอ่ะครับ
Select t1.RecNo, t1.LotNo,
ionz Wed, 15/05/2013 - 23:54
Select t1.RecNo, t1.LotNo, t1.Qty, AVG(t2.Qty) AS Avg30Qty
From Table t1
Left Join Table t2
On t2.RecNo Between t1.RecNo - 30 And t1.RecNo - 1
Group By t1.RecNo, t1.LotNo, t1.Qty
คิดว่ารูปแบบนี้น่าจะใช้ได้ แล
superballsj2 Thu, 16/05/2013 - 09:12
In reply to Select t1.RecNo, t1.LotNo, by ionz
คิดว่ารูปแบบนี้น่าจะใช้ได้
แล้วถ้า RecNo มันไม่เรียงกันล่ะครับ
เพราะมีเงื่อนไขที่ต้อง filter อีกอ่ะครับ เช่น Where LotNo = 'A'
หมายความว่า RecNo ที่เก็บใน
ionz Thu, 16/05/2013 - 11:38
In reply to คิดว่ารูปแบบนี้น่าจะใช้ได้ แล by superballsj2
หมายความว่า RecNo ที่เก็บใน Table อาจจะมีการกระโดดได้ใช่รึเปล่าครับ
แก้ไขระดับ schema เลยได้ไหม ให้ RecNo เป็น identity
ถ้าไม่ได้ก็ต้องปรับ query กันใหม่
SELECT RecNo, LotNo, Qty
, (
SELECT AVG(QTY)
FROM (
SELECT TOP 30 Qty
FROM foo t2
WHERE t2.RecNo < t1.RecNo
ORDER BY t2.RecNo DESC
) top30
) AS AvgQty30
FROM foo t1
SELECT RecNo , LotNo , Qty ,
figgaro Thu, 16/05/2013 - 12:05
SELECT RecNo , LotNo , Qty , AVG(Qty) OVER (
ORDER BY RecNo
ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW
) AS Avg30Qty
FROM table
ลองไปศึกษาเรื่อง Analytic Function ดูครับ
SELECT T1.RecNo, T1.LotNo,
Meen Wed, 15/05/2013 - 23:42
SELECT T1.RecNo, T1.LotNo, T1.Qty, AVG(T2.qty), COUNT(*) FROM
TBL_NAME_HERE T1
INNER JOIN
TBL_NAME_HERE T2 on (T2.RecNo >= T1.RecNo AND T2.RecNo < (T1.RecNo+30))
GROUP BY (T1.RecNo, T1.LotNo, T1.Qty)
แทน TBL_NAME_HERE ด้วยชื่อ Table ดูครับ, ตัว count * ไว้ดูว่ามันดึงข้อมูลมา sum ครบ 30 ตัวรึเปล่าเฉยๆ
วิธีตรงๆมีคนตอบไปแล้ว ถ้าอยาก
lancaster Thu, 16/05/2013 - 02:22
วิธีตรงๆมีคนตอบไปแล้ว
ถ้าอยากได้วิธีที่ performance ดีสุด ดึงมาคำนวณในโปรแกรมเองเลยครับ
Analytic Function ใน SQL
saknarak Thu, 16/05/2013 - 07:48
Analytic Function ใน SQL Server ใช้ได้ตั้งแต่ 2012 เป็นต้นไป
SELECT RecNo , LotNo , Qty , AVG(Qty) OVER (
ORDER BY RecNo
ROWS BETWEEN 30 PRECEDING AND
-1 FOLLOWING
) AS Avg30Qty
FROM table
อ่าว กรรม ของผมเป็น 2008
superballsj2 Thu, 16/05/2013 - 09:03
In reply to Analytic Function ใน SQL by saknarak
อ่าว กรรม ของผมเป็น 2008 จะได้มั้ยเนี่ย เด๋วลองดูก่อน ขอบคุณครับ
ขอบคุณทุกท่านครับ ผมลืมบอกเงื
superballsj2 Thu, 16/05/2013 - 09:28
ขอบคุณทุกท่านครับ
ผมลืมบอกเงื่อนไขไปอีกอย่างอ่ะครับ ว่า RecNo มันไม่เรียงกัน เพราะต้องมีการ filter ข้อมูลด้วยอ่ะครับ ทีนี้จะ Join ยังไงดีอ่ะครับ -*-
ปล. ของผมเป็นเวอชั่น 2008 ใช้ Analytic Function ไม่ได้
ไม่เรียงไม่เป็นไร ที่ต้องการท
saknarak Thu, 16/05/2013 - 13:58
ไม่เรียงไม่เป็นไร
ที่ต้องการทราบคือ อยากได้จำนวน 30 แถวย้อนหลัง หรือว่าอยากได้ RecNo-31 ถึง RecNo-1
;WITH X AS ( SELECT
saknarak Thu, 16/05/2013 - 14:12
;WITH X AS ( SELECT ROW_NUMBER() OVER(ORDER BY RecNo) AS RowNum , RecNo , LotNo , Qty FROM mytable WHERE LotNo='XXX' ) SELECT t1.RecNo, MIN(t1.LotNo) LotNo, MIN(t1.Qty) Qty , AVG(t2.Qty) AvgQty30 FROM X as t1 JOIN X as t2 ON t1.RowNum > t2.RowNum GROUP BY t1.RecNo ORDER BY t1.RecNoดูดขึ้นมาทั้งหมดแล้วเอามาประม
zixs Mon, 20/05/2013 - 01:40
ดูดขึ้นมาทั้งหมดแล้วเอามาประมวลผลในโปรแกรมครับ
ถ้าดาต้ายังเพิ่มขึ้นอีกแนะนำให้เพิ่มอีก column เพื่อเก็บค่านี้เลยนะครับ
จะได้ความเร็วสูงสุดในการดึงข้อมูลออกมา
เรื่องเพิ่ม column
superballsj2 Mon, 20/05/2013 - 17:14
In reply to ดูดขึ้นมาทั้งหมดแล้วเอามาประม by zixs
เรื่องเพิ่ม column คิดว่าน่าจะยากน่ะครับ เพราะข้อมูลมันรันมานานหลายปีแล้วครับ แต่อันนี้เค้าเพิ่มมา request เพิ่ม และถ้าไปเพิ่ม column จะกลายเป็นว่าต้องมานั่งไล่แก้โปรแกรมเก่าๆอีกเพียบเลยอ่ะครับ
ตอนแรกก็คิดจะเอาออกมาคำนวนในโปรแกรมเหมือนกันครับ (เหมือที่คุณ lancaster แนะนำ) แต่อยากรู้ว่ามันจะมี query ที่สามารถดึงออกมาได้หรือป่าวน่ะครับ (ปกติใช้แต่ query ง่ายๆ)
แล้วข้อแตกต่าง ข้อดี - ข้อเสีย ระหว่าง 2 แบบนี้ มีอะไรบ้างหรอครับ
จากที่เจอมา ถ้ายิ่งคำนวณใน
lancaster Mon, 20/05/2013 - 21:29
In reply to เรื่องเพิ่ม column by superballsj2
จากที่เจอมา ถ้ายิ่งคำนวณใน sql มันจะยิ่งช้าตรง database ครับ พอช้าก็จะต้องเปิด conn ค้างนาน เป็นต้นเหตุของ conn เต็ม แล้วยังมีเรื่อง memory ที่ alloc ไว้ให้แต่ละ conn อีกครับ สุดท้ายแรมหมด 555
อ้อ ถ้าเคสของคุณอารมณ์คล้ายๆ gen report ที่ไม่ได้รันบ่อย แบบนั้นใช้ sql ก็ไม่ค่อยต่างกับดึงมาคำนวณเองเท่าไหร่ครับ เคสที่เห็นผลจริงๆคือเป็น query ที่รันเยอะขึ้นตามปริมาณคนเข้าเว็บอะไรงี้ครับ
ใช่ครับ อันนี้แค่ gen report
superballsj2 Tue, 21/05/2013 - 09:52
In reply to จากที่เจอมา ถ้ายิ่งคำนวณใน by lancaster
ใช่ครับ อันนี้แค่ gen report เฉยๆ ผมก็ไม่เคยนึกถึงเคสนั้นเลย เพราะทำแต่แอปของบริษัทที่มีคนใช้แค่กลุ่มเล็กๆ
ขอบคุณสำหรับความรู้ครับผม
แนะนำอีกวิธีไม่รู้ว่าได้รึเปล
gogogokrit Sat, 01/06/2013 - 21:54
แนะนำอีกวิธีไม่รู้ว่าได้รึเปล่านะครับ
คือเขียน Store prodedure สร้าง Table ที่เก็บแยกออกมาตังหากเลย
นอกจากนี้หากยังไม่จำเป็นต้องการทั้งหมดด้วย ให้กำหนดใน Store prodedure ให้ query data มาใส่ table ใหม่ ที่ต้องการเป็นทีละช่วงๆ ไปก่อน (แบบแบ่งออกทีละ 3 เดือนเป้นต้น) เพื่อไม่ได้เป็นภาระของ server มากเกินไป คิดว่าค่อยๆ ทำน่าจะใช้เวลาไม่นานเท่าไหร่ ก็จะได้ table ที่สมบูรณ์หมดทั้งชุด อีก 1 ตาราง แล้วหลังจากนั้นก็เพิ่มเงื่อนไขให้เมื่อเกิด ts ใหม่ๆ ให้ update ตารางใหม่ตาม
ในความคิดเห็นส่วนโจทย์แบบนี้ คุณน่าจะต้องเอามาทำแบบ BI ด้วย ซึ่งปกติงานแบบนี้จะหนักที่ DB server มหาศาล
อีกนึดนึง ผมไม่แน่ใจว่าที่ต้องการต้องระบุ RecNo ด้วยไหม ถ้าคาดเดาช่วงข้อมูลที่ต้องใช้จะสามารถเตรียมข้อมูลเฉพาะส่วนไปก่อนได้ (แล้วมาเติมส่วนที่เหลือในช่วงที่ server ว่างงาน)