003.测验.SQL之债权统计-优快云博客

[img]http://www.moilioncircle.com/images/post/actions/003/subject.jpg[/img]

[b]# 003.测验.SQL之债权统计[/b]

这是一个比较贴近实际业务的SQL练习，技术点比较常用和实用。

作者：史荣久　日期：2014-11-26　许可：CC BY-SA 3.0

[b]## 任务说明[/b]

如下图，业务中，存在以下数据和数据关系：
一个顾客（CUST）会有零或多个债权（LOAN），
债权会有零或多个交易（TRANSACTION）。

LOAN表BALANCE为余额，REPAYDAY为下次还款日，
TRANSACTION表中，ID是自增的，AMOUNT为金额，
OPRTIME为操作日，TYPE=1为贷款，TYPE=2为还款。

![ER图和数据](/images/post/actions/003/er-data.png)
[img]http://www.moilioncircle.com/images/post/actions/003/er-data.png[/img]

[b]## 思考问题[/b]

1）各顾客的余额，用SQL得到下图输出。

![各顾客的余额](/images/post/actions/003/result-1.png)
[img]http://www.moilioncircle.com/images/post/actions/003/result-1.png[/img]

2）还款最多交易，即用SQL得到下图输出。
在2010年从01-01（含）到06-01（不含）时间段，
找出各债权还款最多的交易数据（可能多条），
并显示对应的顾客名，

![还款最多交易](/images/post/actions/003/result-2.png)
[img]http://www.moilioncircle.com/images/post/actions/003/result-2.png[/img]

3）交易表有10亿数据，检索结果响应很慢，
可能是何原因，如何排查原因，如何改善。

4）本题中3张表，设计上有何缺陷，如何改善。

[b]## 测试数据[/b]

通过以下SQL创建表结构和初始数据。


CREATE  TABLE `CUST` (
  `CUSTID` VARCHAR(12) NOT NULL ,
  `NAME` VARCHAR(20) NOT NULL ,
  `AGE` INT(11) NOT NULL ,
  PRIMARY KEY (`CUSTID`) )
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;

CREATE  TABLE `LOAN` (
  `LOANID` VARCHAR(20) NOT NULL ,
  `CUSTID` VARCHAR(12) NOT NULL ,
  `BALANCE` DECIMAL(16,4) NOT NULL ,
  `REPAYDAY` DATE NULL DEFAULT NULL ,
  PRIMARY KEY (`LOANID`) ,
  UNIQUE INDEX `ID_UNIQUE` (`LOANID` ASC) ,
  INDEX `CUST` (`CUSTID` ASC) ,
  CONSTRAINT `CUST`
    FOREIGN KEY (`CUSTID` )
    REFERENCES `CUST` (`CUSTID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;

CREATE  TABLE `TRANSACTION` (
  `ID` INT(11) NOT NULL ,
  `AMOUNT` DECIMAL(16,4) NOT NULL ,
  `TYPE` INT(11) NOT NULL ,
  `OPRTIME` DATETIME NOT NULL ,
  `LOANID` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `LOAN` (`LOANID` ASC) ,
  CONSTRAINT `LOAN`
    FOREIGN KEY (`LOANID` )
    REFERENCES `LOAN` (`LOANID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;

INSERT INTO CUST(CUSTID, NAME, AGE) VALUES
('100000000101', '张三', 23),
('100000000201', '李四', 24),
('100000000301', '王五', 25);

INSERT INTO LOAN(LOANID, CUSTID, BALANCE, REPAYDAY) VALUES
('10000000010100000001', '100000000101', 3000, '2010-07-03'),
('10000000010100000002', '100000000101', 3500, '2010-07-23'),
('10000000020100000001', '100000000201', 4000, '2010-07-04');

INSERT INTO TRANSACTION(ID, AMOUNT, TYPE, OPRTIME, LOANID) VALUES
(1, -10000, 1, '2010-03-03 15:15:15', '10000000010100000001'),
(2,   2000, 2, '2010-04-03 15:15:15', '10000000010100000001'),
(3,   2000, 2, '2010-05-03 15:15:15', '10000000010100000001'),
(4,   3000, 2, '2010-06-03 15:15:15', '10000000010100000001'),
(5,  -8000, 1, '2010-04-23 15:15:15', '10000000010100000002'),
(7,   2000, 2, '2010-05-23 15:15:15', '10000000010100000002'),
(8,   2500, 2, '2010-06-23 15:15:15', '10000000010100000002'),
(9, -10000, 1, '2010-01-04 15:15:15', '10000000020100000001'),
(10,  1000, 2, '2010-02-04 15:15:15', '10000000020100000001'),
(11,  1000, 2, '2010-03-04 15:15:15', '10000000020100000001'),
(12,  1500, 2, '2010-04-04 15:15:15', '10000000020100000001'),
(13,  1500, 2, '2010-05-04 15:15:15', '10000000020100000001'),
(14,  1000, 2, '2010-06-04 15:15:15', '10000000020100000001');