kiki-大地在我脚下

引用

sql分组统计语句（转载）

sql分组统计语句  

  1.报表合计专用的Rollup函数
         销售报表
  广州     1月      2000元
  广州     2月      2500元
  广州                 4500元
  深圳     1月      1000元
  深圳     2月      2000元
  深圳                 3000元
  所有地区         7500元

以往的查询SQL:
Select  area,month,sum(money) from SaleOrder group by area,month
然后广州，深圳的合计和所有地区合计都需要在程序里自行累计

1.其实可以使用如下SQL:   Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)就能产生和报表一模一样的纪录

 

2.如果year不想累加，可以写成   Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)   另外Oracle 9i还支持如下语法:   Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)

 

3.如果使用Cube(area,month)而不是RollUp(area,month)，除了获得每个地区的合计之外，还将获得每个月份的合计，在报表最后显示。

 

4.Grouping让合计列更好读
  RollUp在显示广州合计时，月份列为NULL，但更好的做法应该是显示为"所有月份"
  Grouping就是用来判断当前Column是否是一个合计列，1为yes，然后用Decode把它转为"所有月份"  Select  Decode(Grouping(area),1,'所有地区',area) area,
          Decode(Grouping(month),1,'所有月份',month),
          sum(money)
  From SaleOrder 
  Group by RollUp(area,month);

 

2.对多级层次查询的start with.....connect by
   比如人员组织,产品类别,Oracle提供了很经典的方法

SELECT LEVEL, name, emp_id,manager_emp_id
FROM employee
START WITH manager_emp_id is null
CONNECT BY PRIOR emp_id = manager_emp_id;

上面的语句demo了全部的应用,start with指明从哪里开始遍历树,如果从根开始,那么它的manager应该是Null,如果从某个职员开始,可以写成emp_id='11'
CONNECT BY 就是指明父子关系,注意PRIOR位置
另外还有一个LEVEL列,显示节点的层次

 

3.更多报表/分析决策功能
3.1 分析功能的基本结构
     分析功能() over( partion子句,order by子句,窗口子句)
     概念上很难讲清楚,还是用例子说话比较好.       

 

3.2 Row_Number 和 Rank, DENSE_Rank
    用于选出Top 3 sales这样的报表
    当两个业务员可能有相同业绩时,就要使用Rank和Dense_Rank
    比如
              金额    RowNum  Rank  Dense_Rank
    张三 4000元    1             1        1
    李四 3000元    2             2        2
    钱五 2000元    3             3        3
    孙六 2000元    4             3        3
    丁七 1000元    5             5        4

    这时,应该把并列第三的钱五和孙六都选进去,所以用Ranking功能比RowNumber保险.至于Desnse还是Ranking就看具体情况了。

    SELECT salesperson_id, SUM(tot_sales) sp_sales,
    RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank
    FROM orders
    GROUP BY salesperson_id

3.3 NTILE 把纪录平分成甲乙丙丁四等
        比如我想取得前25%的纪录,或者把25%的纪录当作同一个level平等对待,把另25%当作另一个Level平等对待

    SELECT cust_nbr, SUM(tot_sales) cust_sales,
    NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile
    FROM orders
    GROUP BY cust_nbr
    ORDER BY 3,2 DESC;NTITLE(4)把纪录以 SUM(tot_sales)排序分成4份.

 

3.4 辅助分析列和Windows Function
     报表除了基本事实数据外,总希望旁边多些全年总销量,到目前为止的累计销量,前后三个月的平均销量这样的列来参考.
    这种前后三个月的平均和到目前为止的累计销量就叫windows function, 见下例    SELECT month, SUM(tot_sales) monthly_sales,
           SUM(SUM(tot_sales)) OVER (ORDER BY month
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_preceeding
    FROM orders
    GROUP BY month
    ORDER BY month;



    SELECT month, SUM(tot_sales) monthly_sales,
           AVG(SUM(tot_sales)) OVER (ORDER BY month
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) rolling_avg  
    FROM orders
    GROUP BY month
    ORDER BY month;

    Windows Function的关键就是Windows子句的几个取值
    1 PRECEDING 之前的一条记录
    1 FOLLOWING 之后的一条记录
    UNBOUNDED PRECEDING 之前的所有记录
    CURRENT ROW 当前纪录

 

4.SubQuery总结
  SubQuery天天用了,理论上总结一下.SubQuery 分三种
  1.Noncorrelated 子查询   最普通的样式.
  2.Correlated Subqueries  把父查询的列拉到子查询里面去,头一回cyt教我的时候理解了半天.
  3.Inline View                           也被当成最普通的样式用了.

 

  然后Noncorrelated 子查询又有三种情况
  1.返回一行一列    where price < (select max(price) from goods )
  2.返回多行一列    where price>= ALL (select price from goods where type=2)
                          or where NOT price< ANY(select price from goods where type=2)
                              最常用的IN其实就是=ANY()
  3.返回多行多列    一次返回多列当然就节省了查询时间           UPDATE monthly_orders 
          SET (tot_orders, max_order_amt) =
             (SELECT COUNT(*), MAX(sale_price)
          FROM cust_order)

         
DELETE FROM line_item
          WHERE (order_nbr, part_nbr) IN
           (SELECT order_nbr, part_nbr FROM cust_order c)

  One night when my wife was preparing dinner,our little son took a piece of paper to her which read:For washing the car-$5.00

Playing with little sister-$0.25

Getting a good report card-$5.00

For sweeping the common corridor-$2.00

Total-$12.25

His mother looked at him stading there expecting payment.She picked up the pen and turning the paper over,this is what she wrote:For 10 months I carried you-No charge

For the night I sat up with you and prayed for you-No charge

For the toys,food and clothes-No charge

The full cost of my love-No charge

Whe he finished reading.he had big tears in his eyes.He looked at his mother and said."Mummy,I love you!",Then he took the pen and in great big letters wrote on the "bill":"All paid."

出了几次国，也收集了一些国家的货币，现在整理一下留个纪念。

04年6月份回国途中，在香港跟着Chris蹭了几份饭，实在是面子是过不去^_^。晚上换了100美刀的港币，还被HK 的Exchange收了近50港币的手续费,维港边拉着Chris喝了几瓶啤酒，剩下的就只有这些了.

 06年4月份从毛里求斯回国，在阿联酋迪拜的千禧年酒店给家里打了个国际长途,花了两美元(心疼啊。一分钟8块ＲＭＢ).偶给了100美元，前台的MM告诉偶没美元找，只好给偶迪拉姆。去机场上买了个Ｔ恤后迪拉姆又换成了美元。剩了点这些碎银子。

06年在毛里求斯出差时留的下的，当时留这点钱是用来坐公交车或同当地人拼黑车上班用的，装了满满一钱包，回国时本想花了，因为当地物价又高，这点钱啥也买不了，只好带回China了

06年5月份andy以色列出差回来给偶了两个留做纪念。

05年6月份孟加拉出差回国后留的，本来有Ｎ多钱的，当时感觉没啥用处，全部拿去送人了。只留了这两个破破烂烂的。

在孟加拉时，马来西亚华裔朋友tor送给我的马来货币

05年底哈尔滨朋友Eric从菲律宾回国时给偶寄了点当地的丝织品顺便加了张比索。（怎么不加个英磅泥？）

在迪拜机场等飞机时，和一个未识姓名的中国朋友聊了几次，顺便他送给偶的加纳货币做个纪念（看清楚是１００００块啊），吼吼，发财了。

不过换成人民币只值７块。^_$

April 29th, 2006

作者：Jon Bischke
原文地址：How to Have a 36 Hour Day

你经常听人说“真希望一天能多几个小时”或者类似的话吗？当然，现实中我们每天只有24小时。这么说吧，人和人怎样度过这24个小时是完全不同的。到现在这样的说法已经成了陈词滥调，但我们的24小时和Thomas Edison与Mother Theresa曾拥有的相同，和Oprah Winfrey与Bill Gates 今天拥有的也相同。就像老歌里唱的，“It’s in the way that you use it.”。

但是，我们怎么让一天多于24小时呢？

不可能？我不同意。我们不可能有多于24小时的实际时间，但很可能有多一些实用的时间。实际上，如果做一些相关的简单事务，我认为一天可能有36小时的实用时间。废话少说，如下是我的“一天36小时规则”。

这是一个有关节约时间的列表，你可能想到过也可能没有。遵循一部分规则，你很可能每天多出几个小时来。全部遵循这些规则，你会发现手头的时间很宽裕。这是个吸引人的话题，不是吗？这里的10个方法可以彻底改变你的生活，空余出那些你原以为无法节约的时间。

『一天36小时规则1：调整睡眠』
有些人每天只睡3到5个小时就可以（真让人羡慕），但另外一些人需要多于9小时才感觉休息好了。当然一部分原因是遗传和环境因素。我想说的是有办法让我们每个人都减少睡眠同时又不觉得疲倦。建议如下：

每天定时起床：我是从Steve Pavlina的很棒的blog知道这个方法的。我试了一下就迷上它了。这个方法很简单，只要让你的闹钟每天早晨定时响起来就好了。闹钟响了就起床，晚上困了再睡觉，不困就别上床。Steve说这个办法每周可以节约10到15个小时，我觉得他完全正确。

让你的卧室安静幽暗：很多人在卧室活动，光线亮声音吵。应该尽量减少卧室的音量（如果你的公寓或邻居很吵，试试空气净化器或者白噪声发生器）。逐步减少或消除睡觉时卧室的光线（厚的窗帘或者深色的材料效果很好）。还要尽可能的把带来刺激的东西（比如电视）拿出卧室。

体验小睡（polyphasic sleep）：小睡是一种将一天的睡眠降低到2至5小时的方法。我没有尝试过所以不知道是否可行，不过你可以再去Steve的blog看看这种不寻常的却又有潜在效果的睡眠方法。

优化睡眠节约的时间 ＝ 约1.5小时

『一天36小时规则2：优化食谱』

人在消化和排泄上花费的能量要比其他多的多。以饮食形式进入人体的一切直接影响了人的精力，同时也影响必要的睡眠时间。几年前我特别胖， 每天吸收大量卡路里和蛋白质。结果呢？我需要大量的睡眠。有时候睡10到11个小时（大运动量也没效果）。

现在的我正相反，我更合理（也更正确）的吃蔬菜、水果、谷物、肉类和油脂的混合食品。精力上的差异很明显，我比以前睡的少了。我的食谱仍需要改进，但它已经在我每一天每一个小时里改变着我。

优化食谱节约的时间 ＝ 约1.5小时

『一天36小时规则3：多线工作』

多线工作，看起来很正确。如果同时能做两件事，那一天下来就能做更多事情。但是它没有缺陷吗？一位在路上开车同时又打着电话的女士，可能不是多线工作的好例子。

不过我要说，如果做的好，多线工作是节约时间的最佳途径。边打电话边进行“不费脑”的活动是个好方法。对大多数人，洗衣服洗盘子就是一种不用思考的活动。为什么不在这同时打个电话，起到一石二鸟的作用呢？记住，收发邮件和看电视不是“不费脑”活动。而且，没什么事情比和一个心不在焉的家伙打电话更让人讨厌的了。

另一个多线工作的方法是把锻炼身体融入平时的活动里。要追上一个朋友，用慢跑的方法，同时也就完成了日常的运动。我读书或者坐在电脑前的时候经常伸懒腰，我有一个锻炼用的球，收邮件的时候拿出来玩玩，有点儿BT不过对我很有效。

有成千上万的办法，它们都能使你在日常事务过程中锻炼身体。这绝对是个提高效率并且保持好体型的好办法。

多线工作节约的时间 ＝ 约2小时

『一天36小时规则4：使自己井然有序』

你真应该让自己变得有条理，因为它节约时间并且减轻压力。这方面有很多不同的方法和策略，我发现其中最好的（也是我在用的）是David Allen的Getting Things Done方法。GTD，就是一个掌握和管理需要做和需要记住的事情。它非常有效，因为它能让你从杂乱的小事中解脱出来，释放你的“精神内存”，让你有时间思考更重要的事情，提高创造力。

David Allen的方法并不是唯一的。很多人用Franklin-Covey、Tony Robbin的生活管理方法或者其他什么。我不知道哪个最好，但我认为重要的是我们都应该采用一个这样的方法，那样的话，像“记得买牙膏”这样的事儿就不会消耗我们丁点的脑细胞。

同样，关于GTD也有成千上万的方法。了解这些方法的投资很大程度上是值得的。你不但会变得更积极，也会变得轻松，精力充沛，然后有一次增加了一天中的时间。

使自己井然有序节约的时间 ＝ 约2小时

『一天36小时规则5：提高打字速度』

在这个电脑时代，键盘是我们交流的主要形式。我估计一个人每天平均打字1到2小时，这是个毫无根据的猜想。打字，可能是发邮件、聊天、写备忘录、写报告等等。有些人可能多些，有些人则可能不到。所以假设人均1.5小时。

那么假设你现在每分钟输入40个单词，如果提高到60个，拿就节约了33%的打字时间。提高到80个每分钟就节约了50%。可能每天能节约半小时甚至45分钟。这样下来，一年十年（别说一辈子）能省下巨量的时间。

让人惊讶的是一方面我们在工作上花钱买一些有用的软件，另一方面很多人还不会盲打。这让我发疯。打字越快交流就越简单明了。键盘使你和这个世界交流和分享。

我强烈建议大家花点时间（每天几分钟）来提高打字速度。我用过一个软件TypingMaster，我也很喜欢。它很容易使用，甚至可以设置为追踪你的实时打字，这样它就会把你常漏掉的字词加入特别训练里。这对于每天的打字训练来说很节约时间。

提高打字速度节约的时间 ＝ 约0.75小时

『一天36小时规则6：提高阅读速度』

和打字一样，提高阅读速度同样会提高效率和节约时间。人和人不同，但是平均下来我们每天都要阅读一到两个小时。无论是早报、邮件、工作学习的文章或者是畅销书，这个时代需要我们不停的阅读。

事实是，大多数人的阅读习惯不够好。他们阅读速度慢，经常为了了解某些东西反复阅读。结果是要么不再读（读的太慢又无法领会内容只会让你不舒服），要么是在阅读上花了不必要的时间。

和打字一样，又多种方法提高阅读能力。我列出一些方法：

Active Reading－做不好阅读的一个原因是，当阅读时我们完全处于被动。大脑在活跃的时候更容易集中精力，做到这点最好的办法时记笔记。读书的时候做标记，划出段落、快速记笔记等等。你会发现自己的理解跟上了阅读速度（即使除了做标记的时间）。做标记的另一个重要作用是以后你回过头再读的时候，可以更加快速高效。

提高阅读速度节约的时间 ＝ 约0.75小时

『一天36小时规则7：使用音频学习』

我开始LearnOutLoud.com的学习过程，可能最重要的原因是我坚信使用音频教材确实可以增加时间。同样，也使你对那些过去浪费掉的低效率的时间充满乐趣和充实感。

使用音频也是多线工作的好例子。我每天都听几个小时的有声书或者podcasts。我在开车、锻炼或者在家做杂事的时候听。过去的一年我疯狂的听了大量的书，包括完整版的My Life（克林顿）和The World is Flat（Thomas Friedman），这些书我永远没时间坐下来读。同样，我还通过podcast“参加”了一些会议。

感谢iPod和其他一些MP3播放器，否则用音频学习就没这么容易了。我喜欢做的一件事是，出去跑步同时听一些podcast或者有声书。这对身体和头脑都是一个锻炼。

我坚信今后几年是用音频学习的革命。人们时间越来越少，一点点读自己需要的信息会消耗更多的时间。听是个绝好办法，同样会影响你的生活。

使用音频学习 节约的时间 ＝ 约1.5小时

『一天36小时规则8：善用软件』

选择正确的软件能节约大量时间。当然，不是所有软件都会节约你的时间，有些就很耗时间，比如扫雷：） 有些软件能给你每天、没月增加不少时间，下面是我发现的几个：

Cloudmark Spamblocker（或者其他反垃圾软件），清理垃圾邮件很浪费时间，所以用软件清除它们是很值得的。当然也可以选择GMAIL这样的邮件系统，它本身的过滤机制很有效。

Bloglines（或者其他RSS聚合工具），blog日益成为获得信息的最佳途径，通过聚合可以很容易的得到你订阅的信息。Bloglines甚至支持手机版本，很方便吧。

善用软件 节约的时间 ＝ 约0.5小时

『一天36小时规则9：看电视的时间减半』

有研究表明人均看电视时间是每天四小时。每天忙忙碌碌，居然花多于四个小时看电视？

我不否定所有电视节目，肯定有些节目能让人娱乐一下休息一下，但我不能理解一个人可以花一生中大部分时间，只是为了大多数时候没意义的事情。一年半前我取消了有线电视服务。注意一下自己在看什么节目，还有为什么看？如果你一天看四个小时，那减半就节约了两小时。重复一下，我认为有好节目而没有那么多好节目。

看电视的时间减半 节约的时间 ＝ 约2小时

『一天36小时规则9：向别人求助』

最后的规则是找人帮助自己。这与你的工作还有收入有关，假如你是500强公司的CEO你很容易找手下做这做那，又不用付钱。不过，其他人呢？

首先，人们是乐于帮助你的，别不信。如果要搬家，何不找朋友来帮忙，那样会省不少力气。

另一个可能性是和别人互助。比如你需要清扫工作，那你可以找一些英语不好需要帮助的人，作为交换对方帮你做清扫。这样你节约了时间，对方也受益，结果是双赢。

这样的机会很多只要你努力发现。当然有人求助的时候也要乐于帮忙，这对于节约的时间来说不是问题，不是吗？

另外有更好的省时办法。网上有很多免费或者收取少量费用的服务可以帮你，技术问题我贴到到Experts Exchange，很快就可以得到答案。非技术问题可以找Google Answers这样的服务，收取少量费用但是你会发现，和节约的时间相比是很值得的。

当然还有成千上万的论坛，那里的会有人免费为你解答问题。

向别人求助 节约的时间＝约0.5小时

把这些加起来，每天就可能会有12个小时。当然你会有不同的看法，不过我希望能试验一下。时间是这个星球上最珍贵的商品，在一些方面节约时间那你就可以花时间去作其他的事，那些对你最重要的，有目的的和感到高兴的事。如果大家都这么做，也许，会改变世界。

       不知最近我得罪了那路神仙，最近两天运气实在是不佳，周四早上去上班，天空中不知从那里飞了一坨类似鸟屎的东东，正好落到我胳膊上，郁闷，晚上一姐姐安慰我说可能是喜鹊吧，给你报喜的，我想就算你给我报喜也不用拉点屎到我身上吧，难道是它怕我不知道它来报喜，顾意留点信物给我，效仿孙悟空吗？

       周五，因一同事要离开我们的项目组，晚上一起去吃小肥羊，我们人多要了个包房，不知是包房比较闷，还是有点煤气泄漏，其它人都没事，只我一人感觉头晕，胸中感觉特别的堵，同事还开玩笑说，因为你减肥天天晚上不吃饭，突然吃了一次有点不适应。

       周六中午，和一同事去吃午饭，回来在楼上等电梯时，正好电梯门打开，我俩便进去，这时刚刚从电梯里出来的两个上海中年妇女又冲进电梯时，很紧张兮兮的用上海话乱叫，好像是‘有鬼呀，什么呀等等的乱叫’（听不懂上海话，声音上感觉有点像是这么说的），表情很夸张，搞的我和同事不知所从，真触霉头。

    晚上没事去南京路闲逛时买了五注七星彩，想到这两天连续遇到几件倒霉的事，是应该买张彩票冲冲霉头，还不知道能不能中，呵呵

        每次一到国外出差，都能瘦上不少，呆在国内一年半载也没啥反应，中国的土地比较肥沃啊。农业产量每年增长几个百分点，中国的胖子大军也一年多过一年。偶也是其中一员，上月被一姐姐笑话我半天，郁闷，为了不再被打击,.....

       现在切入正题，因为工作的原因常常坐在电脑前，最近项目又忙的要死，没空去锻炼，唯一的方法只能不吃饭了，饿了两个晚上，我是两腿反软，两眼发蓝光，第三天晚上实现受不了了，晚上10点多下楼买点方便面，想想肥还是要减，饭也不能不吃，找了个折中的方法，买了包40，50克的小碗的，买完单在回来的路上无意中想到了第一次在孟加拉出差时吃方便面，当天记着是和一山东的兄弟在机房加班错过了中午吃饭时间，回来不知道能吃点啥，山东的兄弟说请我吃方便面（不是因为他小气，当时的确没啥吃的）并告诉我，他老是加班没时间去餐厅吃饭，只好在一超市买了一编织袋的方便面，我当时第一反应是便宜也不能这样买啊，想轰抬物价，造成孟加拉经济危机啊，接下的话更吓人，每次要吃八包，我塞，这哥们身体超级魔鬼，没想到这么能吃，难不成都让他转化成化肥了洒到DHAKA这块贫脊的土地上吗来造福人类吗？估计不久的将来他会被当地人把他当龙王崇拜。

       见过方便面后才感觉到自己的无知，有如井底之蛙，大千世界，无奇不有，每包方便面和香皂大小，约20克，难怪这哥们能一次吃八包。不过我的确很心疼浪费八包方便面的塑料袋，在这里要有派头一点，一举一动都代表中国人的形像，所以我们专门打电话叫来当地厨师给我们泡方便面，由于已经是两年前的事情了，也忘了当时做的泡面好不好吃，反正添饱肚子先。不管怎么说，肚子是添饱了，接下来的工作还是需要厨师哥哥的帮助了，帮我们把碗洗干净顺便把桌子收拾干净，我们要做的事就是打个饱嗝，去外面晃悠。

     海外的生活有苦也有乐，比较怀念。在这祝同我在海外战斗过的兄弟们平平安安，健健康康，天天有方便面吃，每次一定不能吃少于八包，呵呵，不胡扯了。减肥ing...

                                                                                                             Write by kiki at 2006/8/31 11 PM