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

lugionline · 发表于 2011-4-12 21:21

原帖由 rollingpig 于 2011-4-12 19:49 发表
两个表连接的效率未必比一个表+分析函数慢多少

这里这个特定的分析函数只需要一遍全表扫描（排序的前提下），所以应当会快，猜的

yzsind · 发表于 2011-4-12 21:27

[quote]原帖由 [i]jvkojvko[/i] 于 2011-4-12 20:24 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=17540040&ptid=1417576][img]http://www.itpub.net/images/common/back.gif[/img][/url]
评审代号：SQL4-4
注释和代码（满分15）：       14.9
思路（满分15）：       14.6
性能（满分10）：       5.6
完善-基本运行（30分）：       29.9
完善-日期交叉测试（30分）：       30
额外（奇思妙想附加分）：       0
违规扣分：       0
总计：       95

我的扣分也就在性能上 [/quote]

这是我的：

/*
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

评审代号：SQL4-29
注释和代码（满分15）：       11.25
思路（满分15）：       11
性能（满分10）：       3
完善-基本运行（30分）：       30
完善-日期交叉测试（30分）：       20
额外（奇思妙想附加分）：       0
违规扣分：
总计：       75.25

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

我太悲剧了，代码很简单，注释也有，为什么扣3.75分。
完善-日期交叉测试扣我10分，由于题目的歧义，我已经在注释里说明了。
我实际测试发现性能比高分的一些人也好很多，不知道为什么性能只有3分。

求评委解释一下啊。

[[i] 本帖最后由 yzsind 于 2011-4-12 21:28 编辑 [/i]]

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

原帖由 yzsind 于 2011-4-12 21:27 发表
我太悲剧了，代码很简单，注释也有，为什么扣3.75分。
完善-日期交叉测试扣我10分，由于题目的歧义，我已经在注释里说明了。
我实际测试发现性能比高分的一些人也好很多，不知道为什么性能只有3分

性能这个分数基本还算中等的，呵呵，这期的性能分数普遍不高，这个分数是根据多位评委的打分求和然后求平均数得到的。
日期交叉测试方面的扣分，我核实了一下，基本评委都给的这个分数，主要评分标准还是以结果为导向的，运行结果有不正确的情况，确实会被扣分的。具体交叉测试哪方面有问题，迟些等评委解答吧

newkid · 发表于 2011-4-12 21:56

原帖由 lugionline 于 2011-4-12 18:20 发表
啊。。。。。。。。

SQL4-27 不就是标准答案最终版的解法吗？怎么分数还这么低啊？

我看你很有当评委的潜质嘛，要不下次拉你入伙？
我特地把27找出来看了一遍，确实和我第三种很像，只是里面有两个自连接journal j1, journal j2和balance b1, balance b2我觉得可以去掉，难道MSSQL没有SUM() OVER()和LEAD,LAG函数？另外几个LEFT JOIN不知道为什么，题目明明有外键。

〇〇 · 发表于 2011-4-12 22:01

我们翻译的27

VAR p_start_date VARCHAR2(8);
VAR p_end_date VARCHAR2(8);
EXEC :p_start_date := '&&p_start_date';
EXEC :p_end_date := '&&p_end_date';
with
journal(usage_id, user_id, service_id, service_date, balance) as
(
select usage_id, user_id, service_id,
case when start_date < TO_DATE(:p_start_date,'YYYYMMDD') then 1
else start_date-TO_DATE(:p_start_date,'YYYYMMDD') +1 end service_date,
1 balance
from service_usage
where end_date >= TO_DATE(:p_start_date,'YYYYMMDD')
and start_date <= TO_DATE(:p_end_date,'YYYYMMDD')
union
select usage_id, user_id, service_id,
case when end_date > TO_DATE(:p_end_date,'YYYYMMDD')
then TO_DATE(:p_end_date,'YYYYMMDD')-TO_DATE(:p_start_date,'YYYYMMDD')
else end_date-TO_DATE(:p_start_date,'YYYYMMDD')+2 end service_date,
-1 balance
from service_usage
where end_date >= TO_DATE(:p_start_date,'YYYYMMDD')
and start_date <= TO_DATE(:p_end_date,'YYYYMMDD')
)
,
balance(company_id, service_id, service_date, balance) as
(
select service_users.company_id,
journal.service_id,
journal.service_date,
sum(journal.balance) balance
from
(
select j1.user_id, j1.service_id, j1.service_date,
min(j1.balance) balance, sum(j2.balance) accumulate
from journal j1, journal j2
where j1.user_id = j2.user_id
and j1.service_id = j2.service_id
and (j1.service_date > j2.service_date
or (j1.service_date = j2.service_date and j1.usage_id >= j2.usage_id))
group by j1.user_id, j1.service_id, j1.service_date, j1.usage_id
having (min(j1.balance) = 1 and sum(j2.balance) = 1)
or (min(j1.balance) = -1 and sum(j2.balance) = 0)
) journal left join service_users on journal.user_id = service_users.user_id
group by service_users.company_id, journal.service_id, journal.service_date
)
,
fee (company_id, service_id, category_id, balance, days, rate) as
(
select
company_summary.company_id,
company_summary.service_id,
service_rates.category_id,
company_summary.previous_balance,
company_summary.service_date - company_summary.previous_date days,
service_rates.rate
from
(
select b1.company_id, b1.service_id, b1.service_date,
b1.balance balance,
max(b2.service_date) previous_date,
sum(b2.balance) previous_balance
from balance b1, balance b2
where b1.company_id = b2.company_id
and b1.service_id = b2.service_id
and b1.service_date > b2.service_date
group by b1.company_id, b1.service_id, b1.service_date, b1.balance
) company_summary join service_rates
on company_summary.company_id = service_rates.company_id
and company_summary.service_id = service_rates.service_id
and company_summary.previous_balance >= service_rates.user_count_min
and company_summary.previous_balance <= service_rates.user_count_max
where company_summary.previous_date is not null
)
,
summary(company_id, company_name, service_id, service_name, category_id, fee) as
(
select fee.company_id,
companies.company_name,
fee.service_id,
services.service_name,
fee.category_id,
fee.fee from
(
select fee.company_id,
fee.service_id,
category_id,
sum(balance * days * rate) fee
from fee
group by fee.company_id, fee.service_id, category_id
) fee
left join companies on fee.company_id = companies.company_id
left join services on fee.service_id = services.service_id
)
select company_id, company_name,
service_id, service_name,
sum(decode(category_id,1,fee)) fee1,
sum(decode(category_id,2,fee)) fee2,
sum(decode(category_id,3,fee)) fee3,
sum(decode(category_id,4,fee)) fee4,
sum(fee) feetotal
from summary
group by company_id, company_name,
service_id, service_name
;

复制代码

newkid · 发表于 2011-4-12 22:14

关于是否去除同一个人的重复记录，OO在35楼已经回答了：
http://www.itpub.net/thread-1411495-4-1.html

lugionline · 发表于 2011-4-12 22:20

那就要多谢谢你了

MSSQL 有SUM Over，但只能按partition后的结果sum，不能做partition同时 order by,所以累计汇总就只能是做自连接，然后找到比当前记录小的所有记录汇总，这就会慢很多。据我所知一句SQL的情况只能这么做，别无它法，但是实际应用，我完全可以弄个临时表，然后用游标遍历记录，虽然游标有点慢，但是比自连接还是快出很多。

left join是为了取得对应的名称吧，我想最后的summary结果会比较少，所以没仔细比较对性能的影响

当评委还是不敢啊，看别人的代码会吐的

，几位评委这次肯定是严重内伤啊，哈哈

regonly1 · 发表于 2011-4-12 22:38

事先说明各位评委的确是辛苦的，尤其是newkid大侠，呵呵，还有每个语句的点评。
但是我想，如果有题意变更的地方，摆在35楼或者N楼的说明明显不是所有人会看到的，缺乏正式性。
虽然我看了我的解答主要扣分不在这个上面（注释被扣10分，是的，我一点注释也没有）：
评审代号：SQL4-17
注释和代码（满分15）：       5
思路（满分15）：       11.5
性能（满分10）：       7.8
完善-基本运行（30分）：       28.5
完善-日期交叉测试（30分）：       22.8
额外（奇思妙想附加分）：
违规扣分：       0
总计：       75.6

原帖由 newkid 于 2011-4-12 22:14 发表
关于是否去除同一个人的重复记录，OO在35楼已经回答了：
http://www.itpub.net/thread-1411495-4-1.html

newkid · 发表于 2011-4-12 22:50

原帖由 regonly1 于 2011-4-12 22:38 发表
事先说明各位评委的确是辛苦的，尤其是newkid大侠，呵呵，还有每个语句的点评。
但是我想，如果有题意变更的地方，摆在35楼或者N楼的说明明显不是所有人会看到的，缺乏正式性。
虽然我看了我的解答主要扣分不在这个上面（注释被扣10分，是的，我一点注释也没有）：
评审代号：SQL4-17
注释和代码（满分15）：       5
思路（满分15）：       11.5
性能（满分10）：       7.8
完善-基本运行（30分）：       28.5
完善-日期交叉测试（30分）：       22.8
额外（奇思妙想附加分）：
违规扣分：       0
总计：       75.6

谢谢你的包容和理解！
我自己曾经作出了一个和OO相反的回答，后来发现之后赶快纠正，我自己也发PM给两位看到我的回答的选手，并且以为前面的选手都已看到OO的说明。确实，当初应该让贝贝发PM通知每个选手。

jiqing1004 · 发表于 2011-4-12 22:57

恶，第四题竟然看错题目了～～

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

回复 #34 newkid 的帖子

浏览过的版块