将CTE应用于递归查询 [英] Applying CTE for recursive queries
问题描述
我正在尝试应用CTE和递归查询.该数据库是MariaDB 10.2或更高版本.
I am trying to apply CTE and recursive queries. The database is MariaDB 10.2 or greater.
业务规则如下:
- 帐户可以是控股帐户也可以是投资组合.
- 持股由一定数量的钱组成.
- 控股可以是活跃的，也可以是非活跃的.
- 投资组合包含零个或多个帐户，这些帐户可以属于多个投资组合.
- 在确定投资组合的价值时，每个帐户的总价值乘以权重"因子.
我的架构如下(注意char仅用于id类型，仅用于说明目的，但我会真正使用int):
My schema is as follows (note char is used for id type for illustration purposes only, but I will really use int):
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;
样本数据如下:
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');
帐户
id name type
p1 portfolio1 portfolio
p2 portfolio2 portfolio
p3 portfolio3 portfolio
h1 holding1 holding
h2 holding2 holding
h3 holding3 holding
h4 holding4 holding
作品集
portfolios_id
p1
p2
p3
馆藏
id value active
h1 50 1
h2 40 0
h3 70 1
h4 40 1
portfolios_has_accounts
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
我的目标是找到:
-
查找仅包含有效馆藏的所有帐户.给定样本数据为p3，h1，h3和h4.不包括p2，因为它包含不活动的h2，而不包括p1，因为它包含p2.
Find all accounts which only contain active holdings. Given sample data it is p3, h1, h3, and h4. p2 is not included because it includes h2 which is not active, and p1 is not included because it includes p2.
投资组合p1的总价值.给定样本数据为170:1 * 50 + 0.5 *(2 * 40 + 1 *(2 * 70 + 0.5 * 40))
The total value of portfolio p1. Given sample data, it is 170: 1*50 + 0.5*( 2*40 + 1*( 2*70 + 0.5*40 ) )
与持股乘以得出投资组合p1的总价值的常数.给定样本数据，它们如下(请注意1 * h1 + 1 * h2 + 1 * h3 + 0.25 * h4 = 170)
The constants which the holdings are multiplied by to result in the total value of portfolio p1. Given the sample data, they are the following (note that 1*h1 + 1*h2 + 1*h3 + 0.25*h4 = 170)
.
id weight
h1 1
h2 1
h3 1
h4 .25
我该怎么做?
推荐答案
请评论是否应该以不同的方式进行操作，或者从性能角度考虑它们是否存在重大问题?
Please comment whether these should be done differently, or from a performance perspective, whether they have any major issues?
目标#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)
目标#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)
目标#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]>
这篇关于将CTE应用于递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
