IT技术博客

        有一天，测试的妹子问我，我想知道我们系统那天的有效交易最多，如果交易一直有效，当然是最后一天，把所有的交易累计就可以了，但是我们系统的交易是有有效期的，所以在累计交易时还要剔除掉过期的交易。例如下面的测试数据：

对于20170201这天有效交易当然是B1、B7和B6

20170202这天的有效交易是B1、B7、B6、B2和B5

20170203这天的有效交易就不能包含B6了，因为B6的到期日是20170203

20170501这天的有效交易就只有B3和B4了

明白了需求后，看看如何用SQL实现

1.我的思路首先是要表自关联，同时关联条件是非等值关联

因为只有交易日小于到期日的交易才是有效的。

SQL1：

SELECT t1.id, t1.trade_date,
t2.trade_date trade_date2,
t2.maturity_date m_date2 FROM ANGU_TEST1  t1,ANGU_TEST1 t2
WHERE t1.trade_date < t2.maturity_date
AND t1.trade_date >= t2.trade_date
  /

ID         TRADE_DATE TRADE_DATE M_DATE2
---------- ---------- ---------- ----------
B1         20170201   20170201   20170203
B1         20170201   20170201   20170301
B1         20170201   20170201   20170311
B7         20170201   20170201   20170203
B7         20170201   20170201   20170301
B7         20170201   20170201   20170311
B6         20170201   20170201   20170203
B6         20170201   20170201   20170301
B6         20170201   20170201   20170311
B2         20170202   20170201   20170203
B2         20170202   20170201   20170301
B2         20170202   20170202   20170302
B2         20170202   20170202   20170305
B2         20170202   20170201   20170311
B5         20170202   20170201   20170203
B5         20170202   20170201   20170301
B5         20170202   20170202   20170302
B5         20170202   20170202   20170305
B5         20170202   20170201   20170311
B3         20170203   20170201   20170301
B3         20170203   20170202   20170302
B3         20170203   20170202   20170305
B3         20170203   20170201   20170311
B3         20170203   20170203   20171010
B4         20170501   20170501   20170701
B4         20170501   20170203   20171010

结果集明显不对，原因是t1.trade_date
>= t2.trade_date这个条件

当天的交易产生了笛卡尔积

SQL2：

SELECT t1.id, t1.trade_date,
 t2.trade_date trade_date2,
t2.maturity_date m_date2 FROM ANGU_TEST1  t1,ANGU_TEST1 t2
WHERE t1.trade_date < t2.maturity_date
AND t1.trade_date > t2.trade_date
/

ID         TRADE_DATE TRADE_DATE M_DATE2
---------- ---------- ---------- ----------
B2         20170202   20170201   20170203
B2         20170202   20170201   20170301
B2         20170202   20170201   20170311
B5         20170202   20170201   20170203
B5         20170202   20170201   20170301
B5         20170202   20170201   20170311
B3         20170203   20170201   20170301
B3         20170203   20170202   20170302
B3         20170203   20170202   20170305
B3         20170203   20170201   20170311
B4         20170501   20170203   20171010

还是有问题，累计时没有计算当天的交易,同时还是有计算重复的问题，所以我决定将其中一个表的交易日去重，这样比较的时候就没有重复的交易，同时补上当天的交易

SQL3：

WITH a AS (--构建一个没有重复交易日的表
SELECT DISTINCT trade_date trade_date FROM ANGU_TEST1
SELECT trade_date,SUM(a) FROM (
 SELECT trade_date,COUNT(*) a FROM (
SELECT  t1.trade_date,
t2.trade_date trade_date2,
t2.maturity_date m_date2 FROM a t1,ANGU_TEST1 t2
WHERE t1.trade_date < t2.maturity_date
 AND t1.trade_date > t2.trade_date)
GROUP BY trade_date
  UNION ALL--单独处理当天的交易
SELECT trade_date,COUNT(*) a FROM ANGU_TEST1 t
GROUP BY t.trade_date)
GROUP BY  trade_date;
TRADE_DATE     SUM(A)
---------- ----------
20170201            3
20170203            5
20170202            5
20170501            2