SQL 中 percentile 的計算函數
問題: 給與一數字分配及指定的 percentile, 找出位於該 percentile 的數字。
假設數字為連續分配下的 percentile 的反函數。
PERCENTILE_CONT: an inverse distribution function that assumes a continuous distribution model.
例如, 選取 salar 中的中位數(median):
假設數字為離散分配下的 percentile 的反函數。
PERCENTILE_DISC: an inverse distribution function that assumes a discrete distribution model.
-- select hr salary
select
PERCENTILE_DISC(0.5) within group (order by e.salary desc) "0.5 Percentile_Disc"
from employees e;
Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr. If you omit this clause, then the function treats all rows of the query result set as a single group.
How to: return boject collection from a package function and use the collection as the table.
Category: SQL_PL/SQL
Steps:
- Create the required object type in the scheme level.
- Create the required collection type in the scheme level.
- Create the required package function that return the required collection type.
- Use the table function in the select statement so that the return result of the package function become the query source.
Example
-- Create object type
create type employee_salary as object (
dep_name varchar2(30),
emp_name varchar2(30),
salary number
);
-- create the collection type employee_salary
create type employee_salary_t as table of employee_salary;
-- Create the package function spec
create or replace package emp_util is
function gen_top_list return employee_salary_t;
end emp_util;
-- Create the package body
create or replace package body emp_util is
function gen_top_list return employee_salary_t is
v_salary_list employee_salary_t;
begin
-- use the object constructor as a expression in the select list to create an object and put it into the collection.
select employee_salary(d.department_name, e.first_name, e.salary) bulk collect
into v_salary_list
from employees e join DEPARTMENTS d using (department_id)
where e.salary > 5000;
return v_salary_list ;
end;
end emp_util;
select DEP_NAME, emp_name, salary from table(emp_util.gen_top_list);
References:
- [Using PL/SQL With Object Types] (https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/objects.htm)
- how to fetch cursor values into an object
Accessing Enterprise Beans: Local and Remote Session Beans
A local client has these characteristics:
- It must run in the same application as the enterprise bean it accesses.
- It can be a web component or another enterprise bean.
- To the local client, the location of the enterprise bean it accesses is not transparent.
The no-interface view of an enterprise bean is a local view. The public methods of the enterprise bean implementation class are exposed to local clients that access the no-interface view of the enterprise bean. Enterprise beans that use the no-interface view do not implement a business interface.
Java EE 7 Documentation
Java Platform, Enterprise Edition: The Java EE Tutorial (Java EE 7)
EJB Testing
Using the Embedded EJB Container to Test Enterprise Applications