HSql练习题

小的练习，都包含一个知识点：

--试题一：
--如表一所示，请用sql语句统计出各分行下的客户余额排名情况，所统计的结果展现形式为：
--Rank_cd 排名    Org_name  所属分行    Cust_id   客户号       Current_bal  当前余额

--客户余额计算口径：客户所有账户余额的总和（一个客户可能有多个账户）。

--数据输入

Rank_cd      Org_name      Cust_id    	Current_bal
（排名）	   （所属分行）   （客户号）       （当前余额）
	

表一：t03_acct_trade_detail

Cust_id     Acct_no     Org_name       AMT         Txn_Dt      Current_bal
(客户号)	(账户号)	(机构名)    (交易金额)    (交易日期)     (当前余额)		
6000121	9000000001	上海分行	100.00	20120701	20000
6000121	9000000002	宁波分行	200.00	20120701	30000
6000122	9000000003	宁波分行	200.00	20120701	30000
6000122	9000000004	宁波分行	200.00	20120701	30000
6000123	9000000005	宁波分行	200.00	20120701	30000
6000123	9000000006	宁波分行	200.00	20120701	30000
6000123	9000000007	宁波分行	200.00	20120701	30000



create table lianxi.t03_acct_trade_detail
(
    Cust_id     bigint,
    Acct_no     bigint,
    Org_name    string,
    AMT         DOUBLE,
    Txn_Dt      string,
    Current_bal bigint
) row format delimited fields terminated by ',';

truncate table lianxi.t03_acct_trade_detail;


load data local inpath '路径' into table lianxi.t03_acct_trade_detail;


select *
from lianxi.t03_acct_trade_detail;

-- 思路：找出相应的值，然后把想要的数据打印出来，最后再进行分区内排序。 当排序无法和group by同时进行时，可以把数据进行处理，最后做排序。
-- eg: group by 不能和开窗的over一起使用。

select *,rank() over(partition by org_name order by current_bal) rn from(
select org_name,cust_id,
       sum(current_bal) as current_bal
       from lianxi.t03_acct_trade_detail group by cust_id,org_name) as li;

--试题二
-- 表二 t03_card_trade_detail(卡交易明细表)，字段分别代表：客户号(Cust_id),卡号(Card_id)，所属分行
--（org_name）,交易金额（AMT）,交易日期(Txn_Dt)。该表记录了客户卡消费的信息。

Cust_id	Card_id	Org_name	AMT	Txn_Dt
6000121	622612010600001	上海分行	100.00	20120101
6000121	622612010600001	上海分行	100.00	20120105
6000121	622612010600002	上海分行	100.00	20120109
6000121	622612010600001	宁波分行	200.00	20120701
6000121	622612010600002	宁波分行	200.00	20120205
6000121	622612010600001	宁波分行	200.00	20120706
6000121	622612010600003	宁波分行	200.00	20120601
6000121	622612010600003	宁波分行	200.00	20120801





drop table lianxi.t04_acct_trade_datail;

create table lianxi.t04_acct_trade_datail(
    Cust_id bigint,
    Card_id bigint,
    Org_name string,
    AMT DOUBLE,
    Txn_Dt string
)row format delimited fields terminated by ',';



load data local inpath '/home/xiaofan/wenjian/to_04.txt' into table lianxi.t04_acct_trade_datail;

-- 请用SQL语句计算一年内，客户每个月的消费总金额，展现形式如下：
--eg:是根据月份分区就根据substr类选择月份日期选择位数判断根据月份分组。再选择相应的数据进行分组

select txn_dt,sum(amt) as AMT,card_id from (
SELECT
card_id,amt,substr(txn_dt,1,6) as txn_dt
from lianxi.t04_acct_trade_datail) as a
 group by txn_dt,card_id;

--练习2，如表三所示，请用SQL技术将表三转换成表四的形状（或者伪代码写出历史拉链表处理思路）
--表三：t03_acctno_bal (账户余额明细表)

Cust_id     Acct_no     Org_name        ATM          Txn_Dt       Current_bal
(客户id)    (账户id)    (所属机构)     (交易金额)    (交易日期)     (当前余额)
6000121	9000000001	上海分行	100.00	20120701	20000
6000121	9000000001	上海分行	100.00	20120701	20000
6000122	9000000002	宁波分行	200.00	20120701	30000



drop table lianxi.t05_acct_trade_detail;

Create table lianxi.t05_acct_trade_detail(
    Cust_id bigint,
    Acct_no bigint,
    Org_name string,
    ATM double,
    Txn_Dt string,
    Current_bal bigint
)row format delimited fields terminated by ',';

truncate table lianxi.t05_acct_trade_detail;

select * from lianxi.t05_acct_trade_detail;

load data local inpath '/home/xiaofan/wenjian/chongfu_sql.txt' into table lianxi.t05_acct_trade_detail;


- 可以去重所有的字段，这样是判断数据里面是否有脏数据，和重复数据。
select distinct cust_id,acct_no,org_name,atm,txn_dt,current_bal from lianxi.t05_acct_trade_detail ;


--通过sql 中insert overwrite 来删除不符合要求的数据。就是脏数据，或者重复数据。

insert overwrite table lianxi.t05_acct_trade_detail
select distinct cust_id,acct_no,org_name,atm,txn_dt,current_bal from lianxi.t05_acct_trade_detail;

--今天重点：
   --1、lead(1,2,3) 里面的三个参数 和over联合使用。  1参数：列名   2参数：读取前面几行的数据    3参数：最后一个空值用什么代替。
   --2、distinct  去除表格中重复的脏数据
   --3、insert overwrite 删除表中不符合要求的数据。就是脏数据，或者重复数据。