Oracle Analytics function--ration_to_report occupancy (percent)

Oracle has a lot of analytic functions, but this function will always forget, I think in this way can let myself remember, not next time Baidu.

Creation table, table data (usually practiced hand table):

Prompt PL/SQL Developer Importfileprompt Created onThursday, July 5, 2018 byAdministratorSetFeedbackoffSetDefineoffprompt Creating CKX001 ...Create TableCKX001 (IDVARCHAR2( -) not NULL, NAMEVARCHAR2( -) not NULL, SEXVARCHAR2( -) not NULL, SARLVARCHAR2( -), ADDRESSVARCHAR2( -), Time DATE) tablespace USERS pctfreeTenInitrans1Maxtrans255Storage (initial 64KNext8K minextents1Maxextents Unlimited);p rompt disabling triggers forCKX001 ...Alter TableCKX001 Disable Alltriggers;prompt Deleting CKX001 ...Delete  fromCKX001;Commit;p rompt Loading CKX001 ...Insert  intoCKX001 (ID, NAME, SEX, SARL, ADDRESS, time)Values('004','Chow Yun Fat','1',' -','Square',NULL);Insert  intoCKX001 (ID, NAME, SEX, SARL, ADDRESS, time)Values('005','Stephen Chow','1',' the','Shopping Malls',NULL);Insert  intoCKX001 (ID, NAME, SEX, SARL, ADDRESS, time)Values('006','Tony Leung','1','1700','Main Street',NULL);Insert  intoCKX001 (ID, NAME, SEX, SARL, ADDRESS, time)Values('001','Miriam Yeung','2',' -',NULL, To_date ('15-05-2018 10:54:19','dd-mm-yyyy Hh24:mi:ss'));Insert  intoCKX001 (ID, NAME, SEX, SARL, ADDRESS, time)Values('002','Cecilia Cheung','2',' +','Central Park', To_date ('15-05-2018 10:54:19','dd-mm-yyyy Hh24:mi:ss'));Insert  intoCKX001 (ID, NAME, SEX, SARL, ADDRESS, time)Values('003','Lizzy','2',' -','Hippo Apartments', To_date ('12-05-2018 10:54:44','dd-mm-yyyy Hh24:mi:ss'));Commit;p rompt6records loadedprompt enabling triggers forCKX001 ...Alter TableCKX001 Enable Alltriggers;SetFeedback onSetDefine onprompt done.

Now we are going to look at the percentage of each person's salary as the average wage:

Don't use ration_to_reropt when always use stupid method, because we ask everyone to the percentage of total wages first to the total wages, and then in that everyone's removal get,

Similar:

Select name,round(sarl/(selectsum from ckx001),4) *  - || ' % '  from ckx001;

Although the effect can be achieved, but we do not think that the actual development of the data is from a few tables, like the need to do a tear, data from the Personnel information table and the other 18 file table of this design,

In fact, the table design of the person has a hole in the brain, although the 18 Chapter table field is different, the majority of the class can be designed to a table, and then a file type of the fields to distinguish it, run off,

Anyway, what I'm going to say is that this percentage of SQL possible datum data comes in very difficult, and I don't want our example to come from a table, so it's easy, I was just looking at my own disgusting SQL

When you want to simplify him, at least to be beautiful and concise, and occasionally can put a force.

Like this:

Select name,roundover (),4)*|| ' % '  from ckx001;

The effect of the top two SQL is the same, but the party our basic data from the very complex SQL is, the following analysis function use will make your SQL clean and tidy.

Oracle Analytics function--ration_to_report occupancy (percent)