Re: N-tile function in postgres

Le 2012-09-24 à 12:32, Rachel Owsley a écrit :

> Hi,
>
> Can anyone help me with an aggregate query I am having trouble with?
>
> I want to get the top 5 or top 10 most frequently shopped in merchant categories for each account holder at a bank and put each of the quintiles/deciles into separate columns. I would also like to put the average transaction amount for each of those top 5-10 categories into separate columns, and the date of the last transaction in each of those 5 to 10 categories into separate columns. I am told that ntile may be an option for doing this, but can’t find any examples for using it in the documentation.
>
> We use Postgresql 9.1.
>
> Thank you so much for your help.
>
> Rachel

Hi!

Look at the tablefunc extension to do cross tabulation. The crosstab family of functions turn a series of rows into columns. Something like this:

a | 1
b | 2

a | b
1 | 2

It obviously works with more columns. That would take care of the final part of your query.

I've never used ntile() myself, but the docs say it returns 1 to the value. Then you may want the min/max amount per decile to extract the values you want. Something like this (untested, made up schema):

WITH raw_values(
SELECT

account_id
, merchant_category_id
, amount

FROM transactions
JOIN merchants USING (merchant_id))

, partitioned_sales AS (
SELECT
account_id
, merchant_category_id
, ntile(10) over (partition by account_id, merchant_category_id order by amount) as "partition"
, min(amount) over (partition by account_id, merchant_category_id order by amount) as amount
FROM raw_values)

SELECT *
FROM partitioned_sales
ORDER BY account_id, merchant_category_id, partition, amount

Hope that helps!
François