노을지기 노리터

출처:

http://cafe.naver.com/bnlifepub/29 

http://blog.naver.com/hunj?Redirect=Log&logNo=60062562067

회사에서 관리하는 지수중 배송기간 ( Lead time ) - 고객이 주문을 넣은 날서부터 물건을 보낸날까지의 기간 - 이란 것이 있다.

고객은 주문을 일찌감치 넣었는데 내부에서 우물쭈물하다 시간을 끌고 있는지
얼마만에 물건을 보내주고 있는지...뭐 이런거다.

 

오늘은 피벗테이블 ( Pivot Table )로 간단하게 이것을 계산해보자.

아래와같은 데이타가 있다. ( 데이타 샘플을 다운 받으시길 바란다 )

 

<그림1>

 

여기서 우리가 해야할 것을 월별로 Lead time을 계산해보는거다.
보다 구체적으로 야그해보면 해당월에 들어온 주문중 해당월에 발송된 주문이 몇퍼센트인지를
계산해보는거다. ( 물론 월말에 들어온 주문이야 그 달에 배송한다는 것이 어렵지만 여기서는
무시하고 넘어가겠다 )

 

위 데이타를 가지고 피벗을 만들면서 귀찮은 문제에 직면하게 된다.

'주문일', '납품일'을 월별로 그룹핑을 하려고 하는데 문제는 열필드에 넣을 '납품일'이란 필드에서 발생한다.
잘 아시다시피 엑셀의 열갯수는 256개가 최대다. 그런데 납품일( 주문일 필드를 넣어도 마찬가지) 의 item갯수가 256개를 훨씬 초과한다는 사실이다. 그래서 납품일을 열필드에 넣을 때
아래와 같은 에러메세지를 보게된다.

 

<그림2>

 

그럼 어떻게 하지? 뭘 어떻게 하나 6만개 이상 넣을 수 있는 행필드로 일단 넣고 그룹핑을 한 후
열필드로 옮기면 되지...( 참~ 십~조~어 ^^)

 

<그림3>

 

<그림4. 주문일, 납품일이 날짜데이타 형식으로 되어 있으면 아래와 같은 친절한 창이 뜬다>

 

그다음 주문일을 행필드로 옮기고 마찬가지로 그룹핑을 해준다.

계산필드에는 금액을 넣어 준후 각 월별 퍼센트를 구해준다

<그림5. [옵션]버튼을 누른 후 [데이타 표시 형식]을 위처럼 조절한다>

 

이제 다 끝났다. 아래 그림의 노란색부분이 그 달에 들어온 주문중 그 달에 나간 주문량의
비율이다.

 

<그림6>

 

피벗테이블( Pivot Table )의 좋은 점은 무엇인가?
.
.
.
그렇지 쉽게 꺽을 수 있다는거다. 월별, 지역별로는 어떤지 한 번 꺽어볼까나?
걍 지역필드를 행필드에 던져 넣기만 하면 끝이다.

 

<그림7>

 

피벗은 정말 엑셀의 꽃중의 꽃이다. 아니 "꽃보다 피벗"이다.

출처: http://dev.mysql.com/tech-resources/articles/wizard/page4.html

MySQL에서 엑셀의 피벗테이블같이 사용하고 싶을 때 유용하다.

Some help from SQL itself

---

The Wizard smiled. "If you like to, you're welcome. However, you could get some help from the database engine itself, provided that you ask nicely." And while he was speaking he typed a very cryptic statement:

mysql> SELECT CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS `',dept,'`') -> FROM departments; CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS `',dept,'`') , SUM(IF(dept = "Development", 1,0)) AS `Development` , SUM(IF(dept = "Personnel", 1,0)) AS `Personnel` , SUM(IF(dept = "Research", 1,0)) AS `Research` , SUM(IF(dept = "Sales", 1,0)) AS `Sales` , SUM(IF(dept = "Training", 1,0)) AS `Training` 5 rows in set (0.00 sec)

"You know," he went on, "you can also use SQL to produce SQL code. This is one of the cases. You have in front of you the list of columns that you should include in your query. Now, with some cut-and-paste, we could get the result you want. Here."

mysql> SELECT location -> , SUM(IF(dept = "Development", 1,0)) AS `Development` -> , SUM(IF(dept = "Personnel", 1,0)) AS `Personnel` -> , SUM(IF(dept = "Research", 1,0)) AS `Research` -> , SUM(IF(dept = "Sales", 1,0)) AS `Sales` -> , SUM(IF(dept = "Training", 1,0)) AS `Training` -> , COUNT(*) AS total -> FROM locations INNER JOIN employees USING (loc_code) -> INNER JOIN departments USING (dept_code) -> GROUP BY location; location Development Personnel Research Sales Training total Boston 2 0 0 1 0 3 Cagliari 0 0 3 0 0 3 London 0 1 0 0 1 2 Manchester 0 0 0 2 0 2 Marseille 2 0 0 1 0 3 Milano 0 0 0 1 1 2 New York 3 0 0 1 0 4 Paris 1 0 0 1 0 2 Roma 0 1 0 1 1 3 9 rows in set (0.36 sec)

The wizard was now unstoppable. He had reached the stage where he simply couldn't help giving away his knowledge.

"Before we go on," he said, taking possession of my keyboard, "let's see how this same method can do more than counting. Let's replace those 1s with a numeric field, and do real summing up."

mysql> SELECT location -> , SUM(IF(dept = "Development", salary,0)) AS `Development` -> , SUM(IF(dept = "Personnel", salary,0)) AS `Personnel` -> , SUM(IF(dept = "Research", salary,0)) AS `Research` -> , SUM(IF(dept = "Sales", salary,0)) AS `Sales` -> , SUM(IF(dept = "Training", salary,0)) AS `Training` -> , SUM(salary) AS total -> FROM locations INNER JOIN employees USING (loc_code) -> INNER JOIN departments USING (dept_code) -> GROUP BY location; location Development Personnel Research Sales Training total Boston 11900 0 0 5950 0 17850 Cagliari 0 0 16800 0 0 16800 London 0 5700 0 0 5700 11400 Manchester 0 0 0 11550 0 11550 Marseille 11150 0 0 5800 0 16950 Milano 0 0 0 5550 4900 10450 New York 17850 0 0 6100 0 23950 Paris 5700 0 0 5400 0 11100 Roma 0 5000 0 5500 5100 15600 9 rows in set (0.00 sec)

"Don't forget to change also the total field. A simple SUM without IF, and your total is ready."

I was looking at the screen, which was showing what seemed to be the complete solution to my problem, but the wizard was shaking his head. "As a matter of fact," he was saying, there is something that we can improve here. We have two queries, in which we are reading the departments table. So we are reading it twice twice. The whole process could be improved, by querying for department code the first time, and omitting the join with departments the second time."

mysql> SELECT CONCAT(', SUM(IF(dept_code = "',dept_code,'", 1,0)) AS `',dept,'`') -> FROM departments;

"Here. Let's get the columns once more. Good. And there it is. This one looks better."

mysql> SELECT location -> , SUM(IF(dept_code = "1", 1,0)) AS `Personnel` -> , SUM(IF(dept_code = "2", 1,0)) AS `Training` -> , SUM(IF(dept_code = "3", 1,0)) AS `Research` -> , SUM(IF(dept_code = "4", 1,0)) AS `Sales` -> , SUM(IF(dept_code = "5", 1,0)) AS `Development` -> , COUNT(*) AS total -> FROM locations INNER JOIN employees USING (loc_code) -> GROUP BY location;

He changed the previous two SQL statements, executed them, with some cut-and-pasting in the middle, and got exactly the same result. Now he was explaining me why he did it. "The first query is scanning all the departments table, and we know that its results will be used to build the second query containing the employees table, which has already a department code. Therefore, we can skip the join with departments, since the only purpose of that join was to get the names of the departments."

"I see" was the only comment I could offer, Since I was overwhelmed by his continuous insight. I got the idea, and I couldn't help thinking that he must have done that before. All those pieces of information were coming just too fast for me. Luckily, all logs were on, so I knew that I would be able to get all the statements back when he would leave. Which was not the case yet. The wizard was now ready to give me his philosophical view of cross tabulating.

출처: http://office.microsoft.com/en-us/outlook-help/change-the-font-or-font-size-of-the-message-list-HA010274437.aspx

The message list appears between the Navigation Pane and Reading Pane in Microsoft Office Outlook 2007. The default font setting is Segoe UI regular 8-point.

You can customize the font and its appearance. For example, you can change message list to use an alternate font or a smaller or larger font size.

1. In Mail, on the View menu, point to Current View, and then click Customize Current View.

 Tip   To open the Customize View: Messages dialog box, right-click the message list heading where Arranged By appears, and then click Custom.

2. Click Other Settings.
3. Under Column Headers and Rows, click Row Font.

4. Click a font, font style, and size that you want to use.
5. On the Font, Other Settings, and Customize View: Messages dialog boxes, click OK.