PLSQL基础练习-14-下

14. 有如下两张表：
交易表 deli_t：

 日期 Busi_date    交易时间 Exch_time    客户编号 Cust_piy_no     交易类型 Exch_type    产品代码 Prd_no    交易量 Del_amt    机构编号 Org_no
20170101        93102                0312003                 证券买入            00001            1000            0312
20170101        103102                0312003                 证券买入            00002            500                0312
20170101        133102                0312003                 证券卖出            00003            800                0312
......                                                                                        
20171231        143102                0320004                 期权买入            00004            500                0320

客户信息表 info_t：

 机构编号 Org_no   机构名称 Org_name
0312             黄埔东路
0320             农林下路
...         
0306             中年广场 

需求 1:

 请写一段筒单的 sql, 提取出 201712 月每个部门每天各种交易类型的交易量给业务部门.
展示字段为: 日期、机构名称、证券买入当日交易量、证券卖出当日交易量

分析：
1. 用 Where 语句首先判断日期是否等于 201712 月
2. 然后观察字段内容得知，涉及两张表，需要关联，用 Inner Join

SELECT d.busi_date AS 日期,
       i.org_name AS 机构名称,
       SUM(CASE WHEN d.exch_type='证券买入' THEN d.del_amt END) AS 证券买入当日交易量,
       SUM(CASE WHEN d.exch_type='证券卖出' THEN d.del_amt END) AS 证券卖出当日交易量
  FROM deli_t d
  JOIN info_t i
    ON d.org_no=i.org_no
 WHERE SUBSTR(d.busi_date,1,6)='201712'  -- d.busi_date 为字符串类型
 GROUP BY d.busi_date,i.org_name;

需求 2:

 提取出 201712 月每天第一个下单的客户、最后一个下单的时间信息.
展示字段为: 日期、第一个下单时间、第一个下单客户、最后下单时间、最后下单客户

分析：

1. 用 Where 语句首先判断日期是否等于 201712 月。
2. 第一个和最后一个，可以用聚合函数 Min 和 Max 对时间求值
3. 第一次关联第一个下单的信息，第二次关联最后一此下单的。
4. 涉及两张表的字段，两表做关联，Inner Join。

SELECT tmp.busi_date AS 日期,
       d1.exch_time AS 第一个下单时间,
       d1.cust_piy_no AS 第一个下单客户,
       d2.exch_time AS 最后下单时间,
       d2.cust_piy_no AS 最后下单客户
  FROM ( 
        SELECT d.busi_date,
               MIN(exch_time) AS min_exch_time,
               MAX(exch_time) AS max_exch_time
          FROM deli_t d
         WHERE SUBSTR(d.busi_date,1,6)='201712'  -- d.busi_date 为字符串类型
         GROUP BY d.busi_date
        ) tmp
   JOIN deli_t d1
     ON d1.busi_date=tmp.busi_date
    AND d1.exch_time=tmp.min_exch_time
   JOIN deli_t d2
     ON d2.busi_date=tmp.busi_date
    AND d2.exch_time=tmp.max_exch_time
  ORDER BY tmp.busi_date;

需求 3:

 提取出 201712 月每天成交量前 10 的产品信息展示字段为: 日期、产品代交易量、排名

分析：
1. 用 Where 判断日期等于 201712 月
2. 成交量前十，首先按照成交量大小排序，用分析函数。
3. 套用子查询，直接返回小于 10 的所有信息。

   WITH T AS (SELECT BUSI_DATE, PRD_NO, SUM(DEL_AMT) AS DEL_AMT
      FROM DELI_T D
     WHERE SUBSTR(D.BUSI_DATE, 1, 6) = '201712'
     GROUP BY BUSI_DATE, PRD_NO
     )
     SELECT * FROM (SELECT T.*,
                           ROW_NUMBER() OVER(PARTITION BY BUSI_DATE, PRD_NO ORDER BY DEL_AMT DESC) AS RN
                      FROM T) T1
      WHERE T1.RN <= 10;