توليد تسلسل تاريخ PostgreSQL وتوليد_سلسلة

مرة واحدة في أحد المشاريع كنا بحاجة إلى وضع تقرير عن المعاملات المالية للفترة مع مجموعة من المجاميع الفرعية في نهاية الشهر.

تكون المهمة بسيطة بشكل عام ، وتحدد الفترات المطلوبة في فاصل زمني كبير ، وربط كل عملية بفترة مناسبة ، وقم بتجميع المبلغ وإضافته.

لإنشاء فترات خلال الفاصل الزمني ، كنت عادةً ما أستخدم وظيفة gener_series ، التي أستخدمها غالبًا لإنشاء تسلسلات رقمية. لقد راجعت الوثائق حول إمكانية إنشاء تسلسل من التواريخ ، واعتبرت مثالًا ، وكتبت استفسارا وحيرت.

select gs::date from generate_series('2018-01-31', '2018-05-31', interval '1 month') as gs; 

كانت النتيجة غير متوقعة مثل المنطقية. لقد أنشأت وظيفة gener_series بشكل متكرر سلسلة من التواريخ بناءً على مبدأ إضافة التحول إلى القيمة السابقة بشكل متسلسل. في نفس الوقت ، في كل خطوة ، تم التحقق من صحة وتحرير التاريخ المستلم. لا يحدث 31 فبراير ، لذلك تم تحويل التاريخ إلى 28 فبراير وأضفت إضافة الشهر الإضافية التسلسل بالكامل إلى 28.

UPD تفسيرات بعد الاسئلة في التعليقات. بشكل عام ، فإن المهمة الأولية أوسع - لتجميع البيانات في الأيام التعسفية من الشهر. على سبيل المثال ، قم بالتجميع في اليوم العشرين من كل شهر ، في اليوم الخامس عشر ، ولكن لا توجد مشاكل في مثل هذه التواريخ عند التوليد. يجب أن تعمل الآلية التي نبحث عنها بشكل متساوٍ على بناء سلسلة من 10 أرقام من كل شهر ، و 21 رقمًا والعمل بشكل صحيح على نهاية الأشهر.

أتساءل كيف ستتصرف عملية الإضافة لعدة أشهر دفعة واحدة؟ ماذا سيحدث إذا أضفنا الفاصل الزمني ليس بشكل متكرر ، ولكن "بشكل مجمّع"؟

 select '2018-01-31'::date +interval '1 mons' 28.02.2018 select '2018-01-31'::date +interval '2 mons' 31.03.2018 

في هذه الحالة ، تتم الإضافة بصراحة.
كيفية إنشاء التواريخ اللازمة باستخدام هذا النهج؟

إذا كان عدد الأشهر معروفًا ، فمن السهل جدًا:

 select '2018-01-31'::date +make_interval(0, i) as gs from generate_series(0, 4, 1) as i 

ماذا لو عرفت فقط تاريخ البدء وتاريخ الانتهاء؟
يمكن حل هذه المشكلة ببساطة عن طريق كتابة وظيفة مخزنة ودورة بسيطة فيها ، ومع ذلك ، نحن مهتمون بخيار التنفيذ عندما لا يكون هناك إمكانية أو رغبة في سد بنية قاعدة البيانات بأشياء غير ضرورية.
دعونا نحاول اختزال المهمة إلى المهمة السابقة.

الشفرة التالية هي إلى حد ما لوحة توصيل ولا تدعي أنها أنيقة ؛ نكتب خيارات الاستعلام الأولى في الشركة مع التركيز على مرونة وقابلية التبديل للكتل

 /*  -  ,         ,      */ with dates as ( select '2018-01-31'::date as dt1, '2018-05-31'::date as dt2 ), /*      ""  */ g_age as ( select age( (select dt2 from dates), (select dt1 from dates)) ), /*       (*12 + )   +1       */ months as ( select (extract(year from (select * from g_age))*12 + extract(month from (select * from g_age))+1)::integer ), /*  ,           -   ,       */ seq as( select ((select dt1 from dates) + make_interval(0, gs)) as gs from generate_series ( 0, (select * from months), 1 ) as gs where ((select dt1 from dates) + make_interval(0, gs)) <= (select dt2 from dates) ) /*         */ select * from seq 

اتضح أن الحل مرهق إلى حد ما ، ولكنه يعمل ومن السهل جدًا دمجه في الطلبات الأخرى من خلال الآلية.
لقد نفذنا التقرير ، لكن فكرة أن هذا الطلب ليس مرهقًا فحسب ، بل يقتصر استخدامه أيضًا عن طريق خطوات فقط على مدار شهور كاملة ، لم يعطِ الراحة.

الخيار 2
بعد فترة ، بدا لي أن إنشاء التاريخ المتسلسل هو في الأساس إجراء تعاودي. ليس فقط في شكله الخالص ، لأنه في حالتنا ، يؤدي حساب التاريخ التالي من التاريخ السابق إلى المشكلة الأصلية. ولكن في كل خطوة يمكننا زيادة الفاصل الزمني المضاف إلى بداية الدورة الشهرية:

 /*    -,     timestamp */ with recursive dates as ( select '2018-01-31'::timestamp as dt1, '2018-05-31'::timestamp as dt2, interval '1 month' as interval ), /*           ,          ,   .  ,        */ pr AS( select 1 as i, (select dt1 from dates) as dt union select i+1 as i, ( (select dt1 from dates) + ( select interval from dates)*i)::timestamp as dt from pr where ( ((select dt1 from dates) + (select interval from dates)*i)::timestamp) <=(select dt2 from dates) ) select dt as gs from pr; 

يعمل هذا الاستعلام بشكل صحيح مع أي فترات زمنية وفترات إدخال.