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
