×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

这就是问题所在. 如果有可能避免最好不要用这种聪明也奇怪的CODING. 不然的话没有人会喜欢你的CODES.

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很重要.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / SQL问题有来了, 前些天把问题的前一半贴出来(#3261474), 在坛子里SQL高手们的帮助下, 获得了我自己觉得比较漂亮的结果, 今天我自己把后一半解决了, 可是怎么看怎么难看, 觉得应该有改进的余地, 再请大侠们看看
    本文发表在 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.
      • 是不是有些customer只有其中一年的数据?如果是,而且你要求这个顾客被输出,那么outer join没有错
        我不懂得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上试试.
    • 如果建一个新的表
      建议你
      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, 不划算.
      • 似乎你没看懂我的pivoting方法。经过处理后,每个顾客会有两行记录(假如两年都有数据),所以SUM是必须的。
        • 是的. 你改的code我今天试过了, 仅改了几个很小的syntax, 运行很好, 结果也验证过了. 比我原先的join整齐多了. 多谢! 我现在仍在考虑的是, 能不能把那一大篇求和写成个参数, 省得重复两遍, 狗了半天也没找到. 不知道是不是真没办法?
          • SQL Server 2000 +, 可用User Defined Function, 但performance 会有些影响
            • 用函数的问题:如果把12个列全部作为参数传进去,也显得有点messy. 如果只传主键,在函数中重新读取数据,看起来也许整洁了,但效率又下降了。
              • 看来只好这样了. 现在的code比我自己原先写的已经漂亮无数倍了. 再谢楼上的朋友们!
          • 看看这个,是不是你想要的结果? (已经经过测试)
            本文发表在 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会差? 我明天试试.
              • 感觉你的要求还是有一定难度的,如果允许使用临时表,临时表+Join就容易多了
                • 我想起来了, 其实在你的code末尾加上个where就行了
                  • 在from t1后面加上 Where [year] <= @prev_year
                    • yes! Thanks again!
                • 至于临时表, 我倒是知道最新的SQL Server支持临时表参数, 但是总觉得这种新DD, 各种DB不通用. 现在学会了, 下个项目换了DB就不能用了, 不如用些通用的SQL范畴的DD或更通用的逻辑,算法之类的....
              • 加几个列就可以计算2-5年前的。要提高性能,首先加上一个组合索引(company+customer_year), 然后在Select语句的最后加上"Order By company, customer", 最后使用Execution Plan评估性能。
                • 对不起我没说清楚, 我是不想处理2-5年前的(这个表最老的数据是5年), 不过我已经想到了, 我可以在求和的那个select里面加上个where, 这样就算egg里挑bone也无可挑剔了. --------- 再谢!!!!
                  • 当然你可以按照你的要求随便修改, 本代码属于Open Source. ;-)
                    • 不错!这段代码可是千锤百炼啊。不过我认为组合索引并不能在这里起作用。如果YEAR有索引,在子查询的WHERE中加上两年的过滤条件。还有个提醒:当心 NULL + 非NULL 产生的后果。如果全是 NOT NULL 列就当我没说。
                      (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.
                        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
                          • sorry, I misundstood your question.
                      • 不错, 是千锤百炼. NULL的问题, 我查了原表, 没有NULL的值, 都是0.00, 可能是设了缺省值. 如果有NULL的话, SQL Server确实会出错(我记得Oracle是按0算的), 但可以用coalesce解决. digitworm的语法不象SQL Server的,
                        在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, 真是刹风景.
                        • 这就叫集体智慧的结晶。跟高手在一起讨论问题的同时自己也受益匪浅。
                          受newkid Pivoting(行转列)想法的影响,加上原来Case表达式,最后用子查询解决长Case语句重复的问题,才得有此方案。

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

                          关于Where语句,好像没有shortcut。
                          • 原来是你的SQL Server太新, 我们的是2003. 还好, 在MS的产品里算是向下兼容不错的了. Where语句只好由它去了, 怪怪的, Oracle好象是可以用别名取代的. 还有, group by 一定要写ytd.company, ytd.customer...
                            • 我写代码的时候是加上ytd别名前缀的,调试的时候发现去掉也行,当时也觉得有点奇怪,没想到是是2005的新特性. A surprise :-o
            • 如果TABLE很大, 可以只SEARCH一次TABLE且只计算需要的RECORDS, 但有重复CODING.
              本文发表在 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.
          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的,这张表已经是存放计算结果了。这种设计有它的考虑:我们经常被要求输出年度报表,每个月一栏,那么这时候就显得有优势了。不管怎么设计,总会有输出的旋转问题(纵->横,或横->纵)
            因此总是会用到一些技巧让程序不好读。
            用UNION把一行分割成12行的办法,数据量一大就很不可取了,因为会多次扫描同一行数据。
            • 根据POOLBEAR说的, 原始数据应该是已经在一个SQL SERVER的TABLE里了. 根据报表的格式设计数据库TABLE本身就不可取.
              只是想提醒POOLBEAR一下不能只在CODING上下工夫, APPLICATION, PROGRAMMING 和DATABASE DESIGN很重要. CODING要清晰明了, 这样别人也会喜欢. PROGRAM 做完了, 不是你个人的而是属于整个SUPPORT TEAM的.
              除非是ONLINE SQL QUERY, 不然的话不要只想一个QUERY就做出一个REPORT.
              • 你说的不可取应该是针对OLTP的设计而言。在数据仓库或决策支持系统,针对输出格式设计是很常见的。目的是为了事先存放计算结果改善报表响应时间。
                以我的猜测小熊的这张表是从更原始的数据(比如客户订单表)统计来的,这样的设计自有它的道理。
                • 这个表确实是为输出设计的, 但是做这个表原因绝不是为输出方便, 而是为了performance, 因为source在另一个DB中, 而输出是web form, 况且原始表中的数据是月更新的. -- 方案不是我设计的, 但是我个人认为非常非常好.
              • 我4个sp出48个report. 都是跟几个表相关的, from 字句基本相同,where语句不太一样,select不太一样, 24个report 是detailed的,24个是summary的
              • 有些分析师就喜欢直接看DB的表, 反正现在的DB GUI都不错, 这些人再会些简单的SQL, 会往EXCEL COPY/PASTE, 才不要看什么REPORT. 我们的VIEW啊, 次级表啊多是给这些人做的... 我做的这个表这也是个原因...
          • 多谢楼上几位. 表不是我设计的, 我不能改. newkid猜得不错, 我的source其实是别人产生的一个view, 不过原始表更不符合DB设计的一般规律 --- 没办法, 这是现实, business提出一个要求, IT就得限时赶出来, 质量远不如时间重要, 特别是
            看不到的质量. 等business试用好了, 就会急着进生产. 一旦进了生产, 就没有人愿意动它了 -- 万一把原先挺"好"的东西弄出了问题, 造成损失, 谁敢负责?

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