StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's an Oracle statement that I prepared before you posted that you were using SQL-Server, but it might give you some ideas, though you will have to roll your own ratio_to_report analytic function using CTE and self-joins. We calculate the cumulative proportion of each type in the products and client route tables and do a non equi-join on the matching proportion bands. The sample data I have used has some round-offs but these will reduce for larger data sets.</p> <p>Here's the setup:</p> <pre><code>create table products (id int not null primary key, "type" int not null, route varchar (20) null); create table clienttable ( "type" int not null, percent number (10, 2) not null, route varchar (20) not null); insert into clienttable ("type", percent, route) values (1, 0.4, 'A'); insert into clienttable ("type", percent, route) values (1, 0.4, 'B'); insert into clienttable ("type", percent, route) values (1, 0.2, 'C'); insert into clienttable ("type", percent, route) values (2, 0.5, 'A'); insert into clienttable ("type", percent, route) values (2, 0.5, 'B'); insert into clienttable ("type", percent, route) values (3, 1.0, 'C'); insert into products (id, "type", route) values (1, 1, null); insert into products (id, "type", route) values (2, 1, null); insert into products (id, "type", route) values (3, 1, null); insert into products (id, "type", route) values (4, 1, null); insert into products (id, "type", route) values (5, 1, null); insert into products (id, "type", route) values (6, 1, null); insert into products (id, "type", route) values (7, 1, null); -- 7 rows for product type 1 so we will expect 3 of route A, 3 of route B, 1 of route C (rounded) insert into products (id, "type", route) values (8, 2, null); insert into products (id, "type", route) values (9, 2, null); insert into products (id, "type", route) values (10, 2, null); insert into products (id, "type", route) values (11, 2, null); insert into products (id, "type", route) values (12, 2, null); -- 5 rows for product type 2 so we will expect 3 of route A and 2 of route B (rounded) insert into products (id, "type", route) values (13, 3, null); insert into products (id, "type", route) values (14, 3, null); -- 2 rows for product type 3 so we will expect 2 of route C </code></pre> <p>and here's the statement </p> <pre><code>select prods.id, prods."type", client.route cr from ( select p.id, p."type", row_number () over (partition by p."type" order by p.id) / count (*) over (partition by p."type") cum_ratio from products p ) prods inner join ( select "type", route, nvl (lag (cum_ratio, 1) over (partition by "type" order by route), 0) ratio_start, cum_ratio ratio_end from (select "type", route, sum (rr) over (partition by "type" order by route) cum_ratio from (select c."type", c.route, ratio_to_report (c.percent) over (partition by "type") rr from clienttable c))) client on prods."type" = client."type" and prods.cum_ratio >= client.ratio_start and prods.cum_ratio < client.ratio_end </code></pre> <p>This gives the following result:-</p> <pre><code>+----+------+----+ | ID | type | CR | +----+------+----+ | 1 | 1 | A | | 2 | 1 | A | | 3 | 1 | B | | 4 | 1 | B | | 5 | 1 | B | | 6 | 1 | C | | 8 | 2 | A | | 9 | 2 | A | | 10 | 2 | B | | 11 | 2 | B | | 13 | 3 | C | +----+------+----+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload