Changes between Version 3 and Version 4 of WikiStart/Optimization


Ignore:
Timestamp:
04/01/25 21:44:39 (3 days ago)
Author:
203206
Comment:

db2

Legend:

Unmodified
Added
Removed
Modified

  • WikiStart/Optimization

    v3 v4  
    11== Оптимизација
    22
    3 === Оптимизиран SQL-код, кој работи поедноставно и побрзо
    4 
    5 {{{
    6 create table account(
    7     id serial primary key,
    8     username varchar(255) not null,
    9     email varchar(255) not null,
    10     password varchar(255) not null,
    11     balance decimal(15, 2) not null
    12 );
    13 
    14 create table "transaction"(
    15     id serial primary key,
    16     amount decimal(15, 2) not null,
    17     currency varchar(255) not null,
    18     type varchar(50) not null,
    19     timestamp timestamp default current_timestamp,
    20     account_id int not null,
    21     foreign key (account_id) references account(id)
    22 );
    23 
    24 create table transfer(
    25     id serial primary key,
    26     s_id int not null,
    27     r_id int not null,
    28     foreign key(s_id) references account(id),
    29     foreign key(r_id) references
    30     account(id)
    31 
    32 );
    33 
    34 create table deposit(
    35     id serial primary key,
    36     account_id int not null,
    37     amount decimal(15,2) not null,
    38     timestamp timestamp default current_timestamp,
    39     foreign key(account_id) references account(id)
    40 );
    41 
    42 create table withdraw(
    43     id serial primary key,
    44     account_id int not null,
    45     amount decimal(15,2) not null,
    46     timestamp timestamp default current_timestamp,
    47     foreign key(account_id) references account(id)
    48 );
    49 
    50 create table transferred_money(
    51     id serial primary key,
    52     s_account_id int not null,
    53     r_account_id int not null,
    54     t_id int not null,
    55     currency varchar(50) not null,
    56     foreign key (s_account_id) references account(id),
    57     foreign key (r_account_id) references account(id),
    58     foreign key (t_id) references transaction(id)
    59 );
    60 
    61 create or replace function after_transaction_trigger()
    62 returns trigger as $$
    63 begin
    64 
    65         if new.type = 'Deposit' then
    66                 insert into Deposit(account_id,amount, timestamp)
    67                 values (new.account_id, new.amount, new.timestamp);
    68        
    69         elsif new.type = 'Withdrawal' then
    70                 insert into Withdraw(account_id,amount, timestamp)
    71                 values (new.account_id, new.amount, new.timestamp);
    72        
    73         elsif new.type like '(MKD) Transfer Out to%' then
    74                 insert into transferred_money(s_account_id, r_account_id, t_id, currency)
    75                 select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
    76                         new.id, new.currency;
    77 
    78         elsif new.type like '(USD) Transfer Out to%' then
    79                 insert into transferred_money(s_account_id, r_account_id, t_id, currency)
    80                 select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
    81                         new.id, new.currency;
    82        
    83         elsif new.type like '(EUR) Transfer Out to%' then
    84                 insert into transferred_money(s_account_id, r_account_id, t_id, currency)
    85                 select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
    86                         new.id, new.currency;
    87                        
    88         elsif new.type like '(GBP) Transfer Out to%' then
    89                 insert into transferred_money(s_account_id, r_account_id, t_id, currency)
    90                 select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
    91                         new.id, new.currency;
    92 
    93         end if;
    94         return new;
    95 end;
    96 $$ language plpgsql;
    97 
    98 create trigger after_transaction
    99 after insert on transaction
    100 for each row
    101 execute function after_transaction_trigger();
    102 
    103 
    104 CREATE OR REPLACE FUNCTION after_transaction_trigger2()
    105 RETURNS TRIGGER AS $$
    106 BEGIN
    107     IF NEW.type = 'Deposit' THEN
    108         -- Log deposit as a self-transfer
    109         INSERT INTO transfer(s_id, r_id)
    110         VALUES (NEW.account_id, NEW.account_id);
    111 
    112     ELSIF NEW.type = 'Withdrawal' THEN
    113         -- Log withdrawal as a self-transfer
    114         INSERT INTO transfer(s_id, r_id)
    115         VALUES (NEW.account_id, NEW.account_id);
    116 
    117     ELSIF NEW.type LIKE '(MKD) Transfer Out to%' OR
    118           NEW.type LIKE '(USD) Transfer Out to%' OR
    119           NEW.type LIKE '(EUR) Transfer Out to%' OR
    120           NEW.type LIKE '(GBP) Transfer Out to%' THEN
    121          
    122         -- Extract recipient username and insert into transfer table
    123         INSERT INTO transfer(s_id, r_id)
    124         SELECT NEW.account_id,
    125                (SELECT id FROM account
    126                 WHERE username = TRIM(SUBSTRING(NEW.type FROM POSITION('Transfer Out to ' IN NEW.type) + 15))
    127                 LIMIT 1);
    128 
    129     END IF;
    130    
    131     RETURN NEW;
    132 END;
    133 $$ LANGUAGE plpgsql;
    134 
    135 CREATE TRIGGER after_transaction2
    136 AFTER INSERT ON transaction
    137 FOR EACH ROW
    138 EXECUTE FUNCTION after_transaction_trigger2();
    139 
    140 
    141 
    142 select * from account;
    143 select * from "transaction";
    144 select * from transfer;
    145 select * from transferred_money;
    146 select * from deposit;
    147 select * from withdraw;
    148 
    149 SELECT
    150     tm.s_account_id AS sender_id,
    151         a_s.username AS sender_username,
    152         tm.r_account_id AS receiver_id,
    153         a_r.username AS receiver_username,
    154     tm.t_id,
    155         t.timestamp,
    156         t.amount
    157    
    158 FROM
    159     transferred_money tm
    160 JOIN
    161     account a_s ON tm.s_account_id = a_s.id
    162 JOIN
    163     account a_r ON tm.r_account_id = a_r.id
    164 JOIN
    165     "transaction" t ON tm.t_id = t.id;
    166 
    167 
    168 SELECT
    169     tm.s_account_id AS sender_id,
    170         a_s.username AS sender_username,
    171         tm.r_account_id AS receiver_id,
    172         a_r.username AS receiver_username,
    173     tm.t_id,
    174         t.timestamp,
    175         t.amount
    176    
    177 FROM
    178     transferred_money tm
    179 JOIN
    180     account a_s ON tm.s_account_id = a_s.id
    181 JOIN
    182     account a_r ON tm.r_account_id = a_r.id
    183 JOIN
    184     "transaction" t ON tm.t_id = t.id
    185 WHERE
    186       timestamp<'2025-03-23 20:30:00.000000';
    187 
    188 SELECT
    189     tm.s_account_id AS sender_id,
    190         a_s.username AS sender_username,
    191         tm.r_account_id AS receiver_id,
    192         a_r.username AS receiver_username,
    193     tm.t_id,
    194         t.timestamp,
    195         t.amount,
    196         tm.currency
    197    
    198 FROM
    199     transferred_money tm
    200 JOIN
    201     account a_s ON tm.s_account_id = a_s.id
    202 JOIN
    203     account a_r ON tm.r_account_id = a_r.id
    204 JOIN
    205     "transaction" t ON tm.t_id = t.id
    206 WHERE tm.currency='(EUR)'
    207 ORDER BY timestamp;
    208 
    209 SELECT
    210         max(t.amount) as maximum,
    211         avg(t.amount) as "average",
    212         min(t.amount) as minimum
    213    
    214 FROM
    215     transferred_money tm
    216 JOIN
    217     account a_s ON tm.s_account_id = a_s.id
    218 JOIN
    219     account a_r ON tm.r_account_id = a_r.id
    220 JOIN
    221     "transaction" t ON tm.t_id = t.id
    222 WHERE tm.currency='(USD)';
    223 
    224 
    225 
    226 
    227 
    228 
    229 
    230 
    231 
    232 
    233 
    234 }}}
     3[wiki:prva_verzija верзија 1]