如果TABLE很大, 可以只SEARCH一次TABLE且只计算需要的RECORDS, 但有重复CODING. -looi500(looi) @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 专业技术讨论 / SQL问题有来了, 前些天把问题的前一半贴出来(#3261474), 在坛子里SQL高手们的帮助下, 获得了我自己觉得比较漂亮的结果, 今天我自己把后一半解决了, 可是怎么看怎么难看, 觉得应该有改进的余地, 再请大侠们看看 -poohbear(毛毛熊); 2006-10-16 {1277} (#3266538@0)
本文发表在 rolia.net 枫下论坛能不能改漂亮些...

原表其实没有ID, 有三个PK列 -- company, customer, year, 然后是m1 ... m12.

我现在想建一个新的表, 有四列 -- company, customer, cur_year_ytd, prev_year_ytd.

前一半(求和), 大侠们的建议都不错, 考虑到我自己的水平, 我选用了looi500和digitworm两位大侠的case方法. bluebluesky大侠的decode方法, 看起来更简洁一些, 可惜是Oracle专用的. 我们SQL Server不支持.

下面是我自己的solution. 可以运行, 结果也是正确的. 缺点是同样的求和code运行了两遍. 一则不好看, 二则运行速度可能慢, 特别是用到了full outer join. 数据量会很大. 虽然因为这段code是定时在夜间运行, 对performance要求不太高, 我自己还是看着它不舒服.

请高手们再帮帮忙...

select c.company company,
c.customer customer,
c.cur_year_ytd cur_year_ytd,
p.prev_year_ytd prev_year_ytd
into newtable
from
(
select company, customer,
case @numMon
when 1 then m1
when 2 then m1 + m2
...
when 12 then m1 + m2 + .... + m12
end as cur_year_ytd
from oldtable
where year = @cur_yr
) c
full outer join
(
select company, customer,
case @numMon
when 1 then m1
when 2 then m1 + m2
...
when 12 then m1 + m2 + .... + m12
end as prev_year_ytd
from oldtable
where year = @pre_yr
) p
on c.company = p.company and c.customer = p.customer
where c.cur_year_ytd > 0.00 or p.prev_year_ytd > 0.00更多精彩文章及讨论，请光临枫下论坛 rolia.net

我忽然意识到, 不必用outer join, 应该用inner join. -poohbear(毛毛熊); 2006-10-16 (#3266773@0)

是不是有些customer只有其中一年的数据？如果是，而且你要求这个顾客被输出，那么outer join没有错 -newkid(newkid); 2006-10-16 {741} (#3266796@0)
我不懂得SQL SERVER语法，不过：

select c.company company,
c.customer customer,
SUM(cur_year_ytd) cur_year_ytd,
SUM(prev_year_ytd) prev_year_ytd
into newtable
from
(
select company, customer,
CASE year
WHEN @cur_yr THEN
case @numMon
when 1 then m1
when 2 then m1 + m2
...
when 12 then m1 + m2 + .... + m12
end
ELSE 0
END as cur_year_ytd
,WHEN @pre_yr THEN
case @numMon
when 1 then m1
when 2 then m1 + m2
...
when 12 then m1 + m2 + .... + m12
end
ELSE 0
END as prev_year_ytd
from oldtable
where year = @cur_yr OR year = @pre_yr
) c
GROUP BY c.company,c.customer
HAVING SUM(cur_year_ytd) > 0.00 or SUM(prev_year_ytd) > 0.00

对啊, 是这个问题, 我当初选用outer join的时候是这么想的, 后来光想着怎么美化, 头一晕竟把原则忘了. 多谢提醒. 您改的code, 好象是可以的. 虽然篇幅没有简化, 但在逻辑上省掉了join. 我明天拿到DB上试试. -poohbear(毛毛熊); 2006-10-16 (#3266831@0)

如果建一个新的表 -looi500(looi); 2006-10-16 {392} (#3266956@0)
建议你
select company, customer, year, ytd
into newtable
from
(
select company, customer, year
case @numMon
when 1 then m1
when 2 then m1 + m2
...
when 12 then m1 + m2 + .... + m12
end as ytd
from oldtable
where year between @cur_yr and @pre_yr
)
然后在新表上做你要做下一步. 另外不应该有SUM(cur_year_ytd)因为根据你的描述一个CUSTOMER只应该有一个cur_year_ytd和prev_year_ytd

谢谢, 我的考虑是如果分两步走, 在第二步的时候要逐行search和update, 不划算. -poohbear(毛毛熊); 2006-10-16 (#3266970@0)
似乎你没看懂我的pivoting方法。经过处理后，每个顾客会有两行记录（假如两年都有数据），所以SUM是必须的。 -newkid(newkid); 2006-10-17 (#3267354@0)

是的. 你改的code我今天试过了, 仅改了几个很小的syntax, 运行很好, 结果也验证过了. 比我原先的join整齐多了. 多谢! 我现在仍在考虑的是, 能不能把那一大篇求和写成个参数, 省得重复两遍, 狗了半天也没找到. 不知道是不是真没办法? -poohbear(毛毛熊); 2006-10-17 (#3268484@0)

SQL Server 2000 +, 可用User Defined Function, 但performance 会有些影响 -sch2000(TonyBoy); 2006-10-17 (#3268693@0)

用函数的问题：如果把12个列全部作为参数传进去，也显得有点messy. 如果只传主键，在函数中重新读取数据，看起来也许整洁了，但效率又下降了。 -newkid(newkid); 2006-10-17 (#3268723@0)

看来只好这样了. 现在的code比我自己原先写的已经漂亮无数倍了. 再谢楼上的朋友们! -poohbear(毛毛熊); 2006-10-17 (#3268767@0)

看看这个，是不是你想要的结果？（已经经过测试） -digitworm(digitworm); 2006-10-17 {1039} (#3269009@0)
本文发表在 rolia.net 枫下论坛declare @curr_year smallint
declare @prev_year smallint
declare @m smallint

set @curr_year = Year(GetDate())
set @prev_year = @curr_year -1
set @m = Month(GetDate())

Select
company,
customer,
curr_ytd = sum(Case [year] When @curr_year Then ytd End),
prev_ytd = Sum(Case [year] When @prev_year Then ytd End)
from
(
Select
company,
customer,
[year],
ytd = case @m
when 1 then m1
when 2 then m1 + m2
when 3 then m1 + m2 + m3
when 4 then m1 + m2 + m3 + m4
when 5 then m1 + m2 + m3 + m4 + m5
when 6 then m1 + m2 + m3 + m4 + m5 + m6
when 7 then m1 + m2 + m3 + m4 + m5 + m6 + m7
when 8 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8
when 9 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9
when 10 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10
when 11 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11
When 12 Then m1 + m2+ m3 + m4 + m5+ m6 + m7 + m8 + m9 + m10 + m11 + m12
Else 0
end
from t1
) as ytd_tbl
Group by company, customer更多精彩文章及讨论，请光临枫下论坛 rolia.net

真的!! 我自己怎么就想不到哪! 谢谢! -- 到底省掉了一半重复code, 我现在想法是, 因为加和的时候没有having, where, case的限制, 把表里面所有的记录都处理了(比如2-5年前的), 是不是performance会差? 我明天试试. -poohbear(毛毛熊); 2006-10-17 (#3269023@0)

感觉你的要求还是有一定难度的，如果允许使用临时表，临时表+Join就容易多了 -digitworm(digitworm); 2006-10-17 (#3269075@0)

我想起来了, 其实在你的code末尾加上个where就行了 -poohbear(毛毛熊); 2006-10-17 (#3269091@0)

在from t1后面加上 Where [year] <= @prev_year -digitworm(digitworm); 2006-10-17 (#3269173@0)

yes! Thanks again! -poohbear(毛毛熊); 2006-10-17 (#3269176@0)

至于临时表, 我倒是知道最新的SQL Server支持临时表参数, 但是总觉得这种新DD, 各种DB不通用. 现在学会了, 下个项目换了DB就不能用了, 不如用些通用的SQL范畴的DD或更通用的逻辑,算法之类的.... -poohbear(毛毛熊); 2006-10-17 (#3269112@0)

加几个列就可以计算2-5年前的。要提高性能，首先加上一个组合索引(company+customer_year), 然后在Select语句的最后加上"Order By company, customer", 最后使用Execution Plan评估性能。 -digitworm(digitworm); 2006-10-17 (#3269140@0)

对不起我没说清楚, 我是不想处理2-5年前的(这个表最老的数据是5年), 不过我已经想到了, 我可以在求和的那个select里面加上个where, 这样就算egg里挑bone也无可挑剔了. --------- 再谢!!!! -poohbear(毛毛熊); 2006-10-17 (#3269162@0)

当然你可以按照你的要求随便修改, 本代码属于Open Source. ;-) -digitworm(digitworm); 2006-10-17 (#3269188@0)

不错！这段代码可是千锤百炼啊。不过我认为组合索引并不能在这里起作用。如果YEAR有索引，在子查询的WHERE中加上两年的过滤条件。还有个提醒：当心 NULL + 非NULL 产生的后果。如果全是 NOT NULL 列就当我没说。 -newkid(newkid); 2006-10-18 {257} (#3269437@0)
(case when @numMon>=1 and m1 is not null then m1 else 0 end)
+(case when @numMon>=2 and m2 is not null then m2 else 0 end)
+(case when @numMon>=3 and m3 is not null then m3 else 0 end)
............

这样就把NULL转换为0了。
可能有语法错误，将就着看吧。

You are right. I noticed this NULL issue before posted. To resolve it, use IsNull function for SQL Server. -digitworm(digitworm); 2006-10-18 {145} (#3269502@0)
Just change "sum(Case [year] When @curr_year Then ytd End)" to " IsNull(sum(Case [year] When @curr_year Then ytd End), 0) " for every year.

SUM对NULL反而不敏感了，我说的是 m1+m2+...+m12 这些地方。如果是 ORACLE, 其中任何一个 NULL 都会使整个和变成 NULL -newkid(newkid); 2006-10-18 (#3269515@0)

sorry, I misundstood your question. -digitworm(digitworm); 2006-10-18 (#3269737@0)

不错, 是千锤百炼. NULL的问题, 我查了原表, 没有NULL的值, 都是0.00, 可能是设了缺省值. 如果有NULL的话, SQL Server确实会出错(我记得Oracle是按0算的), 但可以用coalesce解决. digitworm的语法不象SQL Server的, -poohbear(毛毛熊); 2006-10-18 {226} (#3270581@0)
在SQL SERVER上要稍作修改, 目前还有一个影响美观的小问题是, 我想去掉两年都是0的客户, 可是SQL Server非强迫写where sum(Case [year] When @curr_year Then ytd End) > 0 or sum(Case [year] When @curr_year Then ytd End) > 0, 真是刹风景.

这就叫集体智慧的结晶。跟高手在一起讨论问题的同时自己也受益匪浅。 -digitworm(digitworm); 2006-10-18 {222} (#3271007@0)
受newkid Pivoting（行转列）想法的影响，加上原来Case表达式，最后用子查询解决长Case语句重复的问题，才得有此方案。

BTW，我是在SQL Server 2005上调试的，其它环境可能需要一些修改也很正常。

关于Where语句，好像没有shortcut。

原来是你的SQL Server太新, 我们的是2003. 还好, 在MS的产品里算是向下兼容不错的了. Where语句只好由它去了, 怪怪的, Oracle好象是可以用别名取代的. 还有, group by 一定要写ytd.company, ytd.customer... -poohbear(毛毛熊); 2006-10-18 (#3271063@0)

我写代码的时候是加上ytd别名前缀的，调试的时候发现去掉也行，当时也觉得有点奇怪，没想到是是2005的新特性. A surprise :-o -digitworm(digitworm); 2006-10-18 (#3271099@0)

如果TABLE很大, 可以只SEARCH一次TABLE且只计算需要的RECORDS, 但有重复CODING. -looi500(looi); 2006-10-19 {1732} (#3271636@0)
本文发表在 rolia.net 枫下论坛Tested in DB2:

Select
company,
customer_id,
sum(
Case year
when year(current date) then
(case month(current date)
when 1 then m1
when 2 then m1 + m2
when 3 then m1 + m2 + m3
when 4 then m1 + m2 + m3 + m4
when 5 then m1 + m2 + m3 + m4 + m5
when 6 then m1 + m2 + m3 + m4 + m5 + m6
when 7 then m1 + m2 + m3 + m4 + m5 + m6 + m7
when 8 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8
when 9 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9
when 10 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10
when 11 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11
else m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 + m12
end)
else 0
end
) as curr_ytd,
sum(
Case year
when year(current date) - 1 then
case month(current date)
when 1 then m1
when 2 then m1 + m2
when 3 then m1 + m2 + m3
when 4 then m1 + m2 + m3 + m4
when 5 then m1 + m2 + m3 + m4 + m5
when 6 then m1 + m2 + m3 + m4 + m5 + m6
when 7 then m1 + m2 + m3 + m4 + m5 + m6 + m7
when 8 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8
when 9 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9
when 10 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10
when 11 then m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11
else m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 + m12
end
else 0
end
) as prev_ytd
from db2devl.yourtable
where year between year(current date) -1 and year(current date)
Group by company, customer_id更多精彩文章及讨论，请光临枫下论坛 rolia.net

这就是问题所在. 如果有可能避免最好不要用这种聪明也奇怪的CODING. 不然的话没有人会喜欢你的CODES. -looi500(looi); 2006-10-18 {552} (#3269680@0)
TO POOHBEAR,
你之所以需要CODE繁琐的QUERY, 是因为这个原本的表结构不好. 如果用户需要三年, 四年的YTD, 你可以想象你的QUERY会变成什么样. 如果用户再要求某个月的数据和前几年比较, 你还是要用到繁琐的CASE语句.
建议:
1. 重建原来这个表 company, customer, year, month, amount.
2. 如果重建原来这个表影响太大, 使用UNION在原来这个表上建一个VIEW WITH company, customer, year, month, amount.
3. 建个临时表WITH company, customer, year, month, amount.

有了这个TABLE STRUCTURE你做任何QUERY就会很简单清晰. 对以后别人维护起来会很容易. 这一点对一个PROGRAMMER很重要.

如果我没猜错，原始数据应该是NOMALIZED的，这张表已经是存放计算结果了。这种设计有它的考虑：我们经常被要求输出年度报表，每个月一栏，那么这时候就显得有优势了。不管怎么设计，总会有输出的旋转问题（纵->横，或横->纵） -newkid(newkid); 2006-10-18 {119} (#3269789@0)
因此总是会用到一些技巧让程序不好读。
用UNION把一行分割成12行的办法，数据量一大就很不可取了，因为会多次扫描同一行数据。

根据POOLBEAR说的, 原始数据应该是已经在一个SQL SERVER的TABLE里了. 根据报表的格式设计数据库TABLE本身就不可取. -looi500(looi); 2006-10-18 {256} (#3270415@0)
只是想提醒POOLBEAR一下不能只在CODING上下工夫, APPLICATION, PROGRAMMING 和DATABASE DESIGN很重要. CODING要清晰明了, 这样别人也会喜欢. PROGRAM 做完了, 不是你个人的而是属于整个SUPPORT TEAM的.
除非是ONLINE SQL QUERY, 不然的话不要只想一个QUERY就做出一个REPORT.

你说的不可取应该是针对OLTP的设计而言。在数据仓库或决策支持系统，针对输出格式设计是很常见的。目的是为了事先存放计算结果改善报表响应时间。 -newkid(newkid); 2006-10-18 {87} (#3270516@0)
以我的猜测小熊的这张表是从更原始的数据(比如客户订单表)统计来的,这样的设计自有它的道理。

这个表确实是为输出设计的, 但是做这个表原因绝不是为输出方便, 而是为了performance, 因为source在另一个DB中, 而输出是web form, 况且原始表中的数据是月更新的. -- 方案不是我设计的, 但是我个人认为非常非常好. -poohbear(毛毛熊); 2006-10-18 (#3270690@0)

我4个sp出48个report. 都是跟几个表相关的, from 字句基本相同,where语句不太一样,select不太一样, 24个report 是detailed的,24个是summary的 -hard20(hard20); 2006-10-18 (#3271154@0)
有些分析师就喜欢直接看DB的表, 反正现在的DB GUI都不错, 这些人再会些简单的SQL, 会往EXCEL COPY/PASTE, 才不要看什么REPORT. 我们的VIEW啊, 次级表啊多是给这些人做的... 我做的这个表这也是个原因... -poohbear(毛毛熊); 2006-10-19 (#3271203@0)

多谢楼上几位. 表不是我设计的, 我不能改. newkid猜得不错, 我的source其实是别人产生的一个view, 不过原始表更不符合DB设计的一般规律 --- 没办法, 这是现实, business提出一个要求, IT就得限时赶出来, 质量远不如时间重要, 特别是 -poohbear(毛毛熊); 2006-10-18 {282} (#3270556@0)
看不到的质量. 等business试用好了, 就会急着进生产. 一旦进了生产, 就没有人愿意动它了 -- 万一把原先挺"好"的东西弄出了问题, 造成损失, 谁敢负责?

至于程序方面, 我其实更喜欢在程序里面操纵数据, 毕竟任何一种语言都比SQL功能强. 其实这个DD我已经用程序写过一遍了, 但是现在这一个必须用SQL...

@Florida

如果TABLE很大, 可以只SEARCH一次TABLE且只计算需要的RECORDS, 但有重复CODING.

Replies, comments and Discussions:

More Topics