http://stpeter.im/writings/essays/publicdomain.html

 

 

Who's Afraid of the Public Domain?

by Peter Saint-Andre


Version: 1.3
First Published: 2006-11-26
Last Updated: 2008-08-28


Introduction

You know who you are. You like to write, compose, draw, paint, sculpt, photograph, perform, or engage in some other creative activity. You are what I call a creative individual.

Most people make five assumptions about creative individuals:

  1. Creative individuals would not produce their works without the possibility of making money from them.
  2. Creative individuals are endowed with the inalienable right to control who may copy or modify those works, since without that "copyright" they would not be able to make money from their creative output.
  3. Copyright is a straightforward extension of physical property rights and therefore a creative work is a form of intellectual property.
  4. To protect the rights of creative individuals, governments may legitimately prevent others from copying or modifying creative works.
  5. It is only government-enforced copyright that keeps a creative work safe from the ravages of violation and abuse; when it is no longer so protected, it lapses into a fearsome state of desuetude and disregard called the public domain.

     

These assumptions seem as natural as the air we breathe. I know, because I made them, too. My creative activities -- writing, composing, and the like -- are a large part of who I am, and I didn't want others to profit from or modify my works. Yet slowly but surely I began to question those assumptions. Eventually I overcame completely my fear of the public domain, but only after a great deal of reading and thinking about the history of copyright law, the nature of creative products, and the implications of our ongoing technology revolutions [1]. Although I place all of my personal works [2] in the public domain, I know that the decision to do so is not easy. I've written this essay to share my conclusions so that you can at least appreciate the importance of the public domain to the future of our culture. If you decide to place your works in the public domain, so much the better.

Wherein We Meet the Muse

OK, enough about me. Let's talk about you.

Why do you create? Think back to when you first played an instrument or picked up a pen. Was your fascination with your favorite creative endeavor driven by the desire for money? I doubt it. First of all, there are plenty of easier ways to make money than by penning poems, composing music, writing essays, or blogging -- selling insurance comes to mind. I bet you create because you take great pleasure in the activity itself, because you feel an inner compulsion to create, or simply because you can't help it: it feels as if you were you were born that way and you can't imagine life without your favorite creative activity. The ancients had a name for this non-monetary source of inspiration: The Muse.

That's not to say you don't also have more temporal motivations; but in my experience those motivations are seldom primary, in large part because of the sheer dedication to your craft that is needed (often from an early age) to truly excel in creative production. The drive to create comes first, and only later do you discover that as a result of creation can come fame, fortune, power, and prestige. Indeed, I would hazard that most creative individuals never seek to make a living from their creative output; granted, such individuals are typically the equivalent of Sunday composers, but even some well-known writers and composers (such as William Carlos Williams and Charles Ives) have made their living in other professions and have pursued their creative endeavors on the side. (I, too, have followed this path, which is one reason why I have been so open to questioning copyright -- I never expected to make money from my creative endeavors in the first place.)

Production and Publication

Creative individuals don't merely produce works; they also publish them. In the original sense of "making public", publishing can take many forms: a book, a manuscript, a recording, a recitation, a performance, a transformation into another medium. Some results of publication are physical objects, and some physical objects can be more or less easily copied (compare a single poem to a long novel; compare that novel to a larger-than life statue). Other instances of making public are experiential processes, which given the current state of technology cannot be fully captured in a physical object since they require the human presence of both producer and consumer (musical performances, poetry readings, theater plays, and the like); here, part of the attraction is precisely the human element that cannot be duplicated outside the time and place of performance.

Thus the fact that a work has been made public does not imply that it is amenable to copying. What determines whether a work can be copied is technology. Prior to the development of writing, a poem or myth could be "copied" only by memorizing it, thus enabling one to recite it at will. Prior to the invention of movable type, a written work could be copied (at least during the European Middle Ages) only by visiting a scriptorium in which the work resided and laboriously copying out the manuscript by hand. Prior to the invention of digital storage, retrieval, and publishing (especially the Internet), the ability to copy large texts, images, and audio and video recordings was effectively limited to large-scale publishing and media companies; now that ability is distributed across the world. Although today we are accustomed to live audio and video recordings, we know that they are but a pale reflection of being there (those who experience a concert or recitation or play don't feel cheated by the existence of a recording, since it is not a faithful copy of the original experience); thus the livelihood of performing artists, motivational speakers, and other experience-makers is not yet threatened by the ability to make copies.

Since the march of technological progress cannot be stopped, you may need to think creatively about cashing in on your creative activities (if indeed you want to). While I discuss some suggestions for doing so at the end of this essay, my point now is only that the current state of technology has a major impact on how you can make money from what you create.

Technology also has an impact on the ability of others to modify your works. As most creative individuals do, you probably have something of a parental attitude toward your creations. You may worry more about someone modifying your works than about someone copying your works. For instance, if you write a song or create an image, you don't want someone else to palm it off as their own. Yet here publication is your friend. If you publish early and publish often (especially on the Internet with its "way back machines" and ubiquitous search engines), it becomes more difficult for someone else to claim that they created what you did. With effectively the whole world watching, plagiarism is hard to pull off (as many college students have discovered to their chagrin).

What is Properly Property

Do you own what you've created? It's a tempting thought. After all, you brought it into being. If the analogy of parenthood is appropriate, think of it this way: do parents own their children? Well, no. They have a special interest in their children, but they don't own or even, after a certain amount of time, control or influence them. Children eventually lead separate lives, and so do creative works. This becomes painfully clear when you die. Shakespeare, Beethoven, and other famous creators no longer exercise any control over their works (their heirs might like to, but that's another matter).

In this way, children and creative works are unlike physical property. The house I own today could be maintained, improved upon, transferred, and re-sold to the end of time, all the while being privately owned. By contrast, when I publish ("make public") a poem on my website, in a way it immediately becomes public property, since anyone could memorize it, recite it, write it down on a slip of paper, copy it to an e-reader, cache it on their hard drive, or otherwise "inhabit" it without harming me in the least.

Thomas Jefferson captured this concept as follows:

He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me. That ideas should freely spread from one to another over the globe, for the moral and mutual instruction of man, and improvement of his condition, seems to have been peculiarly and benevolently designed by nature, when she made them, like fire, expansible over all space, without lessening their density in any point, and like the air in which we breathe, move, and have our physical being, incapable of confinement or exclusive appropriation. Inventions then cannot, in nature, be a subject of property.

A Monopoly by Any Other Name

Jefferson went on to write:

Society may give an exclusive right to the profits arising from them [i.e., inventions], as an encouragement to men to pursue ideas which may produce utility, but this may or may not be done, according to the will and convenience of the society, without claim or complaint from any body.

Notice that the "right" to the profits arising from an idea is granted by society. It is not a natural right, since natural rights never expire (e.g., the right of someone to own my house does not expire but instead is passed on through transfer or sale). So unless you believe in perpetual limits over who may copy or modify your works, you cannot maintain that copyright is a right at all. In fact copyright is a government-granted privilege of monopoly power over making copies and modifications to a work. Sure, if you are a Sunday composer or a small-time blogger then it's a minor monopoly, but it's a monopoly nonetheless. I don't know about you, but I don't particularly want to be a monopolist of any kind.

A Fearsome Desuetude

When the government-granted privilege of monopoly power that we call copyright expires, your work passes into the public domain. It sounds like a horrible fate, doesn't it? Once your work is in the public domain, anyone can copy it or modify it without your approval.

Yet the public domain is nothing to fear. The works of Homer, Sophocles, Confucius, Plato, Aristotle, Dante, Shakespeare, Galileo, Newton, Bach, Beethoven, and other creative giants are all in the public domain. Their works are revered, not reviled. Sure, the fact that the Fifth Symphony is in the public domain enabled Chuck Berry to write "Roll Over Beethoven"; but far from defiling Beethoven's good name, Berry's song indicates the level of respect that we still have for Beethoven's works. I bet you'd love it for your works to be similarly known and respected two hundred years from now (what creative individual wouldn't?).

But certain commercial interests, such as the large media companies, do think that the public domain is a fate worse than death for their creative products (in fact, copyright was developed not to protect the creative interests of authors but to protect the commercial interests of printers and publishers [3]). That's why those media companies have bought off the American Congress to continually extend the length of their monopoly privileges. For example, if you're a 30-year-old American today and you live to age 90, your copyrighted work won't pass into the public domain until 2136 -- unless of course the Congress extends the copyright terms again, which it is very likely to do when the earliest Mickey Mouse films are once again due to enter the public domain.

Because of that corporate influence over the copyright laws (at least in America), you face a choice: accept that your works will never pass into the public domain, or willingly place them there. You can place your works into the public domain immediately (as I have done) or specify in your will that your works shall pass into the public domain upon your death. I find it simpler to place my works in the public domain as soon as I publish them, but only you can decide the best course of action for your own works.

Thinking Creatively about the Creative Life

At this point you may be wondering: am I crazy? Have I willingly given up all possibility of benefiting from my creative activities?

Well, not so fast. Just because my works are in the public domain doesn't mean that I couldn't sell them in certain forms. I could publish physical books containing my writings (yes, some folks still buy books), perhaps autographing them to give them unique value. I could give readings of my poems. I could perform my songs in concert. I could sell T-shirts and calendars and other paraphernalia. I could give seminars on blogging or songwriting. In short, I could sell objects and experiences for which there is demand even if my words and music are in the public domain (no comparison of stature intended, but people do it with Shakespeare and Beethoven, so why not with me?).

I freely grant that in some situations and domains it might be more difficult for individuals to cash in on their creations. If you're a non-performing composer or a mute poet, you can't give performances -- though you could teach, write instructional manuals, sell merchandise, and find a collaborator to present your authorized performances. And placing your works in the public domain means that anyone can publish or perform them without compensating you (one solution, used by J.R.R. Tolkien in response to unauthorized copies of The Lord of the Rings in America, was to ask his readers to purchase the book only from his authorized publisher -- which happened to be great publicity! [4]).

The state of technology for the last 500 years has made the creative life relatively easier for you if you're a writer or composer (out with fickle patrons, in with the buying public), and for the last 100 years if you're an actor or musical performer. Yet technology moves on, introducing new challenges and new opportunities.

One of those challenges is the impending death of copyright (or at least the ability to enforce copyright without seriously invading the privacy of those who enjoy your works). Why try to forestall the inevitable when instead you can place your works in the public domain? [5] Personally I think that you're up to the challenge and that you can apply your considerable creativity to the task of successfully living the creative life without the coercive safety net of a government-granted monopoly over copying and modifying your works. It might be difficult, but no one ever said the creative life was easy. That's why being a creative person is such a badge of honor. And it's a lot more fun than selling insurance, isn't it?

Notes

[1] Some history of my reflections on the topic can be found at my blog. A great resource for thinking about copyright is QuestionCopyright.org.

[2] By "personal works" I mean all the works that I publish at my personal Internet domains (ismbook.com, monadnock.net, and stpeter.im). By day I mainly write Internet protocol specifications for the XMPP Standards Foundation, whose documents are published under the Creative Commons Attribution License; I also write specifications for the Internet Engineering Task Force, articles for professional journals, books for technical publishers, and whitepapers for my employer, but unfortunately the publication policies of these entities are not as liberal as I would prefer.

[3] For details, see The Promise of a Post-Copyright World by Karl Fogel.

 

[4] For details, see Intellectual Property and Middle-Earth by Milton Batiste.

 

[5] For information about methods for placing your works in the public domain, visit Creative Commons, specifically their public domain license and their public domain dedication form.

Revision History

1.3 (2008-08-28): Corrected several errors and updated several links.

1.2 (2007-05-23). Added links to my blog and to QuestionCopyright.org.

1.1 (2006-12-30). Added footnotes regarding my personal works and regarding methods for placing works in the public domain.

1.0 (2006-11-26): Initial version


Peter Saint-Andre > Writings > Essays

 

 

Posted by steloflute

http://www.askvg.com/tip-convert-start-screen-into-a-full-screen-start-menu-kind-of-in-windows-8-1/

 

[Tip] Convert Start Screen into a Full Screen Start Menu (kind of) in Windows 8.1

 

When Microsoft announced that the company is going to replace old Start Menu with new Start Screen in Windows 8 OS, lots of people didn't like the move and complained about it. Microsoft didn't change its decision and replaced the good old Start Menu with Windows Phone style Start Screen in Windows 8. They also removed the Start button from Windows 8 Taskbar and replaced it with a hidden thumbnail which appears when you move your mouse cursor to bottom-left corner of screen.

Removal of Start Menu and Start button was a bad decision and Windows 8 users started installing 3rd party software to bring back Start Menu and Start button in Windows 8. Some of these software completely disabled Start Screen and replaced it with Windows 7 style Start Menu.

Microsoft realized the mistake and they have added Start button back to the latest Windows 8.1. But Start Screen is still there and users still need to depend upon it to launch their favorite programs, etc.

But Microsoft has added some new options to Windows 8.1 which can help you in converting Start Screen in a usable Start Menu (kind of). You can make Start Screen like a full screen Start Menu showing all installed programs alphabetically organized into groups.

Actually Microsoft has provided a new feature to Windows 8.1 users which allows you to set "All Apps" view as default in Start Screen which means whenever you access Start Screen using any method such as using WIN key, using Start button or any other method, it'll show "All Apps" page by default not the Start Screen homepage.

Using_Windows_8_1_Start_Screen_Like_Start_Menu.png

This way you can use Start Screen like a Start Menu but in full screen mode. Interested Windows 8.1 users can follow these simple steps to convert Windows 8.1 Start Screen into full screen Start Menu:

1. Right-click on Taskbar and select Properties option.

2. It'll open Taskbar and Navigation properties window.

3. Go to "Navigation" tab and enable "Show the Apps view automatically when I go to Start" option present in "Start screen" section.

Set_All_Apps_Page_as_Default_Homepage_Windows_8_1_Start_Screen.png

4. Apply the changes and now whenever you'll access Start Screen in Windows 8.1, it'll show "All Apps" page by default which will work similar to a Start Menu.

5. You can also enable "List desktop apps first in the Apps view when it's sorted by category" option to show Desktop programs first in "All Apps" page. For this you'll also need to sort apps by category as shown in following image:

Sort_Apps_by_Category_Start_Screen.png

6. If you want, you can also set your current Desktop wallpaper as Start Screen background using following tutorial:

How to Set Desktop Wallpaper as Start Screen Background in Windows 8.1?

It'll make you feel like you are still on Desktop and are using Start Screen right from your Desktop.

NOTE: If you want to make these changes using Registry Editor, check out following tutorial:

Registry Tweaks to Customize Taskbar Navigation and Start Screen Settings in Windows 8.1

 

 

 

 

Posted by steloflute

group by rollup, cube

Database 2014/11/24 23:30

http://huzii.egloos.com/viewer/3638265


Group by, Rollup, Cube의 차이점


네이버 어딘가에서 무단으로 훔쳐왔음.. -_-;
네이버 검색 창에서 rollup으로 검색 후 선정.

편집이 좀 조잡하지만...
내용은 만점.
죄송하고 감사합니다.

ROLLUP operator 


  - ROLLUP구문은 GROUP BY절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진
    집합결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행합니다.

  - SELECT절에 ROLLUP을 사용함으로써 보통의 select된 데이터와 그 데이터의
    총계를 구할 수 있습니다.


※ 우선 아주 간단한 예제부터 살펴 보겠습니다.
   (scott유저의 emp테이블을 가지고 테스트 했습니다.)


-- Group By를 사용해서 직업별로 급여 합계를 구하는 예제 입니다.

SELECT job, SUM(sal)
FROM emp
GROUP BY job 


JOB          SUM(SAL)
---------- ----------
ANALYST          600
CLERK              3200
MANAGER        33925
PRESIDENT      5000
SALESMAN      4000



-- 아래 SQL문은 위의 예제에 ROLLUP구문을 사용해서 직업별로 급여 합계를 구하고
   총계를 구하는 예제 입니다.


SELECT job, SUM(sal)
FROM emp
GROUP BY ROLLUP(job)
 

JOB          SUM(SAL
---------- ----------
ANALYST          6000
CLERK              3200
MANAGER        33925
PRESIDENT       5000
SALESMAN       4000
                       52125   --> 급여 합계에 대한 총계가 추가 되었습니다.




우선 간단하게 ROLLUP  Operator의 예제를 살펴보았습니다.
조금더 복잡한(?) 예제를 하나더 해보면은요..

-- 부서의 직업별로 인원수와 급여 합계를 구하는 예제를 하나더 해보겠습니다.

-- 일반적인 Group By절을 사용해서 SQL문을 구현해보면은요.. 아래와 같이 하면 되겠죠..

SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname, a.job


DNAME       JOB               SAL  EMP_COUNT
----------  ---------- ---------- ----------
ACCOUNTING  CLERK               1300          1
ACCOUNTING  MANAGER          2450          1
ACCOUNTING  PRESIDENT        5000          1
RESEARCH    ANALYST            6000          2
RESEARCH    CLERK                1900          2
RESEARCH    MANAGER           2975          1
SALES       MANAGER              28500          1
SALES       SALESMAN             4000          3



-- 결과를 보면은요..  부서별로 인원이 몇명이고, 또 급여합계가 얼마가 되는지 한눈에 보이지 않죠...
   일일이 부서에 해당하는 직업별 급여와 사원수를 일일이 더해야 되죠..

-- 이런 경우 ROLLUP  Operator를 적용해서 구현을 하면은 부서별 급여합계와 사원 총수를
   쉽게 볼 수 있습니다...

SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job)


DNAME      JOB               SAL  EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK              1300          1
ACCOUNTING MANAGER          2450          1
ACCOUNTING PRESIDENT        5000          1
ACCOUNTING                         8750          3  -->  ACCOUNTING 부서의 급여합계와 전체 사원수..
RESEARCH   ANALYST            6000          2
RESEARCH   CLERK                1900          2
RESEARCH   MANAGER           2975          1
RESEARCH                            10875          5 -->  RESEARCH 부서의 급여합계와 전체 사원수..
SALES      MANAGER              28500          1
SALES      SALESMAN             4000          3
SALES                                  32500          4 -->  SALES부서의 급여합계와 전체 사원수..
                                            52125         12 ->  전체 급여 합계와 전체 사원수


위와 같이 ROLLUP Operator을 일반적인 누적에 대한 총계를 구할때 사용하면 아주 편리하게
사용 할 수 있습니다.
 
 








SELECT NVL(DECODE(B.NO, '1', DNAME), '총계') DNAME,
SUM(CLERK) CLERK,
SUM(MANAGER) MANAGER,
SUM(ETC) ETC,
SUM(DEPT_SAL) DEPT_SAL
FROM (
SELECT B.DNAME, A.CLERK, A.MANAGER, A.ETC, A.CLERK + A.MANA
GER + A.ETC DEPT_SAL
FROM (
SELECT DEPTNO,
SUM(DECODE(JOB,'CLERK',SAL)) CLERK,
SUM(DECODE(JOB,'MANAGER',SAL)) MANAGER,
SUM(DECODE(JOB,'MANAGER',0,'CLERK',0,SAL)) ETC
FROM EMP
GROUP BY DEPTNO
) A, DEPT B
WHERE A.DEPTNO = B.DEPTNO ) A,
(SELECT '1' NO FROM DUAL
UNION ALL
SELECT '2' FROM DUAL) B
GROUP BY DECODE(B.NO, '1',A.DNAME);

위 처럼 오라클에서 지원하는 함수를 이용하지 않고 만들어 봤어요..
참조 하세요.





SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_co
unt
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname, a.job

분석해 볼까여
1.select;
2.from
3.where
4.group

2.테이블 두개가 메모리에 적제가 되겠죠
3.조건에 의해 deptno 을 기준으로 하나의 테이블로 정리 되겠죠
그다음은 4. 가 계산되는데
여기서 4.가 없다고 가정하면
1.로 가서 결국 하나의 행이 결과값으로 도출될 것입니다. 그룹함수에 의해서
총합과 행의 총갯수가 반환되겠죠
그럼 다시 되돌아가서
4.group by 절이 계산을 하게 되는데 우선 b.dname만 있다면 몇개의 레코드가 반환될까여 부서명이
4개라면.. 4개의 레코드가 반환되겠죠(단 4개의 부서가 데이타에 존재한다면)
그 다음에 a.job 까지(직업은 3개라고 가정하면) 두개의 조건이 주어졌습니다.
그럼 몇개의 레코드가 반환될까여 12개의 레코드가 반환될까여????

이번에는 문장을 분석해서 SQL을 작성해 볼까여 (위 강의가 기준)
부서의 직업별로 인원수와 급여 합계를 구하라

키포인트 인원수와 급여합계입니다.
여기서 그룹함수가 사용된다는 것을 알수 있고 GROUP BY절을 사용해야 된다는 것도 알수가 있습니다.
형식은
1.SELECT
2.TABLE
3.WHERE
4.GROUP BY
로 형식이 가추어 지고
1. 컬럼은 4개가 되고 그룹함수 SUM()가 COUNT()가 들어가고여
2. 테이블은 위 강의가 기준이니까 2개이고여
3. 자연스럽게 JOIN 이 일어나겠네여 무슨 조인일까여????
4. GROUP BY절에서 부서와 직업이 GROUP으로 묶여지겠네여...
SQL을 작성하면 위와 같겠지여

 

 

 

CUBE operator 


 ※ CUBE강좌를 보시기 전에 바로 위에 있는 ROLLUP강좌를 꼭 봐주세요..
  
 ROLLUP 강좌예제 중에서 아래 SQL문 예제를 가지고 CUBE강좌를 진행 하려고 합니다.

====================  ROLLUP 강좌의 예제입니다.  ======================= 

SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job)


DNAME      JOB               SAL  EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK              1300          1
ACCOUNTING MANAGER          2450          1
ACCOUNTING PRESIDENT        5000          1
ACCOUNTING                         8750          3  -->  ACCOUNTING 부서의 급여합계와 전체 사원수..
RESEARCH   ANALYST            6000          2
RESEARCH   CLERK                1900          2
RESEARCH   MANAGER           2975          1
RESEARCH                            10875          5 -->  RESEARCH 부서의 급여합계와 전체 사원수..
SALES      MANAGER              28500          1
SALES      SALESMAN             4000          3
SALES                                  32500          4 -->  SALES부서의 급여합계와 전체 사원수..
                                            52125         12 ->  전체 급여 합계와 전체 사원수

=========================================================================


위의 SQL의 결과를 보면 부서별로 각 직업에 해당하는 급여와 사원수를 볼 수 있습니다.

하지만 부서별로 각 직업의 급여와 사원수, 그리고 또 각 직업별로 급여 합계와 사원수
보기 위해서는 두개의 ROLLUP을 사용해서 SQL문을 작성해야 합니다.

아래와 같이 되겠죠..
 

SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job)
UNION
SELECT ' ', job, SUM(sal) sal, COUNT(empno) emp_count
FROM emp
GROUP BY ROLLUP(job)
 
DNAME      JOB               SAL  EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK               1300          1
ACCOUNTING MANAGER          2450          1
ACCOUNTING PRESIDENT        5000          1
ACCOUNTING                         8750          3
RESEARCH    ANALYST           6000          2
RESEARCH    CLERK               1900          2
RESEARCH    MANAGER          2975          1
RESEARCH                           10875          5
SALES          MANAGER         28500          1
SALES          SALESMAN         4000          3
SALES                                 32500          4   => 요기 까지는 첫 번째 ROLLUP를 이용해서 구하고요
                   ANALYST          6000          2
                   CLERK              3200          3
                   MANAGER         33925          3
                   PRESIDENT        5000          1
                   SALESMAN         4000          3
                                           52125         12  => 요 부분은 두 번째 ROLLUP을 이용해서 구했습니다.

 


CUBE Operator를 사용하면 편하게 하나의 SQL문으로 위의 결과를 얻을 수 있습니다.
직접 SQL문을 실행시켜 보면 쉽게 이해가 갑니다.


SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY CUBE(b.dname, a.job)

DNAME      JOB               SAL  EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK               1300          1
ACCOUNTING MANAGER          2450          1
ACCOUNTING PRESIDENT        5000          1
ACCOUNTING                         8750          3 =>ACCOUNTING 부서의 직업별 급여의 총계와 사원수.
RESEARCH    ANALYST           6000          2
RESEARCH    CLERK               1900          2
RESEARCH    MANAGER          2975          1
RESEARCH                           10875          5=>RESEARCH 부서의 직업별 급여의 총계와 사원수.
SALES          MANAGER         28500          1
SALES          SALESMAN         4000          3
SALES                                 32500          4=>SALES 부서의 직업별 급여 총계와 사원수.
                   ANALYST          6000          2
                   CLERK              3200          3
                   MANAGER         33925          3
                   PRESIDENT        5000          1
                   SALESMAN         4000          3   
                                           52125         12  => 직업별로 급여의  총계와 사원수를 보여줍니다.



CUBE를 어느 경우에 사용 할 수 있는지 이해 되셨죠..
CUBE Operator는 Cross-Tab에 대한 Summary를 추출하는데 사용 됩니다
ROLLUP에 의해 나타내어지는 Item Total값과 Column Total값을 나타 낼 수 있습니다.

너무 어렵게 설명했나요... 응용해서 테스트 해보세요..



GROUPING() 함수


GROUPING Function은 ROLLUP, CUBE Operator에 모두 사용할 수 있습니다.

GROUPING Function는 해당 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환하고,
ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 됩니다.

따라서 해당 Row가 결과집합에 의해 산출된 Data인지,
ROLLUP이나 CUBE에 의해서 산출된 Data인지를 알 수 있도록 지원하는 함수입니다.


SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count,
       GROUPING(b.dname) "D", GROUPING(a.job) "S"
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY CUBE(b.dname, a.job)


DNAME        JOB               SAL  EMP_COUNT    D    S
----------   ----------    ------- ---------- ---- ----
ACCOUNTING CLERK            1300          1    0    0
ACCOUNTING MANAGER       2450          1    0    0
ACCOUNTING PRESIDENT     5000          1    0    0
ACCOUNTING                      8750          3    0    1
RESEARCH    ANALYST        6000          2    0    0
RESEARCH    CLERK            1900          2    0    0
RESEARCH    MANAGER       2975          1    0    0
RESEARCH                        10875          5    0    1
SALES          MANAGER      28500          1    0    0
SALES          SALESMAN      4000          3    0    0
SALES                              32500          4    0    1
                   ANALYST         6000          2    1    0
                   CLERK             3200          3    1    0
                   MANAGER      33925          3    1    0
                   PRESIDENT     5000          1    1    0
                   SALESMAN      4000          3    1    0
                                       52125         12    1    1


Posted by steloflute


Generate bitcoin for me

What's this?

티스토리 툴바