我正在尝试应用 CTE 和递归查询。数据库为 MariaDB 10.2 或更高版本。
业务规则如下:
- 账户可以是控股账户,也可以是投资组合账户。
- 控股由一定数量的货币组成。
- 持股可以是活跃的也可以是非活跃的。
- 一个投资组合包含零个或多个账户,并且这些账户可以属于多个投资组合。
- 在确定投资组合的价值时,每个账户的总价值乘以“权重”系数。
我的架构如下(注意char
用于 id 类型仅用于说明目的,但我真的会使用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_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。
投资组合 p1 的总价值。给定样本数据,它是 170: 1*50 + 0.5*( 2*40 + 1*( 2*70 + 0.5*40 ) )
持有量乘以常数即可得出投资组合 p1 的总价值。给定样本数据,它们如下(请注意,1*h1 + 1*h2 + 1*h3 + 0.25*h4 = 170)
.
id weight
h1 1
h2 1
h3 1
h4 .25
我怎样才能做到这一点?