SQL数据库编程大赛（第四期） - 数据库编程

rates values (2,2,1,1,1,28);
insert into service_rates values (2,2,2,2,2,21);
insert into service_rates values (2,2,3,3,3,17);
insert into service_rates values (2,2,4,4,99999999,12);
INSERT INTO service_usage VALUES(1,101,1,DATE 2010-1-5 ,DATE 2010-1-10);
INSERT INTO service_usage VALUES(2,102,1,DATE 2010-1-3 ,DATE 2010-1-11);
INSERT INTO service_usage VALUES(3,103,1,DATE 2010-1-7 ,DATE 2010-1-10);
INSERT INTO service_usage VALUES(4,104,1,DATE 2010-1-10,DATE 2010-1-26);
INSERT INTO service_usage VALUES(11,101,2,DATE 2010-1-1 ,DATE 2010-1-10);
INSERT INTO service_usage VALUES(12,102,2,DATE 2010-1-2 ,DATE 2010-1-8 );
INSERT INTO service_usage VALUES(13,103,2,DATE 2010-1-7 ,DATE 2010-1-10);
INSERT INTO service_usage VALUES(14,104,2,DATE 2010-1-5 ,DATE 2010-1-13);
INSERT INTO service_usage VALUES(21,201,1,DATE 2010-1-2 ,DATE 2010-1-21);
INSERT INTO service_usage VALUES(22,202,1,DATE 2010-1-1 ,DATE 2010-1-17);
INSERT INTO service_usage VALUES(23,203,1,DATE 2010-1-7 ,DATE 2010-1-13);
INSERT INTO service_usage VALUES(24,204,1,DATE 2010-1-3 ,DATE 2010-1-30);
INSERT INTO service_usage VALUES(31,201,2,DATE 2010-1-1 ,DATE 2010-1-2);
INSERT INTO service_usage VALUES(32,202,2,DATE 2010-1-2 ,DATE 2010-1-3);
INSERT INTO service_usage VALUES(33,203,2,DATE 2010-1-4 ,DATE 2010-1-10);
INSERT INTO service_usage VALUES(34,204,2,DATE 2010-1-11,DATE 2010-1-13);
commit;

答题注意：
1.选手必须按照题目给定的表结构答题，否则不得分
2.应提供总体思路和关键步骤的汉字注释
3.并提供样例数据对如下输入变量的输出结果，以便验证

CODE:
VAR p_start_date VARCHAR2(8);
VAR p_end_date VARCHAR2(8);
EXEC :p_start_date := 20100104;
EXEC :p_end_date := 20100130;

4.要求用一个SQL查询语句完成（可以包括子查询，但不能包括ddl和dml语句）
提交的答案：

/*
ITPUB“盛拓传媒杯”SQL数据库编程大赛第4题
 数据库类型：Oracle,任意版本
解题思路：
1、先根据service_usage生成每天一条记录
select to_date(:p_start_date, YYYYMMDD) + rownum - 1 mydate from dual
connect by rownum <=to_date(:p_end_date, YYYYMMDD) - to_date(:p_start_date, YYYYMMDD) + 1
上面这个子查询与service_usage关联(mydate between a.start_date and a.end_date)可以生成每天一笔的消费记录
count(distinct a.user_id) distinct_user_cnt 表示不同的用户数，如果1个用户1天消费多次算1个用户，根据这个数记算档次
count(*) user_servcie_cnt 表示一天所有用户服务次数
2、与service_rates关联确定消费档次，按company_id, service_id，category_id汇总生成fee1,fee2,fee3,fee4,total_fee
3、与services关联得到service_name,与companies关联得到company_name
use_hash(f,g,h),use_hash(d,e),use_merge(a,b,c)这些全是为了大数量时优化，小数据量时可以不要。
*/

VAR p_start_date VARCHAR2(8);
VAR p_end_date VARCHAR2(8);
EXEC :p_start_date := 20100104;
EXEC :p_end_date := 20100130;

select /*+use_hash(f,g,h)*/f.company_id,h.company_name,f.service_id,g.service_name,fee1,fee2,fee3,fee4,total_fee
fro

SQL数据库编程大赛（第四期）(二)