Index: documentation/FEiN Trac Documentation =================================================================== --- documentation/FEiN Trac Documentation (revision efb6ea7601cafce9403e471fe0c1bbe357807bcc) +++ documentation/FEiN Trac Documentation (revision efb6ea7601cafce9403e471fe0c1bbe357807bcc) @@ -0,0 +1,5027 @@ +https://develop.finki.ukim.mk/projects/FEIN/wiki/WikiStart += FEiN = + +== Краток опис +Веќе некое време управувам со моите **финансии** во //Google Sheets//, каде што со тек на време направив **//налик-апликација//**, со доста функции за анализа на тие податоци. + +Начинот на кој управувам со мојот буџет и финансии е така што секојдневно **//внесувам трансакции//** кои со неколку __полиња__ ги **//полнат/празнат полињата//** кои покажуваат на која **//сметка(трансакциски, кредитни, кеш, девизни)** колку преостанало, колку е потрошено од истата и вкупно//. Исто го променуваат **//графикот на потрошувачка//** и //статистиките колку просечно сум истрошил во месецот, колку последните три дена и колку ми останува просечно да трошам на ден до крај на месецот//. Исто така имам и **//категории//** кои ги доделувам //рачно// на секоја трансакција кои подоцна полнат доделени нивни полиња, собирајќи и кажувајќи колку имам **//потрошено по категорија//**. Тие информации подоцна служат за **//креирање на графици за визуелна анализа на потрошеното//**. + +Цел овој систем добро ме служи но станува малце заморно да се внесуваат трансакциите рачно, притоа запазувајќи го форматот кој сам сум си го креирал и пазење да се внесат таговите и датите без синтаксички грешки, кои во почетокот беа подложни на човечки грешки, но остануваат и до ден денес. + +Со креирање на **овој систем** сакам да го **решам** првично тој **проблем**, __доведување на човечката грешка на минимум и намалување на времето за внесување на трансакциите__. Подоцна овој систем би овозможил полесно предавање на податоците во модел за машинско учење кој би предвидувал и проектирал информации околу личната потрошувачка... + +Системот го замислувам во форма на респонзивна веб апликација(најчесто користена од мобилен телефон) која има централна датабаза до која пристапува секој индивидуален корисник преку неговата сметка. + +**FEiN** е име кое на модерен начин го испишува изговорот на зборот Fin кој означува финансирање, но не обично, туку Fine-Tuned, кој збор исто така може да се испише на таков модерен начин. **FEiN** __исто така е и сленг збор во англискиот јазик кој означува голема желба/зависност. Така планирам мојот систем да стане дел од секојдневието, да створи зависност кај луѓето да си го менаџираат буџетот...__ + +== Изработува +||=Име и презиме=||=Индекс=|| +||Василаки Тоцили||211101|| + Предмет:: + Бази на податоци во 2025/2026 зимски семестар[[BR]] + Под менторство на:: + проф. д-р Вангел Ајановски + +== Фази на изработка +||=Фаза=||=Име на фаза=||=Статус=|| +||=Ф0=||[wiki:About Дефинирање на проектот]|| [[span(style=color: green, Одобрена )]] || +||=Ф1=||[wiki:ERModel Концептуален дизајн - ЕР Дијаграм и податочни побарувања]|| [[span(style=color: green, Одобрена )]] || +||=Ф2=||[wiki:RelationalDesign Логички и физички дизајн - Креирање база податоци (со SQL DDL)]|| [[span(style=color: green, Одобрена )]] || +||=Ф3=||[wiki:UseCaseModel Апликативен дизајн - Случаи на употреба и сценарија за пристап до базата]|| [[span(style=color: green, Одобрена )]] || +||=Ф4=||[wiki:PrototypeApplication Прототип апликација]|| [[span(style=color: green, Одобрена )]] || + +||=Milestone 1=||Презентација на прототип || [[span(style=color: green, одржана)]] || + +||=Ф5=||[wiki:Normalization Нормализација]|| [[span(style=color: green, Одобрена )]] || +||=Ф6=||[wiki:AdvancedReports Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)]|| [[span(style=color: green, Одобрена )]] || +||=Ф7=||[wiki:AdvancedDatabaseDevelopment Напреден развој на датабаза (Тригери, погледи)]|| [[span(style=color: green, Одобрена)]] || +||=Ф8=||[wiki:AdvancedApplicationDevelopment Напреден апликативен развој (Трансакции, Pooling)]|| [[span(style=color: green, Одобрена)]] || +||=Ф9=||[wiki:OtherTopics Other topics (Performance, Security, ...)]|| [[span(style=color: blue, Чека на одобрување)]] || + +||=Milestone 2=||Презентација на финалната верзија од проектот|| [[span(style=color: red, не е одржана)]] || + +[wiki:Version1 Верзија 1] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Version1 += FEiN = + +== Краток опис +Веќе некое време управувам со моите **финансии** во //Google Sheets//, каде што со тек на време направив **//налик-апликација//**, со доста функции за анализа на тие податоци. + +Начинот на кој управувам со мојот буџет и финансии е така што секојдневно **//внесувам трансакции//** кои со неколку __полиња__ ги **//полнат/празнат полињата//** кои покажуваат на која **//сметка(трансакциски, кредитни, кеш, девизни)** колку преостанало, колку е потрошено од истата и вкупно//. Исто го променуваат **//графикот на потрошувачка//** и //статистиките колку просечно сум истрошил во месецот, колку последните три дена и колку ми останува просечно да трошам на ден до крај на месецот//. Исто така имам и **//категории//** кои ги доделувам //рачно// на секоја трансакција кои подоцна полнат доделени нивни полиња, собирајќи и кажувајќи колку имам **//потрошено по категорија//**. Тие информации подоцна служат за **//креирање на графици за визуелна анализа на потрошеното//**. + +Цел овој систем добро ме служи но станува малце заморно да се внесуваат трансакциите рачно, притоа запазувајќи го форматот кој сам сум си го креирал и пазење да се внесат таговите и датите без синтаксички грешки, кои во почетокот беа подложни на човечки грешки, но остануваат и до ден денес. + +Со креирање на **овој систем** сакам да го **решам** првично тој **проблем**, __доведување на човечката грешка на минимум и намалување на времето за внесување на трансакциите__. Подоцна овој систем би овозможил полесно предавање на податоците во модел за машинско учење кој би предвидувал и проектирал информации околу личната потрошувачка... + +Системот го замислувам во форма на мобилна апликација која има централна датабаза до која пристапува секој индивидуален корисник преку неговата сметка. + +**FEiN** е име кое на модерен начин го испишува изговорот на зборот Fin кој означува финансирање, но не обично, туку Fine-Tuned, кој збор исто така може да се испише на таков модерен начин. **FEiN** __исто така е и сленг збор во англискиот јазик кој означува голема желба/зависност. Така планирам мојот систем да стане дел од секојдневието, да створи зависност кај луѓето да си го менаџираат буџетот...__ + +== Изработува +||=Име и презиме=||=Индекс=|| +||Василаки Тоцили||211101|| + Предмет:: + Бази на податоци во 2024/2025 зимски семестар[[BR]] + Под менторство на:: + проф. д-р Вангел Ајановски + +== Фази на изработка +||=Фаза=||=Име на фаза=||=Статус=|| +||=Ф0=||[wiki:About Дефинирање на проектот]|| [[span(style=color: green, Одобрена )]] || +||=Ф1=||[wiki:ERModel Концептуален дизајн - ЕР Дијаграм и податочни побарувања]|| [[span(style=color: green, Одобрена )]] || +||=Ф2=||[wiki:RelationalDesign Логички и физички дизајн - Креирање база податоци (со SQL DDL)]|| [[span(style=color: green, Одобрена )]] || +||=Ф3=||[wiki:UseCaseModel Апликативен дизајн - Случаи на употреба и Сценарија за пристап до базата - SQL Погледи]|| [[span(style=color: green, Одобрена )]] || +||=Ф4=||[wiki:AdvancedReports Напредни извештаи од базата (SQL и складирани процедури)]|| [[span(style=color: green, Одобрена )]] || +||=Ф5=||[wiki:PrototypeApplication Прототип апликација - Основни функционалности]|| [[span(style=color: green, Одобрена )]] || +||=Ф6=||[wiki:Normalization Нормализација и подобрување на дизајнот]|| [[span(style=color: orange, Започната)]] || +||=Ф7=||[wiki:AditionalApplicationDevelopment Напреден апликативен развој]|| [[span(style=color: orange, Започната)]] || + + +https://develop.finki.ukim.mk/projects/FEIN/wiki/About += FEiN = + +== Членови на тимот + +||=Име и презиме=||=Индекс=|| +|| Василаки Тоцили || 211101 || + +== Краток опис на проектот + +**FEiN** е респонзивна веб апликација(најчесто користена од мобилен телефон) која овозможува едноставно и ефективно **//управување со личните финансии//**. + +Корисниците **//внесуваат дневни трансакции//**, кои автоматски се пресметуваат и //распределуваат по сметки (трансакциски, кредитни, кеш, девизни)//, **//давајќи увид во преостанатите средства и потрошеното//**. Секоја трансакција може да се //**категоризира**// за понапредна анализа, овозможувајќи **//графички приказ//** на трошењето по категории. Апликацијата **//генерира статистики//** за **//месечна потрошувачка, дневни трошоци, како и проекции за идно трошење**//. + +**FEiN** користи **централна база** на податоци, овозможувајќи сигурност и конзистентност на податоците за сите корисници. Со **FEiN**, наместо со //Excel-базирано следење//, времето за внесување на податоци се намалува, а шансите за човечки грешки се сведуваат на минимум. Во иднина, **FEiN** би овозможил интеграција со модели за машинско учење за предвидување и оптимизирање на личниот буџет. + +== Детален опис на проектот +* **За кого е наменет проектот односно кој според вашата замисла ќе биде одговорен да ја поседува базата на апликацијата и да ја обезбедува како сервис - дали е тоа фирма/институација/организација или е поединец и краток опис за нивните потреби?** + Проектот е наменет за //индивидуални корисници// кои сакаат да управуваат со своите лични финансии на едноставен и интуитивен начин. Корисниците креираат //сметки// со кои пристапуваат до //централна база// на податоци преку апликацијата. Базата ќе биде администрирана централизирано од тимот што стои зад //FEiN//, со цел обезбедување на сигурност, конзистентност и лесен пристап од било кој уред. +* **Какви проблеми во фирмата или организацијата ќе решавате?** + //FEiN// ги решава следните проблеми поврзани со управување на личните финансии: + 1. //Осигурување на точност при внесување податоци//: За разлика од //Excel//, каде што форматните грешки и пропусти се чести, //FEiN// ќе обезбеди //стандардизиран начин за внесување// на трансакции. Ова ќе ја намали можноста за грешки и ќе им олесни на корисниците во следењето на нивниот буџет. + 2. //Подобрено корисничко искуство//: Апликацијата ќе понуди интуитивен и лесен за користење интерфејс, кој ќе го замени комплексното и визуелно ограничено искуство на //Excel//. + 3. //Автоматски пресметки и визуализации//: //FEiN// автоматски ќе ги пресметува вкупните потрошени и преостанати средства, ќе ги категоризира трошоците и ќе прикажува визуелизации за полесна анализа на финансиските податоци. + 4. //Пристап до детални статистики без дополнителни трошоци//: //За разлика од други апликации//, //FEiN// ги нуди сите статистики бесплатно, овозможувајќи длабински увид во финансиските навики. +- **Какви видови корисници ќе има апликацијата - во смисла на типови вработени во фирмата/институцијата или клиенти?** + //FEiN// е наменета исклучиво за //индивидуални корисници//. Во почетната фаза, сите корисници ќе имаат //бесплатен пристап// до апликацијата и сите нејзини функционалности. //Нема// да има различни типови на //кориснички профили// или //премиум функции// во првата верзија. +- **Ако има други слични проекти во фирмата или во околината, или веќе постојат готови решенија во светот, во што е различен вашиот предлог проект.** + FEiN се издвојува по следното: + 1. //Над Excel-базираните системи//: Решавајќи ги проблемите со рачно внесување и форматни грешки, FEiN обезбедува структурирано и автоматизирано управување со личните финансии, во значително подобрено корисничко искуство. + 2. //Конкуренција со слични апликации//: За разлика од многу слични апликации кои нудат напредни статистики и прогнози како премиум функции, FEiN ги нуди истите //бесплатно// и со //отворен код// од самиот почеток. +- **Дали се работи за веб, мобилна и/или десктоп апликација?** + FEiN е планирана како //респонзивна веб апликација(најчесто користена од мобилен телефон)//, со фокус на достапност и практичност за корисниците, кои можат лесно да внесуваат трансакции и да ги следат своите финансии од својот паметен телефон. + +[wiki:AboutVersion1 Верзија 1] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/AboutVersion1 += FEiN = + +== Членови на тимот + +||=Име и презиме=||=Индекс=|| +|| Василаки Тоцили || 211101 || + +== Краток опис на проектот + +**FEiN** е мобилна апликација која овозможува едноставно и ефективно **//управување со личните финансии//**. + +Корисниците **//внесуваат дневни трансакции//**, кои автоматски се пресметуваат и //распределуваат по сметки (трансакциски, кредитни, кеш, девизни)//, **//давајќи увид во преостанатите средства и потрошеното//**. Секоја трансакција може да се //**категоризира**// за понапредна анализа, овозможувајќи **//графички приказ//** на трошењето по категории. Апликацијата **//генерира статистики//** за **//месечна потрошувачка, дневни трошоци, како и проекции за идно трошење**//. + +**FEiN** користи **централна база** на податоци, овозможувајќи сигурност и конзистентност на податоците за сите корисници. Со **FEiN**, наместо со //Excel-базирано следење//, времето за внесување на податоци се намалува, а шансите за човечки грешки се сведуваат на минимум. Во иднина, **FEiN** би овозможил интеграција со модели за машинско учење за предвидување и оптимизирање на личниот буџет. + +== Детален опис на проектот +* **За кого е наменет проектот односно кој според вашата замисла ќе биде одговорен да ја поседува базата на апликацијата и да ја обезбедува како сервис - дали е тоа фирма/институација/организација или е поединец и краток опис за нивните потреби?** + Проектот е наменет за //индивидуални корисници// кои сакаат да управуваат со своите лични финансии на едноставен и интуитивен начин. Корисниците креираат //сметки// со кои пристапуваат до //централна база// на податоци преку апликацијата. Базата ќе биде администрирана централизирано од тимот што стои зад //FEiN//, со цел обезбедување на сигурност, конзистентност и лесен пристап од било кој уред. +* **Какви проблеми во фирмата или организацијата ќе решавате?** + //FEiN// ги решава следните проблеми поврзани со управување на личните финансии: + 1. //Осигурување на точност при внесување податоци//: За разлика од //Excel//, каде што форматните грешки и пропусти се чести, //FEiN// ќе обезбеди //стандардизиран начин за внесување// на трансакции. Ова ќе ја намали можноста за грешки и ќе им олесни на корисниците во следењето на нивниот буџет. + 2. //Подобрено корисничко искуство//: Апликацијата ќе понуди интуитивен и лесен за користење интерфејс, кој ќе го замени комплексното и визуелно ограничено искуство на //Excel//. + 3. //Автоматски пресметки и визуализации//: //FEiN// автоматски ќе ги пресметува вкупните потрошени и преостанати средства, ќе ги категоризира трошоците и ќе прикажува визуелизации за полесна анализа на финансиските податоци. + 4. //Пристап до детални статистики без дополнителни трошоци//: //За разлика од други апликации//, //FEiN// ги нуди сите статистики бесплатно, овозможувајќи длабински увид во финансиските навики. +- **Какви видови корисници ќе има апликацијата - во смисла на типови вработени во фирмата/институцијата или клиенти?** + //FEiN// е наменета исклучиво за //индивидуални корисници//. Во почетната фаза, сите корисници ќе имаат //бесплатен пристап// до апликацијата и сите нејзини функционалности. //Нема// да има различни типови на //кориснички профили// или //премиум функции// во првата верзија. +- **Ако има други слични проекти во фирмата или во околината, или веќе постојат готови решенија во светот, во што е различен вашиот предлог проект.** + FEiN се издвојува по следното: + 1. //Над Excel-базираните системи//: Решавајќи ги проблемите со рачно внесување и форматни грешки, FEiN обезбедува структурирано и автоматизирано управување со личните финансии, во значително подобрено корисничко искуство. + 2. //Конкуренција со слични апликации//: За разлика од многу слични апликации кои нудат напредни статистики и прогнози како премиум функции, FEiN ги нуди истите //бесплатно// и со //отворен код// од самиот почеток. +- **Дали се работи за веб, мобилна и/или десктоп апликација?** + FEiN е планирана како //мобилна апликација//, со фокус на достапност и практичност за корисниците, кои можат лесно да внесуваат трансакции и да ги следат своите финансии од својот паметен телефон. + +https://develop.finki.ukim.mk/projects/FEIN/wiki/ERModel += Верзија 3 + +== ER дијаграм +[[Image(ERDiagramVer3.png)]] + +== Податочни побарувања + +=== Ентитети +1. **User** - ентитет за корисникот кој се логира + - user_id - SERIAL (примарен клуч) + - user_name - VARCHAR(30) (атрибут) + - email - VARCHAR(30) (атрибут) + - password - VARCHAR(30) (атрибут) +2. **Transaction Account** - секој корисник има една или повеќе трансакциски акаунти/сметки(кредитна картичка, дебитна картичка, кеш, девизи...) + - transaction_account_id - SERIAL (примарен клуч) + - user_id - INT (надворешен клуч) + - account_name - VARCHAR(35) (атрибут) + - balance - DECIMAL(10, 2) (атрибут) +3. **Transaction Breakdown** - за секоја трансакција(акција на плаќање/добивање пари) да можe да менува различни акаунти(сте платиле пола со картица, пола со кеш) и секоја трансакција да биде посложена(ако платите вие за повеќе луѓе, и очекувате да бидете отплатени од нив) потребен е ваков механизам во вид на ентитет кој ќе го овозможи тоа + - transaction_breakdown_id - SERIAL (примарен клуч) + - transaction_id - INT (надворешен клуч) + - transaction_account_id - INT (надворешен клуч) + - spent_amount - DECIMAL(10, 2) (атрибут) + - earned_amount - DECIMAL(10, 2) (атрибут) +4. **Transaction** - трансакција, акција на трансфер на пари. Атрибутите amount и net_amount се потребни во случајот за сложени трансакции, кога пример вие плаќате на работа за јадењето на сите колеги во апликација за достава на храна и потоа колегите си го исплаќаат својот дел дали преку кеш директно вам или преку електронски трансфер со е-банкарство, вкупниот износ на целата нарачка кој го плаќате ќе се запише во amount, а додека со тригери ќе се ажурира net_amount со збир на сите вредности од атрибутите на помошните трансакции(earned_amount+spent_amount). Тие ќе бидат истата вредност доколку вие со трансакцијата го плаќате целиот износ за вас или пак добивате пари кои не треба да ги вратите + - transaction_id - SERIAL (примарен клуч) + - tag_id - INT (надворешен клуч) + - transaction_name - VARCHAR(100) (атрибут) + - date - TIMESTAMPZ (атрибут) + - amount - DECIMAL(10, 2) (атрибут) + - net_amount - DECIMAL(10, 2) (атрибут) +5. **Tag** - помошни групи(тагови) со кои може да се класифицираат трансакциите, а со тоа да се изведуваат корисни статистики + - tag_id - SERIAL (примарен клуч) + - tag_name - VARCHAR(50) (атрибут) + + +== Релации +- **!TransactionHasTransactionBreakdown** - 1:N една трансакција може да има повеќе помошни трансакции +- **!TagAssignedToTransaction** - N:N повеќе тагови можат да се доделат на повеќе трансакции +- **!TransactionBreakdownModifiesTransactionAccount** - 1:N една сметка може да биде менувана од повеќе помошни трансакции +- **!UserHasTransactionAccount** - 1:N еден корисник може да има повеќе сметки + +== Историјат +[wiki:ERModelVer1 Верзија 1] \\ +[wiki:ERModelVer2 Верзија 2] \\ +[wiki:ERModelVer3 Верзија 3] + + +https://develop.finki.ukim.mk/projects/FEIN/wiki/ERModelVer2 += Верзија 2 + +== ER дијаграм +[[Image(wiki:ERModel:ERDiagramVer2.png)]] + +== Податочни побарувања + +=== Ентитети +1. **User** - ентитет за корисникот кој се логира + - user_id - SERIAL (примарен клуч) + - user_name - VARCHAR(30) (атрибут) + - email - VARCHAR(30) (атрибут) + - password - VARCHAR(30) (атрибут) +2. **Transaction Account** + - transaction_account_id - SERIAL (примарен клуч) + - user_id - INT (надворешен клуч) + - account_name - VARCHAR(35) (атрибут) + - balance - DECIMAL(10, 2) (атрибут) +3. **Transaction Breakdown** + - transaction_breakdown_id - SERIAL (примарен клуч) + - transaction_id - INT (надворешен клуч) + - transaction_account_id - INT (надворешен клуч) + - spent_amount - DECIMAL(10, 2) (атрибут) + - earned_amount - DECIMAL(10, 2) (атрибут) +4. **Transaction** + - transaction_id - SERIAL (примарен клуч) + - tag_id - INT (надворешен клуч) + - transaction_name - VARCHAR(100) (атрибут) + - date - DATE (атрибут) + - amount - DECIMAL(10, 2) (атрибут) + - net_amount - DECIMAL(10, 2) (атрибут) +5. **Tag** + - tag_id - SERIAL (примарен клуч) + - tag_name - VARCHAR(50) (атрибут) + + +== Релации +- **!TransactionHasTransactionBreakdown** - 1:N една трансакција може да има повеќе помошни трансакции +- **!TagAssignedToTransaction** - N:N повеќе тагови можат да се доделат на повеќе трансакции +- **!TransactionBreakdownModifiesTransactionAccount** - 1:1 една помошна трансакција менува една сметка +- **!UserHasTransactionAccount** - 1:N еден корисник може да има повеќе сметки + +== Историјат +[wiki:ERModelVer1 Верзија 1] \\ +[wiki:ERModelVer2 Верзија 2] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/ERModelVer1 += Верзија 1 + +== ER дијаграм +[[Image(wiki:ERModel:ERDiagramVer1.png)]] + +== Податочни побарувања + +=== Ентитети +1. **User** - ентитет за корисникот кој се логира + - user_id - SERIAL (примарен клуч) + - user_name - VARCHAR(30) (атрибут) + - email - VARCHAR(30) (атрибут) + - password - VARCHAR(30) (атрибут) +2. **Transaction Account** + - transaction_account_id - SERIAL (примарен клуч) + - user_id - INT (надворешен клуч) + - account_name - VARCHAR(35) (атрибут) + - balance - DECIMAL(10, 2) (атрибут) +3. **Transaction Breakdown** + - transaction_breakdown_id - SERIAL (примарен клуч) + - transaction_id - INT (надворешен клуч) + - transaction_account_id - INT (надворешен клуч) + - spent_amount - DECIMAL(10, 2) (атрибут) + - earned_amount - DECIMAL(10, 2) (атрибут) +4. **Transaction** + - transaction_id - SERIAL (примарен клуч) + - day_id - INT (надворешен клуч) + - tag_id - INT (надворешен клуч) + - transaction_name - VARCHAR(100) (атрибут) + - amount - DECIMAL(10, 2) (атрибут) + - net_amount - DECIMAL(10, 2) (атрибут) +5. **Tag** + - tag_id - SERIAL (примарен клуч) + - tag_name - VARCHAR(50) (атрибут) +6. **Day** + - day_id - SERIAL (примарен клуч) + - month_id - INT (надворешен клуч) + - day_name - VARCHAR(9) (атрибут) + - day_number - INT (атрибут) +7. **Month** + - month_id - SERIAL (примарен клуч) + - year_id - INT (надворешен клуч) + - month_name - VARCHAR(20) (атрибут) +8. **Year** + - year_id - SERIAL (примарен клуч) + - year_name - INT (атрибут) + +== Релации +- **has** - 1:N една година има повеќе месеци +- **has** - 1:N еден месец има повеќе денови +- **has** - 1:N еден ден може да има повеќе трансакции +- **has** - 1:N една трансакција може да има повеќе помошни трансакции +- **assigned** - N:N повеќе тагови можат да се доделат на повеќе трансакции +- **modifies** - 1:1 една помошна трансакција менува една сметка +- **has** - 1:N еден корисник може да има повеќе сметки + +== Историјат +[wiki:ERModelVer1 Верзија 1] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/RelationalDesign += Логички и физички дизајн + +== Релациска шема (со мапирачка трансформација) +=== Ознаки +- примарни клучеви - __**болдирани и подвлечени__** +- not null атрибути - **болдирани** +- надворешни клучеви - * до името на надворешниот клуч и во заграда табелата кон која референцира +- останати атрибути - без ознака +=== Табели +- USER (**__user_id__**, **user_name**, **email**, **password**) +- TRANSACTION_ACCOUNT (__**transaction_account_id**__, account_name, balance, user_id* (USER)) +- TRANSACTION (__**transaction_id__**, transaction_name, **amount**, net_amount, **date**) +- TRANSACTION_BREAKDOWN (__**transaction_breakdown_id__**, transaction_id* (TRANSACTION), transaction_account_id* (TRANSACTION_ACCOUNT), spent_amount, earned_amount) +- TAG (__**tag_id__**, **tag_name**) +- TAG_ASSIGNED_TO_TRANSACTION(__**tag_assigned_to_transaction_id**__, **tag_id*** (TAG), **transaction_id*** (TRANSACTION)) + +=== DDL скрипта за креирање и бришење на табелите +[wiki:ddlScript.sql DDL скрипта] +=== DML скрипта за полнење на табелите со податоци +[wiki:dmlScript.sql DML скрипта] +=== Релациски дијаграм изваден од DBeaver +[[Image(DBeaverExportVer2.png)]] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/ddlScript.sql +{{{#!sql +-- Delete tables if they exist +DROP TABLE IF EXISTS transaction_breakdown CASCADE; +DROP TABLE IF EXISTS transaction CASCADE; +DROP TABLE IF EXISTS transaction_account CASCADE; +DROP TABLE IF EXISTS tag CASCADE; +DROP TABLE IF EXISTS "user" CASCADE; +DROP TABLE IF EXISTS tag_assigned_to_transaction CASCADE; + +-- Create USER table +CREATE TABLE "user" ( + user_id SERIAL PRIMARY KEY, + user_name VARCHAR(50) NOT NULL, + email VARCHAR(100) NOT NULL, + password VARCHAR(100) NOT NULL +); + +-- Create TRANSACTION_ACCOUNT table +CREATE TABLE transaction_account ( + transaction_account_id SERIAL PRIMARY KEY, + account_name VARCHAR(50), + balance DECIMAL(10, 2), + user_id INT REFERENCES "user"(user_id) +); + +-- Create TRANSACTION table +CREATE TABLE transaction ( + transaction_id SERIAL PRIMARY KEY, + transaction_name VARCHAR(100), + amount DECIMAL(10, 2) NOT NULL, + net_amount DECIMAL(10, 2), + date TIMESTAMPTZ NOT NULL +); + +-- Create TAG table +CREATE TABLE tag ( + tag_id SERIAL PRIMARY KEY, + tag_name VARCHAR(50) NOT NULL +); + +-- Create TAG_ASSIGNED_TO_TRANSACTION table +CREATE TABLE tag_assigned_to_transaction ( + tag_assigned_to_transaction_id SERIAL PRIMARY KEY, + transaction_id INT NOT NULL REFERENCES transaction(transaction_id) ON DELETE CASCADE, + tag_id INT NOT NULL REFERENCES tag(tag_id) ON DELETE CASCADE +); + +-- Create TRANSACTION_BREAKDOWN table +CREATE TABLE transaction_breakdown ( + transaction_breakdown_id SERIAL PRIMARY KEY, + transaction_id INT REFERENCES transaction(transaction_id), + transaction_account_id INT REFERENCES transaction_account(transaction_account_id), + spent_amount DECIMAL(10, 2), + earned_amount DECIMAL(10, 2) +); +}}} + +https://develop.finki.ukim.mk/projects/FEIN/wiki/dmlScript.sql +{{{#!sql +INSERT INTO "user" (user_name, email, password) VALUES +('John Doe', 'john@example.com', 'password123'), +('Jane Smith', 'jane@example.com', 'securepass'), +('Alice Johnson', 'alice@example.com', 'alicepwd'); + +INSERT INTO tag (tag_name) VALUES +('Food'), +('Work'), +('Groceries'), +('Entertainment'), +('Bills'); + +INSERT INTO transaction_account (account_name, balance, user_id) VALUES +('Cash', 5000.00, 1), +('Bank Account', 20000.00, 1), +('Credit Card', -1000.00, 2), +('Savings', 15000.00, 2), +('Foreign Currency', 300.00, 3); + +INSERT INTO transaction (transaction_name, amount, net_amount, date) VALUES +('Bought groceries', 1000.00, 800.00, '2024-11-23 13:51:35+02:00'), +('Paid rent', 15000.00, 15000.00, '2024-11-01 17:01:41+02:00'), +('Dinner with friends', 2500.00, 1500.00, '2024-11-29 22:02:22+02:00'), +('Movie tickets', 600.00, 600.00, '2024-11-30 17:38:00+02:00'), +('Electricity bill', 1200.00, 1200.00, '2024-11-02 07:03:35+02:00'); + +INSERT INTO tag_assigned_to_transaction (transaction_id, tag_id) VALUES +(1, 1), +(2, 5), +(3, 2), +(4, 4), +(5, 5); + +INSERT INTO transaction_breakdown (transaction_id, transaction_account_id, spent_amount, earned_amount) VALUES +(1, 1, 1000.00, 200.00), -- Spent 1000 on Cash, earned back 200 +(1, 2, 0.00, 800.00), -- Earned back 800 on Bank Account +(2, 2, 15000.00, 0.00), -- Spent 15000 on Bank Account for rent +(3, 1, 2500.00, 1000.00), -- Spent 2500 on Cash, earned back 1000 +(4, 3, 600.00, 0.00); -- Spent 600 on Credit Card for movie +}}} + +https://develop.finki.ukim.mk/projects/FEIN/wiki/UseCaseModel +== Апликативен дизајн - Случаи на употреба и Сценарија за пристап до базата - SQL Погледи + +== Актери +**Ненајавен корисник** +- Не може да управува со апликацијата пред да се најави бидејќи нема да има база на податоци со која може да управува +**Најавен корисник** +- Корисникот креира сметка за пристап до апликацијата за да управува со личните финансии. Може да: + - Додава трансакциски сметки (на пр. готовина, кредитна картичка, заштеда) + - Внесува дневни трансакции со тагови, имиња, износи и датуми + - Прегледува извештаи за месечни/годишни трошоци и распоред на дневни трансакции + +== Сценарија + +- [wiki:RegistracijaKorisnik Регистрација на корисник] +- [wiki:NajavaKorisnik Најава на корисник] +- [wiki:DodavanjeTransakciska Додавање на трансакциска сметка] +- [wiki:PregledSmetki Преглед на сметки] +- [wiki:DodavanjeTransakcija Додавање на трансакција] +- [wiki:PregledNaTransakcii Преглед на трансакции] +- [wiki:AzuriranjeBrisenjeTransakcija Ажурирање/бришење трансакции] +- [wiki:Izvestai Извештаи (годишни/месечни/по тагови/графици)] + +== Најважни случаи на употреба + +- [wiki:RegistracijaKorisnik Регистрација на корисник] +- [wiki:DodavanjeTransakciska Додавање на трансакциска сметка] +- [wiki:DodavanjeTransakcija Додавање на трансакција] +- [wiki:AzuriranjeBrisenjeTransakcija Ажурирање/бришење трансакции] +- [wiki:Izvestai Извештаи (годишни/месечни/по тагови/графици)] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/RegistracijaKorisnik +== Регистрација на корисник + +==== Актери: **Ненајавен корисник** + +1. Корисникот притиска на копчето за регистрација +2. Се појавува форма за регистрација +3. Корисникот ги внесува своето име, емаил и лозинка во апликацијата. +4. Апликацијата внесува нов запис во табелата {{{user}}} +{{{#!sql +INSERT INTO user (user_name, email, password) +VALUES ('Јован Петров', 'jovan.petrovski@example.com', 'hashed_password'); +}}} +5. По успешно креирање на сметка тој може да се најави на системот + +https://develop.finki.ukim.mk/projects/FEIN/wiki/NajavaKorisnik +== Најава на корисник + +==== Актери: **Ненајавен корисник** + +1. Корисникот притиска на копчето за најава +2. Се појавува форма за најава +3. Корисникот ги внесува своите емаил и лозинка во полето за најава +4. Апликацијата проверува дали емаилот и лозинката постојат во табелата {{{user}}} +{{{#!sql +SELECT user_id +FROM user +WHERE email = 'jovan.petrovski@example.com' AND password = 'hashed_password'; +}}} +5. Ако внесените податоци се точни: + - Корисникот е пренасочен кон почетната страница на апликацијата и добива пристап до функционалностите +===== Алтернативен тек: +6. Ако внесените податоци не се точни: + - Апликацијата прикажува порака за грешка: „Невалидно корисничко име или лозинка“ + + +https://develop.finki.ukim.mk/projects/FEIN/wiki/DodavanjeTransakciska +== Додавање на трансакциска сметка + +==== Актери: **Најавен корисник** + +1. Корисникот притиска на копчето „Додај сметка“ +2. Се појавува форма за внес на информации за трансакциската сметка +3. Корисникот ги внесува следниве податоци: + - Име на сметка (на пр. „Трансакциска сметка НЛБ“) + - Почетен баланс (на пр. „50000 МКД“) +4. Апликацијата внесува нов запис во табелата {{{transaction_account}}} поврзан со тековниот корисник. +{{{#!sql +INSERT INTO transaction_account (account_name, balance, user_id) +VALUES ('Трансакциска сметка НЛБ', 50000, 101); +}}} +5. По успешно додавање на сметката, корисникот добива известување: + - „Сметката е успешно додадена!“ +===== Алтернативен тек +6. Корисникот не ги внел сите потребни податоци (на пр. име на сметката е празно): + - Апликацијата прикажува порака: „Ве молиме внесете валидни податоци за сметката“ + +https://develop.finki.ukim.mk/projects/FEIN/wiki/PregledSmetki +== Преглед на сметки + +==== Актери: **Најавен корисник** + +Откако ќе се додаде сметката, корисникот може да ги види сите свои сметки на следниов начин: +1. Корисникот притиска на табот „Мои сметки“ од менито во апликацијата. +2. Апликацијата испраќа SQL прашање до базата за да ги пронајде сите трансакциски сметки поврзани со корисникот +{{{#!sql +SELECT account_name, balance +FROM transaction_account +WHERE user_id = 101; +}}} +3. Апликацијата ги добива податоците од базата и ги прикажува во листа, со следниве информации за секоја сметка: + - Име на сметката + - Тековен баланс +4. Корисникот може да избере една од сметките за дополнителни опции: + - Преглед на трансакции + - Уредување на сметката + - Бришење на сметката + +===== Алтернативен тек +5. Корисникот нема додадено сметки, апликацијата прикажува порака: + - „Немате додадено трансакциски сметки. Притиснете на копчето ‘Додај сметка’ за да започнете“ + +https://develop.finki.ukim.mk/projects/FEIN/wiki/DodavanjeTransakcija +== Додавање трансакција + +==== Актери: **Најавен корисник** +1. Корисникот притиска на копчето за додавање на трансакции +2. Се отвора форма каде корисникот ги внесува следните детали за трансакцијата: + - Дали трансакцијата додава или одзема од сметка (задолжително поле) + - Име на трансакција (задолжително поле) + - Износ на цела трансакција (задолжително поле) + - Датум на трансакцијата (опционално, доколку не се внесе се зема моменталното време) + - Таг за категоризација (опционално, но препорачливо) + - Сметка на која трансакцијата се поврзува (се бира од достапните) +3. Корисникот може да додаде дополнителни детали преку динамично додавање на полиња со клик на копче за додавање кои ќе бидат зачувани како {{{earned_amount}}} и {{{spent_amount}}} во табелата {{{transaction_breakdown}}} +4. Апликацијата ги валидира внесените податоци (на пример, проверува дали сите задолжителни полиња се пополнети и дали износот е валиден број) +5. Апликацијата внесува нов запис во табелите {{{transaction}}} и {{{tag_assigned_to_transaction}}}: +{{{#!sql +INSERT INTO transaction (transaction_name, amount, net_amount, date) +VALUES ('Пазарување', 3500.00, 0, '2024-11-23 13:51:35+02:00'); +RETURNING transaction_id; +}}} +{{{#!sql +INSERT INTO tag_assigned_to_transaction (transaction_id, tag_id) +VALUES (1, 3); +RETURNING tag_assigned_to_transaction_id; +}}} +6. Трансакцијата тригерира креирање запис во {{{transaction_breakdown}}} кој исто така ја ажурира сметката и пресметува {{{net_amount}}} преку тригер: +- Во зависност од тоа дали трансакцијата додава или одзема се прави додавање во соодветното поле на {{{transaction_breakdown}}}: + - За додавање на сметка: +{{{#!sql +INSERT INTO transaction_breakdown (transaction_id, transaction_account_id, earned_amount, spent_amount) +VALUES (1, 2, 3500, 0); +}}} + - За одземање од сметка: +{{{#!sql +INSERT INTO transaction_breakdown (transaction_id, transaction_account_id, earned_amount, spent_amount) +VALUES (1, 2, 0, 3500); +}}} + - На крај се ажурира балансот во {{{transaction_account}}} +{{{#!sql +UPDATE transaction_account: +SET balance = balance - 3500 +WHERE transaction_account_id = 2; +}}} +7. Ако корисникот додал дополнителни детали преку динамично додавање на полиња(на пример, {{{earned_amount}}} и {{{spent_amount}}}), апликацијата внесува соодветни записи во табелата {{{transaction_breakdown}}}: +{{{#!sql +INSERT INTO transaction_breakdown (transaction_id, transaction_account_id, earned_amount, spent_amount) +VALUES (1, 2, 0, 1500), + (1, 3, 200, 0); +}}} +8. Тригер во базата: + - Автоматски ја пресметува и ажурира вредноста на {{{net_amount}}} во табелата {{{transaction}}} според деталите внесени во табелата {{{transaction_breakdown}}} +9. По успешното додавање, тригерот: + - Го ажурира балансот на поврзаната трансакциска сметка во табелата {{{transaction_account}}}: +{{{#!sql +UPDATE transaction_account +SET balance = + CASE + WHEN transaction_account_id = 2 THEN balance - 1500 + WHEN transaction_account_id = 3 THEN balance + 200 + ELSE balance + END +WHERE transaction_account_id IN (2, 3); +}}} + - Го известува корисникот за успешното додавање на трансакцијата. + +https://develop.finki.ukim.mk/projects/FEIN/wiki/PregledNaTransakcii +== Преглед на сите трансакции + +==== Актери: **Најавен корисник** +1. Корисникот пристапува до табот „Трансакции“ од главното мени на апликацијата +2. Апликацијата испраќа SQL прашање до базата за да ги пронајде сите трансакции поврзани со корисникот, хронолошки подредени +{{{#!sql +SELECT + t.transaction_id, + t.transaction_name, + t.amount, + t.net_amount, + t.date, + STRING_AGG(DISTINCT tg.tag_name, ', ') AS tag_names, -- Aggregate multiple tag names + ta.account_name +FROM + public."transaction" t +JOIN + public.transaction_breakdown tb ON t.transaction_id = tb.transaction_id +JOIN + public.transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +LEFT JOIN + public.tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id +LEFT JOIN + public.tag tg ON tat.tag_id = tg.tag_id +WHERE + ta.user_id = 101 +GROUP BY + t.transaction_id, t.transaction_name, t.amount, t.net_amount, t.date, ta.account_name +ORDER BY + t.date DESC; +}}} +3. Апликацијата ги прикажува трансакциите како листа, со следниве информации за секоја трансакција: + - ID на трансакција + - Име на трансакција + - Износ (amount) + - Нето износ (net_amount) + - Датум на трансакцијата + - Таг/ови (ако постојат) + - Сметка на која е поврзана трансакцијата +4. До секоја трансакција, апликацијата прикажува две копчиња: + - „Ажурирај“ + - „Избриши“ + +http://develop.finki.ukim.mk/projects/FEIN/wiki/AzuriranjeBrisenjeTransakcija +== Ажурирање/бришење на трансакции + +==== Актери: **Најавен корисник** + +=== Сценарио за ажурирање на трансакција +1. Корисникот пристапува до табот „Трансакции“ од главното мени на апликацијата +2. Корисникот притиска на копчето „Ажурирај“ од одредена трансакција +3. Се отвора форма со веќе пополнети податоци за избраната трансакција +4. Корисникот ги менува податоците (на пр. износ, датум, тагови, или сметка) +5. Апликацијата ги валидира внесените податоци и ги ажурира соодветните записи во базата: +{{{#!sql +UPDATE transaction +SET transaction_name = 'Променето име', + amount = 4000, + date = '2024-11-24 10:30:00+02:00', +WHERE transaction_id = 1; +}}} +6. Ако има промена на тагови, претходно внесените записи се бришат и се додаваат новите тагови: +{{{#!sql +DELETE FROM public.tag_assigned_to_transaction +WHERE transaction_id = 1; + +INSERT INTO public.tag_assigned_to_transaction (transaction_id, tag_id) +VALUES + (1, 2), -- Пример за ново додаден таг + (1, 3); -- Пример за друг нов таг +}}} +7. Ако е направена промена во {{{amount}}}: + - Ажурирањето тригерира промена на записите во {{{transaction_breakdown}}} и {{{transaction_account}}} преку тригер +8. По успешно ажурирање, корисникот добива порака: + - „Трансакцијата е успешно ажурирана!“ + +=== Сценарио за бришење на трансакција +1. Корисникот пристапува до табот „Трансакции“ од главното мени на апликацијата +2. Корисникот притиска на копчето „Избриши“ од одредена трансакција +3. Апликацијата прикажува порака за потврда: + - „Дали сте сигурни дека сакате да ја избришете оваа трансакција?“ +4. Корисникот потврдува +5. Апликацијата ги брише сите записи поврзани со трансакцијата: + - Бришење од {{{transaction_breakdown}}}: +{{{#!sql +DELETE FROM transaction_breakdown +WHERE transaction_id = 1; +}}} + - Бришење од {{{tag_assigned_to_transaction}}}: +{{{#!sql +DELETE FROM public.tag_assigned_to_transaction +WHERE transaction_id = 1; +}}} + - Бришење од {{{transaction}}}: +{{{#!sql +DELETE FROM transaction +WHERE transaction_id = 1; +}}} +6. Тригер автоматски го ажурира балансот на соодветната сметка во {{{transaction_account}}} +7. Корисникот добива порака: + - „Трансакцијата е успешно избришана!“ + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Izvestai +== Извештаи + +==== Актери: Најавен корисник +1. Корисникот пристапува до табот "Извештаи" од главното мени на апликацијата +2. Се прикажува интерактивна страница со опции за избор на типот на извештај: + - Годишен извештај + - Месечен извештај + - Извештај по тагови + - Извештај по сметки + - Трендови на потрошувачка +3. Корисникот избира тип на извештај и според тоа се пренасочува кон страница за тој извештај + +===== Годишен извештај +- Корисникот избира "Годишен извештај" и потоа одбира година од dropdown мени +- Апликацијата прикажува пита дијаграм со распределба на трошоците по тагови за избраната година +- Дополнително, се прикажува график на времеплов (line chart) кој ги покажува вкупните месечни трошоци за секој месец од годината + +===== Месечен извештај +- Корисникот избира "Месечен извештај" и потоа одбира месец и година од два dropdown менија +- Апликацијата прикажува пита дијаграм со трошоците по тагови за избраниот месец +- Дополнително, се прикажува график на времеплов (line chart) кој ги прикажува дневните трошоци во месецот + +===== Извештај по тагови +- Корисникот избира "Извештај по тагови" и одбира еден или повеќе тагови од листа +- Апликацијата прикажува пита дијаграм кој ги содржи сите избрани тагови +- Под графикот се покажуваат вкупните трошоци за секој избран таг, подредени хронолошки + +===== Извештај по сметки +- Корисникот избира "Извештај по сметки" и одбира сметка од листата +- Апликацијата прикажува график на времеплов (line chart) сите приходи и расходи за избраната сметка +- Апликацијата прикажува листа која ги покажува сите приходи и расходи за избраната сметка под графикот + +===== Трендови на потрошувачка +- Корисникот избира "Трендови на потрошувачка" и времетраење (година, месец, или недела) +- Апликацијата прикажува график кој ги споредува трендовите на трошоците за секој период (на пр. потрошувачка за јануари 2023 наспроти јануари 2024) + +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReports += Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра) + +- [wiki:AdvancedReport1 Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки] +- [wiki:AdvancedReport2 Надминување на баланс на една сметка со трансакција во сегашно време] +- [wiki:AdvancedReport3 Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки] +- [wiki:AdvancedReport4 Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време] +- [wiki:AdvancedReport5 Сумарни приходи и расходи по месеци] +- [wiki:AdvancedReport6 Број на трансакции што ги надминуваат приходите на сметка] +- [wiki:AdvancedReport7 Сумарни податоци за тагови] +- [wiki:AdvancedReport8 Просечно трошење во последните 3 дена за цел систем] +- [wiki:AdvancedReport9 Просечно трошење во последните 3 дена за корисник] +- [wiki:AdvancedReport25 Просечно трошење во последните N дена] +- [wiki:AdvancedReport10 Вкупно потрошено во тековниот месец за цел систем] +- [wiki:AdvancedReport11 Вкупно потрошено во тековниот месец за корисник] +- [wiki:AdvancedReport12 Дневен буџет до крајот на месецот за корисник] +- [wiki:AdvancedReport13 Долг на кредитна картичка од минатиот месец за цел систем] +- [wiki:AdvancedReport14 Долг на кредитна картичка од минатиот месец за корисник] +- [wiki:AdvancedReport15 Трендови на трошење според тагови за цел систем] +- [wiki:AdvancedReport16 Трендови на трошење според тагови за корисник] +- [wiki:AdvancedReport17 Вкупно трошење според тагови] +- [wiki:AdvancedReport18 Сметки со највисоко вкупно трошење во изминатата година] +- [wiki:AdvancedReport19 Најчести времиња за трансакции] +- [wiki:AdvancedReport20 Трансакции според тагови со највисоко трошење за цел систем] +- [wiki:AdvancedReport21 Трансакции според тагови со највисоко трошење за корисник] +- [wiki:AdvancedReport22 Годишни трендови на трансакции за цел систем] +- [wiki:AdvancedReport23 Годишни трендови на трансакции за корисник] +- [wiki:AdvancedReport24 Неактивни тагови] + +== + +[wiki:AdvancedReportsVersion1 Верзија 1] +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport1 +==== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки +{{{#!sql +CREATE OR REPLACE FUNCTION get_transactions_exceeding_account_balance( + p_account_name TEXT +) +RETURNS TABLE ( + user_id INT, + user_name TEXT, + account_name TEXT, + transaction_id INT, + transaction_name TEXT, + transaction_amount NUMERIC, + transaction_date TIMESTAMP, + calculated_balance NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + WITH cumulative_balances AS ( + SELECT + u.user_id, + u.user_name, + ta.account_name, + t.transaction_id, + t.transaction_name, + tb.spent_amount AS transaction_amount, + t.date AS transaction_date, + SUM(tb.earned_amount - tb.spent_amount) + OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date) + AS calculated_balance + FROM transaction_account ta + JOIN "user" u ON ta.user_id = u.user_id + JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id + JOIN transaction t ON tb.transaction_id = t.transaction_id + WHERE ta.account_name = p_account_name + ) + SELECT * + FROM cumulative_balances + WHERE transaction_amount > calculated_balance + AND transaction_amount > 0 + ORDER BY transaction_date DESC; +END; +$$; + +}}} + +==== Релациона алгебра +- U(user_id, user_name) +- TA(transaction_account_id, user_id, account_name) +- TB(transaction_id, transaction_account_id, spent_amount, earned_amount) +- T(transaction_id, transaction_name, date) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.user_id = U.user_id,, U +- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтер според име на акаунт: +- F1 ← σ,,account_name = p_account_name,,(J3) + +Пресметка на кумулативен баланс: +- CB ← γ,,user_id, user_name, account_name, transaction_id, transaction_name, transaction_amount, transaction_date; Σ(transaction_amount) OVER (PARTITION BY transaction_account_id ORDER BY transaction_date) → calculated_balance,,(F1) + +каде `transaction_amount = spent_amount` и кумулативната сума е `Σ(earned_amount - spent_amount)` + +Филтрирање на трансакциите кои го надминуваат балансот: +- R ← σ,,transaction_amount > calculated_balance ∧ transaction_amount > 0,,(CB) + +Подредување хронолошки(почнувајќи од најновата трансакција до најстарата): +- R_final ← τ,,transaction_date DESC,,(R) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport2 +==== Надминување на баланс на една сметка со трансакција во сегашно време +{{{#!sql +CREATE OR REPLACE FUNCTION get_current_account_overdrafts() +RETURNS TABLE ( + user_id INT, + user_name TEXT, + account_name TEXT, + current_balance NUMERIC, + transaction_id INT, + transaction_name TEXT, + transaction_amount NUMERIC, + transaction_date TIMESTAMP +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + u.user_id, + u.user_name, + ta.account_name, + ta.balance, + t.transaction_id, + t.transaction_name, + tb.spent_amount, + t.date + FROM transaction_account ta + JOIN "user" u ON ta.user_id = u.user_id + JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id + JOIN transaction t ON tb.transaction_id = t.transaction_id + WHERE tb.spent_amount > ta.balance + AND tb.spent_amount > 0 + ORDER BY t.date DESC; +END; +$$; +}}} + +==== Релациона алгебра +- U(user_id, user_name) +- TA(transaction_account_id, user_id, account_name, balance) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, transaction_name, date) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.user_id = U.user_id,, U +- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтрирање на трансакции што го надминуваат тековниот баланс: +- R ← σ,,spent_amount > balance ∧ spent_amount > 0,,(J3) + +Подредување хронолошки (од најнова кон најстара трансакција): +- R_final ← τ,,date DESC,,(R) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport3 +==== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки +{{{#!sql +CREATE OR REPLACE FUNCTION get_transactions_exceeding_user_total_balance() +RETURNS TABLE ( + user_id INT, + user_name TEXT, + transaction_id INT, + transaction_name TEXT, + transaction_amount NUMERIC, + transaction_date TIMESTAMP, + calculated_total_balance NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + WITH cumulative_totals AS ( + SELECT + u.user_id, + u.user_name, + t.transaction_id, + t.transaction_name, + tb.spent_amount AS transaction_amount, + t.date AS transaction_date, + SUM(tb.earned_amount - tb.spent_amount) + OVER (PARTITION BY u.user_id ORDER BY t.date) + AS calculated_total_balance + FROM transaction_account ta + JOIN "user" u ON ta.user_id = u.user_id + JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id + JOIN transaction t ON tb.transaction_id = t.transaction_id + ) + SELECT * + FROM cumulative_totals + WHERE transaction_amount > calculated_total_balance + AND transaction_amount > 0 + ORDER BY user_id, transaction_date DESC; +END; +$$; +}}} + +==== Релациона алгебра +- U(user_id, user_name) +- TA(transaction_account_id, user_id) +- TB(transaction_id, transaction_account_id, spent_amount, earned_amount) +- T(transaction_id, transaction_name, date) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.user_id = U.user_id,, U +- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T + +Пресметка на вкупен кумулативен баланс по корисник: +- CT ← γ,,user_id, user_name, transaction_id, transaction_name, transaction_amount, transaction_date; Σ(transaction_amount) OVER (PARTITION BY user_id ORDER BY transaction_date) → calculated_total_balance,,(J3) + +каде `transaction_amount = spent_amount` и кумулативната сума е +`Σ(earned_amount - spent_amount)` за сите сметки на корисникот + +Филтрирање на трансакции кои го надминуваат вкупниот баланс: +- R ← σ,,transaction_amount > calculated_total_balance ∧ transaction_amount > 0,,(CT) + +Подредување хронолошки (по корисник, од најнова кон најстара трансакција): +- R_final ← τ,,user_id, transaction_date DESC,,(R) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport4 +==== Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време +Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува вкупна вредност +{{{#!sql +CREATE OR REPLACE FUNCTION get_current_user_total_balance_overdrafts() +RETURNS TABLE ( + user_id INT, + user_name TEXT, + transaction_id INT, + transaction_amount NUMERIC, + user_total_balance NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + WITH total_user_balance AS ( + SELECT + u.user_id, + SUM(ta.balance) AS total_balance + FROM transaction_account ta + JOIN "user" u ON ta.user_id = u.user_id + GROUP BY u.user_id + ) + SELECT + u.user_id, + u.user_name, + t.transaction_id, + tb.spent_amount AS transaction_amount, + tub.total_balance AS user_total_balance + FROM transaction_account ta + JOIN "user" u ON ta.user_id = u.user_id + JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id + JOIN transaction t ON tb.transaction_id = t.transaction_id + JOIN total_user_balance tub ON u.user_id = tub.user_id + WHERE + tb.spent_amount > tub.total_balance + AND tb.spent_amount > 0 + AND t.date <= CURRENT_DATE; +END; +$$; +}}} + +==== Релациона алгебра +- U(user_id, user_name) +- TA(transaction_account_id, user_id, balance) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, date) + +JOIN за пресметка на вкупен моментален баланс по корисник: +- J1 ← TA ⨝,,TA.user_id = U.user_id,, U +- TBAL ← γ,,user_id; Σ(balance) → total_balance,,(J1) + +JOIN на сите табели за трансакции: +- J2 ← TA ⨝,,TA.user_id = U.user_id,, U +- J3 ← J2 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J4 ← J3 ⨝,,TB.transaction_id = T.transaction_id,, T +- J5 ← J4 ⨝,,U.user_id = TBAL.user_id,, TBAL + +Филтрирање на трансакции кои го надминуваат ВКУПНИОТ моментален баланс: +- R ← σ,,spent_amount > total_balance ∧ spent_amount > 0 ∧ date ≤ CURRENT_DATE,,(J5) + +Подредување по корисник: +- R_final ← τ,,user_id,,(R) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport5 +==== Сумарни приходи и расходи по месеци +Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата +{{{#!sql +CREATE OR REPLACE FUNCTION get_monthly_system_balance() +RETURNS TABLE ( + month TEXT, + total_income NUMERIC, + total_expense NUMERIC, + net_balance NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + TO_CHAR(t.date, 'YYYY-MM'), + SUM(tb.earned_amount), + SUM(tb.spent_amount), + SUM(tb.earned_amount) - SUM(tb.spent_amount) + FROM transaction t + JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id + GROUP BY TO_CHAR(t.date, 'YYYY-MM') + ORDER BY month; +END; +$$; +}}} +Проблематичен месец е месец каде што {{{net_balance}}} е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците + +==== Релациона алгебра +- T(transaction_id, date) +- TB(transaction_id, earned_amount, spent_amount) + +JOIN на табелите: +- J1 ← T ⨝,,T.transaction_id = TB.transaction_id,, TB + +Групирање по месец и пресметка на приходи и расходи: +- M ← γ,,month = FORMAT(date, 'YYYY-MM'); Σ(earned_amount) → total_income, Σ(spent_amount) → total_expense,,(J1) + +Пресметка на нето состојба: +- R ← π,,month, total_income, total_expense, (total_income − total_expense) → net_balance,,(M) + +Подредување по месец: +- R_final ← τ,,month,,(R) + +> За само проблематични месеци, се додава уште еден чекор: + +Филтрирање на проблематични месеци (негативна состојба): +- P ← σ,,net_balance < 0,,(R_final) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport6 +==== Број на трансакции што ги надминуваат приходите на сметка +{{{#!sql +CREATE OR REPLACE FUNCTION get_account_transaction_overdraft_counts() +RETURNS TABLE ( + account_name TEXT, + transactions_exceeding_balance INT +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + WITH cumulative_balances AS ( + SELECT + ta.account_name, + t.transaction_id, + tb.spent_amount, + SUM(tb.earned_amount - tb.spent_amount) + OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date) + AS calculated_balance + FROM transaction_account ta + JOIN transaction_breakdown tb + ON ta.transaction_account_id = tb.transaction_account_id + JOIN transaction t + ON tb.transaction_id = t.transaction_id + ) + SELECT + account_name, + COUNT(transaction_id) AS transactions_exceeding_balance + FROM cumulative_balances + WHERE + spent_amount > calculated_balance + AND spent_amount > 0 + GROUP BY account_name + ORDER BY transactions_exceeding_balance DESC; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, account_name) +- TB(transaction_id, transaction_account_id, spent_amount, earned_amount) +- T(transaction_id, date) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T + +Пресметка на кумулативен баланс по сметка: +- CB ← γ,,account_name, transaction_id, spent_amount; Σ(earned_amount - spent_amount) OVER (PARTITION BY transaction_account_id ORDER BY date) → calculated_balance,,(J2) + +Филтрирање на трансакции што го надминуваат балансот: +- F ← σ,,spent_amount > calculated_balance ∧ spent_amount > 0,,(CB) + +Броење на трансакции по сметка: +- R ← γ,,account_name; COUNT(transaction_id) → transactions_exceeding_balance,,(F) + +Подредување по број на прекршувања: +- R_final ← τ,,transactions_exceeding_balance DESC,,(R) + +== + +> Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки: +{{{#!sql +CREATE OR REPLACE FUNCTION get_total_transaction_overdraft_count() +RETURNS TABLE ( + total_transactions_exceeding_balance INT +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + WITH cumulative_total_balances AS ( + SELECT + t.transaction_id, + tb.spent_amount, + SUM(tb.earned_amount - tb.spent_amount) + OVER (PARTITION BY u.user_id ORDER BY t.date) + AS calculated_total_balance + FROM transaction_account ta + JOIN "user" u + ON ta.user_id = u.user_id + JOIN transaction_breakdown tb + ON ta.transaction_account_id = tb.transaction_account_id + JOIN transaction t + ON tb.transaction_id = t.transaction_id + ) + SELECT + COUNT(transaction_id) AS total_transactions_exceeding_balance + FROM cumulative_total_balances + WHERE + spent_amount > calculated_total_balance + AND spent_amount > 0; +END; +$$; +}}} + +==== Релациона алгебра +- U(user_id) +- TA(transaction_account_id, user_id) +- TB(transaction_id, transaction_account_id, spent_amount, earned_amount) +- T(transaction_id, date) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.user_id = U.user_id,, U +- J2 ← J1 ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J3 ← J2 ⨝,,TB.transaction_id = T.transaction_id,, T + +Пресметка на кумулативен вкупен баланс по корисник: +- CT ← γ,,transaction_id, spent_amount; Σ(earned_amount - spent_amount) OVER (PARTITION BY user_id ORDER BY date) → calculated_total_balance,,(J3) + +Филтрирање на трансакции што го надминуваат вкупниот баланс: +- F ← σ,,spent_amount > calculated_total_balance ∧ spent_amount > 0,,(CT) + +Броење на сите прекршувачки трансакции: +- R_final ← γ,,; COUNT(transaction_id) → total_transactions_exceeding_balance,,(F) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport7 +==== Сумарни податоци за тагови +Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци: +===== Вкупен број на трансакции +{{{#!sql +CREATE OR REPLACE FUNCTION get_monthly_transaction_count_by_tag() +RETURNS SETOF RECORD +LANGUAGE plpgsql +AS $$ +DECLARE + col_list TEXT; + dynamic_query TEXT; +BEGIN + SELECT STRING_AGG( + DISTINCT format( + 'SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', + tag_name, tag_name + ), + ', ' + ) + INTO col_list + FROM tag; + + dynamic_query := format( + 'SELECT + TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month, + %s + FROM transaction t + JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id + JOIN tag tg ON tat.tag_id = tg.tag_id + GROUP BY transaction_month + ORDER BY transaction_month;', + col_list + ); + + RETURN QUERY EXECUTE dynamic_query; +END; +$$; +}}} + +===== Релациона алгебра +- T(transaction_id, date) +- TAT(transaction_id, tag_id) +- TG(tag_id, tag_name) + +JOIN на сите табели: +- J1 ← T ⨝,,T.transaction_id = TAT.transaction_id,, TAT +- J2 ← J1 ⨝,,TAT.tag_id = TG.tag_id,, TG + +Групирање по месец и таг: +- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; COUNT(transaction_id) → transaction_count,,(J2) + +== + +===== Вкупно примени средства +{{{#!sql +CREATE OR REPLACE FUNCTION get_monthly_income_by_tag() +RETURNS SETOF RECORD +LANGUAGE plpgsql +AS $$ +DECLARE + col_list TEXT; + dynamic_query TEXT; +BEGIN + SELECT STRING_AGG( + DISTINCT format( + 'SUM(CASE WHEN tg.tag_name = %L THEN tb.earned_amount ELSE 0 END) AS %I', + tag_name, tag_name + ), + ', ' + ) + INTO col_list + FROM tag; + + dynamic_query := format( + 'SELECT + TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month, + %s + FROM transaction t + JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id + JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id + JOIN tag tg ON tat.tag_id = tg.tag_id + GROUP BY transaction_month + ORDER BY transaction_month;', + col_list + ); + + RETURN QUERY EXECUTE dynamic_query; +END; +$$; +}}} + +==== Релациона алгебра +- T(transaction_id, date) +- TB(transaction_id, earned_amount) +- TAT(transaction_id, tag_id) +- TG(tag_id, tag_name) + +JOIN на сите табели: +- J1 ← T ⨝,,T.transaction_id = TB.transaction_id,, TB +- J2 ← J1 ⨝,,T.transaction_id = TAT.transaction_id,, TAT +- J3 ← J2 ⨝,,TAT.tag_id = TG.tag_id,, TG + +Групирање по месец и таг: +- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; Σ(earned_amount) → total_income,,(J3) + +== + +===== Вкупно потрошени средства +{{{#!sql +CREATE OR REPLACE FUNCTION get_monthly_expense_by_tag() +RETURNS SETOF RECORD +LANGUAGE plpgsql +AS $$ +DECLARE + col_list TEXT; + dynamic_query TEXT; +BEGIN + SELECT STRING_AGG( + DISTINCT format( + 'SUM(CASE WHEN tg.tag_name = %L THEN tb.spent_amount ELSE 0 END) AS %I', + tag_name, tag_name + ), + ', ' + ) + INTO col_list + FROM tag; + + dynamic_query := format( + 'SELECT + TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month, + %s + FROM transaction t + JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id + JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id + JOIN tag tg ON tat.tag_id = tg.tag_id + GROUP BY transaction_month + ORDER BY transaction_month;', + col_list + ); + + RETURN QUERY EXECUTE dynamic_query; +END; +$$; +}}} + +==== Релациона алгебра +- T(transaction_id, date) +- TB(transaction_id, spent_amount) +- TAT(transaction_id, tag_id) +- TG(tag_id, tag_name) + +JOIN на сите табели: +- J1 ← T ⨝,,T.transaction_id = TB.transaction_id,, TB +- J2 ← J1 ⨝,,T.transaction_id = TAT.transaction_id,, TAT +- J3 ← J2 ⨝,,TAT.tag_id = TG.tag_id,, TG + +Групирање по месец и таг: +- G ← γ,,month = FORMAT(date, 'YYYY-MM'), tag_name; Σ(spent_amount) → total_expense,,(J3) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport8 +==== Просечно трошење во последните 3 дена за цел систем +Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции: +{{{#!sql +CREATE OR REPLACE FUNCTION get_average_daily_spending_last_3_days_fixed() +RETURNS TABLE ( + average_spending_last_3_days NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + SUM(daily_spending) / 3 AS average_spending_last_3_days + FROM ( + SELECT + d.day::date AS transaction_date, + COALESCE(SUM(tb.spent_amount), 0) AS daily_spending + FROM ( + SELECT CURRENT_DATE - 2 AS day + UNION ALL + SELECT CURRENT_DATE - 1 + UNION ALL + SELECT CURRENT_DATE + ) d + LEFT JOIN transaction t + ON t.date::date = d.day + LEFT JOIN transaction_breakdown tb + ON t.transaction_id = tb.transaction_id + GROUP BY d.day + ) daily_totals; +END; +$$; +}}} + +==== Релациона алгебра +- T(transaction_id, date) +- TB(transaction_id, spent_amount) +- D(day) -- генерирана релација со последните 3 дена + +Генерирање на последните 3 дена: +- D ← { CURRENT_DATE − 2, CURRENT_DATE − 1, CURRENT_DATE } + +LEFT JOIN со трансакции: +- J1 ← D ⟕,,DATE(T.date) = D.day,, T +- J2 ← J1 ⟕,,T.transaction_id = TB.transaction_id,, TB + +Групирање по ден и пресметка на дневно трошење: +- G ← γ,,day; Σ(spent_amount) → daily_spending,,(J2) + +Пресметка на просек (фиксно делење со 3): +- R_final ← γ,,; (Σ(daily_spending) / 3) → average_spending_last_3_days,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport9 +==== Просечно трошење во последните 3 дена за корисник +Просечно дневно трошење за последните 3 дена за одреден корисник: +{{{#!sql +CREATE OR REPLACE FUNCTION get_average_daily_spending_last_3_days_user( + p_user_id INT +) +RETURNS TABLE ( + average_spending_last_3_days NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + SUM(daily_spending) / 3 AS average_spending_last_3_days + FROM ( + SELECT + d.day::date AS transaction_date, + COALESCE(SUM(tb.spent_amount), 0) AS daily_spending + FROM ( + SELECT CURRENT_DATE - 2 AS day + UNION ALL + SELECT CURRENT_DATE - 1 + UNION ALL + SELECT CURRENT_DATE + ) d + LEFT JOIN transaction_account ta + ON ta.user_id = p_user_id + LEFT JOIN transaction_breakdown tb + ON ta.transaction_account_id = tb.transaction_account_id + LEFT JOIN transaction t + ON t.transaction_id = tb.transaction_id + AND t.date::date = d.day + GROUP BY d.day + ) daily_totals; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, user_id) +- T(transaction_id, date) +- TB(transaction_id, spent_amount) +- D(day) -- генерирани последните 3 дена + +Генерирање на последните 3 дена: +- D ← { CURRENT_DATE − 2, CURRENT_DATE − 1, CURRENT_DATE } + +LEFT JOIN со сметка на корисник: +- J1 ← D ⟕,,TA.user_id = p_user_id,, TA +- J2 ← J1 ⟕,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J3 ← J2 ⟕,,TB.transaction_id = T.transaction_id ∧ DATE(T.date) = D.day,, T + +Групирање по ден и пресметка на дневно трошење: +- G ← γ,,day; Σ(spent_amount) → daily_spending,,(J3) + +Просечно дневно трошење (фиксно делење со 3): +- R_final ← γ,,; (Σ(daily_spending) / 3) → average_spending_last_3_days,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport25 +==== Просечно трошење во последните N дена +- по корисник (со параметар p_user_id) +- за цел систем (со параметар p_user_id = NULL) + +{{{#!sql +CREATE OR REPLACE FUNCTION get_average_daily_spending_last_N_days( + p_days INT, + p_user_id INT DEFAULT NULL -- ако е NULL, се зема цел систем +) +RETURNS TABLE ( + average_spending NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + SUM(daily_spending) / p_days AS average_spending + FROM ( + -- генерација на последни p_days + SELECT + d.day::date AS transaction_date, + COALESCE(SUM(tb.spent_amount), 0) AS daily_spending + FROM ( + SELECT generate_series(CURRENT_DATE - (p_days - 1), CURRENT_DATE, '1 day') AS day + ) d + LEFT JOIN transaction t + ON t.date::date = d.day + LEFT JOIN transaction_breakdown tb + ON t.transaction_id = tb.transaction_id + LEFT JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + AND (p_user_id IS NULL OR ta.user_id = p_user_id) + GROUP BY d.day + ) daily_totals; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, user_id) +- T(transaction_id, date) +- TB(transaction_id, spent_amount) +- D(day) -- генерација на последните p_days + +Генерација на последни p_days: +- D ← { CURRENT_DATE − (p_days − 1), ..., CURRENT_DATE } + +LEFT JOIN со трансакции: +- J1 ← D ⟕,,TRUE,, T +- J2 ← J1 ⟕,,T.transaction_id = TB.transaction_id,, TB +- J3 ← J2 ⟕,,TB.transaction_account_id = TA.transaction_account_id ∧ (p_user_id IS NULL OR TA.user_id = p_user_id),, TA + +Групирање по ден и пресметка на дневно трошење: +- G ← γ,,day; Σ(spent_amount) → daily_spending,,(J3) + +Просечно дневно трошење (фиксно делење со p_days): +- R_final ← γ,,; (Σ(daily_spending) / p_days) → average_spending,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport10 +==== Вкупно потрошено во тековниот месец за цел систем +Вкупна сума на трошоци во тековниот месец +{{{#!sql +CREATE OR REPLACE FUNCTION get_total_spent_current_month() +RETURNS TABLE ( + total_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + FROM transaction_breakdown tb + JOIN transaction t + ON tb.transaction_id = t.transaction_id + WHERE EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE) + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE); +END; +$$; +}}} + +==== Релациона алгебра +- TB(transaction_id, spent_amount) +- T(transaction_id, date) + +JOIN на табелите: +- J1 ← TB ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтрирање на трансакции од тековниот месец и година: +- F ← σ,,EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J1) + +Агрегација на вкупно потрошено: +- R_final ← γ,,; SUM(spent_amount) → total_spent,,(F) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport11 +==== Вкупно потрошено во тековниот месец за корисник +Вкупна сума на трошоци во тековниот месец на еден корисник +{{{#!sql +CREATE OR REPLACE FUNCTION get_total_spent_current_month_user( + p_user_id INT +) +RETURNS TABLE ( + total_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + FROM transaction_breakdown tb + JOIN transaction t + ON tb.transaction_id = t.transaction_id + JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + WHERE ta.user_id = p_user_id + AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE) + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE); +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, user_id) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, date) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтрирање на трансакции за даден корисник и тековен месец: +- F ← σ,,user_id = p_user_id ∧ EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),, (J2) + +Агрегација на вкупно потрошено: +- R_final ← γ,,; SUM(spent_amount) → total_spent,,(F) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport12 +==== Дневен буџет до крајот на месецот за корисник +Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот +{{{#!sql +CREATE OR REPLACE FUNCTION get_daily_budget_user( + p_user_id INT +) +RETURNS TABLE ( + daily_budget NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + COALESCE(SUM(ta.balance), 0) / + (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY') + - DATE_PART('day', CURRENT_DATE) + 1) AS daily_budget + FROM transaction_account ta + WHERE ta.user_id = p_user_id; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, user_id, balance) + +Филтрирање по корисник: +- F ← σ,,user_id = p_user_id,,(TA) + +Агрегација на вкупен баланс: +- S ← γ,,; SUM(balance) → total_balance,,(F) + +Пресметка на дневен буџет до крајот на месецот: +- R_final ← γ,,; total_balance / (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY')- DATE_PART('day', CURRENT_DATE) + 1) → daily_budget,,(S) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport13 +==== Долг на кредитна картичка од минатиот месец за цел систем +Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец +{{{#!sql +CREATE OR REPLACE FUNCTION get_credit_card_debt_last_month() +RETURNS TABLE ( + credit_card_debt NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + COALESCE(SUM(tb.spent_amount), 0) AS credit_card_debt + FROM transaction_breakdown tb + JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + JOIN transaction t + ON tb.transaction_id = t.transaction_id + WHERE (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%') + AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'); +END; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, account_name) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, date) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтрирање на кредитни картички и минат месец: +- F ← σ,,(account_name ILIKE '%кредитна%' OR account_name ILIKE '%credit%') ∧ EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'),,(J2) + +Агрегација на вкупен долг: +- R_final ← γ,,; SUM(spent_amount) → credit_card_debt,,(F) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport14 +==== Долг на кредитна картичка од минатиот месец за корисник +Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник +{{{#!sql +CREATE OR REPLACE FUNCTION get_credit_card_debt_last_month_user( + p_user_id INT +) +RETURNS TABLE ( + credit_card_debt NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + COALESCE(SUM(tb.spent_amount), 0) AS credit_card_debt + FROM transaction_breakdown tb + JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + JOIN transaction t + ON tb.transaction_id = t.transaction_id + WHERE ta.user_id = p_user_id + AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%') + AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'); +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, user_id, account_name) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, date) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтрирање по корисник, кредитна картичка и минат месец: +- F ← σ,,user_id = p_user_id ∧ (account_name ILIKE '%кредитна%' OR account_name ILIKE '%credit%') ∧ EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'),,(J2) + +Агрегација на вкупен долг: +- R_final ← γ,,; SUM(spent_amount) → credit_card_debt,,(F) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport15 +==== Трендови на трошење според тагови за цел систем +Трендови на трошење за секој таг во последните шест месеци +{{{#!sql +CREATE OR REPLACE FUNCTION get_spending_trends_last_6_months() +RETURNS TABLE ( + tag_name TEXT, + month DATE, + total_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + tg.tag_name, + DATE_TRUNC('month', t.date) AS month, + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + FROM tag tg + JOIN tag_assigned_to_transaction tat + ON tg.tag_id = tat.tag_id + JOIN transaction t + ON tat.transaction_id = t.transaction_id + JOIN transaction_breakdown tb + ON t.transaction_id = tb.transaction_id + WHERE t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS') + GROUP BY tg.tag_name, month + ORDER BY tg.tag_name, month; +END; +$$; +}}} + +==== Релациона алгебра +- TG(tag_id, tag_name) +- TAT(tag_id, transaction_id) +- T(transaction_id, date) +- TB(transaction_id, spent_amount) + +JOIN на сите табели: +- J1 ← TG ⨝,,TG.tag_id = TAT.tag_id,, TAT +- J2 ← J1 ⨝,,TAT.transaction_id = T.transaction_id,, T +- J3 ← J2 ⨝,,T.transaction_id = TB.transaction_id,, TB + +Филтрирање на трансакции од последните 6 месеци: +- F ← σ,,date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS'),,(J3) + +Групирање по таг и месец: +- G ← γ,,tag_name, month = DATE_TRUNC('month', date); Σ(spent_amount) → total_spent,,(F) + +Подредување по таг и месец: +- R_final ← τ,,tag_name, month,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport16 +==== Трендови на трошење според тагови за корисник +Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник +{{{#!sql +CREATE OR REPLACE FUNCTION get_spending_trends_last_6_months_user( + p_user_id INT +) +RETURNS TABLE ( + tag_name TEXT, + month DATE, + total_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + tg.tag_name, + DATE_TRUNC('month', t.date) AS month, + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + FROM tag_assigned_to_transaction tat + JOIN transaction t + ON tat.transaction_id = t.transaction_id + JOIN transaction_breakdown tb + ON t.transaction_id = tb.transaction_id + JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + JOIN tag tg + ON tat.tag_id = tg.tag_id + WHERE ta.user_id = p_user_id + AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS') + GROUP BY tg.tag_name, month + ORDER BY tg.tag_name, month; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, user_id) +- T(transaction_id, date) +- TB(transaction_id, transaction_account_id, spent_amount) +- TAT(tag_id, transaction_id) +- TG(tag_id, tag_name) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T +- J3 ← J2 ⨝,,T.transaction_id = TAT.transaction_id,, TAT +- J4 ← J3 ⨝,,TAT.tag_id = TG.tag_id,, TG + +Филтрирање по корисник и последни 6 месеци: +- F ← σ,,user_id = p_user_id ∧ date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS'),,(J4) + +Групирање по таг и месец: +- G ← γ,,tag_name, month = DATE_TRUNC('month', date); Σ(spent_amount) → total_spent,,(F) + +Подредување по таг и месец: +- R_final ← τ,,tag_name, month,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport17 +==== Вкупно трошење според тагови +Вкупно трошење групирано според тагови за тековниот месец +{{{#!sql +CREATE OR REPLACE FUNCTION get_total_spent_by_tag_current_month() +RETURNS TABLE ( + tag_name TEXT, + total_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + tg.tag_name, + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + FROM tag tg + JOIN tag_assigned_to_transaction tat + ON tg.tag_id = tat.tag_id + JOIN transaction t + ON tat.transaction_id = t.transaction_id + JOIN transaction_breakdown tb + ON t.transaction_id = tb.transaction_id + WHERE EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE) + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE) + GROUP BY tg.tag_name + ORDER BY total_spent DESC; +END; +$$; +}}} + +==== Релациона алгебра +- TG(tag_id, tag_name) +- TAT(tag_id, transaction_id) +- T(transaction_id, date) +- TB(transaction_id, spent_amount) + +JOIN на сите табели: +- J1 ← TG ⨝,,TG.tag_id = TAT.tag_id,, TAT +- J2 ← J1 ⨝,,TAT.transaction_id = T.transaction_id,, T +- J3 ← J2 ⨝,,T.transaction_id = TB.transaction_id,, TB + +Филтрирање по тековен месец и година: +- F ← σ,,EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J3) + +Групирање и агрегација по таг: +- G ← γ,,tag_name; SUM(spent_amount) → total_spent,,(F) + +Подредување по вкупно потрошено: +- R_final ← τ,,total_spent DESC,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport18 +==== Сметки со највисоко вкупно трошење во изминатата година +{{{#!sql +CREATE OR REPLACE FUNCTION get_top_accounts_last_year() +RETURNS TABLE ( + account_name TEXT, + total_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + ta.account_name, + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + FROM transaction_breakdown tb + JOIN transaction t + ON tb.transaction_id = t.transaction_id + JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + WHERE t.date >= NOW() - INTERVAL '1 YEAR' + GROUP BY ta.account_name + ORDER BY total_spent DESC + LIMIT 10; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, account_name) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, date) + +JOIN на табелите: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтрирање по последна година: +- F ← σ,,date >= NOW() - INTERVAL '1 YEAR',, (J2) + +Групирање и агрегација по сметка: +- G ← γ,,account_name; SUM(spent_amount) → total_spent,,(F) + +Подредување по вкупно потрошено и лимитирање: +- R_final ← τ,,total_spent DESC LIMIT 10,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport19 +==== Најчести времиња за трансакции +Во кој час од денот корисниците најчесто вршат трансакции +{{{#!sql +CREATE OR REPLACE FUNCTION get_most_frequent_transaction_hours() +RETURNS TABLE ( + transaction_hour INT, + transaction_count BIGINT +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + EXTRACT(HOUR FROM t.date)::INT AS transaction_hour, + COUNT(*) AS transaction_count + FROM transaction t + GROUP BY transaction_hour + ORDER BY transaction_count DESC; +END; +$$; +}}} + +==== Релациона алгебра +- T(transaction_id, date) + +Групирање по час на трансакција: +- G ← γ,,transaction_hour = EXTRACT(HOUR FROM date); COUNT(transaction_id) → transaction_count,,(T) + +Подредување по број на трансакции: +- R_final ← τ,,transaction_count DESC,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport20 +==== Трансакции според тагови со највисоко трошење за цел систем +{{{#!sql +CREATE OR REPLACE FUNCTION get_total_spent_by_tag_system() +RETURNS TABLE ( + tag_name TEXT, + total_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + tg.tag_name, + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + FROM transaction_breakdown tb + JOIN transaction t + ON tb.transaction_id = t.transaction_id + JOIN tag_assigned_to_transaction tat + ON t.transaction_id = tat.transaction_id + JOIN tag tg + ON tat.tag_id = tg.tag_id + GROUP BY tg.tag_name + ORDER BY total_spent DESC; +END; +$$; +}}} + +==== Релациона алгебра +- TB(transaction_id, spent_amount) +- T(transaction_id, date) +- TAT(tag_id, transaction_id) +- TG(tag_id, tag_name) + +JOIN на сите табели: +- J1 ← TB ⨝,,TB.transaction_id = T.transaction_id,, T +- J2 ← J1 ⨝,,T.transaction_id = TAT.transaction_id,, TAT +- J3 ← J2 ⨝,,TAT.tag_id = TG.tag_id,, TG + +Групирање и агрегација по таг: +- G ← γ,,tag_name; SUM(spent_amount) → total_spent,,(J3) + +Подредување по вкупно потрошено: +- R_final ← τ,,total_spent DESC,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport21 +==== Трансакции според тагови со највисоко трошење за корисник +Сумирање на трошењата според тагови за одреден корисник +{{{#!sql +CREATE OR REPLACE FUNCTION get_total_spent_by_tag_user( + p_user_id INT +) +RETURNS TABLE ( + tag_name TEXT, + total_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + tg.tag_name, + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + FROM transaction_breakdown tb + JOIN transaction t + ON tb.transaction_id = t.transaction_id + JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + JOIN tag_assigned_to_transaction tat + ON t.transaction_id = tat.transaction_id + JOIN tag tg + ON tat.tag_id = tg.tag_id + WHERE ta.user_id = p_user_id + GROUP BY tg.tag_name + ORDER BY total_spent DESC; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, user_id) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, date) +- TAT(tag_id, transaction_id) +- TG(tag_id, tag_name) + +JOIN на сите табели: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T +- J3 ← J2 ⨝,,T.transaction_id = TAT.transaction_id,, TAT +- J4 ← J3 ⨝,,TAT.tag_id = TG.tag_id,, TG + +Филтрирање по корисник: +- F ← σ,,user_id = p_user_id,,(J4) + +Групирање и агрегација по таг: +- G ← γ,,tag_name; SUM(spent_amount) → total_spent,,(F) + +Подредување по вкупно потрошено: +- R_final ← τ,,total_spent DESC,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport22 +==== Годишни трендови на трансакции за цел систем +Трошења според сметки за секој квартал од тековната година +{{{#!sql +CREATE OR REPLACE FUNCTION get_quarterly_spending_trends_system() +RETURNS TABLE ( + account_name TEXT, + q1_spent NUMERIC, + q2_spent NUMERIC, + q3_spent NUMERIC, + q4_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + ta.account_name, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent + FROM transaction_breakdown tb + JOIN transaction t + ON tb.transaction_id = t.transaction_id + JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + WHERE EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE) + GROUP BY ta.account_name + ORDER BY ta.account_name; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, account_name) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, date) + +JOIN на табелите: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтрирање по тековна година: +- F ← σ,,EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J2) + +Агрегација по квартали: +- G ← γ,,account_name; SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 1 THEN spent_amount ELSE 0 END) → q1_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 2 THEN spent_amount ELSE 0 END) → q2_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 3 THEN spent_amount ELSE 0 END) → q3_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 4 THEN spent_amount ELSE 0 END) → q4_spent,,(F) + +Подредување по име на сметка: +- R_final ← τ,,account_name,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport23 +==== Годишни трендови на трансакции за корисник +Трошења по квартали за тековната година за одреден корисник +{{{#!sql +CREATE OR REPLACE FUNCTION get_quarterly_spending_trends_user( + p_user_id INT +) +RETURNS TABLE ( + account_name TEXT, + q1_spent NUMERIC, + q2_spent NUMERIC, + q3_spent NUMERIC, + q4_spent NUMERIC +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + ta.account_name, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent + FROM transaction_breakdown tb + JOIN transaction t + ON tb.transaction_id = t.transaction_id + JOIN transaction_account ta + ON tb.transaction_account_id = ta.transaction_account_id + WHERE ta.user_id = p_user_id + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE) + GROUP BY ta.account_name + ORDER BY ta.account_name; +END; +$$; +}}} + +==== Релациона алгебра +- TA(transaction_account_id, user_id, account_name) +- TB(transaction_id, transaction_account_id, spent_amount) +- T(transaction_id, date) + +JOIN на табелите: +- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB +- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T + +Филтрирање по корисник и тековна година: +- F ← σ,,user_id = p_user_id ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE),,(J2) + +Агрегација по квартали: +- G ← γ,,account_name; SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 1 THEN spent_amount ELSE 0 END) → q1_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 2 THEN spent_amount ELSE 0 END) → q2_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 3 THEN spent_amount ELSE 0 END) → q3_spent, SUM(CASE WHEN EXTRACT(QUARTER FROM date) = 4 THEN spent_amount ELSE 0 END) → q4_spent,,(F) + +Подредување по име на сметка: +- R_final ← τ,,account_name,,(G) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReport24 +==== Неактивни тагови +Идентификување тагови кои не биле користени во изминатиот месец +{{{#!sql +CREATE OR REPLACE FUNCTION get_inactive_tags_last_month() +RETURNS TABLE ( + tag_name TEXT +) +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN QUERY + SELECT + tg.tag_name + FROM tag tg + LEFT JOIN tag_assigned_to_transaction tat + ON tg.tag_id = tat.tag_id + LEFT JOIN transaction t + ON tat.transaction_id = t.transaction_id + WHERE t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH'; +END; +$$; +}}} + +==== Релациона алгебра +- TG(tag_id, tag_name) +- TAT(tag_id, transaction_id) +- T(transaction_id, date) + +LEFT JOIN на табелите: +- J1 ← TG ⟕,,TG.tag_id = TAT.tag_id,, TAT +- J2 ← J1 ⟕,,TAT.transaction_id = T.transaction_id,, T + +Филтрирање на неактивни тагови: +- R_final ← σ,,date IS NULL ∨ date < NOW() - INTERVAL '1 MONTH',,(J2) +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedReportsVersion1 += Напредни извештаи од базата (SQL и складирани процедури) + +===== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки +{{{#!sql +WITH CumulativeBalances AS ( + SELECT + t.transaction_id, + t.transaction_name, + t.date AS transaction_date, + ta.account_name, + u.user_id, + u.user_name, + tb.spent_amount AS transaction_amount, + SUM(tb.earned_amount - tb.spent_amount) OVER ( + PARTITION BY ta.transaction_account_id + ORDER BY t.date + ) AS calculated_balance + FROM + transaction_account ta + JOIN + user u ON ta.user_id = u.user_id + JOIN + transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id + JOIN + transaction t ON tb.transaction_id = t.transaction_id + WHERE + ta.account_name = 'Specific Account Name' -- Може да се замени со специфично име на акаунт +) +SELECT + user_id, + user_name, + account_name, + transaction_id, + transaction_name, + transaction_amount, + transaction_date, + calculated_balance +FROM + CumulativeBalances +WHERE + transaction_amount > calculated_balance -- Трансакцијата го надминува пресметаниот баланс + AND transaction_amount > 0 +ORDER BY + user_id, account_name, transaction_date DESC; +}}} + +===== Надминување на баланс на една сметка со трансакција во сегашно време +{{{#!sql +SELECT + u.user_id, + u.user_name, + ta.account_name, + ta.balance AS current_balance, + t.transaction_id, + t.transaction_name, + tb.spent_amount AS transaction_amount, + t.date AS transaction_date +FROM + transaction_account ta +JOIN user u ON ta.user_id = u.user_id +JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id +JOIN transaction t ON tb.transaction_id = t.transaction_id +WHERE + tb.spent_amount > ta.balance -- Трансакцијата го надминува моменталниот баланс на СМЕТКАТА + AND tb.spent_amount > 0 +ORDER BY + u.user_id, ta.account_name, t.date DESC; +}}} + +===== Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки +{{{#!sql +WITH CumulativeTotalBalances AS ( + SELECT + t.transaction_id, + t.transaction_name, + t.date AS transaction_date, + u.user_id, + u.user_name, + tb.spent_amount AS transaction_amount, + SUM(tb.earned_amount - tb.spent_amount) OVER ( + PARTITION BY u.user_id + ORDER BY t.date + ) AS calculated_total_balance + FROM + transaction_account ta + JOIN + user u ON ta.user_id = u.user_id + JOIN + transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id + JOIN + transaction t ON tb.transaction_id = t.transaction_id +) +SELECT + user_id, + user_name, + transaction_id, + transaction_name, + transaction_amount, + transaction_date, + calculated_total_balance +FROM + CumulativeTotalBalances +WHERE + transaction_amount > calculated_total_balance -- Трансакцијата го надминува пресметаниот вкупен баланс од сите сметки + AND transaction_amount > 0 +ORDER BY + user_id, transaction_date DESC; +}}} + +===== Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време +Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува таа вкупна вредност +{{{#!sql +WITH total_user_balance AS ( + SELECT + u.user_id, + SUM(ta.balance) AS total_balance + FROM + transaction_account ta + JOIN user u ON ta.user_id = u.user_id + GROUP BY + u.user_id +) +SELECT + u.user_id, + u.user_name, + SUM(tb.spent_amount) AS total_transaction_amount, + tub.total_balance AS user_total_balance +FROM + transaction_account ta +JOIN user u ON ta.user_id = u.user_id +JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id +JOIN transaction t ON tb.transaction_id = t.transaction_id +JOIN total_user_balance tub ON u.user_id = tub.user_id +WHERE + t.date <= CURRENT_DATE -- Само активни трансакции до денешен датум +GROUP BY + u.user_id, u.user_name, tub.total_balance +HAVING + SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ моментален баланс +ORDER BY + u.user_id; +}}} + +===== Сумарни приходи и расходи по месеци +Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата +{{{#!sql +SELECT + TO_CHAR(t.date, 'YYYY-MM') AS month, -- Форматирање на датумот во формат "Година-Месец" + SUM(CASE WHEN tb.earned_amount > 0 THEN tb.earned_amount ELSE 0 END) AS total_income, -- Вкупен приход + SUM(CASE WHEN tb.spent_amount > 0 THEN tb.spent_amount ELSE 0 END) AS total_expense, -- Вкупен расход + SUM(CASE WHEN tb.earned_amount > 0 THEN tb.earned_amount ELSE 0 END) - + SUM(CASE WHEN tb.spent_amount > 0 THEN tb.spent_amount ELSE 0 END) AS net_balance -- Нето состојба +FROM + transaction_breakdown tb +JOIN + transaction t ON tb.transaction_id = t.transaction_id +GROUP BY + TO_CHAR(t.date, 'YYYY-MM') -- Групирање по месец +ORDER BY + month; +}}} +Проблематичен месец е месец каде што {{{net_balance}}} е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците + +===== Број на трансакции што ги надминуваат приходите на сметка +{{{#!sql +WITH CumulativeBalances AS ( + SELECT + t.transaction_id, + ta.account_name, + SUM(tb.earned_amount - tb.spent_amount) OVER ( + PARTITION BY ta.transaction_account_id + ORDER BY t.date + ) AS calculated_balance, + tb.spent_amount + FROM + transaction_account ta + JOIN + transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id + JOIN + transaction t ON tb.transaction_id = t.transaction_id +) +SELECT + account_name, + COUNT(transaction_id) AS transactions_exceeding_balance +FROM + CumulativeBalances +WHERE + spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс + AND spent_amount > 0 +GROUP BY + account_name +ORDER BY + transactions_exceeding_balance DESC; +}}} +Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки: +{{{#!sql +WITH CumulativeTotalBalances AS ( + SELECT + t.transaction_id, + u.user_id, + SUM(tb.earned_amount - tb.spent_amount) OVER ( + PARTITION BY u.user_id + ORDER BY t.date + ) AS calculated_total_balance, + tb.spent_amount + FROM + transaction_account ta + JOIN + user u ON ta.user_id = u.user_id + JOIN + transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id + JOIN + transaction t ON tb.transaction_id = t.transaction_id +) +SELECT + COUNT(transaction_id) AS total_transactions_exceeding_balance +FROM + CumulativeTotalBalances +WHERE + spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс + AND spent_amount > 0; +}}} + +===== Сумарни податоци за тагови +Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци: + - Вкупен број на трансакции +{{{#!sql +DO $$ +DECLARE + col_list TEXT; -- Листа на тагови за изведување на пивот + dynamic_query TEXT; -- Динамичко SQL прашање +BEGIN + -- Чекор 1: Динамички се генерира листа на колони + SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', tag_name, tag_name), ', ') + INTO col_list + FROM tag; + + -- Чекор 2: Се гради динамичкото прашање + dynamic_query := format( + 'SELECT + TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month, + %s + FROM + transaction t + JOIN + tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id + JOIN + tag tg ON tat.tag_id = tg.tag_id + GROUP BY + transaction_month + ORDER BY + transaction_month;', + col_list + ); + + -- Чекор 3: Извршување на динамичкото прашање + EXECUTE dynamic_query; +END $$; +}}} + - Вкупно примени средства +{{{#!sql +DO $$ +DECLARE + col_list TEXT; -- Листа на тагови за изведување на пивот + dynamic_query TEXT; -- Динамичко SQL прашање +BEGIN + -- Чекор 1: Динамички се генерира листа на колони + SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN tb.earned_amount ELSE 0 END) AS %I', tag_name, tag_name), ', ') + INTO col_list + FROM tag; + + -- Чекор 2: Се гради динамичкото прашање + dynamic_query := format( + 'SELECT + TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month, + %s + FROM + transaction t + JOIN + transaction_breakdown tb ON t.transaction_id = tb.transaction_id + JOIN + tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id + JOIN + tag tg ON tat.tag_id = tg.tag_id + GROUP BY + transaction_month + ORDER BY + transaction_month;', + col_list + ); + + -- Чекор 3: Извршување на динамичкото прашање + EXECUTE dynamic_query; +END $$; +}}} + - Вкупно потрошени средства +{{{#!sql +DO $$ +DECLARE + col_list TEXT; -- Листа на тагови за изведување на пивот + dynamic_query TEXT; -- Динамичко SQL прашање +BEGIN + -- Чекор 1: Динамички се генерира листа на колони + SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN tb.spent_amount ELSE 0 END) AS %I', tag_name, tag_name), ', ') + INTO col_list + FROM tag; + + -- Чекор 2: Се гради динамичкото прашање + dynamic_query := format( + 'SELECT + TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month, + %s + FROM + transaction t + JOIN + transaction_breakdown tb ON t.transaction_id = tb.transaction_id + JOIN + tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id + JOIN + tag tg ON tat.tag_id = tg.tag_id + GROUP BY + transaction_month + ORDER BY + transaction_month;', + col_list + ); + + -- Чекор 3: Извршување на динамичкото прашање + EXECUTE dynamic_query; +END $$; +}}} + +===== Просечно трошење во последните 3 дена за цел систем +Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции: +{{{#!sql +SELECT + AVG(daily_spending) AS average_spending_last_3_days +FROM ( + SELECT + t.date::date AS transaction_date, + SUM(tb.spent_amount) AS daily_spending + FROM + transaction t + JOIN + transaction_breakdown tb ON t.transaction_id = tb.transaction_id + WHERE + t.date >= CURRENT_DATE - INTERVAL '2 DAY' + AND t.date < CURRENT_DATE + INTERVAL '1 DAY' + GROUP BY + t.date::date +) daily_totals; +}}} + +===== Просечно трошење во последните 3 дена за корисник +Просечно дневно трошење за последните 3 дена за одреден корисник: +{{{#!sql +SELECT + AVG(daily_spending) AS average_spending_last_3_days +FROM ( + SELECT + t.date::date AS transaction_date, + SUM(tb.spent_amount) AS daily_spending + FROM + transaction t + JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id + JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id + WHERE + ta.user_id = 101 -- ID на конкретниот корисник + AND t.date >= CURRENT_DATE - INTERVAL '2 DAY' + AND t.date < CURRENT_DATE + INTERVAL '1 DAY' + GROUP BY + t.date::date +) daily_totals; +}}} + +===== Вкупно потрошено во тековниот месец за цел систем +Вкупна сума на трошоци во тековниот месец +{{{#!sql +SELECT + SUM(tb.spent_amount) AS total_spent +FROM + transaction_breakdown tb +JOIN transaction t ON tb.transaction_id = t.transaction_id +WHERE + EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE) + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE); +}}} + +===== Вкупно потрошено во тековниот месец за корисник +Вкупна сума на трошоци во тековниот месец на еден корисник +{{{#!sql +SELECT + SUM(tb.spent_amount) AS total_spent +FROM + transaction_breakdown tb +JOIN transaction t ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +WHERE + ta.user_id = 101 -- ID на конкретниот корисник + AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE) + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE); +}}} + +===== Дневен буџет до крајот на месецот за корисник +Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот +{{{#!sql +SELECT + SUM(ta.balance) / (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY') - DATE_PART('day', CURRENT_DATE)) AS daily_budget +FROM + transaction_account ta +WHERE + ta.user_id = 101; -- ID на конкретниот корисник +}}} + +===== Долг на кредитна картичка од минатиот месец за цел систем +Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец +{{{#!sql +SELECT + SUM(tb.spent_amount) AS credit_card_debt +FROM + transaction_breakdown tb +JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +JOIN transaction t ON tb.transaction_id = t.transaction_id +WHERE + (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%') + AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'); +}}} + +===== Долг на кредитна картичка од минатиот месец за корисник +Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник +{{{#!sql +SELECT + SUM(tb.spent_amount) AS credit_card_debt +FROM + transaction_breakdown tb +JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +JOIN transaction t ON tb.transaction_id = t.transaction_id +WHERE + ta.user_id = 101 -- ID на конкретниот корисник + AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%') + AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'); +}}} + +===== Трендови на трошење според тагови за цел систем +Трендови на трошење за секој таг во последните шест месеци +{{{#!sql +SELECT + tg.tag_name, + DATE_TRUNC('month', t.date) AS month, + SUM(tb.spent_amount) AS total_spent +FROM + tag tg +JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id +JOIN transaction t ON tat.transaction_id = t.transaction_id +JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id +WHERE + t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS') +GROUP BY + tg.tag_name, month +ORDER BY + tg.tag_name, month; +}}} + +===== Трендови на трошење според тагови за корисник +Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник +{{{#!sql +SELECT + tg.tag_name, + DATE_TRUNC('month', t.date) AS month, + SUM(tb.spent_amount) AS total_spent +FROM + tag_assigned_to_transaction tat +JOIN transaction t ON tat.transaction_id = t.transaction_id +JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id +JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +JOIN tag tg ON tat.tag_id = tg.tag_id +WHERE + ta.user_id = 101 -- ID на конкретниот корисник + AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS') +GROUP BY + tg.tag_name, month +ORDER BY + tg.tag_name, month; +}}} + +===== Вкупно трошење според тагови +Вкупно трошење групирано според тагови за тековниот месец +{{{#!sql +SELECT + tg.tag_name, + SUM(tb.spent_amount) AS total_spent +FROM + tag tg +JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id +JOIN transaction t ON tat.transaction_id = t.transaction_id +JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id +WHERE + EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE) + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE) +GROUP BY + tg.tag_name +ORDER BY + total_spent DESC; +}}} + +===== Сметки со највисоко вкупно трошење во изминатата година +{{{#!sql +SELECT + ta.account_name, + SUM(tb.spent_amount) AS total_spent +FROM + transaction_breakdown tb +JOIN transaction t ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +WHERE + t.date >= NOW() - INTERVAL '1 YEAR' +GROUP BY + ta.account_name +ORDER BY + total_spent DESC +LIMIT 10; +}}} + +===== Најчести времиња за трансакции +Во кој час од денот корисниците најчесто вршат трансакции +{{{#!sql +SELECT + EXTRACT(HOUR FROM t.date) AS transaction_hour, + COUNT(*) AS transaction_count +FROM + transaction t +GROUP BY + transaction_hour +ORDER BY + transaction_count DESC; +}}} + +===== Трансакции според тагови со највисоко трошење за цел систем +{{{#!sql +SELECT + tg.tag_name, + SUM(tb.spent_amount) AS total_spent +FROM + transaction_breakdown tb +JOIN transaction t ON tb.transaction_id = t.transaction_id +JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id +JOIN tag tg ON tat.tag_id = tg.tag_id +GROUP BY + tg.tag_name +ORDER BY + total_spent DESC; +}}} + +===== Трансакции според тагови со највисоко трошење за корисник +Сумирање на трошењата според тагови за одреден корисник +{{{#!sql +SELECT + tg.tag_name, + SUM(tb.spent_amount) AS total_spent +FROM + transaction_breakdown tb +JOIN transaction t ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id +JOIN tag tg ON tat.tag_id = tg.tag_id +WHERE + ta.user_id = 101 -- ID на конкретниот корисник +GROUP BY + tg.tag_name +ORDER BY + total_spent DESC; +}}} + +===== Годишни трендови на трансакции за цел систем +Трошења според сметки за секој квартал од тековната година +{{{#!sql +SELECT + ta.account_name, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent +FROM + transaction_breakdown tb +JOIN transaction t ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +WHERE + EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE) +GROUP BY + ta.account_name +ORDER BY + ta.account_name; +}}} + +===== Годишни трендови на трансакции за корисник +Трошења по квартали за тековната година за одреден корисник +{{{#!sql +SELECT + ta.account_name, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent, + SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent +FROM + transaction_breakdown tb +JOIN transaction t ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id +WHERE + ta.user_id = 101 -- ID на конкретниот корисник + AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE) +GROUP BY + ta.account_name +ORDER BY + ta.account_name; +}}} + +===== Неактивни тагови +Идентификување тагови кои не биле користени во изминатиот месец +{{{#!sql +SELECT + tg.tag_name +FROM + tag tg +LEFT JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id +LEFT JOIN transaction t ON tat.transaction_id = t.transaction_id +WHERE + t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH'; +}}} + +https://develop.finki.ukim.mk/projects/FEIN/wiki/PrototypeApplication += Прототип апликација + +== Презентација на финалниот изглед на апликацијата +[wiki:Presentation Презентација] + +== Инструкции за стартување на апликацијата +[wiki:Instructions Инструкции] + + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Presentation +== Опис на финална имплементација со функционалности и насоки за употреба + +Финалната верзија на апликацијата ги опфаќа сите сценарија од фаза Ф3, а тоа се: +- Регистрација на корисник +- Најава на корисник +- Додавање на трансакциска сметка +- Преглед на сметки +- Додавање на трансакција +- Преглед на трансакции +- Ажурирање/бришење трансакции +- Извештаи (годишни/месечни/по тагови/графици) + +== Опис на секое од наведените сценарија +=== Регистрација на корисник +[https://youtu.be/9s8dOSDpf38 видео] +=== Најава на корисник +[https://youtu.be/elvXAcs-mVA видео] +=== Додавање на трансакциска сметка +[https://youtu.be/jfItnKE0whg видео] +=== Преглед на сметки +[https://youtu.be/DxqRu2akhJ0 видео] +=== Додавање на трансакција +[https://youtu.be/BxtTVXZDi2A видео] +=== Преглед на трансакции +[https://youtu.be/jZQE1guSBPo видео] +=== Ажурирање трансакции +[https://youtu.be/vxQXTLj2HgE видео] +=== Бришење трансакции +[https://youtu.be/pET4XEN1pxM видео] +=== Извештаи (годишни/месечни/по тагови/графици) +[https://youtu.be/bkKTstZj0uU видео] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Instructions +== Упатство за стартување и користење на апликацијата (Верзија 2) + +Препорачувам користење на Visual Studio Code. +Потребно е да се инсталира Python, истото може да се направи [https://www.python.org/downloads/ овде]. + +Во VS Code, препорачувам користење на виртуелна околина, пример //venv//. Повеќе на [https://docs.python.org/3/library/venv.html линков]. + +Откако ќе се активира виртуелна околина, може да се инсталираат потребните python пакети во нејзе. Тие се наоѓаат во текстуелната датотека //requirements.txt//. За да се инсталираат сите одеднаш, може да се искорсти командата: +{{{#!cmd +pip install -r requirements.txt +}}} + +Кога сѐ од горенаведеното ќе се направи, може да се отворат две //bash// терминали во VS Code кои ќе се постават една до друга (root е root-от на проектот): +- Во првата терминала се пишува командата +{{{#!cmd +uvicorn app.main:app --host 0.0.0.0 --port 8000 --reload +}}} + со неа се стартува backend-от кој се поврзува автоматски со датабазата на проектот дадена од професорот. +- Во втората терминала се пишува командата +{{{#!cmd +python -m cli.cli_app +}}} + со неа се извршува прототип апликацијата која се наоѓа во //cli// директориумот под името //cli_app//. + +Во втората терминала може да се почне со користење на прототип апликацијата, притоа секое повикување и користење на backend-от и базата се отсликува во првата терминала. + +=== Верзии +[wiki:InstructionsVersion1 Верзија 1] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/InstructionsVersion1 +== Упатство за стартување и користење на апликацијата (Верзија 1) + +Препорачувам користење на Visual Studio Code. +Потребно е да се инсталира Python, истото може да се направи [https://www.python.org/downloads/ овде]. + +Во VS Code, препорачувам користење на виртуелна околина, пример //venv//. Повеќе на [https://docs.python.org/3/library/venv.html линков]. + +Откако ќе се активира виртуелна околина, може да се инсталираат потребните python пакети во нејзе. Тие се наоѓаат во текстуелната датотека //requirements.txt//. За да се инсталираат сите одеднаш, може да се искорсти командата: +{{{#!cmd +pip install -r requirements.txt +}}} + +Јас користев Docker Containers за извршување и поврзување на прототип апликацијата со локална датабаза заедно. За тоа потребно е да се инсталира Docker Desktop, инструкции за тоа на [https://docs.docker.com/desktop/setup/install/windows-install/ линков]. + +Кога сѐ од горенаведеното ќе се направи, може да се отворат две //bash// терминали во VS Code кои ќе се постават една до друга (root е root-от на проектот): +- Во првата терминала се пишува командата +{{{#!cmd +docker-compose up --build +}}} + со неа се build-аат и подигаат Docker контејнерите. Првиот пат, процесот ќе потрае подолго, бидејќи треба да ги симне и инсталира Docker сликите, истото да го направи со python пакетите и сето тоа да го из-build-а. +- Во втората терминала се пишува командата +{{{#!cmd +python -m cli.cli_app +}}} + со неа се извршува прототип апликацијата која се наоѓа во //cli// директориумот под името //cli_app//. + +Во втората терминала може да се почне со користење на прототип апликацијата, притоа секое повикување и користење на backend-от и базата се отсликува во првата терминала. + + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Normalization += Нормализација + +== Денормализирана форма + +Форма во кој има една табела во која се внесени сите ентитети и нивни релации помеѓу себе, без никакви правила + +||= //user_id =||= //user_name =||= //email =||= //password =||= //transaction_account_id =||= //account_name =||= //balance =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =|| +|| 1|| james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || 1, 2 || credit card, cash || 5000, 1200 || 1, 2 || electricity bill, burger || -1800, -800 || -1800, -200 || 18:03:25 Sep 26, 2025, 12:43:00 Sep 26, 2025 || 1,2 || -1800, -800 || 0, 600 || 1, 2 || bills, food || +|| 2|| anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm || 1 || debit card || 3200 || 1 || nail polish || -200 || -200 || 19:31:32 Sep 12 2025 || 1 || -200 || 0 || 1 || beauty || + +Во денормализираната табела постојат следниве основни функционални зависности: + +- {user_id} → {user_name, email, password} +- {transaction_account_id} → {account_name, balance, user_id} +- {transaction_id} → {transaction_name, amount, net_amount, date} +- {transaction_breakdown_id} → {transaction_id, transaction_account_id, spent_amount, earned_amount} +- {tag_id} → {tag_name} + +== Прва нормална форма + +Форма во која повторно има една табела, но овојпат, таа е ограничена со следниве работи: +- Подредувањето на редовите не претставува никакво значење +- Во ќелиите на секоја колона има по една вредност +- Не се мешаат типови на податоци во една ќелија +- Табелата има примарен композитен клуч(user_id, transaction_id, transaction_account_id, transaction_breakdown_id) +- Нема повторувачки групи, сите што биле во денормализираната форма сега се во посебен ред + +==== R +||= //user_id =||= //user_name =||= //email =||= //password =||= //transaction_account_id =||= //account_name =||= //balance =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =|| +|| 1|| james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || 1|| credit card || 5000|| 1|| electricity bill || -1800|| -1800|| 18:03:25 Sep 26, 2025 || 1|| -1800|| 0|| 1|| bills || +|| 1|| james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || 2|| cash || 1200|| 2|| burger || -800|| -200|| 12:43:00 Sep 26, 2025 || 2|| -800|| 600|| 2|| food || +|| 2|| anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm || 3|| debit card || 3200|| 3|| nail polish || -200|| -200|| 19:31:32 Sep 12 2025 || 3|| -200|| 0|| 3|| beauty || + +==== Избор на примарен клуч во 1NF + +Во првата нормална форма е дефиниран композитен примарен клуч: +//(user_id, transaction_id, transaction_account_id, transaction_breakdown_id)// + +tag_id може да биде вклучен во композитниот примарен клуч во 1NF бидејќи 1NF само ограничува по атомност и уникатност на ред, но не треба да биде вклучен бидејќи ја нарушува минималноста на клучот, додава парцијални зависности и погрешно ја моделира many-to-many релацијата. +Затоа, tag_id мора да биде одделен во посебна релација во погорните нормални форми. + +Овој клуч е избран бидејќи eдинствено го идентификува секој ред, ги опфаќа сите повторувачки групи од денормализираната форма и не постои подмножество од овој клуч кое може самостојно да го идентификува редот. + +=== Премин од 1NF во 2NF +Проблем се делумните зависности, во 1NF постојат атрибути кои зависат само од дел од примарниот клуч, на пример: +- {user_id} → {user_name, email, password} +- {transaction_id} → {transaction_name, amount, net_amount, date} + +Ова претставува кршење на втората нормална форма, решение за тоа е декомпозиција, односно секоја група на атрибути која зависи од дел од композитниот клуч се издвојува во посебна табела каде тој дел станува примарен клуч. Пример: +- USER(user_id PK, user_name, email, password) + +== Втора нормална форма + +Форма која ги следи овие правила: +- Веќе е во прва нормална форма +- Секој од атрибутите кој не е клуч, зависи од целосниот примарен клуч - со тоа се спречуваат аномалии на внесување, бришење и менување + +==== R1 + +{ user_id } → {user_name, email, password} + +||= USER =||= =||= =||= =|| +||= //user_id =||= //user_name =||= //email =||= //password =|| +|| 1 || james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || +|| 2 || anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm || + +====== Lossless join test + +R: +||= //user_id =||= //user_name =||= //email =||= //password =||= //transaction_account_id =||= //account_name =||= //balance =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =|| +R1: +||= //user_id =||= //user_name =||= //email =||= //password =|| + +R ∩ R1 = { user_id, user_name, email, password } + +Бидејќи {user_id} → {user_name, email, password}, а user_id ∈ (R ∩ R1), следи дека (R ∩ R1) → R1 + +⇒ Декомпозицијата е lossless + +R1.1 = R - { user_name, email, password } + +==== R2 + +{transaction_account_id} → {account_name, balance, user_id} + +||= TRANSACTION_ACCOUNT =||= =||= =||= || +||= //transaction_account_id =||= //account_name =||= //balance =||= //user_id || +|| 1 || credit card || 5000 || 1 || +|| 2 || cash || 1200 || 1 || +|| 3 || debit card || 3200 || 2 || + +====== Lossless join test + +R1.1: +||= //user_id =||= //transaction_account_id =||= //account_name =||= //balance =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =|| +R2: +||= //transaction_account_id =||= //account_name =||= //balance =||= //user_id || + +R1.1 ∩ R2 = { transaction_account_id, account_name, balance, user_id } + +Бидејќи {transaction_account_id} → {account_name, balance, user_id}, а transaction_account_id ∈ (R1.1 ∩ R2), следи дека (R1.1 ∩ R2) → R2 + +⇒ Декомпозицијата е lossless + +R2.1 = R1.1 - { account_name, balance, user_id } + +==== R3 + +{transaction_id} → {transaction_name, amount, net_amount, date, tag_id} + +{transaction_id} → {tag_id} → {tag_name} + +||= TRANSACTION_WITH_TAG_NAME =||= =||= =||= =||= =||= =||= =|| +||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //tag_id =||= //tag_name =|| +|| 1 || electricity bill || -1800 || -1800 || 18:03:25 Sep 26, 2025 || 1 || bills || +|| 2 || burger || -800 || -200 || 12:43:00 Sep 26, 2025 || 2 || food || +|| 3 || nail polish || -200 || -200 || 19:31:32 Sep 12 2025 || 3 || beauty || + +====== Lossless join test + +R2.1: +||= //transaction_account_id =||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =||= //tag_id =||= //tag_name =|| +R3: +||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //tag_id =||= //tag_name =|| + +R2.1 ∩ R3 = { transaction_id, transaction_name, amount, net_amount, date, tag_id, tag_name } + +Бидејќи {transaction_id} → {transaction_name, amount, net_amount, date, tag_id, tag_name}, а transaction_id ∈ (R2.1 ∩ R3), следи дека (R2.1 ∩ R3) → R3 + +⇒ Декомпозицијата е lossless + +R3.1 = R2.1 - { transaction_name, amount, net_amount, date, tag_id, tag_name } + +==== R4 + +{transaction_breakdown_id} → {transaction_id, transaction_account_id, spent_amount, earned_amount} + +||= TRANSACTION_BREAKDOWN =||= =||= =||= =||= =|| +||= //transaction_breakdown_id =||= //transaction_id =||= //transaction_account_id =||= //spent_amount =||= //earned_amount =|| +|| 1 || 1 || 1 || -1800 || 0 || +|| 2 || 2 || 2 || -800 || 600 || +|| 3 || 3 || 3 || -200 || 0 || + +====== Lossless join test + +R3.1: +||= //transaction_account_id =||= //transaction_id =||= //transaction_breakdown_id =||= //spent_amount =||= //earned_amount =|| +R4: +||= //transaction_breakdown_id =||= //transaction_id =||= //transaction_account_id =||= //spent_amount =||= //earned_amount =|| + +R3.1 ∩ R4 = { transaction_breakdown_id, transaction_id, transaction_account_id, spent_amount, earned_amount } + +Бидејќи {transaction_breakdown_id} → {transaction_id, transaction_account_id, spent_amount, earned_amount}, а transaction_breakdown_id ∈ (R3.1 ∩ R4), следи дека (R3.1 ∩ R4) → R4 + +⇒ Декомпозицијата е lossless + +R4.1 = R3.1 - { transaction_breakdown_id, transaction_id, transaction_account_id, spent_amount, earned_amount } + +R4.1 = ∅ + +=== Премин од 2NF во 3NF + +Во 2NF, за поедноставување, се разгледува случај со еден таг по трансакција, оваа претпоставка се отстранува при премин во 3NF. + +Проблем се транзитивните зависности, кои во 2NF сè уште постојат, на пример: +- {transaction_id} → {tag_id} → {tag_name} + +Ова значи дека некои атрибути кои не се клучеви зависат од други атрибути кои исто така не се клучеви, што ја крши 3NF. + +Решение за ова е отстранување на транзитивноста, односно секој атрибут кој не зависи директно од примарниот клуч се издвојува во нова табела. Пример: +- TAG(tag_id PK, tag_name) +- TAG_ASSIGNED_TO_TRANSACTION(transaction_id PK, tag_id PK) + +== Трета нормална форма + +Формава ги следи овие правила: +- Веќе е во втора нормална форма +- Се отргнуваат транзитивните функционални зависности, со тоа се овозможува сите атрибути кои не се клучеви да зависат само од примарниот клуч и од никој друг атрибут + +==== R1 + +{user_id} → {user_name, email, password} + +||= USER =||= =||= =||= =|| +||= //user_id =||= //user_name =||= //email =||= //password =|| +|| 1 || james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || +|| 2 || anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm || + +==== R2 + +{transaction_account_id} → {account_name, balance, user_id} + +||= TRANSACTION_ACCOUNT =||= =||= =||= =|| +||= //transaction_account_id =||= //account_name =||= //balance =||= //user_id =|| +|| 1 || credit card || 5000 || 1 || +|| 2 || cash || 1200 || 1 || +|| 3 || debit card || 3200 || 2 || + +==== R3.2 + +{transaction_id} → {transaction_name, amount, net_amount, date} + +||= TRANSACTION =||= =||= =||= =||= =|| +||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =|| +|| 1 || electricity bill || -1800 || -1800 || 18:03:25 Sep 26, 2025 || +|| 2 || burger || -800 || -200 || 12:43:00 Sep 26, 2025 || +|| 3 || nail polish || -200 || -200 || 19:31:32 Sep 12 2025 || + +====== Lossless join test + +R3: +||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =||= //tag_id =||= //tag_name =|| +R3.2: +||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =|| + +R3 ∩ R3.2 = { transaction_id, transaction_name, amount, net_amount, date } + +Бидејќи {transaction_id} → {transaction_name, amount, net_amount, date}, а transaction_id ∈ (R3 ∩ R3.2), следи дека (R3 ∩ R3.2) → R3.2 + +⇒ Декомпозицијата е lossless + +R3.3 = R3 - { transaction_id, transaction_name, amount, net_amount, date } + +==== R3.4 + +{tag_id} → {tag_name} + +||= TAG =||= =|| +||= //tag_id =||= //tag_name =|| +|| 1 || bills || +|| 2 || food || +|| 3 || beauty || + +====== Lossless join test + +R3.3: +||= //tag_id =||= //tag_name =|| +R3.4: +||= //tag_id =||= //tag_name =|| + +R3.3 ∩ R3.4 = { tag_id, tag_name } + +Бидејќи {tag_id} → {tag_name}, а tag_id ∈ (R3.3 ∩ R3.4), следи дека (R3.3 ∩ R3.4) → R3.4 + +⇒ Декомпозицијата е lossless + +R3.5 = R3.3 - { tag_id, tag_name } + +R3.5 = ∅ + +==== R4 + +{transaction_breakdown_id} → {transaction_id, transaction_account_id, spent_amount, earned_amount} + +||= TRANSACTION_BREAKDOWN =||= =||= =||= =||= =|| +||= //transaction_breakdown_id =||= //transaction_id =||= //transaction_account_id =||= //spent_amount =||= //earned_amount =|| +|| 1 || 1 || 1 || -1800 || 0 || +|| 2 || 2 || 2 || -800 || 600 || +|| 3 || 3 || 3 || -200 || 0 || + +==== R5 + +{transaction_id, tag_id} → {} + +||= TAG_ASSIGNED_TO_TRANSACTION =||= =|| +||= //transaction_id =||= //tag_id =|| +|| 1 || 1 || +|| 2 || 2 || +|| 3 || 3 || + +==== Избор на примарни клучеви во 3NF +По правилото дека секој примарен клуч мора минимално и единствено да го идентификува редот. +Примери: +- USER.user_id — природен идентификатор +- TRANSACTION.transaction_id — сурогатен клуч +- TAG_ASSIGNED_TO_TRANSACTION(transaction_id, tag_id) — композитен клуч за M:N релација +- TRANSACTION_BREAKDOWN.transaction_breakdown_id — сурогатен клуч + +==== Третата нормална форма погоре исто така е и BCNF + +=== + +Сите функционални зависности можат да се проверат локално во поединечни табели, без потреба од JOIN операции, oва значи дека шемата е dependency-preserving, lossless-join и е во BCNF. + +== Заклучок +Со примената на формална анализа на функционални зависности, правилен избор на примарни клучеви и проверка со lossless-join тест се елиминираат сите аномалии, се обезбедува логичка коректност и се добива шема погодна за имплементација во релациона датабаза. + +Финални табели: +||= USER =||= =||= =||= =|| +||= //user_id =||= //user_name =||= //email =||= //password =|| +|| 1 || james || james@fein.com || $2y$10$rcqLdIzMcYfmGFWCP3kix.JNTOzjIp0xVehMd11wzaanAXUDFLQMu || +|| 2 || anita || anita@fein.com || $2y$10$CsSsqA.FFhBR/TWCZCUWYOPxYA.HmGb7ULQPgJGhv3vQS2xRqluYm || +// +||= TRANSACTION_ACCOUNT =||= =||= =||= =|| +||= //transaction_account_id =||= //account_name =||= //balance =||= //user_id =|| +|| 1 || credit card || 5000 || 1 || +|| 2 || cash || 1200 || 1 || +|| 3 || debit card || 3200 || 2 || +// +||= TRANSACTION =||= =||= =||= =||= =|| +||= //transaction_id =||= //transaction_name =||= //amount =||= //net_amount =||= //date =|| +|| 1 || electricity bill || -1800 || -1800 || 18:03:25 Sep 26, 2025 || +|| 2 || burger || -800 || -200 || 12:43:00 Sep 26, 2025 || +|| 3 || nail polish || -200 || -200 || 19:31:32 Sep 12 2025 || +// +||= TAG =||= =|| +||= //tag_id =||= //tag_name =|| +|| 1 || bills || +|| 2 || food || +|| 3 || beauty || +// +||= TRANSACTION_BREAKDOWN =||= =||= =||= =||= =|| +||= //transaction_breakdown_id =||= //transaction_id =||= //transaction_account_id =||= //spent_amount =||= //earned_amount =|| +|| 1 || 1 || 1 || -1800 || 0 || +|| 2 || 2 || 2 || -800 || 600 || +|| 3 || 3 || 3 || -200 || 0 || +// +||= TAG_ASSIGNED_TO_TRANSACTION =||= =|| +||= //transaction_id =||= //tag_id =|| +|| 1 || 1 || +|| 2 || 2 || +|| 3 || 3 || + +== + +[wiki:NormalizationVer1 Верзија 1] +https://develop.finki.ukim.mk/projects/FEIN/wiki/NormalizationVer1 += Нормализација + +Ја дизајнирав базата на податоци со surrogate примарни клучеви(system-generated identifier) со една атрибутна колона (на пр. {{{user_id}}}, {{{transaction_id }}}) за едноставност и конзистентност. Вака, секој атрибут кој што не е клуч, директно зависи од примарниот клуч, што значи дека шемата ги исполнува 1NF, 2NF и 3NF/BCNF. + +Единствената намерна редундантност е колоната {{{net_amount}}} во {{{TRANSACTION}}}. Ја вклучив затоа што овозможува побрзи пребарувања и извештаи, иако е изведлива од {{{TRANSACTION_BREAKDOWN}}}. Ова беше свесен компромис заради перформанси. + +Кај many-to-many табелите ({{{TRANSACTION_BREAKDOWN}}} и {{{TAG_ASSIGNED_TO_TRANSACTION}}}) користам surrogate клучеви заради конзистентност на целата шема иако композитни клучеви би биле построго нормализирано решение, surrogate пристапот обезбедува унифицирана структура и јасност. + +=== Анализа по Табела +==== 1. USER + +USER (**__user_id__**, **user_name**, **email**, **password**) + +{{{user_id → user_name, email, password}}} + +- 1NF: Сите атрибути се атомски, нема повторувачки групи. +- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности. +- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. +Додадов UNIQUE ограничување на email затоа што е природен идентификатор. + +==== 2. TRANSACTION_ACCOUNT + +TRANSACTION_ACCOUNT (__**transaction_account_id**__, account_name, balance, user_id* (USER)) + +{{{transaction_account_id → account_name, balance, user_id}}} + +- 1NF: Сите атрибути се атомски, нема повторувачки групи. +- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности. +- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. + +==== 3. TRANSACTION + +TRANSACTION (__**transaction_id__**, transaction_name, **amount**, net_amount, **date**) + +{{{transaction_id → transaction_name, amount, net_amount, date}}} + +Атрибутот {{{net_amount}}} може да се изведе од {{{TRANSACTION_BREAKDOWN}}}. Го задржав намерно заради перформанси, за да избегнам постојано пресметување. За да нема неконзистентност, ќе се одржува преку triggers и апликациска логика. +- 1NF: Сите атрибути се атомски, нема повторувачки групи. +- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности. +- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч, со свесен компромис. + +==== 4. TRANSACTION_BREAKDOWN + +TRANSACTION_BREAKDOWN (__**transaction_breakdown_id__**, transaction_id* (TRANSACTION), transaction_account_id* (TRANSACTION_ACCOUNT), spent_amount, earned_amount) + +{{{transaction_breakdown_id → transaction_id, transaction_account_id, spent_amount, earned_amount}}} + +- 1NF: Сите атрибути се атомски, нема повторувачки групи. +- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности. +- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. +Го задржав surrogate примарниот клуч за унифициран пристап. + +==== 5. TAG + +TAG (__**tag_id__**, **tag_name**) + +{{{tag_id → tag_name}}} + +tag_name → tag_id бидејќи имињата на тагови се уникатни. + +- 1NF: Сите атрибути се атомски, нема повторувачки групи. +- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности. +- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. +Го дефинирав UNIQUE (tag_name) за да обезбедам интегритет. + +==== 6. TAG_ASSIGNED_TO_TRANSACTION + +TAG_ASSIGNED_TO_TRANSACTION(__**tag_assigned_to_transaction_id**__, **tag_id*** (TAG), **transaction_id*** (TRANSACTION)) + +{{{tag_assigned_to_transaction_id → tag_id, transaction_id}}} + +- 1NF: Сите атрибути се атомски, нема повторувачки групи. +- 2NF: Сите табели имаат едноатрибутни surrogate примарни клучеви → нема делумни зависности. +- 3NF/BCNF: Сите атрибути кои не се клучеви зависат само од примарниот клуч. +И овде користам surrogate клуч за конзистентност, но истовремено додадов UNIQUE(tag_id, transaction_id) за да спречам дупликати. + +**Шемата е во 3NF (практично BCNF) за сите табели. Единствениот свесен исклучок е {{{net_amount}}} во {{{TRANSACTION}}}, кој е додаден заради перформанси.** + +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedDatabaseDevelopment += Напреден развој на датабаза (Тригери, погледи) + +=== Тригери: +- [wiki:Trigger1 Автоматска пресметка на нето износ на трансакција] +- [wiki:Trigger2 Автоматско ажурирање на баланс на трансакциска сметка] +- [wiki:Trigger3 Контрола на баланс со дозволен лимит за тип на акаунт] +- [wiki:Trigger5 Автоматско бришење на трансакции без помошни(breakdown) записи] +- [wiki:Trigger6 Превенција на бришење на акаунти со историја] + +- [wiki:Trigger4 Автоматско поставување на датум на трансакција] //Тривијално-може да се постигне со поставување на `DEFAULT` вредност на атрибутот + +=== Погледи: +- [wiki:View1 Целсен преглед на трансакции] +- [wiki:View2 Преглед на трансакциски сметки со баланси] +- [wiki:View3 Месечно потрошено по корисник] +- [wiki:View4 Извештај по тагови] +- [wiki:View5 Извештај за дневно трошење] + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger1 += Автоматска пресметка на нето износ на трансакција + +==== Опис +Овој тригер служи за автоматска пресметка и ажурирање на нето износот (`net_amount`) на една трансакција врз основа на сите поврзани записи во табелата `transaction_breakdown` + +Нето износот се дефинира како `net_amount = SUM(earned_amount - spent_amount)` + +Тригерот се активира секогаш кога ќе се: +- додаде нов запис +- ажурира постоечки запис +- избрише запис +од табелата `transaction_breakdown` + +Со ова се обезбедува конзистентност на податоците, без потреба апликацијата рачно да ја пресметува вредноста + +==== Табели опфатени со тригерот +- transaction +- transaction_breakdown + +==== Тип на тригер +- AFTER INSERT +- AFTER UPDATE +- AFTER DELETE +Активиран над табелата `transaction_breakdown` + +==== SQL Код +{{{#!sql +CREATE OR REPLACE FUNCTION update_transaction_net_amount() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +BEGIN + UPDATE transaction t + SET net_amount = ( + SELECT COALESCE(SUM(tb.earned_amount - tb.spent_amount), 0) + FROM transaction_breakdown tb + WHERE tb.transaction_id = t.transaction_id + ) + WHERE t.transaction_id = COALESCE(NEW.transaction_id, OLD.transaction_id); + + RETURN NULL; +END; +$$; + + +CREATE TRIGGER trg_update_transaction_net_amount +AFTER INSERT OR UPDATE OR DELETE +ON transaction_breakdown +FOR EACH ROW +EXECUTE FUNCTION update_transaction_net_amount(); +}}} + +==== Објаснување на логиката + +При секоја промена во `transaction_breakdown`, тригерот: +- Го пронаоѓа поврзаниот `transaction_id` +- Ги сумира сите `earned_amount` и `spent_amount` +- Ја ажурира колоната `net_amount` во табелата `transaction` + +Се користи `COALESCE` за да се избегне `NULL` вредност кога нема breakdown записи + +==== Причина за користење +Овој тригер е неопходен бидејќи: +- `net_amount` е деривиран атрибут +- не смее да зависи од апликативна логика + +спречува: +- неконзистентни податоци +- човечки грешки +- дуплирање на бизнис логика + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger2 += Автоматско ажурирање на баланс на трансакциска сметка + +==== Опис +Овој тригер овозможува автоматско ажурирање на балансот (`balance`) на трансакциските сметки при секоја промена во табелата `transaction_breakdown` + +Секој запис во `transaction_breakdown` претставува: +- приход (`earned_amount`) +- трошење (`spent_amount`) + +Тригерот гарантира дека балансот на сметката секогаш ја рефлектира реалната состојба по секоја трансакција + +==== Табели опфатени со тригерот +- transaction_account +- transaction_breakdown + +==== Тип на тригер +- AFTER INSERT +- AFTER UPDATE +- AFTER DELETE +Активиран над табелата `transaction_breakdown` + +==== SQL Код +{{{#!sql +CREATE OR REPLACE FUNCTION update_transaction_account_balance() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +BEGIN + -- При бришење на запис (rollback на ефектот) + IF TG_OP = 'DELETE' THEN + UPDATE transaction_account + SET balance = balance - OLD.earned_amount + OLD.spent_amount + WHERE transaction_account_id = OLD.transaction_account_id; + + -- При додавање на нов запис + ELSIF TG_OP = 'INSERT' THEN + UPDATE transaction_account + SET balance = balance + NEW.earned_amount - NEW.spent_amount + WHERE transaction_account_id = NEW.transaction_account_id; + + -- При ажурирање на постоечки запис + ELSIF TG_OP = 'UPDATE' THEN + UPDATE transaction_account + SET balance = balance + - OLD.earned_amount + OLD.spent_amount + + NEW.earned_amount - NEW.spent_amount + WHERE transaction_account_id = NEW.transaction_account_id; + END IF; + + RETURN NULL; +END; +$$; + + +CREATE TRIGGER trg_update_transaction_account_balance +AFTER INSERT OR UPDATE OR DELETE +ON transaction_breakdown +FOR EACH ROW +EXECUTE FUNCTION update_transaction_account_balance(); +}}} + +==== Објаснување на логиката +`DELETE`: +- Се поништува ефектот од избришаниот запис + +`INSERT`: +- Приходите се додаваат на балансот +- Трошоците се одземаат од балансот + +`UPDATE`: +- Прво се поништува стариот ефект +- Потоа се применува новиот ефект + + +==== Причина за користење +Овој тригер е клучен затоа што: +- `balance` е динамичка и критична вредност +- рачно ажурирање е ризично +- апликацијата не смее да биде единствен извор на вистина +- базата ја контролира финансиската логика +- се елиминираат грешки при ажурирање и бришење + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger3 += Контрола на баланс со дозволен лимит за тип на акаунт + +==== Опис + +Овој тригер овозможува напредна валидација на трансакции со цел да се спречи надминување на дозволениот лимит на трансакциска сметка, при што: +- се поддржува дозволен лимит и наместо фиксна граница (0), тригерот користи параметаризиран лимит, кој се дефинира при креирање на тригерот +- се обработува edge case кога при `UPDATE` се менува трансакцискиот акаунт +- се применува различна логика во зависност од типот на акаунт +- типот на акаунтот не е експлицитен атрибут, туку се утврдува преку името на акаунтот, кое мора да го содржи типот (пример: Cash Wallet, Credit Card VISA, Forex EUR Account) + +Тригерот се извршува само ако името на акаунтот го содржи типот проследен како аргумент пред внесување или ажурирање на помошна трансакција + +Пример: +- кеш сметки → лимит 0 +- кредитни сметки → лимит -10000 + +==== Табели опфатени со тригерот +- `transaction_account` +- `transaction_breakdown` + +==== Тип на тригер +- **BEFORE INSERT +- **BEFORE UPDATE +Активиран над табелата `transaction_breakdown` + +==== Поддржани типови на акаунти +|| //Тип на акаунт || //Пример име || //Логика || +|| **CASH || Cash, Cash Wallet, Cash MKD || Не е дозволен негативен баланс || +|| **DEBIT || Debit Account || Не е дозволен негативен баланс || +|| **CREDIT || Credit Card VISA, Credit Card Gold || Дозволен негативен баланс до дефиниран лимит || +|| **FOREX || Forex EUR, FX USD || Дозволен негативен баланс до дефиниран лимит || + +==== Параметри на тригерот +|| //Параметар || //Опис || +|| **`TG_ARGV[0]` || Тип на акаунт (пример: cash, credit, debit, forex) || +|| **`TG_ARGV[1]` || Дозволен минимален баланс (лимит) || + +==== SQL код +{{{#!sql +CREATE OR REPLACE FUNCTION enforce_account_limit_by_name() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +DECLARE + allowed_type TEXT; + allowed_limit NUMERIC; + + old_balance NUMERIC; + new_balance NUMERIC; + + old_account_name TEXT; + new_account_name TEXT; +BEGIN + allowed_type := LOWER(TG_ARGV[0]); + allowed_limit := TG_ARGV[1]::NUMERIC; + + -- =============================== + -- INSERT + -- =============================== + IF TG_OP = 'INSERT' THEN + SELECT account_name, balance + INTO new_account_name, new_balance + FROM transaction_account + WHERE transaction_account_id = NEW.transaction_account_id; + + IF LOWER(new_account_name) LIKE '%' || allowed_type || '%' THEN + new_balance := new_balance + + COALESCE(NEW.earned_amount, 0) + - COALESCE(NEW.spent_amount, 0); + + IF new_balance < allowed_limit THEN + RAISE EXCEPTION + 'The limit for the % account has been exceeded! Balance: %, limit: %', + allowed_type, new_balance, allowed_limit; + END IF; + END IF; + + RETURN NEW; + END IF; + + -- =============================== + -- UPDATE + -- =============================== + IF TG_OP = 'UPDATE' THEN + + -- Читање на стар и нов акаунт + SELECT account_name, balance + INTO old_account_name, old_balance + FROM transaction_account + WHERE transaction_account_id = OLD.transaction_account_id; + + SELECT account_name, balance + INTO new_account_name, new_balance + FROM transaction_account + WHERE transaction_account_id = NEW.transaction_account_id; + + -- Проверка на стариот акаунт (rollback ефект) + IF LOWER(old_account_name) LIKE '%' || allowed_type || '%' THEN + old_balance := old_balance + - COALESCE(OLD.earned_amount, 0) + + COALESCE(OLD.spent_amount, 0); + + IF old_balance < allowed_limit THEN + RAISE EXCEPTION + 'The limit for the % account has been exceeded during rollback! Balance: %, limit: %', + allowed_type, old_balance, allowed_limit; + END IF; + END IF; + + -- Проверка на новиот акаунт (apply нов ефект) + IF LOWER(new_account_name) LIKE '%' || allowed_type || '%' THEN + new_balance := new_balance + + COALESCE(NEW.earned_amount, 0) + - COALESCE(NEW.spent_amount, 0); + + IF new_balance < allowed_limit THEN + RAISE EXCEPTION + 'The limit for the % account has been exceeded during updation! Balance: %, limit: %', + allowed_type, new_balance, allowed_limit; + END IF; + END IF; + + RETURN NEW; + END IF; + + RETURN NEW; +END; +$$; +}}} + +===== Креирање на тригерите: + +- Кеш акаунти (без дозволен минус) +{{{#!sql +CREATE TRIGGER trg_cash_account_limit +BEFORE INSERT OR UPDATE +ON transaction_breakdown +FOR EACH ROW +EXECUTE FUNCTION enforce_account_limit_by_name('cash', 0); +}}} + +- Кредитни акаунти (дозволен минус до -10000) +{{{#!sql +CREATE TRIGGER trg_credit_account_limit +BEFORE INSERT OR UPDATE +ON transaction_breakdown +FOR EACH ROW +EXECUTE FUNCTION enforce_account_limit_by_name('credit', -10000); +}}} + +- Дебитни акаунти (без дозволен минус) +{{{#!sql +CREATE TRIGGER trg_debit_account_limit +BEFORE INSERT OR UPDATE +ON transaction_breakdown +FOR EACH ROW +EXECUTE FUNCTION enforce_account_limit_by_name('debit', 0); +}}} + +- Forex акаунти (дозволен минус до -1000) +{{{#!sql +CREATE TRIGGER trg_forex_account_limit +BEFORE INSERT OR UPDATE +ON transaction_breakdown +FOR EACH ROW +EXECUTE FUNCTION enforce_account_limit_by_name('forex', -1000); +}}} + +==== Објаснување на логиката + +Типот на акаунтот се утврдува со пребарување во `account_name`, се користи `LIKE %type%` за флексибилност + +При `UPDATE`: +- се проверува стариот акаунт (rollback) +- потоа новиот акаунт (apply) + +Лимитот се применува само ако типот се совпаѓа + +Дозволениот лимит се проследува како аргумент на тригерот, функцијата ја чита вредноста преку `TG_ARGV`, балансот се пресметува пред реалното запишување, доколку се надмине лимитот → трансакцијата се блокира + +Овој пристап овозможува флексибилна и повторно употреблива логика + +==== Причина за користење +- Поддршка за различни типови сметки +- Централизирана контрола на бизнис логика +- Избегнување "hardcoded" вредности +- Лесна промена без измена на кодот + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger5 += Автоматско бришење на трансакции без помошни(breakdown) записи + +==== Опис +Овој тригер автоматски ја брише трансакцијата доколку по бришење на запис од `transaction_breakdown` повеќе не постојат поврзани `breakdown` записи за таа трансакција + +Со ова се обезбедува дека не постојат „празни“ трансакции кои немаат финансиско значење + +==== Табели опфатени со тригерот +- `transaction` +- `transaction_breakdown` + +==== Тип на тригер +- **AFTER DELETE +Активиран над табелата `transaction_breakdown` + +==== SQL код +{{{#!sql +CREATE OR REPLACE FUNCTION delete_empty_transaction() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +BEGIN + -- Проверка дали постојат други breakdown записи за истата трансакција + IF NOT EXISTS ( + SELECT 1 + FROM transaction_breakdown + WHERE transaction_id = OLD.transaction_id + ) THEN + DELETE FROM transaction + WHERE transaction_id = OLD.transaction_id; + END IF; + + RETURN NULL; +END; +$$; + + +CREATE TRIGGER trg_delete_empty_transaction +AFTER DELETE +ON transaction_breakdown +FOR EACH ROW +EXECUTE FUNCTION delete_empty_transaction(); +}}} + +==== Објаснување на логиката +По бришење на breakdown запис: +- се проверува дали трансакцијата има други поврзани записи +Доколку нема: + - трансакцијата се смета за невалидна + - автоматски се брише +Ова однесување ја моделира композицијата помеѓу `transaction` и `transaction_breakdown` + +==== Причина за користење + +Овој тригер е важен затоа што: +- трансакција без breakdown нема значење +- спречува акумулација на некорисни податоци +- ја одржува базата чиста и логична +Оваа логика не може секогаш безбедно да се остави на апликацијата + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger6 += Превенција на бришење на акаунти со историја + +==== Опис +Овој тригер спречува бришење на трансакциска сметка доколку таа има поврзани записи во табелата `transaction_breakdown` + +Со ова се заштитува финансиската историја и се спречува губење на важни податоци + +==== Табели опфатени со тригерот +- `transaction_account` +- `transaction_breakdown` + +==== Тип на тригер +- **BEFORE DELETE +Активиран над табелата `transaction_account` + +==== SQL код +{{{#!sql +CREATE OR REPLACE FUNCTION prevent_account_delete_with_history() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +BEGIN + IF EXISTS ( + SELECT 1 + FROM transaction_breakdown + WHERE transaction_account_id = OLD.transaction_account_id + ) THEN + RAISE EXCEPTION + 'The transaction account cannot be deleted because it contains history of transactions, delete them first!'; + END IF; + + RETURN OLD; +END; +$$; + + +CREATE TRIGGER trg_prevent_account_delete_with_history +BEFORE DELETE +ON transaction_account +FOR EACH ROW +EXECUTE FUNCTION prevent_account_delete_with_history(); +}}} + +==== Објаснување на логиката +Пред бришење на сметката: +- се проверува дали постојат поврзани breakdown записи +Доколку постојат: +- бришењето се прекинува +- се прикажува соодветна грешка +Ова обезбедува зачувување на историските податоци + +==== Причина за користење +Овој тригер е важен бидејќи: +- финансиските податоци не смеат да се губат +- бришење на сметка може да доведе до неконзистентни извештаи, сите извештаи остануваат точни и доверливи +- `ON DELETE CASCADE` не е соодветен за ваков случај: +`ON DELETE CASCADE` автоматски ги брише сите поврзани записи во зависните табели кога ќе се избрише родителскиот запис + +Иако ова е корисно во одредени сценарија, во финансиски системи како FEiN тоа претставува сериозен ризик за губење на финансиската историја, која никогаш не смее автоматски да се избрише + +Сметката можеби повеќе не е активна, но историјата останува, а `ON DELETE CASCADE` имплицира дека: +- ако сметката не постои → трансакциите не постоеле, што е бизнис логички неточно + +https://develop.finki.ukim.mk/projects/FEIN/wiki/Trigger4 += Автоматско поставување на датум на трансакција + +==== Опис +Овој тригер автоматски го поставува датумот на трансакцијата (`date`) доколку при внесување на трансакција истиот не е зададен, базата на податоци ќе го користи тековниот системски датум и време + +Со ова се обезбедува дека секоја трансакција има валиден временски запис + +==== Табели опфатени со тригерот +- `transaction` + +==== Тип на тригер +- **BEFORE INSERT +Активиран над табелата `transaction` + +==== SQL код +{{{#!sql +CREATE OR REPLACE FUNCTION set_transaction_date() +RETURNS TRIGGER +LANGUAGE plpgsql +AS $$ +BEGIN + IF NEW.date IS NULL THEN + NEW.date := NOW(); + END IF; + + RETURN NEW; +END; +$$; + + +CREATE TRIGGER trg_set_transaction_date +BEFORE INSERT +ON transaction +FOR EACH ROW +EXECUTE FUNCTION set_transaction_date(); +}}} + +==== Објаснување на логиката +Пред внесување на нова трансакција: +- се проверува дали колоната `date` е празна +- доколку е `NULL`, се поставува тековниот датум и време + +==== Причина за користење +Овој тригер е важен затоа што: +- овозможува стандардизирано однесување при внесување податоци +- времето е клучен атрибут за финансиски податоци +- апликацијата не секогаш е сигурен извор +- се елиминираат празни или невалидни датуми +Исто така: +- го поедноставува API-то +- ја намалува комплексноста на frontend-логиката + +https://develop.finki.ukim.mk/projects/FEIN/wiki/View1 += Целосен преглед на трансакции + +==== Опис +Овој поглед обезбедува целосен преглед на трансакциите, комбинирајќи податоци од повеќе табели во еден приказ + +Погледот е наменет за: +- прикажување трансакции во кориснички интерфејс +- извештаи +- аналитички пребарувања + +==== Табели опфатени со погледот +- `transaction` +- `transaction_breakdown` +- `transaction_account` +- `tag` +- `tag_assigned_to_transaction` + +==== SQL код +{{{#!sql +CREATE OR REPLACE VIEW vw_transaction_overview AS +SELECT + t.transaction_id, + t.transaction_name, + t.date, + t.net_amount, + + ta.transaction_account_id, + ta.account_name, + + STRING_AGG(DISTINCT tg.tag_name, ', ') AS tags + +FROM transaction t +JOIN transaction_breakdown tb + ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta + ON ta.transaction_account_id = tb.transaction_account_id +LEFT JOIN tag_assigned_to_transaction tat + ON tat.transaction_id = t.transaction_id +LEFT JOIN tag tg + ON tg.tag_id = tat.tag_id + +GROUP BY + t.transaction_id, + t.transaction_name, + t.date, + t.net_amount, + ta.transaction_account_id, + ta.account_name; +}}} + +==== Објаснување на логиката +- Се комбинираат основните податоци за трансакцијата +- Се прикажува сметката на која трансакцијата влијае +- Таговите се агрегираат во една текстуална колона со `STRING_AGG` +- `LEFT JOIN` се користи за тагови бидејќи тие се опционални + +==== Причина за користење +Овој поглед е неопходен бидејќи: +- ја централизира логиката за читање на трансакции +- ја намалува комплексноста во апликацијата +- обезбедува конзистентен формат за приказ на податоци +Без ваков поглед, секој извештај би морал повторно да ги имплементира истите `JOIN`-ови +https://develop.finki.ukim.mk/projects/FEIN/wiki/View2 += Преглед на трансакциски сметки со баланси + +==== Опис +Овој поглед обезбедува преглед на сите трансакциски сметки, заедно со нивниот тековен баланс и агрегирани финансиски податоци + +Погледот е наменет за: +- dashboard приказ +- брз преглед на финансиска состојба +- извештаи по сметка + +==== Табели опфатени со погледот +- `transaction_account` + +==== SQL код +{{{#!sql +CREATE OR REPLACE VIEW vw_transaction_account_summary AS +SELECT + transaction_account_id, + account_name, + balance +FROM transaction_account +GROUP BY + transaction_account_id, + account_name, + balance; +}}} + +==== Објаснување на логиката +Брз и краток поглед кој ги зема полињата од `transaction_account` + +==== Причина за користење +Овој поглед е важен затоа што: +- balance покажува моментална состојба +- сите релевантни податоци за сметка се достапни во една структура + +https://develop.finki.ukim.mk/projects/FEIN/wiki/View3 += Месечно потрошено по корисник + +==== Опис +Овој поглед обезбедува преглед на месечната потрошувачка за секој корисник, каде податоците се групираат по корисник и месец(придружен со година) + +Погледот е наменет за: +- месечни финансиски извештаи +- тренд анализи +- визуелизации (графици) + +==== Табели опфатени со погледот +- `transaction` +- `transaction_breakdown` +- `transaction_account` + +==== SQL код +{{{#!sql +CREATE OR REPLACE VIEW vw_monthly_spending_by_user AS +SELECT + ta.user_id, + EXTRACT(MONTH FROM t.date) AS month, + EXTRACT(YEAR FROM t.date) AS year, + + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + +FROM transaction t +JOIN transaction_breakdown tb + ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta + ON ta.transaction_account_id = tb.transaction_account_id + +GROUP BY + ta.user_id, + EXTRACT(YEAR FROM t.date), + EXTRACT(MONTH FROM t.date); +}}} + +==== Објаснување на логиката +- Се користи датумот на трансакцијата како временска референца +- Се агрегира само `spent_amount` +- Податоците се групираат по корисник и по месец +Ова овозможува јасен временски преглед на трошоците + +==== Причина за користење +Овој поглед е важен затоа што: +- месечните извештаи се честa потреба +- сложените агрегации се централизирани +- апликацијата не мора да имплементира временска логика + +https://develop.finki.ukim.mk/projects/FEIN/wiki/View4 += Извештај по тагови + +==== Опис +Овој поглед обезбедува преглед на потрошувачката по тагови, со што се овозможува категоризација и анализа на трошоците според нивната намена (храна, транспорт, сметки, забава и сл.) + +Погледот ги поврзува трансакциите со нивните тагови и ја сумира вкупната потрошувачка за секој таг + +==== Табели опфатени со погледот +- `tag` +- `tag_assigned_to_transaction` +- `transaction` +- `transaction_breakdown` + +==== SQL код +{{{#!sql +CREATE OR REPLACE VIEW vw_spending_by_tag AS +SELECT + tg.tag_id, + tg.tag_name, + + COALESCE(SUM(tb.spent_amount), 0) AS total_spent, + COUNT(DISTINCT t.transaction_id) AS transaction_count + +FROM tag tg +JOIN tag_assigned_to_transaction tat + ON tat.tag_id = tg.tag_id +JOIN transaction t + ON t.transaction_id = tat.transaction_id +JOIN transaction_breakdown tb + ON tb.transaction_id = t.transaction_id + +GROUP BY + tg.tag_id, + tg.tag_name; +}}} + +==== Објаснување на логиката +- Се земаат само трансакции кои имаат доделен таг +- `spent_amount` се агрегира по таг +- `COUNT(DISTINCT ...)` се користи за да се избегне дуплирање на трансакции + +==== Причина за користење +- категоризацијата е суштинска за лични финансии +- овозможува анализа на трошоци по намена +- ја елиминира потребата од сложени `JOIN` и `GROUP BY` барања + +https://develop.finki.ukim.mk/projects/FEIN/wiki/View5 += Извештај за дневно трошење + +==== Опис +Овој поглед обезбедува преглед на дневната потрошувачка за секој корисник, каде податоците се групираат по корисник и датум + +Погледот е наменет за: +- дневни извештаи +- анализа на навики +- пресметка на просечна потрошувачка + +==== Табели опфатени со view-от +- `transaction` +- `transaction_breakdown` +- `transaction_account` + +==== SQL код +{{{#!sql +CREATE OR REPLACE VIEW vw_daily_spending_by_user AS +SELECT + ta.user_id, + DATE(t.date) AS spending_date, + + COALESCE(SUM(tb.spent_amount), 0) AS total_spent + +FROM transaction t +JOIN transaction_breakdown tb + ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta + ON ta.transaction_account_id = tb.transaction_account_id + +GROUP BY + ta.user_id, + DATE(t.date); +}}} + +==== Објаснување на логиката +- Се користи само датумскиот дел од `timestamp` +- Се агрегира исклучиво `spent_amount` +- Групирањето по корисник овозможува персонализирани анализи + +==== Причина за користење +- дневната анализа дава најпрецизен увид во навиките +- е основа за пресметка на просеци и лимити +- ги поедноставува временските пресметки + +https://develop.finki.ukim.mk/projects/FEIN/wiki/AdvancedApplicationDevelopment += Напреден апликативен развој (Трансакции, Pooling) + +За да се разликуваат трансакциите во системот како ентитети и трансакциите како теоретски поим во областа на датабазите, првите ќе ги нарекувам "финансиски трансакции", а вторите "трансакции во датабаза" во понатамошните спомнувања + +=== Pooling на конекции +Го користам пакетот `postgres.js` (инстанциран во [source:/app/lib/db.ts]), кој автоматски воспоставува pooling. Без потреба од дополнителни конфигурации, овој систем одржува активни конекции(стандардно 10 паралелни конекции) што драстично ја намалува латенцијата помеѓу серверските акции во Next.js и операциите на базата на податоци. + +Овој систем е заштитен од дуплирање на конекции при hot-reload во развојна околина, со зачувување на инстанцата во `global` објектот. + +=== Трансакции во датабаза +Користам `sql.begin()` блокови за секоја операција што бара прво проверка или повеќекратен упис, елиминирајќи го ризикот од race conditions. + +'''Пример 1: Внесување на финансиска трансакција''' + +Сместена во [source:/app/(app)/add/actions.ts#L205:292], акцијата `addTransaction` ги извршува следните чекори како една атомична целина: + 1. Впишува основен запис во табелата `transaction`. + 2. Впишува релациони записи во `transaction_breakdown`. + 3. Го ажурира балансот на соодветната сметка во `transaction_account`. + 4. Селектира постојни или внесува нови тагови во `tag`. + 5. Гради поврзување во табелата `tag_assigned_to_transaction`. + +'''Пример 2: Валидација и манипулација врз основа на проверка''' + +За да се осигураме дека нема да се зачуваат дупликати поради истовремени кликови или барања(race conditions), се користи истата `sql.begin()` логика при креирање и промена на профили: +- '''Регистрација на профил:''' Во [source:/app/(auth)/actions.ts#L68:79] проверката дали внесениот `email` веќе постои се извршува во иста трансакција со креирањето на новиот профил. Доколку друга регистрација се обиде со истиот мејл во тој момент, трансакцијата се блокира. +- '''Ажурирање на профил:''' При промена на email во [source:/app/(app)/profile/actions.ts#L35:51], сите проверки за преклопување и самата `UPDATE` команда се спроведуват атомично. +- '''Додавање тагови:''' Во [source:app/(app)/add/actions.ts#L80:90], акцијата за додавање нов таг користи трансакција за првично да изврши `SELECT` и да провери дали постои таг со тоа име, пред да направи `INSERT`. + +'''Заштитен механизам:''' Ако било кој од чекорите во блоковите врати грешка(на пример: тагот веќе постои, балансот е невалиден или податоците се некомплетни), целиот `sql.begin` блок прави '''rollback''' и извршувањето се прекинува, што значи дека ниту еден делумен податок не останува трајно зачуван во базата. + +https://develop.finki.ukim.mk/projects/FEIN/wiki/OtherTopics += Останати теми: Безбедност, перформанси и одржување на базата + +=== 1. Безбедност на ниво на база +Имплементирани се следните безбедносни механизми директно при комуникацијата со базата, со што се намалува нападниот вектор независно од самиот NextJS сервер: + - '''Заштита од SQL вбризгување(SQL injection):''' Клиентската библиотека `postgres.js` користи параметризирани прашалници преку Tagged Template Literals(на пример `sql\`...\``) по дизајн. Ова спречува директно извршување на малициозен SQL код преку корисничките влезни параметри, бидејќи влезот е секогаш парсиран како податок, а не како команда. + - '''Енкриптирана комуникација(SSL/TLS):''' Како што е наведено во конфигурацискиот фајл([source:/app/lib/db.ts#L11]), конекцијата кон базата строго наметнува SSL сертификат преку параметарот `ssl: 'require'`. Ова ја штити транзицијата на чувствителните податоци и лозинки од "man-in-the-middle" напади. + - '''Безбедно поврзување со база:''' Конекциските стрингови никогаш не се чуваат во изворниот код. Тие се изолирани преку заштитени околински променливи (`POSTGRES_URL`). + +=== 2. Перформанси и оптимизација +Анализата е направена со `EXPLAIN (ANALYZE, BUFFERS)`, при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за `PRIMARY KEY` и `UNIQUE` ограничувања. Овие индекси не се третираат како дополнителна оптимизација, туку како дел од основната релациска структура на базата. + +Првично тестирање беше направено со мал број редови во табелите: + +[wiki:SmallDatabasePerformance Перформанси со мала датабаза] + +Во оваа состојба PostgreSQL користеше `Seq Scan` и по додавање на индексите. Тоа е очекувано однесување, бидејќи за табели со многу мал број редови целосното читање на табелата е поевтино од пристап преку индекс. + +Поради тоа, за пореална анализа беше генериран поголем сет на тест-податоци, при што податоците се логички поврзани преку постоечките релации во базата. + +По генерирање на тест-податоците, базата ја има следната состојба: + +||= Табела =||= Број на редови =|| +|| `user` || 103 || +|| `transaction` || 100012 || +|| `transaction_account` || 505 || +|| `transaction_breakdown` || 110011 || +|| `tag` || 31 || +|| `tag_assigned_to_transaction` || 125016 || + +Оваа количина на податоци е доволна за PostgreSQL optimizer-от да започне да прави различни планови за извршување и да користи дел од предложените индекси кога тие се поисплатливи од `Seq Scan`. + +==== Предложени дополнителни индекси ==== + +Следните индекси се предложени затоа што колоните често се користат во `JOIN` и `WHERE` услови во аналитичките извештаи: + +{{{#!sql +CREATE INDEX IF NOT EXISTS idx_transaction_date +ON "transaction"(date); + +CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id +ON transaction_account(user_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id +ON transaction_breakdown(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id +ON transaction_breakdown(transaction_account_id); + +CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id +ON tag_assigned_to_transaction(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id +ON tag_assigned_to_transaction(tag_id); +}}} + +Индексот `idx_transaction_date` е наменет за извештаи кои филтрираат трансакции според временски период. Индексот `idx_transaction_account_user_id` е наменет за извештаи кои ги ограничуваат резултатите на конкретен корисник. Индексите на `transaction_breakdown` и `tag_assigned_to_transaction` се наменети за побрзо поврзување на трансакциите со сметки и тагови. + +==== Сценарио 1: Трендови на трошење според тагови за корисник ==== + +'''Цел:''' +Овој извештај ја прикажува потрошувачката по тагови за конкретен корисник во последните шест месеци. Прашалникот е комплексен затоа што ги поврзува табелите `tag_assigned_to_transaction`, `transaction`, `transaction_breakdown`, `transaction_account` и `tag`, а дополнително врши филтрирање по корисник и датум, групирање по таг и месец, и сумирање на потрошената сума. + +'''Анализиран SQL:''' + +{{{#!sql +EXPLAIN (ANALYZE, BUFFERS) +SELECT +tg.tag_name, +DATE_TRUNC('month', t.date) AS month, +SUM(tb.spent_amount) AS total_spent +FROM tag_assigned_to_transaction tat +JOIN "transaction" t +ON tat.transaction_id = t.transaction_id +JOIN transaction_breakdown tb +ON t.transaction_id = tb.transaction_id +JOIN transaction_account ta +ON tb.transaction_account_id = ta.transaction_account_id +JOIN tag tg +ON tat.tag_id = tg.tag_id +WHERE +ta.user_id = 5 +AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS') +GROUP BY +tg.tag_name, month +ORDER BY +tg.tag_name, month; +}}} + +'''Релевантни индекси за ова сценарио:''' + +{{{#!sql +CREATE INDEX IF NOT EXISTS idx_transaction_date +ON "transaction"(date); + +CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id +ON transaction_account(user_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id +ON transaction_breakdown(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id +ON transaction_breakdown(transaction_account_id); + +CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id +ON tag_assigned_to_transaction(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id +ON tag_assigned_to_transaction(tag_id); +}}} + +'''Пред додавање на индексите:''' + +{{{#!sql +GroupAggregate (cost=4898.54..4900.99 rows=98 width=52) (actual time=51.593..51.622 rows=7 loops=1) +Group Key: tg.tag_name, (date_trunc('month'::text, t.date)) +Buffers: shared hit=4383 +-> Sort (cost=4898.54..4898.79 rows=98 width=26) (actual time=51.574..51.582 rows=81 loops=1) +Sort Key: tg.tag_name, (date_trunc('month'::text, t.date)) +Sort Method: quicksort Memory: 28kB +-> Hash Join (cost=2498.81..4895.30 rows=98 width=26) (actual time=28.248..51.522 rows=81 loops=1) +Hash Cond: (tat.tag_id = tg.tag_id) +-> Hash Join (cost=2497.11..4893.07 rows=98 width=18) (actual time=28.204..51.436 rows=81 loops=1) +Hash Cond: (tat.transaction_id = t.transaction_id) +-> Seq Scan on tag_assigned_to_transaction tat +(actual time=0.014..9.236 rows=125016 loops=1) +-> Hash +-> Nested Loop +-> Hash Join +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Seq Scan on transaction_breakdown tb +(actual time=0.012..8.429 rows=110011 loops=1) +-> Hash +-> Seq Scan on transaction_account ta +Filter: (user_id = 5) +Rows Removed by Filter: 500 +-> Index Scan using transaction_pkey on transaction t +Index Cond: (transaction_id = tb.transaction_id) +Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval))) +-> Hash +-> Seq Scan on tag tg +Planning Time: 1.392 ms +Execution Time: 51.744 ms +}}} + +'''По додавање на индексите:''' + +{{{#!sql +GroupAggregate (cost=2490.16..2492.59 rows=97 width=52) (actual time=6.936..6.963 rows=7 loops=1) +Group Key: tg.tag_name, (date_trunc('month'::text, t.date)) +Buffers: shared hit=3273 read=76 +-> Sort (cost=2490.16..2490.40 rows=97 width=26) (actual time=6.921..6.928 rows=81 loops=1) +Sort Key: tg.tag_name, (date_trunc('month'::text, t.date)) +Sort Method: quicksort Memory: 28kB +-> Nested Loop (cost=16.95..2486.96 rows=97 width=26) (actual time=0.155..6.835 rows=81 loops=1) +-> Nested Loop (cost=16.80..2480.67 rows=97 width=18) (actual time=0.135..6.624 rows=81 loops=1) +-> Nested Loop (cost=16.38..2443.39 rows=77 width=22) (actual time=0.104..5.104 rows=81 loops=1) +-> Nested Loop (cost=16.09..2050.95 rows=1089 width=10) (actual time=0.087..0.458 rows=1000 loops=1) +-> Bitmap Heap Scan on transaction_account ta +Recheck Cond: (user_id = 5) +-> Bitmap Index Scan on idx_transaction_account_user_id +Index Cond: (user_id = 5) +-> Bitmap Heap Scan on transaction_breakdown tb +Recheck Cond: (transaction_account_id = ta.transaction_account_id) +-> Bitmap Index Scan on idx_transaction_breakdown_account_id +Index Cond: (transaction_account_id = ta.transaction_account_id) +-> Index Scan using transaction_pkey on transaction t +Index Cond: (transaction_id = tb.transaction_id) +Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval))) +-> Index Scan using idx_tag_assigned_transaction_id on tag_assigned_to_transaction tat +Index Cond: (transaction_id = tb.transaction_id) +-> Memoize +Cache Key: tat.tag_id +-> Index Scan using tag_pkey on tag tg +Index Cond: (tag_id = tat.tag_id) +Planning Time: 1.738 ms +Execution Time: 7.055 ms +}}} + +'''Споредба:''' + +||= Метрика =||= Пред индекси =||= По индекси =|| +|| Planning Time || 1.392 ms || 1.738 ms || +|| Execution Time || 51.744 ms || 7.055 ms || +|| Подобрување на Execution Time || / || ~86.36% || +|| Забрзување || / || ~7.33x || +|| Тип на скенирање пред индекси || `Seq Scan` на големите табели || / || +|| Тип на скенирање по индекси || / || `Bitmap Index Scan`, `Bitmap Heap Scan`, `Index Scan` || +|| Дали новите индекси се користат || Не || Да || + +По додавање на индексите, PostgreSQL експлицитно ги користи: + +* `idx_transaction_account_user_id` преку `Bitmap Index Scan` +* `idx_transaction_breakdown_account_id` преку `Bitmap Index Scan` +* `idx_tag_assigned_transaction_id` преку `Index Scan` + +Индексот `idx_transaction_date` не е директно искористен во овој план, затоа што optimizer-от прво ги ограничува податоците преку корисничките сметки и breakdown записите, а потоа пристапува до трансакциите преку примарниот клуч `transaction_pkey`. + +'''Заклучок:''' + +Ова сценарио има најголемо подобрување. Времето на извршување се намали од 51.744 ms на 7.055 ms, што е приближно 86.36% подобрување, односно околу 7.33 пати побрзо извршување. Причината е што по додавање на индексите PostgreSQL повеќе не мора секвенцијално да ги чита `transaction_account`, `transaction_breakdown` и `tag_assigned_to_transaction`, туку директно ги лоцира релевантните записи преку индексите. Ова покажува дека индексите се корисни за извештаи кои филтрираат податоци за конкретен корисник и потоа ги поврзуваат трансакциите со тагови. + +==== Сценарио 2: Сметки со највисоко вкупно трошење во изминатата година ==== + +'''Цел:''' +Овој извештај ги прикажува сметките со најголема вкупна потрошувачка во последната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по датум, `SUM` агрегација, `GROUP BY`, `ORDER BY` и `LIMIT`. + +'''Анализиран SQL:''' + +{{{#!sql +EXPLAIN (ANALYZE, BUFFERS) +SELECT +ta.account_name, +COALESCE(SUM(tb.spent_amount), 0) AS total_spent +FROM transaction_breakdown tb +JOIN "transaction" t +ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta +ON tb.transaction_account_id = ta.transaction_account_id +WHERE +t.date >= NOW() - INTERVAL '1 YEAR' +GROUP BY +ta.account_name +ORDER BY +total_spent DESC +LIMIT 10; +}}} + +'''Релевантни индекси за ова сценарио:''' + +{{{#!sql +CREATE INDEX IF NOT EXISTS idx_transaction_date +ON "transaction"(date); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id +ON transaction_breakdown(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id +ON transaction_breakdown(transaction_account_id); +}}} + +'''Пред додавање на индексите:''' + +{{{#!sql +Limit (cost=4969.28..4969.31 rows=10 width=49) (actual time=73.712..73.717 rows=10 loops=1) +Buffers: shared hit=1540 +-> Sort (cost=4969.28..4970.54 rows=505 width=49) (actual time=73.711..73.714 rows=10 loops=1) +Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC +Sort Method: top-N heapsort Memory: 26kB +-> HashAggregate (cost=4952.06..4958.37 rows=505 width=49) (actual time=73.611..73.658 rows=113 loops=1) +Group Key: ta.account_name +-> Hash Join (cost=2756.94..4883.26 rows=13760 width=23) (actual time=34.771..69.224 rows=13568 loops=1) +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Hash Join (cost=2740.57..4830.47 rows=13760 width=10) (actual time=34.564..66.184 rows=13568 loops=1) +Hash Cond: (tb.transaction_id = t.transaction_id) +-> Seq Scan on transaction_breakdown tb +(actual time=0.012..8.780 rows=110011 loops=1) +-> Hash +-> Seq Scan on transaction t +Filter: (date >= (now() - '1 year'::interval)) +Rows Removed by Filter: 87672 +-> Hash +-> Seq Scan on transaction_account ta +Planning Time: 0.538 ms +Execution Time: 73.792 ms +}}} + +'''По додавање на индексите:''' + +{{{#!sql +Limit (cost=3675.91..3675.93 rows=10 width=49) (actual time=47.820..47.826 rows=10 loops=1) +Buffers: shared hit=1540 read=36 +-> Sort (cost=3675.91..3677.17 rows=505 width=49) (actual time=47.818..47.822 rows=10 loops=1) +Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC +Sort Method: top-N heapsort Memory: 26kB +-> HashAggregate (cost=3658.68..3665.00 rows=505 width=49) (actual time=47.717..47.765 rows=113 loops=1) +Group Key: ta.account_name +-> Hash Join (cost=1463.44..3589.80 rows=13776 width=23) (actual time=9.049..43.449 rows=13568 loops=1) +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Hash Join (cost=1447.08..3536.98 rows=13776 width=10) (actual time=8.845..40.394 rows=13568 loops=1) +Hash Cond: (tb.transaction_id = t.transaction_id) +-> Seq Scan on transaction_breakdown tb +(actual time=0.011..9.164 rows=110011 loops=1) +-> Hash +-> Bitmap Heap Scan on transaction t +Recheck Cond: (date >= (now() - '1 year'::interval)) +Heap Blocks: exact=834 +-> Bitmap Index Scan on idx_transaction_date +Index Cond: (date >= (now() - '1 year'::interval)) +-> Hash +-> Seq Scan on transaction_account ta +Planning Time: 0.654 ms +Execution Time: 47.902 ms +}}} + +'''Споредба:''' + +||= Метрика =||= Пред индекси =||= По индекси =|| +|| Planning Time || 0.538 ms || 0.654 ms || +|| Execution Time || 73.792 ms || 47.902 ms || +|| Подобрување на Execution Time || / || ~35.08% || +|| Забрзување || / || ~1.54x || +|| Тип на скенирање пред индекси || `Seq Scan` на `transaction` || / || +|| Тип на скенирање по индекси || / || `Bitmap Index Scan` + `Bitmap Heap Scan` на `transaction` || +|| Дали новите индекси се користат || Не || Да || + +По додавање на индексите, PostgreSQL го користи `idx_transaction_date` преку: + +{{{#!sql +Bitmap Index Scan on idx_transaction_date +Index Cond: (date >= (now() - '1 year'::interval)) +}}} + +Останатите табели, како `transaction_breakdown` и `transaction_account`, сè уште се читаат со `Seq Scan`, затоа што query-то пресметува агрегат за сите сметки, а не за еден конкретен корисник или една конкретна сметка. + +'''Заклучок:''' + +Времето на извршување се намали од 73.792 ms на 47.902 ms, што претставува приближно 35.08% подобрување. Ова подобрување е директно поврзано со индексот `idx_transaction_date`, бидејќи извештајот филтрира трансакции само од последната година. Пред индексот, PostgreSQL мораше да ја чита целата табела `transaction` и да отфрли 87672 редови преку filter. По додавање на индексот, PostgreSQL користи `Bitmap Index Scan` за побрзо да ги најде трансакциите од последната година. + +==== Сценарио 3: Годишни трендови на трансакции за корисник ==== + +'''Цел:''' +Овој извештај ја прикажува потрошувачката по квартали за конкретен корисник во тековната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по корисник и временски период, `SUM` агрегации со `CASE WHEN`, `GROUP BY` и `ORDER BY`. + +Во анализата условот за година е напишан како временски опсег, наместо само со `EXTRACT(YEAR FROM t.date)`, за B-Tree индексот на `date` да може поефикасно да се користи кога табелата ќе порасне. + +'''Анализиран SQL:''' + +{{{#!sql +EXPLAIN (ANALYZE, BUFFERS) +SELECT +ta.account_name, +SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent, +SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent, +SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent, +SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent +FROM transaction_breakdown tb +JOIN "transaction" t +ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta +ON tb.transaction_account_id = ta.transaction_account_id +WHERE +ta.user_id = 5 +AND t.date >= DATE_TRUNC('year', CURRENT_DATE) +AND t.date < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 YEAR' +GROUP BY +ta.account_name +ORDER BY +ta.account_name; +}}} + +'''Релевантни индекси за ова сценарио:''' + +{{{#!sql +CREATE INDEX IF NOT EXISTS idx_transaction_date +ON "transaction"(date); + +CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id +ON transaction_account(user_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id +ON transaction_breakdown(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id +ON transaction_breakdown(transaction_account_id); +}}} + +'''Пред додавање на индексите:''' + +{{{#!sql +GroupAggregate (cost=2511.77..2514.21 rows=5 width=145) (actual time=26.493..26.495 rows=1 loops=1) +Group Key: ta.account_name +Buffers: shared hit=3706 +-> Sort (cost=2511.77..2511.93 rows=67 width=31) (actual time=26.366..26.372 rows=70 loops=1) +Sort Key: ta.account_name +Sort Method: quicksort Memory: 28kB +-> Nested Loop (cost=11.67..2509.73 rows=67 width=31) (actual time=0.322..26.308 rows=70 loops=1) +-> Hash Join (cost=11.38..2103.69 rows=1089 width=27) (actual time=0.301..21.727 rows=1000 loops=1) +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Seq Scan on transaction_breakdown tb +(actual time=0.019..8.175 rows=110011 loops=1) +-> Hash +-> Seq Scan on transaction_account ta +Filter: (user_id = 5) +Rows Removed by Filter: 500 +-> Index Scan using transaction_pkey on transaction t +Index Cond: (transaction_id = tb.transaction_id) +Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone)) +AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval))) +Rows Removed by Filter: 1 +Planning Time: 0.495 ms +Execution Time: 26.571 ms +}}} + +'''По додавање на индексите:''' + +{{{#!sql +GroupAggregate (cost=2458.99..2461.40 rows=5 width=145) (actual time=5.086..5.088 rows=1 loops=1) +Group Key: ta.account_name +Buffers: shared hit=3023 +-> Sort (cost=2458.99..2459.16 rows=66 width=31) (actual time=4.954..4.960 rows=70 loops=1) +Sort Key: ta.account_name +Sort Method: quicksort Memory: 28kB +-> Nested Loop (cost=16.38..2457.00 rows=66 width=31) (actual time=0.104..4.917 rows=70 loops=1) +-> Nested Loop (cost=16.09..2050.95 rows=1089 width=27) (actual time=0.086..0.420 rows=1000 loops=1) +-> Bitmap Heap Scan on transaction_account ta +Recheck Cond: (user_id = 5) +-> Bitmap Index Scan on idx_transaction_account_user_id +Index Cond: (user_id = 5) +-> Bitmap Heap Scan on transaction_breakdown tb +Recheck Cond: (transaction_account_id = ta.transaction_account_id) +-> Bitmap Index Scan on idx_transaction_breakdown_account_id +Index Cond: (transaction_account_id = ta.transaction_account_id) +-> Index Scan using transaction_pkey on transaction t +Index Cond: (transaction_id = tb.transaction_id) +Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone)) +AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval))) +Rows Removed by Filter: 1 +Planning Time: 0.724 ms +Execution Time: 5.169 ms +}}} + +'''Споредба:''' + +||= Метрика =||= Пред индекси =||= По индекси =|| +|| Planning Time || 0.495 ms || 0.724 ms || +|| Execution Time || 26.571 ms || 5.169 ms || +|| Подобрување на Execution Time || / || ~80.55% || +|| Забрзување || / || ~5.14x || +|| Тип на скенирање пред индекси || `Seq Scan` на `transaction_breakdown` и `transaction_account` || / || +|| Тип на скенирање по индекси || / || `Bitmap Index Scan`, `Bitmap Heap Scan`, `Index Scan` || +|| Дали новите индекси се користат || Не || Да || + +По додавање на индексите, PostgreSQL ги користи: + +* `idx_transaction_account_user_id` преку `Bitmap Index Scan` +* `idx_transaction_breakdown_account_id` преку `Bitmap Index Scan` + +Индексот `idx_transaction_date` не е директно искористен во овој план. Причината е што optimizer-от прво ги ограничува податоците преку конкретниот `user_id` и неговите сметки, а потоа пристапува до трансакциите преку `transaction_pkey`. Во овој случај филтрирањето по корисник е поселективно од самото филтрирање по датум. + +'''Заклучок:''' + +Времето на извршување се намали од 26.571 ms на 5.169 ms, што претставува приближно 80.55% подобрување, односно околу 5.14 пати побрзо извршување. Ова подобрување е резултат на тоа што PostgreSQL повеќе не ја чита целата табела `transaction_breakdown` и целата табела `transaction_account`, туку директно ги наоѓа сметките на конкретниот корисник и breakdown записите поврзани со тие сметки. + +==== Финален заклучок од анализата ==== + +Првичната анализа со мал број податоци не покажа реална употреба на индексите, затоа што табелите беа премногу мали и PostgreSQL правилно избра `Seq Scan`. Поради тоа беше генериран поголем, логички поврзан сет на тест-податоци, кој содржи 100012 трансакции, 110011 breakdown записи и 125016 записи за доделени тагови. + +По зголемување на количината на податоци, индексите почнаа да имаат значајно влијание врз execution plan-от и времето на извршување. + +||= Сценарио =||= Execution Time пред индекси =||= Execution Time по индекси =||= Подобрување =||= Индекси искористени =|| +|| Трендови на трошење според тагови за корисник || 51.744 ms || 7.055 ms || ~86.36% || Да || +|| Сметки со највисоко трошење во изминатата година || 73.792 ms || 47.902 ms || ~35.08% || Да || +|| Годишни трендови на трансакции за корисник || 26.571 ms || 5.169 ms || ~80.55% || Да || + +Најголемо подобрување се забележува кај извештаите кои филтрираат по конкретен корисник, затоа што индексите `idx_transaction_account_user_id` и `idx_transaction_breakdown_account_id` овозможуваат PostgreSQL директно да ги најде релевантните сметки и breakdown записи, наместо да ги чита целите табели. + +Кај извештајот за сметки со највисоко трошење во последната година, најважен е индексот `idx_transaction_date`, затоа што условот по датум ја намалува количината на трансакции кои треба да се обработат. + +Заклучокот е дека предложените индекси се оправдани и корисни за FEiN, особено кога базата содржи поголем број трансакции. Тие значително го намалуваат времето на извршување на аналитичките извештаи и ја подобруваат скалабилноста на системот. + + + + + +=== 3. Интегритет и конзистентност +Со цел самата база да биде отпорна на грешки, имплементирани се стриктни ограничувања (`CONSTRAINTS`) и тригери кои ја заштитуваат финансиската историја на корисниците. + +{{{#!sql +ALTER TABLE "user" +ADD CONSTRAINT user_email_unique UNIQUE (email); +}}} + +- '''Надворешни клучеви:''' Релациите помеѓу табелите се реализирани преку надворешни клучеви (`FOREIGN KEY`). Со тоа се спречува внесување на записи кои референцираат непостоечки корисници, сметки, трансакции или тагови. +- '''Заштита на финансиска историја:''' За разлика од автоматско каскадно бришење, кај трансакциските сметки се користи тригер кој спречува бришење на сметка доколку таа има поврзани записи во `transaction_breakdown`. Ова е важно затоа што бришење на сметка со постоечка историја може да доведе до губење на финансиски податоци и неточни извештаи. +- '''Одржување на логичка конзистентност:''' Дополнително, системот користи тригер кој автоматски ја брише трансакцијата доколку по бришење на breakdown запис повеќе не постојат поврзани breakdown записи за таа трансакција. Со тоа се спречува постоење на „празни“ трансакции без финансиско значење. +- '''Ограничувања на уникатност:''' Корисничките мејлови се заштитени со `UNIQUE` ограничување, со што се гарантира дека во системот не можат да постојат два кориснички профили со иста емаил адреса. +- '''Типови на податоци за финансиски вредности:''' Сите монетарни вредности во системот се складираат со типот `NUMERIC(10,2)`. Овој пристап обезбедува фиксна прецизност до две децимали и ги елиминира грешките кои можат да настанат при користење на типови со подвижна запирка(`FLOAT` или `DOUBLE PRECISION`) во финансиски пресметки. + +=== 4. Одржување на базата +- '''Зачувување на структурните промени:''' Секоја промена на структурата на базата се документира во Trac документацијата и се имплементира директно во PostgreSQL преку SQL наредби. На тој начин документацијата останува усогласена со реалната имплементација на системот. +- '''Чување на SQL скриптите:''' Секоја SQL скрипта извршена после првата DDL скрипта ја чувам хронолошки, за во случај да треба базата да се иницијализира повторно, да ги извршам редоследно и да ја добијам истата состојба која сум ја имал. +- '''Одговорност за инфраструктурата:''' FEiN е дизајниран да работи врз PostgreSQL сервер обезбеден од надворешна инфраструктура. Конфигурацијата на серверот, резервните копии и механизмите за обновување на податоците се надвор од опсегот на самата апликација и зависат од околината во која е поставена базата на податоци. +https://develop.finki.ukim.mk/projects/FEIN/wiki/SmallDatabasePerformance +Анализата е направена со `EXPLAIN (ANALYZE, BUFFERS)`, при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за `PRIMARY KEY` и `UNIQUE` ограничувања. Овие индекси не се третираат како дополнителна оптимизација, туку како дел од основната релациска структура на базата. + +Во моментот на тестирањето, базата ја има следната количина на податоци: + +||= Табела =||= Број на редови =|| +|| `user` || 3 || +|| `transaction` || 12 || +|| `transaction_account` || 5 || +|| `transaction_breakdown` || 11 || +|| `tag` || 11 || +|| `tag_assigned_to_transaction` || 16 || + +Поради малата количина на податоци, очекувано е PostgreSQL во повеќе случаи да избере `Seq Scan`, бидејќи целосното читање на мала табела е поевтино од пристап преку индекс. Затоа, во анализата не се гледа само времето на извршување, туку и дали индексот навистина се користи во execution plan-от. + +==== Предложени дополнителни индекси ==== + +Следните индекси се предложени затоа што колоните често се користат во `JOIN` и `WHERE` услови во аналитичките извештаи: + +{{{#!sql +CREATE INDEX IF NOT EXISTS idx_transaction_date +ON "transaction"(date); + +CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id +ON transaction_account(user_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id +ON transaction_breakdown(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id +ON transaction_breakdown(transaction_account_id); + +CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id +ON tag_assigned_to_transaction(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id +ON tag_assigned_to_transaction(tag_id); +}}} + +Индексот `idx_transaction_date` е наменет за извештаи кои филтрираат трансакции според временски период. Индексот `idx_transaction_account_user_id` е наменет за извештаи кои ги ограничуваат резултатите на конкретен корисник. Индексите на `transaction_breakdown` и `tag_assigned_to_transaction` се наменети за побрзо поврзување на трансакциите со сметки и тагови. + +==== Сценарио 1: Трендови на трошење според тагови за корисник ==== + +'''Цел:''' +Овој извештај ја прикажува потрошувачката по тагови за конкретен корисник во последните шест месеци. Прашалникот е комплексен затоа што ги поврзува табелите `tag_assigned_to_transaction`, `transaction`, `transaction_breakdown`, `transaction_account` и `tag`, а дополнително врши филтрирање по корисник и датум, групирање по таг и месец, и сумирање на потрошената сума. + +'''Анализиран SQL:''' + +{{{#!sql +EXPLAIN (ANALYZE, BUFFERS) +SELECT +tg.tag_name, +DATE_TRUNC('month', t.date) AS month, +SUM(tb.spent_amount) AS total_spent +FROM tag_assigned_to_transaction tat +JOIN "transaction" t +ON tat.transaction_id = t.transaction_id +JOIN transaction_breakdown tb +ON t.transaction_id = tb.transaction_id +JOIN transaction_account ta +ON tb.transaction_account_id = ta.transaction_account_id +JOIN tag tg +ON tat.tag_id = tg.tag_id +WHERE +ta.user_id = 1 +AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS') +GROUP BY +tg.tag_name, month +ORDER BY +tg.tag_name, month; +}}} + +'''Релевантни индекси за ова сценарио:''' + +{{{#!sql +CREATE INDEX IF NOT EXISTS idx_transaction_date +ON "transaction"(date); + +CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id +ON transaction_account(user_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id +ON transaction_breakdown(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id +ON transaction_breakdown(transaction_account_id); + +CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id +ON tag_assigned_to_transaction(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id +ON tag_assigned_to_transaction(tag_id); +}}} + +'''План пред додавање на индексите:''' + +{{{#!sql +GroupAggregate (cost=6.54..6.77 rows=9 width=53) (actual time=0.176..0.189 rows=7 loops=1) +Group Key: tg.tag_name, (date_trunc('month'::text, t.date)) +Buffers: shared hit=5 +-> Sort (cost=6.54..6.57 rows=9 width=25) (actual time=0.162..0.167 rows=16 loops=1) +Sort Key: tg.tag_name, (date_trunc('month'::text, t.date)) +Sort Method: quicksort Memory: 25kB +-> Hash Join (cost=4.99..6.40 rows=9 width=25) (actual time=0.119..0.142 rows=16 loops=1) +Hash Cond: (tat.tag_id = tg.tag_id) +-> Hash Join (cost=3.74..5.10 rows=9 width=16) (actual time=0.086..0.099 rows=16 loops=1) +Hash Cond: (tat.transaction_id = t.transaction_id) +-> Hash Join (cost=2.35..3.67 rows=10 width=16) (actual time=0.057..0.066 rows=16 loops=1) +Hash Cond: (tat.transaction_id = tb.transaction_id) +-> Seq Scan on tag_assigned_to_transaction tat +-> Hash +-> Hash Join +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Seq Scan on transaction_breakdown tb +-> Hash +-> Seq Scan on transaction_account ta +Filter: (user_id = 1) +Rows Removed by Filter: 2 +-> Hash +-> Seq Scan on transaction t +Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval))) +-> Hash +-> Seq Scan on tag tg +Planning Time: 0.733 ms +Execution Time: 0.296 ms +}}} + +'''План по додавање на индексите:''' + +{{{#!sql +GroupAggregate (cost=6.54..6.77 rows=9 width=53) (actual time=0.154..0.166 rows=7 loops=1) +Group Key: tg.tag_name, (date_trunc('month'::text, t.date)) +Buffers: shared hit=5 +-> Sort (cost=6.54..6.57 rows=9 width=25) (actual time=0.142..0.146 rows=16 loops=1) +Sort Key: tg.tag_name, (date_trunc('month'::text, t.date)) +Sort Method: quicksort Memory: 25kB +-> Hash Join (cost=4.99..6.40 rows=9 width=25) (actual time=0.100..0.122 rows=16 loops=1) +Hash Cond: (tat.tag_id = tg.tag_id) +-> Hash Join (cost=3.74..5.10 rows=9 width=16) (actual time=0.068..0.081 rows=16 loops=1) +Hash Cond: (tat.transaction_id = t.transaction_id) +-> Hash Join (cost=2.35..3.67 rows=10 width=16) (actual time=0.040..0.049 rows=16 loops=1) +Hash Cond: (tat.transaction_id = tb.transaction_id) +-> Seq Scan on tag_assigned_to_transaction tat +-> Hash +-> Hash Join +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Seq Scan on transaction_breakdown tb +-> Hash +-> Seq Scan on transaction_account ta +Filter: (user_id = 1) +Rows Removed by Filter: 2 +-> Hash +-> Seq Scan on transaction t +Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval))) +-> Hash +-> Seq Scan on tag tg +Planning Time: 0.921 ms +Execution Time: 0.242 ms +}}} + +'''Споредба:''' + +||= Метрика =||= Пред индекси =||= По индекси =|| +|| Planning Time || 0.733 ms || 0.921 ms || +|| Execution Time || 0.296 ms || 0.242 ms || +|| Тип на скенирање || `Seq Scan` || `Seq Scan` || +|| Дали новите индекси се користат || Не || Не || + +'''Заклучок:''' +Времето на извршување се намали од 0.296 ms на 0.242 ms, што претставува намалување од приближно 18.2%. Сепак, execution plan-от и по додавање на индексите користи `Seq Scan` над сите релевантни табели, а не `Index Scan` или `Bitmap Index Scan`. Затоа подобрувањето не може да се припише директно на индексите. Најверојатната причина е малата количина на податоци и фактот што сите блокови се веќе во shared buffers. Индексите остануваат логични за идно зголемување на бројот на трансакции, тагови и breakdown записи. + +==== Сценарио 2: Сметки со највисоко вкупно трошење во изминатата година ==== + +'''Цел:''' +Овој извештај ги прикажува сметките со најголема вкупна потрошувачка во последната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по датум, `SUM` агрегација, `GROUP BY`, `ORDER BY` и `LIMIT`. + +'''Анализиран SQL:''' + +{{{#!sql +EXPLAIN (ANALYZE, BUFFERS) +SELECT +ta.account_name, +COALESCE(SUM(tb.spent_amount), 0) AS total_spent +FROM transaction_breakdown tb +JOIN "transaction" t +ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta +ON tb.transaction_account_id = ta.transaction_account_id +WHERE +t.date >= NOW() - INTERVAL '1 YEAR' +GROUP BY +ta.account_name +ORDER BY +total_spent DESC +LIMIT 10; +}}} + +'''Релевантни индекси за ова сценарио:''' + +{{{#!sql +CREATE INDEX IF NOT EXISTS idx_transaction_date +ON "transaction"(date); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id +ON transaction_breakdown(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id +ON transaction_breakdown(transaction_account_id); +}}} + +'''План пред додавање на индексите:''' + +{{{#!sql +Limit (cost=3.85..3.86 rows=5 width=40) (actual time=0.110..0.114 rows=3 loops=1) +Buffers: shared hit=3 +-> Sort (cost=3.85..3.86 rows=5 width=40) (actual time=0.108..0.111 rows=3 loops=1) +Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC +Sort Method: quicksort Memory: 25kB +-> HashAggregate (cost=3.73..3.79 rows=5 width=40) (actual time=0.097..0.101 rows=3 loops=1) +Group Key: ta.account_name +-> Hash Join (cost=2.47..3.67 rows=11 width=12) (actual time=0.059..0.069 rows=11 loops=1) +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Hash Join (cost=1.36..2.51 rows=11 width=8) (actual time=0.032..0.038 rows=11 loops=1) +Hash Cond: (tb.transaction_id = t.transaction_id) +-> Seq Scan on transaction_breakdown tb +-> Hash +-> Seq Scan on transaction t +Filter: (date >= (now() - '1 year'::interval)) +-> Hash +-> Seq Scan on transaction_account ta +Planning Time: 0.403 ms +Execution Time: 0.191 ms +}}} + +'''План по додавање на индексите:''' + +{{{#!sql +Limit (cost=3.85..3.86 rows=5 width=40) (actual time=0.100..0.104 rows=3 loops=1) +Buffers: shared hit=3 +-> Sort (cost=3.85..3.86 rows=5 width=40) (actual time=0.098..0.101 rows=3 loops=1) +Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC +Sort Method: quicksort Memory: 25kB +-> HashAggregate (cost=3.73..3.79 rows=5 width=40) (actual time=0.086..0.090 rows=3 loops=1) +Group Key: ta.account_name +-> Hash Join (cost=2.47..3.67 rows=11 width=12) (actual time=0.060..0.070 rows=11 loops=1) +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Hash Join (cost=1.36..2.51 rows=11 width=8) (actual time=0.030..0.036 rows=11 loops=1) +Hash Cond: (tb.transaction_id = t.transaction_id) +-> Seq Scan on transaction_breakdown tb +-> Hash +-> Seq Scan on transaction t +Filter: (date >= (now() - '1 year'::interval)) +-> Hash +-> Seq Scan on transaction_account ta +Planning Time: 0.451 ms +Execution Time: 0.166 ms +}}} + +'''Споредба:''' + +||= Метрика =||= Пред индекси =||= По индекси =|| +|| Planning Time || 0.403 ms || 0.451 ms || +|| Execution Time || 0.191 ms || 0.166 ms || +|| Тип на скенирање || `Seq Scan` || `Seq Scan` || +|| Дали новите индекси се користат || Не || Не || + +'''Заклучок:''' +Времето на извршување се намали од 0.191 ms на 0.166 ms, што претставува намалување од приближно 13.1%. Сепак, планот за извршување не користи ниту еден од новите индекси. PostgreSQL продолжува со `Seq Scan` на `transaction`, `transaction_breakdown` и `transaction_account`, затоа што табелите имаат многу мал број редови и читањето на целата табела е поевтино од пристап преку индекс. Поради тоа, малото подобрување не се смета како директен ефект од индексите. Индексот на `transaction.date` сепак е оправдан за идно зголемување на бројот на трансакции, бидејќи извештајот филтрира според временски период. + +==== Сценарио 3: Годишни трендови на трансакции за корисник ==== + +'''Цел:''' +Овој извештај ја прикажува потрошувачката по квартали за конкретен корисник во тековната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по корисник и временски период, `SUM` агрегации со `CASE WHEN`, `GROUP BY` и `ORDER BY`. + +Во анализата условот за година е напишан како временски опсег, наместо само со `EXTRACT(YEAR FROM t.date)`, за B-Tree индексот на `date` да може поефикасно да се користи кога табелата ќе порасне. + +'''Анализиран SQL:''' + +{{{#!sql +EXPLAIN (ANALYZE, BUFFERS) +SELECT +ta.account_name, +SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent, +SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent, +SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent, +SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent +FROM transaction_breakdown tb +JOIN "transaction" t +ON tb.transaction_id = t.transaction_id +JOIN transaction_account ta +ON tb.transaction_account_id = ta.transaction_account_id +WHERE +ta.user_id = 1 +AND t.date >= DATE_TRUNC('year', CURRENT_DATE) +AND t.date < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 YEAR' +GROUP BY +ta.account_name +ORDER BY +ta.account_name; +}}} + +'''Релевантни индекси за ова сценарио:''' + +{{{#!sql +CREATE INDEX IF NOT EXISTS idx_transaction_date +ON "transaction"(date); + +CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id +ON transaction_account(user_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id +ON transaction_breakdown(transaction_id); + +CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id +ON transaction_breakdown(transaction_account_id); +}}} + +'''План пред додавање на индексите:''' + +{{{#!sql +Sort (cost=4.16..4.17 rows=3 width=136) (actual time=0.141..0.144 rows=3 loops=1) +Sort Key: ta.account_name +Sort Method: quicksort Memory: 25kB +Buffers: shared hit=3 +-> HashAggregate (cost=4.08..4.14 rows=3 width=136) (actual time=0.126..0.131 rows=3 loops=1) +Group Key: ta.account_name +-> Hash Join (cost=2.35..3.85 rows=7 width=20) (actual time=0.070..0.089 rows=11 loops=1) +Hash Cond: (t.transaction_id = tb.transaction_id) +-> Seq Scan on transaction t +Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone)) +AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval))) +-> Hash +-> Hash Join +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Seq Scan on transaction_breakdown tb +-> Hash +-> Seq Scan on transaction_account ta +Filter: (user_id = 1) +Rows Removed by Filter: 2 +Planning Time: 0.402 ms +Execution Time: 0.242 ms +}}} + +'''План по додавање на индексите:''' + +{{{#!sql +Sort (cost=4.16..4.17 rows=3 width=136) (actual time=0.131..0.135 rows=3 loops=1) +Sort Key: ta.account_name +Sort Method: quicksort Memory: 25kB +Buffers: shared hit=3 +-> HashAggregate (cost=4.08..4.14 rows=3 width=136) (actual time=0.117..0.122 rows=3 loops=1) +Group Key: ta.account_name +-> Hash Join (cost=2.35..3.85 rows=7 width=20) (actual time=0.063..0.082 rows=11 loops=1) +Hash Cond: (t.transaction_id = tb.transaction_id) +-> Seq Scan on transaction t +Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone)) +AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval))) +-> Hash +-> Hash Join +Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) +-> Seq Scan on transaction_breakdown tb +-> Hash +-> Seq Scan on transaction_account ta +Filter: (user_id = 1) +Rows Removed by Filter: 2 +Planning Time: 0.500 ms +Execution Time: 0.212 ms +}}} + +'''Споредба:''' + +||= Метрика =||= Пред индекси =||= По индекси =|| +|| Planning Time || 0.402 ms || 0.500 ms || +|| Execution Time || 0.242 ms || 0.212 ms || +|| Тип на скенирање || `Seq Scan` || `Seq Scan` || +|| Дали новите индекси се користат || Не || Не || + +'''Заклучок:''' +Времето на извршување се намали од 0.242 ms на 0.212 ms, што претставува намалување од приближно 12.4%. Сепак, планот останува ист во однос на пристапот до табелите: PostgreSQL користи `Seq Scan`, а не новите индекси. Причината е малата количина на податоци: `transaction` има 12 редови, `transaction_breakdown` има 11 редови, а `transaction_account` има 5 редови. За ваква големина, секвенцијалното читање е поевтино од пристап преку индекс. Индексите сепак се соодветни за идно зголемување на базата, особено затоа што извештајот филтрира по `user_id` и по временски период. + +==== Финален заклучок од анализата ==== + +Во сите три сценарија, PostgreSQL продолжи да користи `Seq Scan` и по додавање на предложените индекси. Тоа значи дека индексите не беа реално искористени во моменталниот execution plan. + +Ова однесување е очекувано поради малата количина на податоци во тест базата. Во моментот на тестирањето, најголемите релевантни табели имаат само 12 трансакции, 11 breakdown записи и 16 tag-assignment записи. За вакви табели, PostgreSQL правилно проценува дека целосно скенирање на табелата е поевтино од користење индекс. + +Иако execution time се намали во сите три сценарија, тоа не може директно да се припише на индексите, бидејќи execution plan-от не покажува `Index Scan`, `Bitmap Index Scan` или `Index Only Scan`. Малите разлики во времето најверојатно се резултат на кеширање, повторно користење на податоци во shared buffers и нормална варијација при извршување. + +Сепак, предложените индекси остануваат оправдани како подготовка за поголема количина на податоци. Со раст на бројот на корисници, трансакции, сметки, тагови и breakdown записи, индексите на `transaction.date`, `transaction_account.user_id`, `transaction_breakdown.transaction_id`, `transaction_breakdown.transaction_account_id`, `tag_assigned_to_transaction.transaction_id` и `tag_assigned_to_transaction.tag_id` ќе овозможат поефикасно филтрирање и поврзување на податоците во аналитичките извештаи. + + +