Tags:

คือผมต้องการดึงข้อมูลย้อนหลัง 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 แต่ผมคิดว่ามันน่าจะมีวิธีที่ดี หรือง่ายกว่านี้มั้ยอ่ะครับ

Get latest news from Blognone
By: ionz
AndroidWindows
on 15 May 2013 - 23:54 #573515

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

By: superballsj2
iPhoneWindowsIn Love
on 16 May 2013 - 09:12 #573726 Reply to:573515
superballsj2's picture

คิดว่ารูปแบบนี้น่าจะใช้ได้

แล้วถ้า RecNo มันไม่เรียงกันล่ะครับ

เพราะมีเงื่อนไขที่ต้อง filter อีกอ่ะครับ เช่น Where LotNo = 'A'

By: ionz
AndroidWindows
on 16 May 2013 - 11:38 #573852 Reply to:573726

หมายความว่า 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

By: figgaro
ContributorAndroidWindows
on 16 May 2013 - 12:05 #573602
figgaro's picture

SELECT RecNo , LotNo , Qty , AVG(Qty) OVER (
ORDER BY RecNo
ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW
) AS Avg30Qty
FROM table

ลองไปศึกษาเรื่อง Analytic Function ดูครับ


อนุญาตเฉพาะเว็บส่วนตัวเท่านั้น

By: Meen
Windows PhoneAndroid
on 15 May 2013 - 23:42 #573611

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 ตัวรึเปล่าเฉยๆ

By: lancaster
ContributorUbuntuWindows
on 16 May 2013 - 02:22 #573660

วิธีตรงๆมีคนตอบไปแล้ว

ถ้าอยากได้วิธีที่ performance ดีสุด ดึงมาคำนวณในโปรแกรมเองเลยครับ

By: saknarak
Android
on 16 May 2013 - 07:48 #573700
saknarak's picture

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

By: superballsj2
iPhoneWindowsIn Love
on 16 May 2013 - 09:03 #573722 Reply to:573700
superballsj2's picture

อ่าว กรรม ของผมเป็น 2008 จะได้มั้ยเนี่ย เด๋วลองดูก่อน ขอบคุณครับ

By: superballsj2
iPhoneWindowsIn Love
on 16 May 2013 - 09:28 #573735
superballsj2's picture

ขอบคุณทุกท่านครับ

ผมลืมบอกเงื่อนไขไปอีกอย่างอ่ะครับ ว่า RecNo มันไม่เรียงกัน เพราะต้องมีการ filter ข้อมูลด้วยอ่ะครับ ทีนี้จะ Join ยังไงดีอ่ะครับ -*-

ปล. ของผมเป็นเวอชั่น 2008 ใช้ Analytic Function ไม่ได้

By: saknarak
Android
on 16 May 2013 - 13:58 #573925
saknarak's picture

ไม่เรียงไม่เป็นไร
ที่ต้องการทราบคือ อยากได้จำนวน 30 แถวย้อนหลัง หรือว่าอยากได้ RecNo-31 ถึง RecNo-1

By: saknarak
Android
on 16 May 2013 - 14:12 #573932
saknarak's picture
;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
By: zixs
ContributoriPhoneWindows PhoneAndroid
on 20 May 2013 - 01:40 #575207
zixs's picture

ดูดขึ้นมาทั้งหมดแล้วเอามาประมวลผลในโปรแกรมครับ
ถ้าดาต้ายังเพิ่มขึ้นอีกแนะนำให้เพิ่มอีก column เพื่อเก็บค่านี้เลยนะครับ
จะได้ความเร็วสูงสุดในการดึงข้อมูลออกมา


Blog: https://medium.com/@tanakritsai

By: superballsj2
iPhoneWindowsIn Love
on 20 May 2013 - 17:14 #575456 Reply to:575207
superballsj2's picture

เรื่องเพิ่ม column คิดว่าน่าจะยากน่ะครับ เพราะข้อมูลมันรันมานานหลายปีแล้วครับ แต่อันนี้เค้าเพิ่มมา request เพิ่ม และถ้าไปเพิ่ม column จะกลายเป็นว่าต้องมานั่งไล่แก้โปรแกรมเก่าๆอีกเพียบเลยอ่ะครับ

ตอนแรกก็คิดจะเอาออกมาคำนวนในโปรแกรมเหมือนกันครับ (เหมือที่คุณ lancaster แนะนำ) แต่อยากรู้ว่ามันจะมี query ที่สามารถดึงออกมาได้หรือป่าวน่ะครับ (ปกติใช้แต่ query ง่ายๆ)

แล้วข้อแตกต่าง ข้อดี - ข้อเสีย ระหว่าง 2 แบบนี้ มีอะไรบ้างหรอครับ

By: lancaster
ContributorUbuntuWindows
on 20 May 2013 - 21:29 #575561 Reply to:575456

จากที่เจอมา ถ้ายิ่งคำนวณใน sql มันจะยิ่งช้าตรง database ครับ พอช้าก็จะต้องเปิด conn ค้างนาน เป็นต้นเหตุของ conn เต็ม แล้วยังมีเรื่อง memory ที่ alloc ไว้ให้แต่ละ conn อีกครับ สุดท้ายแรมหมด 555

อ้อ ถ้าเคสของคุณอารมณ์คล้ายๆ gen report ที่ไม่ได้รันบ่อย แบบนั้นใช้ sql ก็ไม่ค่อยต่างกับดึงมาคำนวณเองเท่าไหร่ครับ เคสที่เห็นผลจริงๆคือเป็น query ที่รันเยอะขึ้นตามปริมาณคนเข้าเว็บอะไรงี้ครับ

By: superballsj2
iPhoneWindowsIn Love
on 21 May 2013 - 09:52 #575774 Reply to:575561
superballsj2's picture

ใช่ครับ อันนี้แค่ gen report เฉยๆ ผมก็ไม่เคยนึกถึงเคสนั้นเลย เพราะทำแต่แอปของบริษัทที่มีคนใช้แค่กลุ่มเล็กๆ

ขอบคุณสำหรับความรู้ครับผม

By: gogogokrit on 1 June 2013 - 21:54 #581024

แนะนำอีกวิธีไม่รู้ว่าได้รึเปล่านะครับ
คือเขียน Store prodedure สร้าง Table ที่เก็บแยกออกมาตังหากเลย
นอกจากนี้หากยังไม่จำเป็นต้องการทั้งหมดด้วย ให้กำหนดใน Store prodedure ให้ query data มาใส่ table ใหม่ ที่ต้องการเป็นทีละช่วงๆ ไปก่อน (แบบแบ่งออกทีละ 3 เดือนเป้นต้น) เพื่อไม่ได้เป็นภาระของ server มากเกินไป คิดว่าค่อยๆ ทำน่าจะใช้เวลาไม่นานเท่าไหร่ ก็จะได้ table ที่สมบูรณ์หมดทั้งชุด อีก 1 ตาราง แล้วหลังจากนั้นก็เพิ่มเงื่อนไขให้เมื่อเกิด ts ใหม่ๆ ให้ update ตารางใหม่ตาม

ในความคิดเห็นส่วนโจทย์แบบนี้ คุณน่าจะต้องเอามาทำแบบ BI ด้วย ซึ่งปกติงานแบบนี้จะหนักที่ DB server มหาศาล

อีกนึดนึง ผมไม่แน่ใจว่าที่ต้องการต้องระบุ RecNo ด้วยไหม ถ้าคาดเดาช่วงข้อมูลที่ต้องใช้จะสามารถเตรียมข้อมูลเฉพาะส่วนไปก่อนได้ (แล้วมาเติมส่วนที่เหลือในช่วงที่ server ว่างงาน)