ntile as quartile : NTILE « Analytical Functions « Oracle PL / SQL






ntile as quartile

 

SQL> CREATE TABLE sales(
  2    product_id            NUMBER(6),
  3    cid           NUMBER,
  4    time_id               DATE,
  5    sold         NUMBER(3),
  6    amount                NUMBER(10,2),
  7    cost                  NUMBER(10,2)
  8  );

Table created.

SQL>
SQL> CREATE TABLE products(
  2    product_id           NUMBER(6),
  3    name         VARCHAR2(50),
  4    subcategory     VARCHAR2(50),
  5    category        VARCHAR2(50),
  6    min_price       NUMBER(8,2)
  7  );

Table created.

SQL>
SQL>
SQL>
SQL> select b.subcategory, sum(a.sold),ntile(4) over (ORDER BY SUM(a.sold) desc) as quartile
  2  from sales a, products b
  3  where a.product_id = b.product_id
  4  and to_char(a.time_id, 'yyyy-mm') = '2001-06'
  5  group by b.subcategory;

no rows selected

SQL>
SQL>
SQL> drop table sales;

Table dropped.

SQL>
SQL> drop table products;

Table dropped.

SQL>

   
  








Related examples in the same category

1.NTILE(buckets) to calculate n-tiles: tertiles, quartiles, and so on
2.NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS ntile
3.NTILE(4) function is used for that ordering
4.NTILE(4) in reverse order
5.To get a clearer picture of the NTILE function
6.NTile with NULLS LAST
7.ntile and row_number