SQL โดยใช้ผลลัพธ์จากแถวก่อนหน้า

ฉันกำลังพยายามคำนวณแบบ Excel ใน SQL ซึ่งเกี่ยวข้องกับการใช้อัตราการปิด (ClRate) จากแถวก่อนหน้า และใช้เพื่อคำนวณค่าในแถวถัดไป ตารางเริ่มตั้งแต่วันที่ 1 มกราคม และมี 1,000 แถว และแต่ละแถวมีข้อมูลที่ทราบและข้อมูลที่ต้องคำนวณ (แสดงใน [ ])

Date     RecQty   RecAmt     IssQty        IssAmt       ClQty        ClAmt          ClRate
1 Jan       -        -           -           -           100         $20,000          200
2 Jan      +10      +$2100       -5         [ ]          [  ]         [  ]            [ ]

การคำนวณเพื่อสร้างผลลัพธ์ที่ต้องการอยู่ในตารางด้านล่าง

Date     RecQty   RecAmt     IssQty        IssAmt       ClQty        ClAmt             ClRate
1 Jan                                                    100         $20,000             200
2 Jan      +10      +$2100       -5       -[200*5]   [100+10-5]  [20,000+2100-200*5]  [ClAmt/ClQty]

IssAmt ในแต่ละวันจะคำนวณโดยการคูณ IssQty ด้วย ClRate ของวันก่อนหน้า ClQty คำนวณเป็น ClQty วันก่อนหน้า + RecQty วันปัจจุบัน - IssQty วันปัจจุบัน ClAmt คำนวณเป็นวันก่อนหน้า ClAmt+ RecAmt วันปัจจุบัน - IssAmt วันปัจจุบัน สุดท้าย ClRate ในแต่ละวันจะคำนวณเป็น ClAmt / ClQty

ClRate เดียวที่ทราบคือแถวรายการเปิดของตาราง (1 มกราคม) หลังจากนั้น จะต้องคำนวณ ClRate สำหรับแต่ละแถวถัดไป

ใน Excel คุณเพียงทำการคำนวณนี้โดยเชื่อมโยงเซลล์ที่ต้องการของแถวก่อนหน้าแล้วคัดลอก/วางสูตรไปยังแถวทั้งหมดด้านล่าง

คุณจะทำเช่นนี้ใน SQL ได้อย่างไร? ฉันได้ลองเข้าร่วม CTE, ลูป และ LAG ด้วยตนเองแล้ว ดูเหมือนว่าสิ่งเหล่านี้จะไม่ได้ผล เหตุผลก็คือไม่ทราบ ClRate สำหรับแต่ละแถวตั้งแต่วันที่ 2 มกราคมเป็นต้นไป และแม้ว่า Excel จะสามารถจัดการผลลัพธ์การคำนวณได้ทันทีที่ใช้ในแถวต่อไปนี้ แต่ SQL ก็ไม่สามารถทำเช่นนี้ได้

การขอความช่วยเหลือเพื่อแก้ไขปัญหานี้ ฉันใช้ SQL Server 2017 และ SSMS หากจำเป็นฉันสามารถให้รหัสได้

ตาราง DDL

CREATE TABLE [Auto].[IronOreTbl](
    [Id] [int] NULL,
    [Counter] [int] NULL,
    [TDate] [date] NOT NULL,
    [RecQty] [decimal](16, 2) NULL,
    [RecAmt] [decimal](16, 2) NULL,
    [IssQty] [decimal](16, 2) NULL,
    [IssAmt] [decimal](16, 2) NULL,
    [ClQty] [decimal](16, 2) NULL,
    [ClAmt] [decimal](16, 2) NULL,
    [ClRate] [decimal](16, 2) NULL

) ON [PRIMARY]
GO

INSERT INTO [Auto].[IronOreTbl]
    ([Id],[Counter],[TDate],[RecQty],[RecAmt],[IssQty],[IssAmt],[ClQty],[ClAmt],[ClRate])
    VALUES
    (1,0,'2019-01-01',NULL,NULL,NULL,NULL,100,20000,200),
    (2,1,'2019-01-02',10,2100,5,NULL,105,NULL,NULL),
    (3,2,'2019-01-03',8,1600,2,NULL,111,NULL,NULL),
    (4,3,'2019-01-04',20,2400,10,NULL,121,NULL,NULL)

ความพยายาม CTE

;WITH ClAmtCTE AS

    (
    SELECT
        Id,RecQty,RecAmt,IssQty,ClQty,ClAmt,ClRate
        ,EffRate = ClRate
        ,CumHoldVal= ClAmt
        --CAST(ClAmt AS decimal(16,2))
        ,CumClRt=CAST(ClRate AS decimal(16,2))
        ,[Counter]
    FROM
        [Auto].IronOreTbl
    WHERE
        Id=1 

    UNION ALL

    SELECT
        C2.Id,C2.RecQty,c2.RecAmt,C2.IssQty,C2.ClQty,C2.ClAmt,c2.ClRate,
        EffRate = (SELECT CumClRt WHERE C2.ID=C2.[Counter]+1), 
        CumRN = 
        CAST(
            (
            CumHoldVal+ISNULL(C2.RecAmt,0)-
            (EffRate)*ISNULL(C2.IssQty,0)
            )
        AS decimal(16,2)
        ),
        CumClRt=CAST(CumHoldVal/C2.ClQty AS decimal(16,2)),
        C2.[Counter],

    FROM
        [Auto].IronOreTbl C2 
    INNER JOIN ClAmtCTE C1 ON C1.Id = C2.[Counter]

sql sql-server common-table-expression sql-server-2017 self-join
person Mangésh Méhendalé    schedule 04.05.2020    source แหล่งที่มา
comment
คุณได้แท็ก CTE ซึ่งมาถูกทางแล้ว ความพยายามในการสืบค้น CTE ของคุณมีลักษณะอย่างไร   -  person devlin carnate    schedule 04.05.2020
comment
ฉันกำลังเพิ่มตาราง DDL และ CTE ในโพสต์   -  person Mangésh Méhendalé    schedule 04.05.2020

คำตอบ (1)


arrow_upward
0
arrow_downward

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

;WITH ClAmtCTE AS

                (
                SELECT
                    Id,RecQty,RecAmt,IssQty,ClQty,ClAmt,ClRate
                    ,EffRate = ClRate
                    ,CumHoldVal= ClAmt
                    --CAST(ClAmt AS decimal(16,2))
                    ,CumClRt=CAST(ClRate AS decimal(16,2))
                    ,[Counter]
                FROM
                    [Auto].IronOreTbl
                WHERE
                    Id=1 

                UNION ALL

                SELECT
                    C2.Id,C2.RecQty,c2.RecAmt,C2.IssQty,C2.ClQty,C2.ClAmt,c2.ClRate,
                    EffRate = (SELECT CumClRt WHERE C2.ID=C2.[Counter]+1), 
                    CumRN = 
                    CAST(
                        (
                        CumHoldVal+ISNULL(C2.RecAmt,0)-
                        ((SELECT CumClRt WHERE C2.ID=C2.[Counter]+1))*ISNULL(C2.IssQty,0)
                        )
                    AS decimal(16,2)
                    ),
                    CumClRt=CAST((CumHoldVal+ISNULL(C2.RecAmt,0)-
                        ((SELECT CumClRt WHERE C2.ID=C2.[Counter]+1))*ISNULL(C2.IssQty,0))/C2.ClQty AS decimal(16,2)),
                    C2.[Counter]

                FROM
                    [Auto].IronOreTblC2 
                INNER JOIN ClAmtCTE C1 ON C1.Id = C2.[Counter]
                )

                UPDATE [Auto].IronOreTbl
                SET ClRate = T1.CumClRt,
                ClAmt=CumHoldVal
                FROM ClAmtCTE T1
                INNER JOIN [Auto].IronOreTbl T2
                ON T1.ID = T2.Id
                OPTION (MAXRECURSION 0)

การพยายามและในสิ่งที่ดูเหมือนว่าการแก้ปัญหานั้นได้สอนฉันหลายอย่าง โดยหลักๆแล้วได้แก่:

  1. การคำนวณที่คุณสามารถตั้งค่าและรันในฮาร์ทบีทในสเปรดชีตนั้นเป็นอะไรก็ได้ยกเว้นใน SQL

  2. Excel มีความสามารถในการคำนวณทันทีและใช้ผลลัพธ์ที่คำนวณได้ทันทีในการคำนวณแบบอ้างอิง SQL ไม่สามารถทำเช่นนี้ได้ การมีความสามารถนี้ใน Excel หมายความว่าคุณไม่จำเป็นต้องวนซ้ำข้อมูล - หนึ่งครั้งเพื่อให้ Excel คำนวณผลลัพธ์ จากนั้นให้ Excel นำผลลัพธ์ไปใช้ในตำแหน่งที่จำเป็น

  3. สำหรับการใช้งานและอรรถประโยชน์ทั้งหมดในฐานะสื่อเก็บข้อมูล SQL มีวิธีอีกยาวไกลในการเข้าร่วมการคำนวณมาตรฐานในโลกแห่งความเป็นจริง - ดังตัวอย่างนี้ สินเชื่อหลายรายการ การเปลี่ยนแปลงอัตราดอกเบี้ย และการคำนวณดอกเบี้ยที่เกี่ยวข้อง ฯลฯ

person Mangésh Méhendalé    schedule 06.05.2020