×

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

如果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
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...