Filtering accumulated values

Find out the customers who account for half of the total sales and sort them from the highest to the lowest by the sales amount.

SQL

WITH A AS
    (SELECT customer,sales_amount,
            row_number() OVER (ORDER BY sales_amount) sales_rank
     FROM customer_sales)
SELECT customer,sales_amount
FROM (SELECT customer,sales_amount,
             sum(sales_amount) OVER (ORDER BY sales_rank)
             accumulated_amount FROM A)
WHERE accumulated_amount>(SELECT sum(sales_amount)/2
                          FROM customer_sales)
ORDER BY sales_amount desc

The above syntax computes the cumulative sales amount from the lowest to the highest, and then finds those customers whose accumulated sales amounts constitutes the first half of the total amount in an opposite direction. Otherwise, it would be very difficult to handle the customer just around the midline, but it would have to perform sort twice in opposite directions. Besides, to help the window function handle records with the same sales amount when it accumulates the values, the user will need to rewrite the subquery to compute the ranks first.

SPL

A
1 =demo.query("select * from customer_sales").sort(sales_amount:-1)
2 =A1.cumulate(sales_amount) Compute the cumulative sales amount
3 =A2.m(-1)/2 The final accumulated value will be used as the total sales.
4 =A2.pselect(~>=A3) Determine the position that passes the half of total sales.
5 =A1(to(A4))

With SPL , the users can perform the query according to their natural process of thinking.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.