All geek questions in one place

Using CTE for recursive queries

I am trying to apply CTE and recursive queries. Database - MariaDB 10.2 or higher.

Business rules are as follows:

  • An account can be a holding or a portfolio.
  • A holding consists of a certain amount of money.
  • Holdings can be active and inactive.
  • A portfolio contains zero or more accounts, and these accounts may belong to more than one portfolio.
  • The total value of each account is multiplied by the weight factor in determining the value of the portfolio.

My schema is as follows (the char note is used for id type for illustrative purposes only, but I really use int ):

enter image description here

 CREATE TABLE IF NOT EXISTS accounts ( id CHAR(4) NOT NULL, name VARCHAR(45) NOT NULL, type ENUM('holding', 'portfolio') NULL, PRIMARY KEY (id)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS holdings ( accounts_id CHAR(4) NOT NULL, value DECIMAL(6,2) NOT NULL, active TINYINT NOT NULL, PRIMARY KEY (accounts_id), CONSTRAINT fk_holdings_accounts FOREIGN KEY (accounts_id) REFERENCES accounts (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS portfolios ( accounts_id CHAR(4) NOT NULL, PRIMARY KEY (accounts_id), CONSTRAINT fk_portfolios_accounts1 FOREIGN KEY (accounts_id) REFERENCES accounts (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS portfolios_has_accounts ( portfolios_id CHAR(4) NOT NULL, accounts_id CHAR(4) NOT NULL, weight DECIMAL(4,2) NOT NULL, PRIMARY KEY (portfolios_id, accounts_id), INDEX fk_portfolios_has_accounts_accounts1_idx (accounts_id ASC), INDEX fk_portfolios_has_accounts_portfolios1_idx (portfolios_id ASC), CONSTRAINT fk_portfolios_has_accounts_portfolios1 FOREIGN KEY (portfolios_id) REFERENCES portfolios (accounts_id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_portfolios_has_accounts_accounts1 FOREIGN KEY (accounts_id) REFERENCES accounts (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

Sample data:

 INSERT INTO accounts(id,name,type) VALUES ('p1','portfolio1','portfolio'),('p2','portfolio2','portfolio'),('p3','portfolio3','portfolio'),('h1','holding1','holding'),('h2','holding2','holding'),('h3','holding3','holding'),('h4','holding4','holding'); INSERT INTO holdings(accounts_id,value,active) VALUES ('h1','50','1'),('h2','40','0'),('h3','70','1'),('h4','40','1'); INSERT INTO portfolios(accounts_id) VALUES ('p1'),('p2'),('p3'); INSERT INTO portfolios_has_accounts(portfolios_id,accounts_id,weight) VALUES ('p1','h1','1'),('p1','p2','0.5'),('p2','h2','2'),('p2','p3','1'),('p3','h3','2'),('p3','h4','0.5');

accounts

 id name type p1 portfolio1 portfolio p2 portfolio2 portfolio p3 portfolio3 portfolio h1 holding1 holding h2 holding2 holding h3 holding3 holding h4 holding4 holding

briefcases

 portfolios_id p1 p2 p3

holdings

 id value active h1 50 1 h2 40 0 h3 70 1 h4 40 1

portfolios_has_accounts

 portfolios_id accounts_id weight p1 h1 1 p1 p2 0.5 p2 h2 2 p2 p3 1 p3 h3 2 p3 h4 0.5

My goals are to find:

  • Find all accounts containing only active holdings. For sample data, these are p3, h1, h3 and h4. p2 does not turn on because it turns on h2, which is inactive, and p1 is not turned on, because it turns on p2.

  • The total value of the portfolio p1. With the given sample data, this is 170: 1 * 50 + 0.5 * (2 * 40 + 1 * (2 * 70 + 0.5 * 40))

  • The constants that the holdings are multiplied by the total portfolio value p1. Given the sample data, they are as follows (note that 1 * h1 + 1 * h2 + 1 * h3 + 0.25 * h4 = 170)

.

 id weight h1 1 h2 1 h3 1 h4 .25

How can i do this?

+5
source share
1 answer

Comment on whether this should be done differently or in terms of efficiency, do they have any serious problems?

Goal number 1

 MariaDB [recursion]> WITH RECURSIVE t AS ( -> SELECT accounts_id FROM holdings WHERE active=0 -> UNION ALL -> SELECT pha.portfolios_id -> FROM portfolios_has_accounts pha -> INNER JOIN t ON t.accounts_id=pha.accounts_id -> ) -> SELECT a.* FROM accounts a -> LEFT OUTER JOIN t ON t.accounts_id=a.id -> WHERE t.accounts_id IS NULL; +----+------------+-----------+ | id | name | type | +----+------------+-----------+ | h1 | holding1 | holding | | h3 | holding3 | holding | | h4 | holding4 | holding | | p3 | portfolio3 | portfolio | +----+------------+-----------+ 4 rows in set (0.00 sec)

Goal number 2

 MariaDB [recursion]> WITH RECURSIVE t AS ( -> SELECT pha.*, h.value -> FROM portfolios_has_accounts pha -> LEFT OUTER JOIN holdings h ON h.accounts_id=pha.accounts_id -> WHERE pha.portfolios_id="p1" -> UNION ALL -> SELECT pha.portfolios_id, pha.accounts_id, pha.weight*t.weight, h.value -> FROM t -> INNER JOIN portfolios_has_accounts pha ON pha.portfolios_id=t.accounts_id -> LEFT OUTER JOIN holdings h ON h.accounts_id=pha.accounts_id -> ) -> SELECT SUM(weight*value) FROM t WHERE value IS NOT NULL; +-------------------+ | SUM(weight*value) | +-------------------+ | 170.0000 | +-------------------+ 1 row in set (0.00 sec)

Goal number 3

 MariaDB [recursion]> WITH RECURSIVE t AS ( -> SELECT pha.*, h.value -> FROM portfolios_has_accounts pha -> LEFT OUTER JOIN holdings h ON h.accounts_id=pha.accounts_id -> WHERE pha.portfolios_id="p1" -> UNION ALL -> SELECT pha.portfolios_id, pha.accounts_id, pha.weight*t.weight, h.value -> FROM t -> INNER JOIN portfolios_has_accounts pha ON pha.portfolios_id=t.accounts_id -> LEFT OUTER JOIN holdings h ON h.accounts_id=pha.accounts_id -> ) -> SELECT accounts_id, weight FROM t WHERE value IS NOT NULL; +-------------+--------+ | accounts_id | weight | +-------------+--------+ | h1 | 1.00 | | h2 | 1.00 | | h3 | 1.00 | | h4 | 0.25 | +-------------+--------+ 4 rows in set (0.01 sec) MariaDB [recursion]>
0
source

Source: https://habr.com/ru/post/1270084/

More articles:


All Articles