“盛拓传媒杯”SQL数据库编程大赛第四期评分及所有参赛选手答题公布！

~贝贝~ · 发表于 2011-4-12 17:18

经过多日进展的评审，第四期评审结果现在揭晓！具体评审得分及点评如下，附件中依旧有所有答题的压缩包和点评表格！

TOP10得主正在统计中，稍后就公布，大家别着急！参与的puber等我的pm，我一会就发给大家！

评审代号	综合得分	点评及建议
SQL4-33	99.15	把一行消费记录拆分为增(+1)、减(-1)两行并对标记累加得到人数，比其他的拆分方法减少了中间结果从而提高了效率。T4排重思路有些费解，newcnt如何经过两次累加演变成人数curr_cnt，最好多加注释。
SQL4-26	95.65	代码思路清晰，高效。本解法把时间区间划分为较大颗粒(见集合date_piece)，比起拆分到天的做法会高效一些.
SQL4-4	95	题目要求的total_fee写成了fee_total，略微扣点点分，用GROUP BY ROLLUP做横向合计比较新鲜。
SQL4-20	94	代码清晰高效，注释不足，row_number()去除重复不太必要，直接在算headcount的时候用COUNT(DISTINCT USER_ID)即可。
SQL4-25	92.6	代码思路清晰。不过t和m的连接是多余的，直接用t和service_usage连接即可。最后和companies和services的外连接可改为内连接，因为题目已说明有外键存在。
SQL4-12	92.15	直接了当的拆分到天的做法。
SQL4-27	92.1	结构清晰，该题对mssql而言确实构造会繁琐一些
SQL4-8	91.2	本解法把时间区间划分为较大颗粒，比起拆分到天的做法会高效一些。tmp_date2里面的自连接可以改为分析函数实现，如果再精心改写的话UNION也可以去掉。
SQL4-2	91	代码书写非常规范，但效率不高，中间CTE构造略显啰嗦，use_days和category可以合并为一层。use_fee_all里面的TO_CHAR和NVL有些多余。
SQL4-39	90.75	process_data里面虽然是拆分到天，但用的是两个数字而不是一个日期型来表示，有些独特。但此答题的性能较差。
SQL4-15	89.5	结构清晰，但构造cte过程略显啰嗦。t3里面的DISTINCT 子查询可以不要，直接COUNT(DISTINCT user_id). t4里面的GROUP BY没有必要带t3.service_date。用户数在service_rates的定位应该写到WHERE里面去。
SQL4-34	88.4	该代码利用了pivot新特性，但效率较差。t1中把services s,companies c连接进去有些太早，这样增加了分组键company_name和service_name，从而增加了负担。
SQL4-10	88.25	tmp1的过滤有误，a的起止日期可能全部落在输入区间之外，但是包含输入期间。
SQL4-19	87.3	本解法把时间区间划分为较大颗粒(见集合C)，比起拆分到天的做法会高效一些。构造集合d1和d的思路比较难懂，注释不是很充分。
SQL4-28	85	代码性能稍差，usage_match和period用内连接即可。
SQL4-3	83.9	中间CTE过程稍显啰嗦，usage_usernumber_by_day没有去除重复。几个中间结果的排序是不必要的。
SQL4-16	82.1	代码较高效，但cte嵌入代码行中略为难读。step2没有去除重复。step3的DECODE没有必要。
SQL4-13	81.8	service_usage3里的日期过滤可以提早到service_usage1完成，才不会全表做拆分。全文没有一句注释，且结果未排序。
SQL4-35	81.75	直接了当的拆分到天的做法。但性能较差。companyB的serviceA的FEE1计算有误，导致总数有错误。
SQL4-21	80.9	ALL_TIME里最后两个UNION没有必要，在前两个UNION的时候界定一下就可以了。没有去除重复。但结果列标题不符合题目要求。
SQL4-41	79.6	day_cnt_stat里面的日期过滤条件写错了，应该是AND不是OR. 没有去除重复。
SQL4-22	77.7	子查询usages的CASE没有必要那么复杂，应该在WHERE做区间的过滤。构造出来的NULL也给后面增加了负担。
SQL4-18	76.2	没有去除重复。sc里面的排序是不必要的。
SQL4-17	75.6	没有去除重复；对日期的过滤可以推入到service_usage表，这样就不需要全表都做多行拆分。用TABLE()函数拆分比较新颖。
SQL4-36	75.45	条件判断过于复杂，且缺乏注释，g_comp_sev_to_day的CASE可以简单地用GREATEST,LEAST完成。没有去除重复。
SQL4-29	75.25	结构清晰，性能稍差。计费的时候应该使用distinct_user_cnt而不是user_servcie_cnt，这个错误导致重复计费。
SQL4-37	74.65	tsrv的过滤有误，性能较差。t的起止日期可能全部落在输入区间之外，但是包含输入期间。tcnt没有去重复。
SQL4-40	72.2	没有去除重复。最后的外连接有些不必要，题目没有要求输出所有公司和服务的组合。
SQL4-38	70.85	性能较差。a里面应该根据输入区间做过滤，减少后面的计算负担。d里面没有去除重复。
SQL4-5	66.9	未排序；t3的cnt去除了重复，t3和t4连接的时候，也能够找到正确的费率段，但是同一个人的交叉情况会重复计费sum。
SQL4-1	39.5	01集合tds和service_usage的连接条件有误；tmp_date_scale的构造思路也有误，start_date和end_date是首尾包含的，即使它们相等也应该算使用一天。同一用户使用期间的交叉情况也没有考虑。
SQL4-7	28.3	拆分记录时，区间分为 60天以上和以下，不知道是什么道理。最后行转列的计费部分出了错，忘记乘以人数了，SUM(RATE)应该为SUM(RATE*cn)
SQL4-14	12.1	DB2的，非参赛指定语言，相关地方改为Oracle语法后，得出错误结果
SQL4-9	11.2	计费公式有误，既然使用记录已经拆分到天，应该逐一算每天的费用然后汇总：SUM(DECODE(CATEGORY_ID,1,cnt*rate)) 而不是分开汇总再来相乘。
SQL4-23	11	结果完全错误。V1的num_day计算有误，没有去除重复而且分区键不够充分，会把不同company_id的用户混起来。日期过滤条件应该是>=和<=。
SQL4-11	10.7	x的过滤条件有误，会遗漏很多数据。all_user的计算错误，要求是同一天出现的用户数。
SQL4-6	10	service_company_rate做行转列有些太早了。service_users_usage的GROUP BY方法有误，会导致交叉的时段分到不同组，从而无法正确计算某天的人数。
SQL4-24	10	结果完全错误。没用指定的绑定变量做输入；没有去除重复。fee1~fee4的计费公式弄错了，对题意理解有误。
SQL4-30	10	子查询U中对用户数的计算是错误的，按题意应该是每天使用服务的人数。这个理解上的失误导致整个结果出错。
SQL4-31	10	子查询T中对用户数的计算是错误的，按题意应该是每天使用服务的人数。这个理解上的失误导致整个结果出错。
SQL4-32	10	将开始、结束分为两种事件并赋予标记的思路是可取的, 但是思路过于繁复导致出错。比如event_type的累加可以简单地用分析函数SUM实现而无需用递归。

附上此次SQL大赛活动相关链接，方便大家查阅：
第四期SQL大赛活动链接：http://www.itpub.net/thread-1411495-1-1.html
第三期SQL大赛活动链接：http://www.itpub.net/thread-1408182-1-1.html
第二期SQL大赛活动链接：http://www.itpub.net/thread-1403356-1-1.html
第一期SQL大赛活动链接：http://www.itpub.net/thread-1400067-1-1.html

SQL数据库编程大赛第三期评分及所有答题公布链接：http://www.itpub.net/thread-1415335-1-1.html
SQL数据库编程大赛第二期评分及所有答题公布链接：http://www.itpub.net/thread-1411980-1-1.html
SQL数据库编程大赛第一期评分及所有答题公布链接：http://www.itpub.net/thread-1407072-1-1.html

~贝贝~ · 发表于 2011-4-12 17:21

此题newkid解题思路总结公布如下，另此贴还附上此解题思路的文档下载，方便大家查阅：

这道题的风格比较务实，不像前三题带有游戏的性质，算是回归了SQL的传统功能。尽管它煞有介事地用了五张表，对经验丰富的程序员来说仍然是小菜一碟，不少人觉得难度降低了。尽管如此，仍然有将近一半的人没有给出正确答案，可见简单的题目也不可轻视，此外，如何把一道看似平庸的题目做出新意，这才是难点所在。

在这里要特别感谢regonly1同学，他写了一个脚本制造测试数据，本来我是要自己动手的，现在就顺手牵羊，以逸待劳了。实际上我自己的答案也是利用他产生的数据来调试、改良的。

题目的计费公式和人数相关，因此我们首先要算出计费区间内的人数，然后再去套用计费公式。每笔使用记录的区间都各不相同，长短不一，给分组统计造成了障碍，因此不难想到把这些记录分割成小单元让它们对齐。最直接的思路就是分割到最小计费单位即日期，把一行使用记录按其覆盖的日期拆分成多行，每天一行，然后按日期汇总人数，再用这个人数去计费。这是很多人采用的办法，示例如下：

SELECT t.company_id
,c.company_name
,t.service_id
,s.service_name
,t.fee1
,t.fee2
,t.fee3
,t.fee4
,t.fee1+t.fee2+t.fee3+t.fee4 as total_fee
FROM (SELECT u.company_id
,u.service_id
----- 行转列输出
,NVL(SUM(DECODE(r.category_id,1,r.rate*u.user_cnt*u.days)),0) AS fee1
,NVL(SUM(DECODE(r.category_id,2,r.rate*u.user_cnt*u.days)),0) AS fee2
,NVL(SUM(DECODE(r.category_id,3,r.rate*u.user_cnt*u.days)),0) AS fee3
,NVL(SUM(DECODE(r.category_id,4,r.rate*u.user_cnt*u.days)),0) AS fee4
FROM (SELECT ----- 把同一公司、同一服务、人数一致的记录做汇总，算出总天数
----- 这一层聚合运算是为了减少中间结果集的行数，减轻后面行转列的负担
company_id
,service_id
,user_cnt
,COUNT(*) days ---- 这样的人数总共有几天
FROM (SELECT usr.company_id
,usg.service_id
,COUNT(DISTINCT usg.user_id) user_cnt --------- 每个用户在每天只按1人算，DISTNCT 去除重复
FROM service_usage usg
,service_users usr
,(SELECT ROWNUM rn ---- 根据输入的起止日期构造出以个N行集合，连接后就把一行拆成多行。
FROM DUAL
CONNECT BY ROWNUM<=TO_DATE(:p_end_date,'YYYYMMDD')-TO_DATE(:p_start_date,'YYYYMMDD')+1
)
WHERE usg.user_id = usr.user_id
AND usg.end_date>=TO_DATE(:p_start_date,'YYYYMMDD') ---- 使用记录必需落在输入的起止日期的区间内
AND usg.start_date<=TO_DATE(:p_end_date,'YYYYMMDD')
---下面的这个条件限制了当前使用记录要拆成多少天，LEAST和GREATEST是排除输入区间外的日期
AND rn <= LEAST(usg.end_date,TO_DATE(:p_end_date,'YYYYMMDD'))+1
-GREATEST(usg.start_date,TO_DATE(:p_start_date,'YYYYMMDD'))
GROUP BY usr.company_id
,usg.service_id
,GREATEST(usg.start_date,TO_DATE(:p_start_date,'YYYYMMDD'))+rn-1 ---- 拆出来的每天的日期
)
GROUP BY company_id,service_id,user_cnt
) u
,service_rates r
WHERE u.service_id = r.service_id
AND u.company_id = r.company_id
AND u.user_cnt BETWEEN r.user_count_min AND r.user_count_max ---- 属于哪一档收费标准
GROUP BY u.company_id
,u.service_id
) t
,companies c
,services s
WHERE t.company_id=c.company_id AND t.service_id=s.service_id
ORDER BY company_id,service_id;

复制代码

上述方法有个美中不足之处，就是在把一行数据拆成多行时，可能产生很多中间记录，特别是当使用记录的覆盖范围比较大时，一行要分成很多行，效率不如人意。实际上数据没有必要打得那么“碎”，如果我们能够求出所有的交叠的区间，那么只要用该区间的首尾日期相减就可以得到这一段的天数了，而不必拆分到每一天再加回来。例如有四个从小到大的日期A,B,C,D, 某一行使用记录区间是AC, 另一条是BD，它们之间的交叠部分是BC这一段。我们只要把第一条拆成AB和BC两段，而第二条只需拆成BC和CD两段，那么AB这段是一条，BC这段有两条，CD这段仍然是一条，这样就分组计数就可以很容易求出来。从这个例子我们也可以看到分段的思路，就是把每条使用记录的首尾日期拿出来进行排序，这样排序后的两行数据之间自然而然就形成了一个个区间。

WITH date_periods AS (
---- 这个子查询求出所有交叠部分的区间，拿它作为打散数据的刻度表
SELECT t.*
---- 两行数据之间的日期相减得到区间的大小。
---- N个区间总共会有N+1条记录，最后一行的LEAD是NULL所以算出来的DAYS也会是NULL, 等会要过滤掉。
,LEAD(period_date) OVER(PARTITION BY company_id,service_id ORDER BY period_date) - period_date AS days
FROM ( SELECT DISTINCT ----- 去除刻度表中的重复日期
usr.company_id
,usg.service_id
---- LEAST和GREATEST是排除输入区间外的日期
,(CASE WHEN rn=1 THEN GREATEST(usg.start_date,TO_DATE(:p_start_date,'YYYYMMDD'))
---- 下面end_date+1是为了把闭合区间转换为前闭后开区间，这样只需相减就可得出天数
ELSE LEAST(usg.end_date,TO_DATE(:p_end_date,'YYYYMMDD'))+1
END) period_date
FROM service_usage usg
,service_users usr
,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=2) --- 把一行拆成两行, rn=1起始日期，rn=2终止日期
WHERE usg.user_id = usr.user_id
AND usg.end_date>=TO_DATE(:p_start_date,'YYYYMMDD')---- 使用记录必需落在输入的起止日期的区间内
AND usg.start_date<=TO_DATE(:p_end_date,'YYYYMMDD')
) t
)
SELECT --------- 下面开始的写法和上一种答案差不多
t.company_id
,c.company_name
,t.service_id
,s.service_name
,t.fee1
,t.fee2
,t.fee3
,t.fee4
,t.fee1+t.fee2+t.fee3+t.fee4 as total_fee
FROM (SELECT u.company_id
,u.service_id
,NVL(SUM(DECODE(r.category_id,1,r.rate*u.user_cnt*u.days)),0) AS fee1
,NVL(SUM(DECODE(r.category_id,2,r.rate*u.user_cnt*u.days)),0) AS fee2
,NVL(SUM(DECODE(r.category_id,3,r.rate*u.user_cnt*u.days)),0) AS fee3
,NVL(SUM(DECODE(r.category_id,4,r.rate*u.user_cnt*u.days)),0) AS fee4
FROM (SELECT ----- 把同一公司、同一服务、人数一致的记录做汇总，算出总天数
----- 这一层聚合运算是为了减少中间结果集的行数，减轻后面行转列的负担
company_id
,service_id
,user_cnt
,SUM(days) days ---- 这样的人数总共有几天
FROM (SELECT usr.company_id
,usg.service_id
,COUNT(DISTINCT usg.user_id) user_cnt
,d.days
FROM service_usage usg
,service_users usr
,date_periods d ------ 用上面产生的刻度表连接回使用记录表，得到分段
WHERE usg.user_id = usr.user_id
AND usg.end_date>=TO_DATE(:p_start_date,'YYYYMMDD')
AND usg.start_date<=TO_DATE(:p_end_date,'YYYYMMDD')
AND usr.company_id = d.company_id
AND usg.service_id = d.service_id
AND d.period_date BETWEEN usg.start_date AND usg.end_date
AND d.days>0 ----- 过滤掉最后一行
GROUP BY usr.company_id
,usg.service_id
,d.period_date
,d.days
)
GROUP BY company_id,service_id,user_cnt
) u
,service_rates r
WHERE u.service_id = r.service_id
AND u.company_id = r.company_id
AND u.user_cnt BETWEEN r.user_count_min AND r.user_count_max ---- 属于哪一档收费标准
GROUP BY u.company_id
,u.service_id
) t
,companies c
,services s
WHERE t.company_id=c.company_id AND t.service_id=s.service_id
ORDER BY company_id,service_id;

复制代码

更进一步，有没有办法不将记录拆散呢？我们沿用上述的求区间思路，对这些区间进行观察。这些时间区间的划分，已经充分考虑了交叉的情况，保证了这段区间内人数是固定不变的。人数的变化发生在区间的首尾。通过观察，我们可以看到这些区间的首尾无非就是用户增加（如果日期来自start_date）或减少（如果日期来自end_date），如果用户A的一个使用记录被刻度表割成N段，那么该用户对人数的影响仅在首尾两段存在。我们把人数增加标记为+1的记录，减少则标记为-1的记录，如果对这些增减标记沿着时间轴来作一个逐行累计（利用分析函数SUM(flag) OVER(...)），则本例子中那些中间那些“不受影响”的区间会“继承”前面的累计结果（所谓不受影响是指不受用户A的影响，因为用户A还处于区间内。人数肯定是要变的，这是由形成区间的其他用户记录决定的），这个SUM就是我们想要的区间的人数。这个方法只需把每行使用记录拆成+1和-1的两行，而无需把刻度表连接回使用记录表。

这个方法还存在一点障碍，就是同一用户自己使用记录的交叉情况。因为+1/-1标记的累加是很盲目的，我们无从得知到底有多少用户留在本区间内。这就要求在实施上述方法之前，必需对用户使用记录做一个去重合并，即如果有用户发生了使用记录交叉的情况，必需先合并为连续记录。我们抛开所有其他用户的记录，只对单个用户自己切割形成的区间进行观察。如果没有交叠情况，则这个标记的累计值将会是1（开始）和0（结束）。如果发生了交叠，则这个累计值会升高，最后又逐渐回到0。那么我们只要把首尾的累计值为1和0的记录找出来，中间的都可以丢弃，这样就得到了一个合并后的区间。如果仅按日期排序累加，那么假如有两条记录恰好是同一天开始，则这个累计值将从2开始而不是1。为了辨别这种情况，我们在排序的时候特地加入一个唯一ID(下面用了ROWNUM)作为排序键，保证一定有一条记录是从1开始，从0结束。

下面就是这个思路的具体实现，merged_usage子查询是合并交叉区间的过程。

WITH merged_usage AS ( ------- 合并过的使用记录
SELECT *
FROM (SELECT service_id,user_id,flag,period_date
,SUM(flag) OVER(PARTITION BY service_id,user_id ORDER BY period_date,rn) sum_flag
FROM (SELECT usg.service_id
,usg.user_id
---- LEAST和GREATEST是排除输入区间外的日期
,(CASE WHEN rn=1 THEN GREATEST(usg.start_date,TO_DATE(:p_start_date,'YYYYMMDD'))
---- 下面end_date+1是为了把闭合区间转换为前闭后开区间，这样只需相减就可得出天数
ELSE LEAST(usg.end_date,TO_DATE(:p_end_date,'YYYYMMDD'))+1
END) period_date --- 区间日期用于计算天数。end_date+1是为了转换为前闭后开区间，这样只需相减就可得出天数
,(CASE WHEN rn=1 THEN 1 ELSE -1 END) flag ---- 起始为+1, 终止为-1
,ROWNUM rn
FROM service_usage usg
,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=2) --- 把一行拆成两行, rn=1起始，rn=2终止
WHERE usg.end_date>=TO_DATE(:p_start_date,'YYYYMMDD')---- 使用记录必需落在输入的起止日期的区间内
AND usg.start_date<=TO_DATE(:p_end_date,'YYYYMMDD')
) u
)
WHERE flag=1 AND sum_flag=1 OR flag=-1 AND sum_flag=0
)
,u2 AS (
------这一层聚合运算并不是必需的，只是试图减少中间结果的行数，减轻下次计算的负担
SELECT ------ 按公司、服务和日期进行聚合，算出这一段区间的用户增减数
usr.company_id
,usg.service_id
,usg.period_date
,SUM(usg.flag) AS net_flag_sum ---- 起始为+1, 终止为-1, 求和得出用户数增减数净值
FROM merged_usage usg
,service_users usr
WHERE usg.user_id = usr.user_id
GROUP BY usr.company_id
,usg.service_id
,usg.period_date
)
,u3 AS (
SELECT company_id
,service_id
----- 沿着时间轴截至当前的用户增减数逐行累计值，即该区间内的用户数
,SUM(net_flag_sum) OVER(PARTITION BY company_id,service_id ORDER BY period_date) user_cnt
---- 两行数据之间的日期相减得到区间的大小。
,LEAD(period_date) OVER(PARTITION BY company_id,service_id ORDER BY period_date) - period_date as days
FROM u2
)
,u4 AS (
------这一层聚合运算并不是必需的，只是试图减少中间结果的行数，减轻下次计算的负担
------仅仅在user_cnt有很多重复的时候才有意义
SELECT company_id
,service_id
,user_cnt
,SUM(days) AS days --- 这个人数的总天数
FROM u3
GROUP BY company_id
,service_id
,user_cnt
)
SELECT t.company_id
,c.company_name
,t.service_id
,s.service_name
,t.fee1
,t.fee2
,t.fee3
,t.fee4
,t.fee1+t.fee2+t.fee3+t.fee4 as total_fee
FROM (SELECT company_id,service_id
,NVL(MAX(DECODE(category_id,1,fee)),0) fee1
,NVL(MAX(DECODE(category_id,2,fee)),0) fee2
,NVL(MAX(DECODE(category_id,3,fee)),0) fee3
,NVL(MAX(DECODE(category_id,4,fee)),0) fee4
FROM (SELECT u.company_id ------- 这一层聚合运算是为了减少行转列的DECODE工作量。
,u.service_id
,r.category_id
,SUM(r.rate*u.user_cnt*u.days) fee
FROM u4 u
,service_rates r
WHERE u.service_id = r.service_id
AND u.company_id = r.company_id
AND u.user_cnt BETWEEN r.user_count_min AND r.user_count_max ---- 属于哪一档收费标准
GROUP BY u.company_id
,u.service_id
,r.category_id
)
GROUP BY company_id,service_id
) t
,companies c
,services s
WHERE t.company_id=c.company_id AND t.service_id=s.service_id
ORDER BY company_id,service_id
;