SQL menggunakan hasil dari baris sebelumnya

Saya mencoba melakukan perhitungan seperti Excel dalam SQL. Ini melibatkan penggunaan kurs penutupan (ClRate) dari baris sebelumnya dan menggunakannya untuk menghitung nilai di baris berikutnya. Tabel dimulai dari 1 Jan dan memiliki 1000 baris dan setiap baris memiliki data yang diketahui dan data yang perlu dihitung (ditunjukkan pada [ ])

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

Perhitungan untuk menghasilkan hasil yang diinginkan ada pada tabel di bawah ini

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 untuk setiap hari akan dihitung dengan mengalikan IssQty dengan ClRate hari sebelumnya. ClQty dihitung sebagai ClQty hari sebelumnya + RecQty hari ini - IssQty hari ini. ClAmt dihitung sebagai ClAmt hari sebelumnya+ RecAmt hari ini - IssAmt hari ini. Terakhir, ClRate untuk setiap hari dihitung sebagai ClAmt / ClQty

Satu-satunya ClRate yang diketahui adalah baris inventaris pembuka tabel (1 Jan) - setelah itu ClRate untuk setiap baris berikutnya perlu dihitung.

Di Excel, Anda cukup melakukan penghitungan ini dengan menautkan sel yang diperlukan pada baris sebelumnya dan menyalin/menempelkan rumus ke semua baris di bawahnya.

Bagaimana Anda melakukan ini di SQL? Saya telah mencoba menggabungkan sendiri CTE, loop, dan LAG - sepertinya tidak ada yang berhasil. Alasannya adalah ClRate untuk setiap baris mulai 2 Jan dan seterusnya tidak diketahui - dan meskipun Excel dapat menangani hasil komputasi dengan cepat yang digunakan pada baris berikutnya - SQL tidak dapat melakukan hal ini.

Mencari bantuan untuk menyelesaikan masalah ini. Saya menggunakan SQL Server 2017 dan SSMS. Jika diperlukan, saya dapat memberikan kodenya

Tabel 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)

Upaya 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 sumber
comment
Anda telah menandai CTE, yang berada di jalur yang benar. Seperti apa upaya kueri CTE Anda?   -  person devlin carnate    schedule 04.05.2020
comment
Saya menambahkan tabel DDL dan CTE ke postingan   -  person Mangésh Méhendalé    schedule 04.05.2020

Jawaban (1)


arrow_upward
0
arrow_downward

Kode berikut mencapai hasil yang diinginkan - meskipun saya sudah hampir melakukannya beberapa hari yang lalu, masih perlu waktu lama untuk mengikat bit terakhir; untuk mencocokkan ClRate dengan baris yang benar. Ada masalah tambahan yang ditemui ketika pada hari-hari di mana tidak ada masalah, tapi hanya kuitansi, tarif yang diambil berada di baris yang salah (saya masih tidak tahu mengapa dan bagaimana hal ini terjadi - yang saya tahu hanyalah kode sebelumnya diperlukan revisi untuk mengatasi perbedaan ini dan kode yang dimodifikasi memperhatikan hal yang sama)

;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)

Mencoba dan, menurut saya, menyelesaikannya telah mengajari saya beberapa hal. Terutama ini termasuk:

  1. Perhitungan yang dapat Anda atur dan jalankan dalam sekejap di spreadsheet sama sekali tidak ada di SQL

  2. Excel memiliki kemampuan untuk menghitung dengan cepat dan memanfaatkan hasil yang dihitung dengan cepat dalam perhitungan dependen. SQL tidak bisa melakukan ini. Memiliki kemampuan ini di Excel berarti Anda tidak perlu mengulangi data - sekali agar Excel menghitung hasilnya dan kemudian Excel menerapkan hasilnya di tempat yang diperlukan.

  3. Untuk semua penggunaan dan kegunaannya sebagai media penyimpanan data, SQL masih harus menempuh jalan panjang dalam menangani perhitungan standar dunia nyata - seperti contoh ini; banyak pinjaman, perubahan suku bunga dan perhitungan bunga yang relevan, dll.

person Mangésh Méhendalé    schedule 06.05.2020