003.测验.SQL之债权统计
这是一个比较贴近实际业务的SQL练习,技术点比较常用和实用。
作者:史荣久 日期:2014-11-26 许可:CC BY-SA 3.0
任务说明
如下图,业务中,存在以下数据和数据关系:
一个顾客(CUST)会有零或多个债权(LOAN), 债权会有零或多个交易(TRANSACTION)。LOAN表BALANCE为余额,REPAYDAY为下次还款日,
TRANSACTION表中,ID是自增的,AMOUNT为金额, OPRTIME为操作日,TYPE=1为贷款,TYPE=2为还款。思考问题
1)各顾客的余额,用SQL得到下图输出。
2)还款最多交易,即用SQL得到下图输出。
在2010年从01-01(含)到06-01(不含)时间段, 找出各债权还款最多的交易数据(可能多条), 并显示对应的顾客名,3)交易表有10亿数据,检索结果响应很慢,
可能是何原因,如何排查原因,如何改善。4)本题中3张表,设计上有何缺陷,如何改善。
测试数据
通过以下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');