title: Decile ranking performance in Netezza
body: <p>I have a small(ish) aggregated data set in Netezza, about 10m rows, on a TwinFin 6.</p>
  
  <p>To simplify the question a bit, I've cut down the number of columns:</p>
  
  <pre><code>CUSTOMER_SALES_AGG
  
  CUSTOMER_ID
  NUMBER_TRANS
  TOTAL_DOLLARS
  TOTAL_ITEMS
  </code></pre>
  
  <p>This table is distributed on CUSTOMER_ID, with 1 row per customer ID, collecting the total transactions the customer has made, the total dollars they've spent, and the # of items that they've purchased.</p>
  
  <p>I'm attempting to calculate the decile ranking of each customer across all customers, by # transactions, total $ spent, and total items bought. E.G. if a customer spent >= 90% of other customers, they would rank in the 1st decile.</p>
  
  <p>I've built a query:</p>
  
  <pre><code>SELECT
      CUSTOMER_ID, 
      NUMBER_TRANS,
      NTILE(10) OVER(ORDER BY NUMBER_TRANS DESC NULLS LAST) as TRANS_DECILE,
      TOTAL_DOLLARS,
      NTILE(10) OVER(ORDER BY TOTAL_DOLLARS DESC NULLS LAST) as DOLLARS_DECILE,
      TOTAL_ITEMS,
      NTILE(10) OVER(ORDER BY TOTAL_ITEMS DESC NULLS LAST) as ITEMS_DECILE
  FROM CUSTOMER_SALES_AGG;
  </code></pre>
  
  <p>This works, but it's <em>very</em> slow, taking nearly 10-20 minutes to run.</p>
  
  <p>Since doing a decile computation requires sorting the data and then dividing that sorted data into groups, it seems like the MPP structure of Netezza would handle this very well. If I was partitioning the deciles I could redistribute and do the ranking on each SPU, it could be even faster.</p>
  
  <p>Any ideas on how to speed this up?</p>