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

本期题目：
某项房产资源服务按天数、人数收费。收费标准与那一天的用户数有关，对于来自同一个房产经纪公司的用户，某天的总用户数越多则折扣越多。

CODE:

CREATE TABLE services ( -------- 各项服务
service_id NUMBER PRIMARY KEY
,service_nameVARCHAR2(20)
);
CREATE TABLE companies ( -------- 使用服务的公司
company_id NUMBER PRIMARY KEY
,company_name VARCHAR2(40)
);

CREATE TABLE service_users ( -------公司下的用户（房产经纪）
user_id NUMBER PRIMARY KEY
,company_idNUMBER NOT NULL REFERENCES companies(company_id)
,user_name VARCHAR2(20)
);
CREATE TABLE service_usage (
------- 使用情况，起止日为闭合区间（首尾包含），如果只用一天，则start_date=end_date
------该表为大表，数据较多
usage_id NUMBER PRIMARY KEY
,user_id NUMBER NOT NULL REFERENCES service_users(user_id)
,service_idNUMBER NOT NULL REFERENCES services(service_id)
,start_dateDATE NOT NULL -------- 数据只含日期，不含时间
,end_date DATE NOT NULL -------- 数据只含日期，不含时间
,CONSTRAINT check_dates CHECK (end_date>=start_date)
);
CREATE TABLE service_rates ( -------收费标准，每个公司各不相同, 按人数段计费
service_id NUMBER NOT NULL REFERENCES services(service_id)
,company_id NUMBER NOT NULL REFERENCES companies(company_id)
,category_id NUMBER NOT NULL ------ 1,2,3,4表示1档，2档，3档，4档
,user_count_min NUMBER NOT NULL ------ 该档人数下限，1档从1开始
,user_count_max NUMBER NOT NULL ------ 该档人数上限，同样是闭合区间。最后一档不妨设为99999999
,rate NUMBER NOT NULL ------ 该档的费率，乘以天数再乘以人数就是总费用
,PRIMARY KEY (company_id,service_id,category_id)
);
输入：
起止日期(yyyymmdd字符串), 闭合区间（首尾包含）, 如果只计算一天，则p_start_date=p_end_date
绑定变量:
VAR p_start_date VARCHAR2(8);
VAR p_end_date VARCHAR2(8);
输出：费用报表
company_id,company_name,service_id,service_name,fee1,fee2,fee3,fee4,total_fee
其中:fee1,fee2,fee3,fee4分别表示1档，2档，3档，4档的费用,total_fee是上述4项的总和。若存在某档无数据，则总和为NULL。
按照company_id,service_id排序(升序)。

测试数据：

CODE:

INSERT INTO services VALUES(1,'Service A');
INSERT INTO services VALUES(2,'Service B');
INSERT INTO services VALUES(3,'Service C');
INSERT INTO companies VALUES(1,'company A');
INSERT INTO companies VALUES(2,'company B');
INSERT INTO companies VALUES(3,'company C');
INSERT INTO service_users VALUES(101,1,'User 1 Comp A');
INSERT INTO service_users VALUES(102,1,'User 2 Comp A');
INSERT INTO service_users VALUES(103,1,'User 3 Comp A');
INSERT INTO service_users VALUES(104,1,'User 4 Comp A');
INSERT INTO service_users VALUES(201,2,'User 1 Comp B');
INSERT INTO service_users VALUES(202,2,'User 2 Comp B');
INSERT INTO service_users VALUES(203,2,'User 3 Comp B');
INSERT INTO service_users VALUES(204,2,'User 4 Comp B');
INSERT INTO service_users VALUES(301,3,'User 1 Comp C');
INSERT INTO service_users VALUES(302,3,'User 2 Comp C');
INSERT INTO service_users VALUES(303,3,'User 3 Comp C');
insert into service_rates values (1,1,1,1,1,20);
insert into service_rates values (1,1,2,2,2,15);
insert into service_rates values (1,1,3,3,3,10);
insert into service_rates values (1,1,4,4,99999999,5);
insert into service_rates values (2,1,1,1,1,30);
insert into service_rates values (2,1,2,2,2,25);
insert into service_rates values (2,1,3,3,3,20);
insert into service_rates values (2,1,4,4,99999999,15);
insert into service_rates values (1,2,1,1,1,22);
insert into service_rates values (1,2,2,2,2,17);
insert into service_rates values (1,2,3,3,3,12);
insert into service_rates values (1,2,4,4,99999999,8);
insert into service_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语句）

原文见：http://www.itpub.net/thread-1411495-1-1.html

参赛者答案：http://www.itpub.net/thread-1417576-1-1.html

我提交的答案：

/*
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
  from (select /*+use_hash(d,e)*/ d.company_id,d.service_id,
               sum(decode(e.category_id, 1, rate * user_servcie_cnt, null)) fee1,
               sum(decode(e.category_id, 2, rate * user_servcie_cnt, null)) fee2,
               sum(decode(e.category_id, 3, rate * user_servcie_cnt, null)) fee3,
               sum(decode(e.category_id, 4, rate * user_servcie_cnt, null)) fee4,
               sum(rate * user_servcie_cnt) total_fee
          from (select /*+use_merge(a,b,c)*/b.company_id,a.service_id,
                       count(distinct a.user_id) distinct_user_cnt, --表示不同的用户数，如果1个用户1天消费多次算1个用户，根据这个数记算档次
                       count(*) user_servcie_cnt --表示一天所有用户服务次数
                  from service_usage a,
                       service_users b,
                       (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) c
                 where a.user_id = b.user_id
                   and c.mydate between a.start_date and a.end_date --用于根据service_usage生成每天一笔的消费记录
                 group by b.company_id, a.service_id, c.mydate) d,service_rates e
         where d.company_id = e.company_id
           and d.service_id = e.service_id
           and d.distinct_user_cnt between e.user_count_min and e.user_count_max --确认消费档次
         group by d.company_id, d.service_id) f,services g,companies h --关联显示company_name,service_name
 where f.company_id = h.company_id and f.service_id = g.service_id
 order by f.company_id, f.service_id;

--输出结果
COMPANY_ID COMPANY_NAME SERVICE_ID SERVICE_NAME FEE1 FEE2 FEE3 FEE4 TOTAL_FEE
---------- ---------------------------------------- ---------- -------------------- ---------- ---------- ---------- ---------- ----------
1 company A 1 Service A 320 90 90 20 520
1 company A 2 Service B 90 50 240 120 500
2 company B 1 Service A 198 136 252 224 810
2 company B 2 Service B 280 280

解题思路：见上面的备注。

评委点评：结构清晰，性能稍差。计费的时候应该使用distinct_user_cnt而不是user_servcie_cnt，这个错误导致重复计费。

个人分析：

1、采用了较笨的按天枚举计算方法，性能在选手里还算可以，但是当数据量大了性能问题严重。

2、题目有歧义，在论坛里很多网友也讨论了，就是当1个客户1天内同一服务使用多次时如何计算？这个对我这种按天枚举计算的算法没什么问题，只是改变一下变量即可，在这点评委扣分较狠，所以分数低了很多。