手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表全部文章

一句可以围观的SQL

窝窝(Walkerlee)最近说他折腾了一个SQL,提升了他的工作效率。表示要到博客来投稿,今天我把这个SQL放出来了。
嗯。我总不能说,因为我确实会这个SQL就断了他投稿的心,否则他会砍我的。

前因:
数据库结构如下:

要求:一句SQL实现,分别统计type1,2,3,4 一共多少money (group by uid)

原先做法:分开查询 然后用php合并结果,如

SQL代码
  1. select sum(money) form test where type = 1 group by uid  
  2. select sum(money) form test where type = 2 group by uid  
  3. select sum(money) form test where type = 3 group by uid  
  4. select sum(money) form test where type = 4 group by uid  

现在要求一句SQL。
窝窝问我的时候,我说if(a,b,c)这个函数可以解决,于是他很愤怒。。。。
贴出了最终的SQL语句:

SQL代码
  1. SELECT  
  2. uid,  
  3. SUM( IF(`type`=1, money, 0 ) ) AS `BuyByCash`,  
  4. SUM( IF (`type`=3, money, 0 ) ) AS `BuyByCard`,  
  5. SUM( IF( `type` IN (1,3), money, 0 ) ) AS `BuyByCashAndCard`,  
  6. SUM( IF(`type`=2, money, 0 ) ) AS `SellByCash`,  
  7. SUM( IF(`type`=4, money, 0 ) ) AS `SellByCard`,  
  8. SUM( IF( `type` IN (2,4), money, 0 ) ) AS `BuyByCashAndCard`,  
  9. SUM(money) AS `Amount`,  
  10. COUNT(*) AS `DataNums`  
  11. FROM `test`   
  12. group by `uid`  

窝窝比较郁闷,因为以前处理起来很麻烦,而现在一句就解决了。


此事完结后,他又介绍了一个小技巧:
我有一个relation表,用来存储订单下面的游客

SQL代码
  1. ALTER TABLE `travel_order_customer_relation`  
  2.   ADD CONSTRAINT `travel_order_customer_relation_ibfk_1` FOREIGN KEY (`oid`) REFERENCES `travel_orders` (`id`) ON DELETE CASCADE

然后我用了一个外键,这样order被删除时候,这个关系表里面的相关数据也就自动删除了
----
然后我也很郁闷的说,我的数据表不是INNODB的,所以这个功能我不能用。。。也很纠结

Tags: mysql, group, sum, if

由一个笑话看需求理解

这,是一个笑话:

突然让我想起两年前的那个图,产品经理拿到一个要做秋千的需求,经过层层转达,最后做出一个十不象来,事实上就是人们所说的,每一次信息传递,都会丢失大约20%左右的信息,当信息传达次数过多的时候,需求和结果就已经很偏离方向了。
上海以前有一个节目《智力大冲浪》,其中有一个游戏就是COPY不走样,但事实上这真的不太可能。
在实际工作中,往往都是老板说要做一个什么东西,然后产品经理进行规划,事实上这时候已经可能偏了大约10%左右。然后产品经理规划好了,找开发主管讨论,这时候又偏了10%左右。开发主管找小弟把需求复述一遍,需求还剩70%,这时候根据开发人员的水平来划分:
1、特别好的开发人员能够保证60%的准确度+15%的自己理解(可能会有实际偏差也可能就正好把需求损失的补回来)
2、一般的开发人员,能够保证60%的完成度
3、稍差一点的开发人员,能够保证50%
4、差一点的,30%
5、如果连程序都写不完的,就不谈百分比了。。。。

细想想,难道不正是这样?

儿童咳嗽,不要总当感冒治

也是一个转贴啊,主要是我们家的小朋友也有点这种症状,如果有相同类似的小朋友们可以看看。当然我也不知道这是对还是错。。。

原文来自:http://geek.techweb.com.cn/viewthread.php?tid=231886
引起儿童慢性咳嗽的原因很多,过敏是重要原因之一。

过敏性咳嗽患儿的表现:

?长期咳嗽,久治不见好转;

?一般在夜间或清晨发作,白天通常不咳嗽或很少咳嗽;

?过敏性咳嗽一般不发烧,吐白色泡沫痰,多以咳嗽为主,不喘。

?咳三阵是它的最大特点,晚上睡觉前咳一阵,半夜醒来咳一阵,早上醒来咳一阵,有的儿童一连咳上3个月;

?过敏性咳嗽的孩子喜欢揉眼睛和鼻子,爱抓头皮,睡觉时爱出汗、不安分,喜欢蜷曲着睡。

治疗误区:

孩子出现过敏性咳嗽时,父母的第一反应往往还是“孩子又感冒了”,并把它当成感冒而一味使用抗生素和止咳药治疗,但实际上不对证的治疗是不可能获得良好效果的。咳嗽时间长了,患儿可以发展成典型的支气管哮喘。

正确治疗:

?在季节交替、气温骤变时,父母应尽量为孩子防寒保暖,避免着凉、感冒;

小快克的一分为二设计其实就是将一整袋药品分为两个相等的半份,这样便可以使年轻父母可以更好的掌握用药剂量。小快克的设计受到了广大年轻父母的欢迎,这无疑也是给那些不负责任的厂家好好的上了一课。

其实宝宝作为一家人中最受爱护的对象,从出生开始便会集万千宠爱于一身,很多的父母长辈都希望宝宝可以健康快乐的成长。如果宝宝受到病痛的伤害与侵扰,那将是全家人的烦心事,尤其是在选择用药剂量上,一直以来都困扰着很多的父母。

?避开过敏源,家里不要养宠物和养花,不要铺地毯,避免接触花粉、尘螨、油烟、油漆等,避免食用会引起过敏症状的食物,如海产品、冷饮等;

Tags: 咳嗽

phpstorm 函数提示

其实我一直以为phpstorm的函数提示只能提示参数的,但最近我在把菜单一一看完的时候才发现,原来,它真的可以提示函数的详细信息。
说白了, 就是将phpdoc这一块的内容全部提示出来。具体怎么操作呢?
在macosx上,快捷键是F1或者ctrl+j
其他平台上未知,但没有关系,可以通过keymap来看一下定义的是什么快捷键,也可以看这个:

看那个QuickDocumentation,就知道了。
因为菜单里的提示只是默认的,所以如果有多种快捷键,还是看keymap的定义吧。比如我现在的定义是:

Tags: phpstorm

转:apache一个优化小技巧

determine the MaxClient

決定 web server 效能一個最重要的因素就是記憶體的量,而藉由調整 MaxClient,可以避免 Apache 產生過多無用的 child process。這個數字到底要調到多少,老實說也沒有一個標準答案,網路上大家比較建議的算法都是用機器有的記憶體和每個 child process 的 size 去做粗估。但事實上,要估計 apache child process 的 size 也不是一件容易的事,如果你用 ps aux 這個指令去看,出來的 VSZ or RSS value,其實都會把 shared memory 重覆記算(詳細內容請參考: Understanding memory usage on Linux , Memory usage determination with the ps command ),所以後來我採用的作法是:先將 MaxClent 設成一個比較小的值 (e.g. 30),然後再定期去看 error log 是否有 run out of clients 的情形,如果有的話,就再幫 MaxClients 加個 5。

原文来自:http://brooky.cc/2011/06/30/practical-guide-on-setup-wordpress-on-amazon-ec2-free-tier/

disable “AllowOverride”

 

理論上,AllowOverride 會造成 Apache 在路徑的每一層去找找看是否有 .htaccess 這個 file。
例如說:如果 virtual host 的設定是:

DocumentRoot /www/htdocs AllowOverride all

那任何一個 /index.html 的 request, Apache 都會試著去找 /.htaccess, /www/.htaccess, and /www/htdocs/.htaccess.
所以除非必要,是可以將 AllowOverride 設成 None.

但如果你用了 wordpress Pretty Permalinks”這個功能,那就會需要 rewrite rule 的功能。在這種情況下,可以考慮將 AllowOverride 的設定直接寫在 config 檔裡。

----------
上文中的链接:Memory usage determination with the ps command

The ps command can also be used to monitor memory usage of individual processes.

The ps v PID command provides the most comprehensive report on memory-related statistics for an individual process, such as:

  • Page faults
  • Size of working segment that has been touched
  • Size of working segment and code segment in memory
  • Size of text segment
  • Size of resident set
  • Percentage of real memory used by this process
The following is an example:
# ps v 
PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
 36626 pts/3 A 0:00 0 316 408 32768 51 60 0.0 0.0 ps v

The most important columns on the resulting ps report are described as follows:

PGIN
Number of page-ins caused by page faults. Since all I/O is classified as page faults, this is basically a measure of I/O volume.
SIZE
Virtual size (in paging space) in kilobytes of the data section of the process (displayed as SZ by other flags). This number is equal to the number of working segment pages of the process that have been touched times 4. If some working segment pages are currently paged out, this number is larger than the amount of real memory being used. SIZE includes pages in the private segment and the shared-library data segment of the process.
RSS
Real-memory (resident set) size in kilobytes of the process. This number is equal to the sum of the number of working segment and code segment pages in memory times 4. Remember that code segment pages are shared among all of the currently running instances of the program. If 26 ksh processes are running, only one copy of any given page of the ksh executable program would be in memory, but the ps command would report that code segment size as part of the RSS of each instance of the ksh program.
TSIZ
Size of text (shared-program) image. This is the size of the text section of the executable file. Pages of the text section of the executable program are only brought into memory when they are touched, that is, branched to or loaded from. This number represents only an upper bound on the amount of text that could be loaded. The TSIZ value does not reflect actual memory usage. This TSIZ value can also be seen by executing the dump -ov command against an executable program (for example, dump -ov /usr/bin/ls).
TRS
Size of the resident set (real memory) of text. This is the number of code segment pages times 4. This number exaggerates memory use for programs of which multiple instances are running. The TRS value can be higher than the TSIZ value because other pages may be included in the code segment such as the XCOFF header and the loader section.
%MEM
Calculated as the sum of the number of working segment and code segment pages in memory times 4 (that is, the RSS value), divided by the size of the real memory in use, in the machine in KB, times 100, rounded to the nearest full percentage point. This value attempts to convey the percentage of real memory being used by the process. Unfortunately, like RSS, it tends the exaggerate the cost of a process that is sharing program text with other processes. Further, the rounding to the nearest percentage point causes all of the processes in the system that have RSS values under 0.005 times real memory size to have a %MEM of 0.0.
Note: The ps command does not indicate memory consumed by shared memory segments or memory-mapped segments. Because many applications use shared memory or memory-mapped segments, the svmon command is a better tool to view the memory usage of these segments.
-----------
第二篇:http://virtualthreads.blogspot.com/2006/02/understanding-memory-usage-on-linux.html

这篇文章来自blogspot,一般情况下打不开,所幸我用Read it later,直接用广本模式,让他帮我打开了。HOHO,有点长,慢慢看:

This entry is for those people who have ever wondered, "Why the hell is a simple KDE text editor taking up 25 megabytes of memory?" Many people are led to believe that many Linux applications, especially KDE or Gnome programs, are "bloated" based solely upon what tools like ps report. While this may or may not be true, depending on the program, it is not generally true -- many programs are much more memory efficient than they seem.

What ps reports
The ps tool can output various pieces of information about a process, such as its process id, current running state, and resource utilization. Two of the possible outputs are VSZ and RSS, which stand for "virtual set size" and "resident set size", which are commonly used by geeks around the world to see how much memory processes are taking up.

For example, here is the output of ps aux for KEdit on my computer:


USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
dbunker 3468 0.0 2.7 25400 14452 ? S 20:19 0:00 kdeinit: kedit

According to ps, KEdit has a virtual size of about 25 megabytes and a resident size of about 14 megabytes (both numbers above are reported in kilobytes). It seems that most people like to randomly choose to accept one number or the other as representing the real memory usage of a process. I'm not going to explain the difference between VSZ and RSS right now but, needless to say, this is the wrong approach; neither number is an accurate picture of what the memory cost of running KEdit is.

Why ps is "wrong"
Depending on how you look at it, ps is not reporting the real memory usage of processes. What it is really doing is showing how much real memory each process would take up if it were the only process running. Of course, a typical Linux machine has several dozen processes running at any given time, which means that the VSZ and RSS numbers reported by ps are almost definitely "wrong". In order to understand why, it is necessary to learn how Linux handles shared libraries in programs.

Most major programs on Linux use shared libraries to facilitate certain functionality. For example, a KDE text editing program will use several KDE shared libraries (to allow for interaction with other KDE components), several X libraries (to allow it to display images and copy and pasting), and several general system libraries (to allow it to perform basic operations). Many of these shared libraries, especially commonly used ones like libc, are used by many of the programs running on a Linux system. Due to this sharing, Linux is able to use a great trick: it will load a single copy of the shared libraries into memory and use that one copy for every program that references it.

For better or worse, many tools don't care very much about this very common trick; they simply report how much memory a process uses, regardless of whether that memory is shared with other processes as well. Two programs could therefore use a large shared library and yet have its size count towards both of their memory usage totals; the library is being double-counted, which can be very misleading if you don't know what is going on.

Unfortunately, a perfect representation of process memory usage isn't easy to obtain. Not only do you need to understand how the system really works, but you need to decide how you want to deal with some hard questions. Should a shared library that is only needed for one process be counted in that process's memory usage? If a shared library is used my multiple processes, should its memory usage be evenly distributed among the different processes, or just ignored? There isn't a hard and fast rule here; you might have different answers depending on the situation you're facing. It's easy to see why ps doesn't try harder to report "correct" memory usage totals, given the ambiguity.

Seeing a process's memory map
Enough talk; let's see what the situation is with that "huge" KEdit process. To see what KEdit's memory looks like, we'll use the pmap program (with the -d flag):


Address Kbytes Mode Offset Device Mapping
08048000 40 r-x-- 0000000000000000 0fe:00000 kdeinit
08052000 4 rw--- 0000000000009000 0fe:00000 kdeinit
08053000 1164 rw--- 0000000008053000 000:00000 [ anon ]
40000000 84 r-x-- 0000000000000000 0fe:00000 ld-2.3.5.so
40015000 8 rw--- 0000000000014000 0fe:00000 ld-2.3.5.so
40017000 4 rw--- 0000000040017000 000:00000 [ anon ]
40018000 4 r-x-- 0000000000000000 0fe:00000 kedit.so
40019000 4 rw--- 0000000000000000 0fe:00000 kedit.so
40027000 252 r-x-- 0000000000000000 0fe:00000 libkparts.so.2.1.0
40066000 20 rw--- 000000000003e000 0fe:00000 libkparts.so.2.1.0
4006b000 3108 r-x-- 0000000000000000 0fe:00000 libkio.so.4.2.0
40374000 116 rw--- 0000000000309000 0fe:00000 libkio.so.4.2.0
40391000 8 rw--- 0000000040391000 000:00000 [ anon ]
40393000 2644 r-x-- 0000000000000000 0fe:00000 libkdeui.so.4.2.0
40628000 164 rw--- 0000000000295000 0fe:00000 libkdeui.so.4.2.0
40651000 4 rw--- 0000000040651000 000:00000 [ anon ]
40652000 100 r-x-- 0000000000000000 0fe:00000 libkdesu.so.4.2.0
4066b000 4 rw--- 0000000000019000 0fe:00000 libkdesu.so.4.2.0
4066c000 68 r-x-- 0000000000000000 0fe:00000 libkwalletclient.so.1.0.0
4067d000 4 rw--- 0000000000011000 0fe:00000 libkwalletclient.so.1.0.0
4067e000 4 rw--- 000000004067e000 000:00000 [ anon ]
4067f000 2148 r-x-- 0000000000000000 0fe:00000 libkdecore.so.4.2.0
40898000 64 rw--- 0000000000219000 0fe:00000 libkdecore.so.4.2.0
408a8000 8 rw--- 00000000408a8000 000:00000 [ anon ]
... (trimmed) ...
mapped: 25404K writeable/private: 2432K shared: 0K

I cut out a lot of the output; the rest is similar to what is shown. Even without the complete output, we can see some very interesting things. One important thing to note about the output is that each shared library is listed twice; once for its code segment and once for its data segment. The code segments have a mode of "r-x--", while the data is set to "rw---". The Kbytes, Mode, and Mapping columns are the only ones we will care about, as the rest are unimportant to the discussion.

If you go through the output, you will find that the lines with the largest Kbytes number are usually the code segments of the included shared libraries (the ones that start with "lib" are the shared libraries). What is great about that is that they are the ones that can be shared between processes. If you factor out all of the parts that are shared between processes, you end up with the "writeable/private" total, which is shown at the bottom of the output. This is what can be considered the incremental cost of this process, factoring out the shared libraries. Therefore, the cost to run this instance of KEdit (assuming that all of the shared libraries were already loaded) is around 2 megabytes. That is quite a different story from the 14 or 25 megabytes that ps reported.

What does it all mean?
The moral of this story is that process memory usage on Linux is a complex matter; you can't just run ps and know what is going on. This is especially true when you deal with programs that create a lot of identical children processes, like Apache. ps might report that each Apache process uses 10 megabytes of memory, when the reality might be that the marginal cost of each Apache process is 1 megabyte of memory. This information becomes critial when tuning Apache's MaxClients setting, which determines how many simultaneous requests your server can handle (although see one of my past postings for another way of increasing Apache's performance).

It also shows that it pays to stick with one desktop's software as much as possible. If you run KDE for your desktop, but mostly use Gnome applications, then you are paying a large price for a lot of redundant (but different) shared libraries. By sticking to just KDE or just Gnome apps as much as possible, you reduce your overall memory usage due to the reduced marginal memory cost of running new KDE or Gnome applications, which allows Linux to use more memory for other interesting things (like the file cache, which speeds up file accesses immensely).

Tags: apache