Postgresql – Ranking each row in Postgres from 1-100 when comparing in groups

postgresql

For each row in my table, I want to calculate its "expensiveness" when grouped together with other rows with same year/month and size.

Example data:

year,month,size,price
2017,1,300,1000
2017,1,300,1100

I tried a query like this:

select 
*,
ntile(100) over (partition by year, month, size order by price) as percentile
from mytable;

The problem I have is that rows with the same price can get different percentiles, especially if a certain price is very common. Is there an alternate way to rank things from 1-100 that would give results that seem more consistent?

I don't mind if I need to create multiple views/queries to achieve this, e.g. perhaps I should calculate every percentile first and then my main query finds the "nearest" percentile, etc.

Also to note: sometimes the data may be sparse per group, e.g. less than 100 rows per group. Also, prices often group together so there will be even less discrete values.

Best Answer

I think I may have found the answer. The following query seems to produce results that are as I expect:

select 
*,
(100*cume_dist() over (partition by year, month, size order by price))::int as percentile
from mytable;

Test case

Building on this table:

CREATE TABLE hotel_data (
   hotel_id int
 , day      date  -- using "day", not "date"
 , spaces   int
 , price    int
 , PRIMARY KEY (hotel_id, day)  -- provides essential index automatically
);

Days per hotel_id must be unique (enforced by PK here), or the rest is invalid.

Multicolumn index for base table:

CREATE INDEX mv_hotel_mult_idx ON mv_hotel (day, hotel_id);

Note the reversed order as compared to the PK. You will probably need both indexes, for the following query, the 2nd index is essential. Detailed explanation:

Direct query without `MATERIALIZED VIEW`

SELECT hotel_id, day, sum_price
FROM  (
   SELECT hotel_id, day, price, spaces
        , sum(price)      OVER w * 2   AS sum_price
        , min(spaces)     OVER w       AS min_spaces
        , last_value(day) OVER w - day AS day_diff
        , count(*)        OVER w       AS day_ct
   FROM   hotel_data
   WHERE  day BETWEEN '2014-01-01'::date AND '2014-01-31'::date
   AND    spaces >= 2
   WINDOW w AS (PARTITION BY hotel_id ORDER BY day
                ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) -- adapt to nights - 1
   ) sub
WHERE  day_ct = 4
AND    day_diff = 3  -- make sure there is not gap
AND    min_spaces >= 2
ORDER  BY sum_price, hotel_id, day;
-- LIMIT 1 to get only 1 winner;

Also see @ypercube's variant with lag(), which can replace day_ct and day_diff with a single check.

How?

In the subquery, only consider days within your time frame ("in January" means, the last day is included in the time frame).
The frame for the window functions spans the current row plus the next num_nights - 1 (4 - 1 = 3) rows (days). Calculate the difference in days , the count of rows and the minimum of spaces to make sure the range is long enough, gapless and always has enough spaces.
- Unfortunately, the frame clause of window functions does not accept dynamic values, so ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING` cannot be parameterized for a prepared statement.
I carefully drafted all window functions in the subquery to reuse the same window, using a single sort step.
The resulting price sum_price is already multiplied by the number of spaces requested.

With `MATERIALIZED VIEW`

To avoid inspecting many rows without chance of success, save only the columns you need plus three redundant, calculated values from the base table. Be sure the MV is up to date. If you are not familiar with the concept, read the manual first.

CREATE MATERIALIZED VIEW mv_hotel AS
SELECT hotel_id, day
     , first_value(day) OVER (w ORDER BY day) AS range_start
     , price, spaces
     ,(count(*)    OVER w)::int2 AS range_len
     ,(max(spaces) OVER w)::int2 AS max_spaces

FROM  (
   SELECT *
        , day - row_number() OVER (PARTITION BY hotel_id ORDER BY day)::int AS grp
   FROM   hotel_data
   ) sub1
WINDOW w AS (PARTITION BY hotel_id, grp);

range_start stores the first day of each continuous range for two purposes:
- to mark a set of rows as members of a common range
- to show the start of the range for possible other purposes.
range_len is the number of days in the gapless range.
max_spaces is the maximum of open spaces in the range.
- Both columns are used to exclude impossible rows from the query immediately.
I cast both to smallint ( max. 32768 should be plenty for both) to optimize storage: only 52 bytes per row (incl. heap tuple header and item identifier). Details:
- Configuring PostgreSQL for read performance

Multicolumn index for MV:

CREATE INDEX mv_hotel_mult_idx ON mv_hotel (range_len, max_spaces, day);

Query based on MV

SELECT hotel_id, day, sum_price
FROM  (
   SELECT hotel_id, day, price, spaces
        , sum(price)      OVER w * 2   AS sum_price
        , min(spaces)     OVER w       AS min_spaces
        , count(*)        OVER w       AS day_ct
   FROM   mv_hotel
   WHERE  day BETWEEN '2014-01-01'::date AND '2014-01-31'::date
   AND    range_len >= 4   -- exclude impossible rows
   AND    max_spaces >= 2  -- exclude impossible rows
   WINDOW w AS (PARTITION BY hotel_id, range_start ORDER BY day
                ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) -- adapt to $nights - 1
   ) sub
WHERE  day_ct = 4
AND    min_spaces >= 2
ORDER  BY sum_price, hotel_id, day;
-- LIMIT 1 to get only 1 winner;

This is faster than the query on the table because more rows can be eliminated immediately. Again, the index is essential. Since partitions are gapless here, checking day_ct is enough.

SQL Fiddle demonstrating both.

Repeated use

If you use it a lot, I would create an SQL function and only pass parameters. Or a PL/pgSQL function with dynamic SQL and EXECUTE to allow adapting the frame clause.

Alternative

Range types with date_range to store continuous ranges in a single row might be an alternative - complicated in your case with potential variations on prices or spaces per day.

Postgresql – Comparing the data in two Postgres databases

This question (in some shape or form - no two questions are identical), for example see the threads here and here - I did answer there.

The two most popular (Open Source) tools in this area are Liquibase and Flyaway. There are numerous commercial tools which you can find by Googling.

I would like to echo Paul White's welcome, but just a wee word of warning. Tool recommendations aren't really on-topic here. Check out the tour and also the "help us to help you" blog - both at the bottom left of the page. Also, take a look at the help centre for everything you could ever want to know about dba.se. These forums are a great resource and you'll get the most from them if you take the time to check out and follow the guidelines.

Googling the term "diff tool for postgresql" gives some other ideas. Perhaps a good approach would be to try out a couple (few) of them and if you're having problems or require more specific advice (too broad is also off-topic :-( ) - I got this and this - PostgresQL specific. Best of luck.

Best Answer

Related Solutions

Postgresql – Store millions of rows of denomalized data or some SQL magic