0

I have table that describes a movie release. This table consists of columns:

  • movie_id int
  • release_day int
  • showtime_count int
  • ticket_sold_count int

e.g. (1, 1, 100, 200) tuple describes movie ID 1, on the first day of the movie release when it had 100 showtimes and sold 200 tickets.

I have a movie that is 5 days into the release (e.g. (2,1,100,200), (2,2,150,300), (2,3,100,250), (2,4,150,300), (2,5,100,250)) and I want to find previous releases that have performed similarly up to this day into their release.

I know that I can calculate a distance between two points using <->. But how would I index and search the entire database for a movie where distance for days 1, 2, 3, 4 and 5 is the lowest?

3
  • Do you want to munge showtime_count and ticket_sold_count together into one 10-dimensional point even though they have very different scales? PostgreSQL's "point" built-in type is two-dimensional, for arbitrary dimensional points you probably want the "cube" extension. (Assuming you want Postgresql for this task in the first place.)
    – jjanes
    Commented Sep 26, 2019 at 21:50
  • I am open to suggestions. You are right about different scales. How does cube extension address this and what would recommend if not postgres to use for this task?
    – Gajus
    Commented Sep 27, 2019 at 2:45
  • "cube" offers an indexable euclidean distance operator "<->" in high-dimensional space. It doesn't solve the scaling problem. You could do z-transform, for example, to do that. The problem there is your new movie needs to get transformed the same way to be used in a search, so you need to memorize the averages and standard deviations, not just use them dynamically when you prep the dataset. For data mining, you probably want specialized software for it, it is not really a DBA topic. Some things to look at would be R, Knime, and Pandas.
    – jjanes
    Commented Sep 27, 2019 at 11:44

1 Answer 1

1

Just focusing on the database and ignoring the statistics issues, and assuming the 5 days you use is a design decision and not just ephemeral example, and that we can ignore anything with an incomplete record, then you can do it like this:

Set up with random data:

create table movie as select x as movie_id, y as release_day, floor(random()*1000)::int as showtime_count, floor(random()*100000)::int as ticket_sold_count 
from generate_series(1,1000000) f(x), generate_series(1,6) g(y);

denormalize and index:

create materialized view release as 
    select movie_id, 
           cube(
              array_agg(showtime_count order by release_day)||
              array_agg(ticket_sold_count order by release_day)
            ) as datavector
    from movie where
        release_day <=5 
    group by movie_id 
    having count(release_day)=5 and 
           count(distinct release_day)=5;

create index on release using gist (datavector);

And use:

explain (analyze,buffers) select * from release order by datavector <-> '(230, 16, 178, 507, 366, 8798, 4724, 1736, 5765, 808)' limit 1;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..0.59 rows=1 width=44) (actual time=1.800..1.802 rows=1 loops=1)
   Buffers: shared hit=95
   ->  Index Scan using release_datavector_idx on release  (cost=0.41..176884.39 rows=999999 width=44) (actual time=1.798..1.798 rows=1 loops=1)
         Order By: (datavector <-> '(230, 16, 178, 507, 366, 8798, 4724, 1736, 5765, 808)'::cube)
         Buffers: shared hit=95
 Planning Time: 0.091 ms
 Execution Time: 1.841 ms

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.