Search Postgresql Archives

Re: N-tile function in postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thank you, François! This is very helpful! I’ll give this query a try. I don’t know the cross-tab function, but that’s exactly what I want to do for the column output. Regarding the sample query, I see the min (amount), but how is the upper bound defined for each decile?

 

Thanks,

 

Rachel  

 

From: François Beausoleil [mailto:francois@xxxxxxxxxxx]
Sent: Monday, September 24, 2012 12:57 PM
To: Rachel Owsley
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux