Numeric and Weighted Distribution Queries

Spreading values across rows (or subsets of rows within a set of rows) was once cumbersome and difficult, but the invention of window functions (which have been around for a while now) have made it much easier. Here are some examples you can take and tweak to fit your needs.

Note: I created these examples in Netezza because you can reference column aliases created in the same select clause, which makes the examples much cleaner. But this logic works in any database that supports window functions, such as SQL Server, you just might have to copy and paste the formula to multiple places.

Numeric Distribution

As basic as it gets, this is just spreading a value equally across rows. In this example each trade has freight which is being spread across the items:

select	*,
	TOTAL_FREIGHT / count(*) 
		over (partition by TRADE_ID) as ITEM_FREIGHT,
	PRICE - COST - ITEM_FREIGHT as NET_PROFIT
from	TRADE
order by
	TRADE_ID,
	LINE_ID;

Weighted Mean figure 1

Weighted Distribution

This is spreading a value across rows based on each row’s weight. In this case the weight is the proportion of each item’s cost to the total cost of its trade:

select	*,
	COST / sum(COST) 
		over (partition by TRADE_ID) as ITEM_COST_RATIO,
	TOTAL_FREIGHT * ITEM_COST_RATIO as WEIGHTED_ITEM_FREIGHT,
	PRICE - COST - WEIGHTED_ITEM_FREIGHT as NET_PROFIT
from	TRADE
order by
	TRADE_ID,
	LINE_ID;

Weighted Mean figure 2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s