بر اساس سوابق موجود در گروه محاسبه کنید و نتیجه را در اولین رکورد پر کنید – از SQL تا SPL #8

توضیحات و تجزیه و تحلیل مشکل:

ID و NBR جدول پایگاه داده MS SQL زمینه های گروه بندی هستند. پس از مرتب سازی سوابق موجود در گروه بر اساس قسمت زمانی DT ، ترتیب 0 و 1 در وضعیت وضعیت “وضعیت” متفاوت است.

وظیفه: اکنون ما باید سه ستون محاسبه شده را مطابق با قوانین خاص بر اساس سوابق موجود در گروه تولید کنیم و آنها را در اولین رکورد هر گروه پر کنیم ، در حالی که 0 را برای سایر سوابق دیگر پر می کنیم.

محاسبه ستون CONT1STA1: هنگامی که حداقل یک رکورد در این گروه با وضعیت = 1 وجود دارد ، مقدار 1 را اختصاص دهید ، در غیر این صورت 0 را پر کنید.

محاسبه ستون DateLagindays: هنگامی که حداقل یک رکورد در این گروه با وضعیت = 1 وجود دارد ، و حداقل یک رکورد با وضعیت = 0 وجود دارد ، و اولین رکورد دوم زودتر از اولین رکورد اولی است ، مقدار فاصله روز بین این دو را اختصاص دهید ، در غیر این صورت 0 را پر کنید.

عود ستون محاسبه شده: هنگامی که حداقل یک رکورد با وضعیت = 1 در این گروه وجود دارد ، و حداقل یک رکورد با وضعیت = 0 وجود دارد و اولین رکورد اولی زودتر از آخرین رکورد دوم است ، مقدار 1 را اختصاص دهید ، در غیر این صورت 0 را پر کنید.

مقایسه کد:

راه حل SQL：

with cte as (
    select id, nbr, dt, status
        , row_number() over (partition by id, nbr order by id, nbr, dt asc, status desc) rn
        , max(status) over (partition by id, nbr) partition_status
        , min(case when Status = 1 then dt else null end) over (partition by id, nbr) partition_dt1
        , max(case when Status = 1 then dt else null end) over (partition by id, nbr) partition_dt2
    from tb
)
select id, nbr, dt, status
    , case when rn = 1 then partition_status else 0 end Cont1Sta1
    , case when rn = 1 then datediff(day, dt, coalesce(partition_dt1, dt)) else 0 end DateLagInDays
    , case when rn = 1 and exists (select 1 from cte c2 where c2.id = c1.id and c2.nbr = c1.nbr and c2.dt > c1.partition_dt2) then 1 else 0 end Recurrence
from cte c1
order by id, nbr, dt asc, status desc;

پس از گروه بندی SQL ، باید بلافاصله جمع شود و زیر مجموعه ها را نمی توان برای محاسبات پیچیده تر چند مرحله ای نگه داشت. در این حالت ، چندین توابع پنجره فقط برای محاسبات مکرر قابل استفاده است که از نظر کد دست و پا گیر است. SQL اعداد توالی طبیعی ندارد ، بنابراین ما باید ابتدا از توابع پنجره استفاده کنیم تا ابتدا شماره های دنباله ای را تولید کنیم. بیان آخرین مورد هم مناسب نیست و در اینجا باید با تاریخ های مرتبط با آن مقایسه کنیم.

راه حل SPL:

SPL می تواند از زیر مجموعه های گروه بندی شده ، با شماره های توالی طبیعی استفاده و استفاده مجدد کند ، و می تواند مستقیماً به اولین یا آخر مراجعه کند: try.demo

A1 ： داده های بار ، با 3 ستون محاسبه شده به طور پیش فرض به 0 و طبقه بندی شده توسط تاریخ.

A2 ： گروه اما جمع نیست.

A3 ： داده های هر گروه را محاسبه کنید: موقعیت ضبط P1F را برای اولین وضعیت = 1 ، موقعیت ضبط P0F برای وضعیت اول = 0 و موقعیت ضبط P0L برای آخرین وضعیت = 0 محاسبه کنید. سه ستون محاسبه شده را به نوبه خود با توجه به منطق تجارت محاسبه کنید.

عملکرد PSelect موقعیت سابقه ای را که معیارها را برآورده می کند ، برمی گرداند و پیش فرض برای بازگشت اولین مورد ، A را نشان می دهد که همه موقعیت ها را بازگرداند. تابع M می تواند مقادیر را بر اساس موقعیت بازیابی کند ، جایی که -1 نشان دهنده آخرین مورد است.

بارگیری کردن esproc spl به صورت رایگان و امتحان کنید: بارگیری رایگانبشر